Database Migrations

Migrations track and apply schema changes to your database in a controlled, version-controlled manner. We can use Drizzle Kit to generate migration files for us as a result of schema changes (changing a property, adding a table, etc.).

The Drizzle team suggests several ways to manage migrations which you should read up on if you want to know more.

Practical Example: adding a new table

Let's say we want to add a new table to the database called projects. We can do this by adding the following to the packages/database/src/schema/schema.ts file:

import { sql } from 'drizzle-orm';
import { pgTable, text } from 'drizzle-orm/pg-core';
import { organizations } from './core';
export * from './core';
export const projects = pgTable('projects', {
  id: text('id')
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  name: text('name').notNull(),
  organizationId: text('organization_id')
    .notNull()
    .references(() => organizations.id, { onDelete: 'cascade' }),
});

Now, we can generate a migration file by running the following command:

pnpm --filter "@kit/database" drizzle:generate

This will create a migration file in the packages/database/src/schema/*.sql directory. Drizzle generates the migration file by diffing the current schema with the new schema, and will update the snapshots file to reflect the changes.

If you open the SQL migration file, you will see the following:

-- migrations/0001_add_projects.sql
CREATE TABLE "projects" (
	"id" text PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"name" text NOT NULL,
	"organization_id" text NOT NULL
);
--> statement-breakpoint
ALTER TABLE "projects" ADD CONSTRAINT "projects_organization_id_organizations_id_fk" FOREIGN KEY ("organization_id") REFERENCES "public"."organizations"("id") ON DELETE cascade ON UPDATE no action;

Now, we can apply the migration to the database by running the following command:

pnpm --filter "@kit/database" drizzle:migrate

This will apply the migration to the database.

We can now import the projects table in our codebase and use it to query the database.

import { projects } from '@kit/database';

In the next section, we'll learn how to use the Drizzle client to interact with the database and the schema that we defined.

Next: Client →