Database

NextShip uses PostgreSQL with Drizzle ORM for type-safe database access.

Why Drizzle?

Lightweight - No Rust engine like Prisma
SQL-like - Familiar syntax for SQL developers
Type-safe - Full TypeScript inference
Edge-ready - Works in serverless environments

Configuration

Database client is configured in src/lib/db/index.ts:

import { drizzle } from "drizzle-orm/neon-http";
import { neon } from "@neondatabase/serverless";
 
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle(sql);

Defining Tables

Tables are defined in src/lib/db/schema.ts:

import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core";
 
export const users = pgTable("users", {
  id: text("id").primaryKey(),
  email: text("email").notNull().unique(),
  name: text("name"),
  emailVerified: boolean("email_verified").default(false),
  image: text("image"),
  role: text("role", { enum: ["user", "admin"] }).default("user"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});
 
export const posts = pgTable("posts", {
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  title: text("title").notNull(),
  content: text("content"),
  authorId: text("author_id").references(() => users.id),
  published: boolean("published").default(false),
  createdAt: timestamp("created_at").defaultNow(),
});

Queries

Select

// Get all users
const allUsers = await db.select().from(users);
 
// With conditions
const admins = await db
  .select()
  .from(users)
  .where(eq(users.role, "admin"));
 
// Specific columns
const emails = await db
  .select({ email: users.email })
  .from(users);

Insert

await db.insert(users).values({
  id: crypto.randomUUID(),
  email: "user@example.com",
  name: "John Doe",
});
 
// Insert multiple
await db.insert(users).values([
  { id: "1", email: "a@example.com" },
  { id: "2", email: "b@example.com" },
]);

Update

await db
  .update(users)
  .set({ name: "Jane Doe" })
  .where(eq(users.id, userId));

Delete

await db
  .delete(users)
  .where(eq(users.id, userId));

Joins

const postsWithAuthors = await db
  .select({
    post: posts,
    author: users,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

Migrations

Push (Development)

Directly pushes schema changes to the database:

pnpm db:push

Generate Migrations (Production)

# Generate migration files
pnpm db:generate
 
# Apply migrations
pnpm db:migrate

Drizzle Studio

Visual database browser:

pnpm db:studio

Opens at https://local.drizzle.studio

Neon Branching

Neon supports database branching for development:

Create a branch in Neon console
Use the branch connection string in .env.local
Test changes safely before merging

Best Practices

Use $defaultFn for auto-generated IDs
Always add createdAt and updatedAt timestamps
Use references for foreign keys
Keep schema file organized by domain