Acme Inc.

Database tables are organized in src/db/tables/ with each feature having its own table file.

Table Structure

src/db/tables/
├── auth.ts           # Users, sessions, accounts, verifications
├── api-keys.ts       # API key management
├── organizations.ts  # Organizations, members, invitations
├── settings.ts       # User settings
└── index.ts          # Barrel export

Creating a Table

For complete step-by-step instructions, see the New Table Template.

Basic Table Definition

Use the createTable helper to define tables with automatic RLS support:

import { createTable, commonColumns } from "@/db/table-utils";
import { text, boolean, integer } from "drizzle-orm/pg-core";

export const posts = createTable("posts", {
  ...commonColumns,
  title: text().notNull(),
  content: text(),
  published: boolean().default(false),
  views: integer().default(0),
});

Common Columns

Every table includes these standard columns via commonColumns:

Column	Type	Description
`id`	UUID	Primary key (auto-generated)
`createdAt`	Timestamp	Creation timestamp (default: `now()`)
`updatedAt`	Timestamp	Last update timestamp (auto-updated)

These columns are automatically included when you spread ...commonColumns.

Column Types

Drizzle supports all PostgreSQL column types:

Text Types

import { text, varchar, char } from "drizzle-orm/pg-core";

export const myTable = createTable("my_table", {
  name: text().notNull(),
  email: varchar({ length: 255 }),
  code: char({ length: 6 }),
  bio: text(),
});

Numeric Types

import { integer, smallint, bigint, real, doublePrecision, numeric } from "drizzle-orm/pg-core";

export const products = createTable("products", {
  quantity: integer().default(0),
  stock: smallint(),
  views: bigint(),
  price: numeric({ precision: 10, scale: 2 }),
  rating: real(),
});

Boolean

import { boolean } from "drizzle-orm/pg-core";

export const tasks = createTable("tasks", {
  completed: boolean().default(false),
  isPublic: boolean().notNull().default(true),
});

UUID

import { uuid } from "drizzle-orm/pg-core";

export const posts = createTable("posts", {
  userId: uuid()
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  categoryId: uuid(),
});

Timestamps

import { timestamp } from "drizzle-orm/pg-core";

export const events = createTable("events", {
  startsAt: timestamp({ withTimezone: true }),
  endsAt: timestamp({ withTimezone: true }),
  deletedAt: timestamp({ withTimezone: true }), // Soft delete
});

JSON/JSONB

import { json, jsonb } from "drizzle-orm/pg-core";

export const settings = createTable("settings", {
  preferences: jsonb(),
  metadata: json(),
});

Enums

Define enums in src/db/enums.ts:

// src/db/enums.ts
import { pgEnum } from "drizzle-orm/pg-core";

export const userRoleEnum = pgEnum("user_role", ["admin", "user", "guest"]);
export const statusEnum = pgEnum("status", ["active", "inactive", "pending"]);

Use in tables:

import { userRoleEnum, statusEnum } from "@/db/enums";

export const users = createTable("users", {
  role: userRoleEnum("role").default("user"),
  status: statusEnum("status").default("active"),
});

Constraints

Not Null

export const users = createTable("users", {
  email: text().notNull(),
  name: text(), // Nullable
});

Default Values

export const posts = createTable("posts", {
  status: text().default("draft"),
  views: integer().default(0),
  createdAt: timestamp().defaultNow(),
  published: boolean().default(false),
});

Unique

export const users = createTable("users", {
  email: text().notNull().unique(),
  username: text().unique(),
});

Foreign Keys and Relationships

Foreign Keys

Reference other tables with automatic cascade:

import { users } from "@/db/tables";

export const posts = createTable("posts", {
  ...commonColumns,
  title: text().notNull(),
  userId: uuid()
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
});

Options:

onDelete: "cascade" - Delete posts when user is deleted
onDelete: "set null" - Set userId to null when user is deleted
onDelete: "restrict" - Prevent user deletion if posts exist

Relations

Define relations for type-safe joins in a separate relations object:

import { relations } from "drizzle-orm";
import { posts, users } from "./tables";

export const postsRelations = relations(posts, ({ one }) => ({
  author: one(users, {
    fields: [posts.userId],
    references: [users.id],
  }),
}));

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts),
}));

Query with relations:

const postsWithAuthor = await db.query.posts.findMany({
  with: {
    author: true,
  },
});

Indexes

Add indexes for frequently queried columns:

import { index } from "drizzle-orm/pg-core";

export const apiKeys = createTable(
  "apikeys",
  {
    ...commonColumns,
    key: text().notNull(),
    userId: uuid().notNull(),
    enabled: boolean().default(true),
  },
  (t) => [
    index("api_keys_user_id_idx").on(t.userId),
    index("api_keys_key_idx").on(t.key),
    index("api_keys_enabled_idx").on(t.enabled),
  ],
);

Naming Conventions

Consistent naming is critical for maintainability.

Context	Convention	Example
Table names (DB)	`snake_case`	`api_keys`, `user_settings`
Table names (code)	`camelCase`	`apiKeys`, `userSettings`
Column names (DB)	`snake_case`	`user_id`, `created_at`
Column names (code)	`camelCase`	`userId`, `createdAt`
Variable names	`camelCase`	`const myTable`

Drizzle automatically converts between conventions.

Real-World Example

Here's the actual apiKeys table from the project:

import { commonColumns, createTable } from "@/db/table-utils";
import { users } from "@/db/tables";
import { boolean, index, integer, text, timestamp, uuid } from "drizzle-orm/pg-core";

export const apiKeys = createTable(
  "apikeys",
  {
    ...commonColumns,
    name: text(),
    start: text(),
    prefix: text(),
    key: text().notNull(),
    userId: uuid()
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
    refillInterval: integer(),
    refillAmount: integer(),
    lastRefillAt: timestamp({ withTimezone: true }),
    enabled: boolean().notNull().default(true),
    rateLimitEnabled: boolean().notNull().default(false),
    rateLimitTimeWindow: integer(),
    rateLimitMax: integer(),
    requestCount: integer().notNull().default(0),
    remaining: integer(),
    lastRequest: timestamp({ withTimezone: true }),
    expiresAt: timestamp({ withTimezone: true }),
    permissions: text(), // JSON string
    metadata: text(), // JSON string
  },
  (t) => [
    index("api_keys_user_id_idx").on(t.userId),
    index("api_keys_key_idx").on(t.key),
    index("api_keys_enabled_idx").on(t.enabled),
    index("api_keys_expires_at_idx").on(t.expiresAt),
  ],
);

Row Level Security

The createTable helper automatically enables RLS. Define policies inline:

import { pgPolicy } from "drizzle-orm/pg-core";
import { authenticatedRole, isOwner } from "@/db/rls";

export const posts = createTable(
  "posts",
  {
    ...commonColumns,
    userId: uuid().notNull(),
  },
  (t) => [
    // Users can only see their own posts
    pgPolicy("posts-select-own", {
      for: "select",
      to: authenticatedRole,
      using: isOwner(t.userId),
    }),
  ],
);

Best Practices

Follow these guidelines for consistent table definitions.

Always use commonColumns for consistent base columns
Use notNull() explicitly for required fields
Add indexes for columns used in WHERE clauses
Use foreign keys with appropriate cascade behavior
Define relations for type-safe joins
Use enums for fixed sets of values
Add timestamps for audit trails
Consider soft deletes with deletedAt column

Next Steps

Database Operations - CRUD operations with createDrizzleOperations
Migrations - Managing schema changes
New Table Template - Step-by-step table creation guide