Working with your database
How to modify schemas and manage database migrations
Overview
The database schema lives in the @nct/db package and uses Drizzle ORM. All schema changes follow the same workflow:
- Modify the schema files
- Generate a migration
- Apply the migration locally
- Test your changes
- Apply to staging/production
Always test schema changes locally before deploying to production. Database migrations can be destructive and cause data loss if not handled carefully.
Schema files
Database schemas are defined in the packages/db/src/schema/ directory:
Each schema file defines a table using Drizzle's SQLite syntax:
import * as t from "drizzle-orm/sqlite-core"
import {
type AnySQLiteColumn,
sqliteTable as table,
} from "drizzle-orm/sqlite-core"
import { timestamps } from "../utils"
export const users = table(
"users",
{
id: t.int().primaryKey({ autoIncrement: true }),
firstName: t.text("first_name"),
lastName: t.text("last_name"),
email: t.text().notNull().unique(),
invitee: t.int().references((): AnySQLiteColumn => users.id),
role: t.text().$type<"guest" | "user" | "admin">().default("guest"),
...timestamps,
},
(tbl) => [t.uniqueIndex("email_idx").on(tbl.email)]
)Working with TypeScript types
Drizzle provides a type helper via the Type API. This ensures your types are always in sync with the table schema(s).
You need to export them for use in the application. When you add a new table or modify a schema:
Create a types file
import type { InferInsertModel, InferSelectModel } from "drizzle-orm"
import type { users } from "../schema/users.sql"
export type SelectUser = InferSelectModel<typeof users>
export type InsertUser = InferInsertModel<typeof users>
export const USER_ROLES = ["guest", "user", "admin"] as const
export type UserRole = (typeof USER_ROLES)[number]We use the InferInsertModel and InferSelectModel to create SelectUser and InsertUser types.
Define custom types (Optional)
export const USER_ROLES = ["guest", "user", "admin"] as const
export type UserRole = (typeof USER_ROLES)[number]In the users schema we defined a role with three types. This is additionally created and exported for use in the application.
Use the types
"use server"
import { cache } from "react"
import { type SelectUser, users } from "@nct/db"
import { getDbAsync } from "@/lib/db"
export const getUsers = cache(async (): Promise<SelectUser[]> => {
const db = await getDbAsync()
return await db.select().from(users)
})This now adds type-hinting to the getUsers function when used in the application.
Development
The @nct/db package uses tsup to build and bundle the package. If you have run the turbo run dev command from the project root, this automatically activates the dev command in the database package.
If you have not, you can either run dev mode manually, or build instead:
// Run tsup in watch mode (acts like Next.js HMR)
turbo run dev
// Build the db package
npm run buildYou will always want to ensure you have run the build command before pushing to production, however, due to the nature of Turborepo, this is automatically done for you.
Making schema changes
Let's walk through common schema modifications.
A limitation of SQLite is that you cannot change a column. Under-the-hood, Drizzle with replicate your table, modify it accordingly, and drop the original which can cause data loss.
If you need to modify a column (i.e. type), it is safest to:
- Add new column with default value
- Run migration
- Copy data from old to new column (custom SQL)
- Remove old column
- Run another migration
Adding a new column
Update the schema file
export const users = table("users", {
id: t.int().primaryKey({ autoIncrement: true }),
firstName: t.text("first_name"),
lastName: t.text("last_name"),
email: t.text().notNull().unique(),
role: t.text().$type<"guest" | "user" | "admin">().default("guest"),
// Add new column
phoneNumber: t.text("phone_number"),
})Generate the migration
npm run db:generateThis creates a new migration file in packages/db/drizzle/migrations/ with a timestamp:
drizzle/migrations/
└── 0001_add_phone_number.sqlReview the generated SQL (Optional)
ALTER TABLE users ADD COLUMN phone_number TEXT;Build the db package
npm run buildApply migrations
Change to the app directory before running:
// apply migrations locally
npm run db:migrate:local
// apply to production database
npm run db:migrate:prodCreating a new table
Create a new schema file
export const comments = table("comments", {
id: t.int().primaryKey({ autoIncrement: true }),
postId: t.int("post_id").notNull().references(() => posts.id),
userId: t.int("user_id").notNull().references(() => users.id),
content: t.text().notNull(),
createdAt: t.text("created_at").notNull(),
})Add the types
import type { InferInsertModel, InferSelectModel } from "drizzle-orm"
import type { comments } from "../schema/comments.sql"
export type SelectComment = InferSelectModel<typeof comments>
export type InsertComment = InferInsertModel<typeof comments>Export from schema index
import type { D1Database } from "@cloudflare/workers-types"
import { drizzle } from "drizzle-orm/d1"
export * from "./schema/posts.sql"
export * from "./schema/users.sql"
export * from "./schema/comments.sql"
export * from "./types/posts.types"
export * from "./types/users.types"
import { posts } from "./schema/posts.sql"
import { users } from "./schema/users.sql"
import { comments } from "./schema/comments.sql"
export const schema = {
users,
posts,
comments,
} as const
// Connection factory - called in Workers
export function createDrizzleD1(d1: D1Database) {
return drizzle(d1, { schema })
}
export type Database = ReturnType<typeof createDrizzleD1>Generate migrations
npm run db:generateBuild the db package
npm run buildApply migrations
Change to the app directory before running:
// apply migrations locally
npm run db:migrate:local
// apply to production database
npm run db:migrate:prodFAQs
How is this guide?
Last updated on