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.