Drizzle ORM

A migration is just a set of changes to your database table. You can have as many migrations as needed as your requirements change over time. For example, one migration might create a new table, one might delete a column, and one might add 2 new columns.

An "up" migration moves the state of the database from its current schema to the schema that you want. So, to get a "blank" database to the state it needs to be ready to run your application, you run all the "up" migrations.

If something breaks, you can run one of the "down" migrations to revert the database to a previous state. "Down" migrations are also used if you need to reset a local testing database to a known state.

1. Install drizzle, postgres and drizzle-kit. Drizzle Kit is a CLI tool that will help us run our migrations and the Postgres driver allows drizzle and our program to talk to the database.

npm i drizzle-orm postgres
npm i -D drizzle-kit

2. Create src/db/schema.ts. We'll be defining all of our tables in this file. For now, just create a users table with the following fields:

   • id: a UUID that will serve as the primary key

   • created_at: a TIMESTAMP that can not be null

   • updated_at: a TIMESTAMP that can not be null

   • email: VARCHAR(256) that can not be null and must be unique.

import { pgTable, timestamp, varchar, uuid } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at")
    .notNull()
    .defaultNow()
    .$onUpdate(() => new Date()),
  email: varchar("email", { length: 256 }).unique().notNull(),
});

export type NewUser = typeof users.$inferInsert;

The $onUpdate function sets the updatedAt field to a default value whenever the row is updated.

3. Get your connection string. A connection string is just a URL with all the information needed to connect to a database. The format is:

protocol://username:password@host:port/database

Here are examples:

• macOS (no password, your username): postgres://wagslane:@localhost:5432/chirpy

• Linux (password from last lesson, postgres user): postgres://postgres:postgres@localhost:5432/chirpy

Test your connection string by running psql, for example:

psql "postgres://wagslane:@localhost:5432/chirpy"

It should connect you to the chirpy database directly. If it's working, great. exit out of psql and save the connection string.

4. Create a drizzle.config.ts file at the root of your project. This file will contain the configuration for our migrations.

import { defineConfig } from "drizzle-kit";

export default defineConfig({
  schema: "./src/db/schema.ts",
  out: "./src/db/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: "your_connection_string",
  },
});

5. The connections string needs an additional sslmode=disable query string:

protocol://username:password@host:port/database?sslmode=disable

Your application code needs to know to not try to use SSL locally.

6. Run the npx drizzle-kit generate command to generate the migration files based on our current schema state. These files are created in the directory you specified in the out field of drizzle.config.ts.

npx drizzle-kit generate

7. If everything looks like it should, run the migration.

npx drizzle-kit migrate

Run and submit the CLI tests.

• You can add scripts to your package.json to make running drizzle-kit easier. For example:

{
  "scripts": {
    "generate": "drizzle-kit generate",
    "migrate": "drizzle-kit migrate"
  }
}