Database schema

Learn how to extend and modify the database schema

It is recommended to learn more about Drizzle before diving into these guides.

Modifing the schema

The database schema is managed by Drizzle ORM in lives in packages/db/src/db.ts. Tables are defined in separate schema files, packages/db/src/[table]/[table].sql.ts.

Adding a new table

Let's add a new database table for a simple todo list.

Go ahead a create a new file in packages/db/src/todos/todos.sql.ts with the following content.

import { varchar } from 'drizzle-orm/pg-core'

import { pgTable } from '../_table'
import { timestamps, workspaceId, userId } from '../utils'

export const todos = pgTable('todos', {
  ...workspaceId,
  userId: userId('user_id'),
  todo: varchar('avatar', { length: 255 }),
  ...timestamps,
})

Let's dissect what's going on here.

We imported pgTable from ../_table, this is a custom table creator that allows you to prefix your tables in order to use the same database for multiple projects.

The workspaceId, is a utility that adds a primary key and workspaceId to the table. userId('user_id') is utility to create columns that contain user ids.

Lastly timestamps will add a created_at and updated_at column to our table.

To use our new todos table with Drizzle ORM we need to import it and add it to our schema.

Open packages/db/src/db.ts, import todos.sql.ts and add it to the schema object.

import * as todos from './todos/todos.sql'

const schema = {
  // other tables...
  todos,
}

That's it, now we can update the database and query the new table.

Migrating changes

For testing purposes you can run yarn db:push to push the changes to your local database.

Use yarn db:migrate to create a new migration.

Learn more about migrations.

Conventions

• Schema file extensions are prefixed with .sql, eg todos.sql.ts. Drizzle Kit will automatically stitch these files together in a single schema.

• ID's use Cuid2. Cuid2 ID's are secure, collision-resistant ids optimized for horizontal scaling and performance.

• Table names are plural, eg todos, workspaces, etc.

Utilities

The database package ships with a set of reusable utilities to help you build consistent schemas.