Creating Database Tables

info

This guide covers table creation using Drizzle ORM. For database setup instructions, see Setting Up PostgreSQL.

Schema Overview

Database tables are defined in src/server/db/schema.ts using Drizzle's schema definition syntax. The schema provides type-safe table definitions that are used for migrations and queries.

Table Structure

Here's the anatomy of a table definition:

import { pgTable, text, timestamp, boolean } from "drizzle-orm/pg-core";
import { createId } from '@paralleldrive/cuid2';

export const posts = pgTable('post', {
    id: text('id').primaryKey().$defaultFn(() => createId()),
    title: text('title').notNull(),
    content: text('content'),
    published: boolean('published').default(false),
    authorId: text('author_id')
        .notNull()
        .references(() => users.id, { onDelete: 'cascade' }),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
});

Column Types

Drizzle supports various PostgreSQL column types:

import { 
    text,          // TEXT
    varchar,       // VARCHAR
    boolean,       // BOOLEAN
    timestamp,     // TIMESTAMP
    integer,       // INTEGER
    serial,        // SERIAL
    json,          // JSON
    jsonb,         // JSONB
    date,          // DATE
    numeric,       // NUMERIC
} from "drizzle-orm/pg-core";

Column Modifiers

Customize columns with modifiers:

// Column constraints
.notNull()        // NOT NULL
.unique()         // UNIQUE
.default(value)   // DEFAULT value
.primaryKey()     // PRIMARY KEY

// Foreign keys
.references(() => otherTable.id, {
    onDelete: 'cascade',  // CASCADE on delete
    onUpdate: 'cascade'   // CASCADE on update
})

Relationships

Define relationships between tables:

// One-to-Many relationship
export const comments = pgTable('comment', {
    id: text('id').primaryKey().$defaultFn(() => createId()),
    postId: text('post_id')
        .notNull()
        .references(() => posts.id, { onDelete: 'cascade' }),
    content: text('content').notNull(),
});

// Many-to-Many relationship
export const postTags = pgTable('post_tag', {
    postId: text('post_id')
        .notNull()
        .references(() => posts.id, { onDelete: 'cascade' }),
    tagId: text('tag_id')
        .notNull()
        .references(() => tags.id, { onDelete: 'cascade' }),
    // Composite primary key
    primaryKey: { columns: [postId, tagId] },
});

Best Practices

1. Naming Conventions

   • Use singular form for table names
   • Use snake_case for column names
   • Use camelCase for table exports

2. Default Values

   • Add createdAt and updatedAt timestamps
   • Use createId() for ID generation
   • Set sensible defaults for boolean flags

3. Data Integrity

   • Add appropriate NOT NULL constraints
   • Define foreign key relationships
   • Use cascading deletes when appropriate

After Creating Tables

After defining new tables:

# Generate migration
pnpm drizzle-kit generate:pg

# Apply migration
pnpm db:push

Schema Organization

Keep related tables in the same section of your schema file, or split into multiple files for larger projects:

// src/server/db/schema/post.ts
export const posts = pgTable('post', {...});
export const comments = pgTable('comment', {...});
export const postTags = pgTable('post_tag', {...});

warning

Remember to:

1. Generate migrations after schema changes
2. Test migrations both up and down
3. Commit migration files to version control