Schema Migrations with Drizzle

Drizzle is a TypeScript ORM that supports Postgres, MySQL, and SQLite. It also has a CLI, drizzle-kit, for managing migrations and few other things. This guide will show you how to use Drizzle Kit CLI to manage your schema migrations. We are going to assume that you already have a project set up with your favorite Typescript framework.

Start from example project

You can start by cloning a small example project that we've set up here.

Clone the repo and run npm i to install the dependencies:

git clone https://github.com/niledatabase/niledatabase.git
cd examples/migrations/drizzle
npm i

This will install drizzle-kit, drizzle-orm, dotenv, and pg-node - all of which are needed for this guide. pg-node can be replaced with another postgres client like postgres.js.

To run this example, you'll need a .env file with a DATABASE_URL environment variable set to a postgres database. You can copy the connection string from your Nile database home page.

Configure Drizzle

Drizzle kit is configured via a drizzle.config.ts file, which you can find in the root of the example project.

Here's an example drizzle.config.ts file. You'll need to set the schema field to the path to your schema file, and the out field to the path where you want to store your migrations. The dialect field is the database you're using, in our case it's postgresql, and the dbCredentials field is the credentials to your database. You typically want to use an environment variable for your database credentials.

import { defineConfig } from "drizzle-kit";
import dotenv from "dotenv";

dotenv.config();

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./db/out",
  dialect: "postgresql",
  dbCredentials: {
    url:
      process.env.DATABASE_URL ||
      "postgresql://username:password@db.thenile.dev:5432/db",
  },
});

Code-first Schema management

In code-first schema management, you define your schema as Typescript objects, and then use the Drizzle Kit CLI to generate a migration that you can run against your database to update your database schema accordingly.

Example Schema Definition in Drizzle

The scehma definitions for your application live in src/db/schema.ts (or if you choose a different location, you'll need to update the drizzle.config.ts file). Note that we include the built-in tenants table that Nile automatically provisions for you in your database. While the table is pre-provisioned, we want to create the Drizzle object for it so that we can interact with the table in our application - for example to create a new tenant.

import { sql } from "drizzle-orm";
import {
  pgTable,
  primaryKey,
  uuid,
  text,
  timestamp,
  varchar,
  boolean,
  vector,
} from "drizzle-orm/pg-core";

export const tenants = pgTable("tenants", {
  id: uuid("id")
    .default(sql`gen_random_uuid()`)
    .primaryKey(),
  name: text("name"),
  created: timestamp("created"),
  updated: timestamp("updated"),
  deleted: timestamp("deleted"),
});

export const todos = pgTable(
  "todos",
  {
    id: uuid("id").default(sql`gen_random_uuid()`),
    tenantId: uuid("tenant_id"),
    title: varchar("title", { length: 256 }),
    estimate: varchar("estimate", { length: 256 }),
    embedding: vector("embedding", { dimensions: 768 }),
    complete: boolean("complete"),
  },
  (table) => {
    return {
      pk: primaryKey({ columns: [table.tenantId, table.id] }),
    };
  }
);

Generating a Migration

Now that we have our schema defined, we can generate a migration using Drizzle Kit.

npx drizzle-kit generate

If all goes well, you'll see something like this:

➜  drizzle git:(drizzle_migrations) ✗ npx drizzle-kit generate
...
2 tables
tenants 5 columns 0 indexes 0 fks
todos 6 columns 0 indexes 0 fks

[✓] Your SQL migration file ➜ db/out/0000_absurd_captain_britain.sql 🚀

You can open the generated file in the db/out directory and see the SQL that Drizzle will run against your database to make the changes specified in your schema.

Running Migrations

To run the migration, you can use the Drizzle Kit CLI again.

npx drizzle-kit migrate

If you don't see any errors, you've successfully run your migration.

Seeding and Querying Data

Now that we have our schema defined, we can write some code to insert data into our database and query it.

If you run the example with npx tsx src/index.ts you should see the following output:

New tenant created!
New todo created!
Getting all todos from the database:  [
  {
    id: 'd8896674-a7eb-4405-a4de-4ad6fbd2f5fc',
    tenantId: '01929704-3250-70bf-9568-0a6858dfd4e9',
    title: 'My New Todo',
    estimate: null,
    embedding: null,
    complete: null
  }
]

The code that produces this output can be found in src/index.ts. As you can see, we initialize a connection to our database using the drizzle object and then use the schema objects that we defined in src/db/schema.ts to seed and query our database.

import 'dotenv/config';
import { drizzle } from 'drizzle-orm/node-postgres';
import { todos, tenants } from './db/schema';

const db = drizzle(process.env.DATABASE_URL!);
async function main() {

  const tenant: typeof tenants.$inferInsert = {
    name: 'My New Tenant',
  };

  const tenantId = await db.insert(tenants).values(tenant).returning({ id: tenants.id });

  console.log('New tenant created!')

  const todo: typeof todos.$inferInsert = {
    title: 'My New Todo',
    tenantId: tenantId[0].id,
  };

  await db.insert(todos).values(todo);

  console.log('New todo created!')

  const rows = await db.select().from(todos);
  console.log('Getting all todos from the database: ', rows)
}

main();

This will create a new tenant and a new todo, and then query for all the todos in the database.

Schema Changes

Now lets say that you want to add a new column to the todos table. You would make the following change to your schema:

// ...
    complete: boolean("complete"),
    dueDate: timestamp("due_date"), // new column!
// ...

Now you can generate and run a new migration as follows:

npx drizzle-kit generate
npx drizzle-kit migrate

This will generate a new migration file in the db/out directory, which will look something like this:

ALTER TABLE "todos" ADD COLUMN "due_date" timestamp;

And apply the changes to your database.

Database-first Schema management

Coming soon.