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 a schema.ts file. 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
   • name: a unique string that can not be null

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

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

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 of 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/gator
• Linux (password from last lesson, postgres user): postgres://postgres:postgres@localhost:5432/gator

Test your connection string by running psql, for example:

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

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

4. Add the connection string to the .gatorconfig.json file instead of the example string we used earlier. When using it with psql, you'll use it in the format we just used. However, here in the config file it 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.

5. 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/<path_to_schema>",
  out: "src/<path_to_generated_files>",
  dialect: "postgresql",
  dbCredentials: {
    url: "your_connection_string",
  },
});

You can use the readConfig function to read the connection string from the .gatorconfig.json file instead of hard-coding it.

1. 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

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

npx drizzle-kit migrate

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"
  }
}