Your First Schema

Now that you understand D1 and Drizzle fundamentals, let’s build a real schema. We’ll create a simple blog data model with Users and Posts tables, learning each concept as we go.

What We’re Building

By the end of this chapter, you’ll have a complete schema with two related tables:

erDiagram
  users ||--o{ posts : "has many"
  users {
      integer id PK
      text name
      text email UK
      integer created_at
  }
  posts {
      integer id PK
      text title
      text content
      integer author_id FK
      integer created_at
  }

This entity relationship diagram shows:

users: Stores user information with a unique email
posts: Stores blog posts, each belonging to one user
Relationship: One user can have many posts (one-to-many)

Setting Up the Schema File

All Drizzle schemas live in a TypeScript file. In our starter project, this file is located at:

Create this file if it doesn’t exist, and add the required imports:

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

Let’s break down these imports:

Import	Purpose
`sqliteTable`	Function to define a new table
`text`	Column type for strings
`integer`	Column type for numbers (including IDs and timestamps)

Step 1: Define the Users Table

Let’s build the users table piece by piece, understanding each part.

Start with the Table Structure

export const users = sqliteTable("users", {
  // columns go here
});

The sqliteTable function takes two arguments:

Table name: "users" — the name in your database
Column definitions: An object describing each column

Add the Primary Key

Every table needs a unique identifier. We’ll use an auto-incrementing integer:

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
});

Breaking this down:

integer("id") — Creates an INTEGER column named “id”
.primaryKey() — Marks this as the primary key
{ autoIncrement: true } — SQLite assigns IDs automatically (1, 2, 3…)

Add the Name Column

Users need a name, and it should be required:

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
});

The .notNull() modifier means this field is required — INSERT queries without a name will fail.

Add the Email Column

Email should be required and unique (no two users can share an email):

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
});

The .unique() modifier creates a database constraint. Attempting to insert a duplicate email will throw an error.

Add the Timestamp Column

Track when users were created using a timestamp:

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

This timestamp column has several parts:

Part	Purpose
`integer("created_at", { mode: "timestamp" })`	Store as INTEGER, but Drizzle converts to/from JavaScript `Date`
`.notNull()`	Timestamp is required
`.$defaultFn(() => new Date())`	Automatically set to current time on INSERT

Complete Users Table

Here’s the finished users table:

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

Step 2: Define the Posts Table

Now let’s add the posts table with a relationship to users.

Basic Post Structure

Start with the essential columns:

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
});

Posts have:

id: Auto-incrementing primary key (same pattern as users)
title: Required text for the post title
content: Required text for the post body

Add the Foreign Key Relationship

Here’s where it gets interesting. Each post belongs to a user, so we need a foreign key:

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id),
});

The foreign key definition:

integer("author_id") — Column storing the user’s ID
.notNull() — Every post must have an author
.references(() => users.id) — Links to the id column in the users table

Why use an arrow function? The () => users.id syntax allows referencing users even though it’s defined above. This avoids circular dependency issues in more complex schemas.

Add the Timestamp

Complete the posts table with a creation timestamp:

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

Complete Schema

Here’s the entire schema file with both tables:

import { sqliteTable, text, integer } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  email: text("email").notNull().unique(),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

export const posts = sqliteTable("posts", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  title: text("title").notNull(),
  content: text("content").notNull(),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id),
  createdAt: integer("created_at", { mode: "timestamp" })
    .notNull()
    .$defaultFn(() => new Date()),
});

Understanding the Type System

One of Drizzle’s strengths is automatic type inference. When you define a table, TypeScript knows the exact shape of your data.

Inferred Types

Drizzle generates types for insert and select operations:

// TypeScript knows the shape of a user row
type User = {
  id: number;
  name: string;
  email: string;
  createdAt: Date;
};

// TypeScript knows which fields are required for insert
type NewUser = {
  name: string;        // required
  email: string;       // required
  id?: number;         // optional (auto-generated)
  createdAt?: Date;    // optional (has default)
};

You don’t need to write these types — Drizzle infers them automatically from your schema.

Type Safety in Action

With these inferred types, TypeScript catches errors at compile time:

// ✅ Correct: all required fields provided
await db.insert(users).values({
  name: "Alice",
  email: "alice@example.com",
});

// ❌ TypeScript Error: missing required field 'email'
await db.insert(users).values({
  name: "Bob",
});

// ❌ TypeScript Error: 'title' is not a field on users
await db.insert(users).values({
  name: "Carol",
  email: "carol@example.com",
  title: "Hello",  // Error: no such column
});

Generating Migrations

With your schema defined, the next step is generating SQL migration files. Drizzle Kit handles this automatically.

Run the Generate Command

pnpm drizzle-kit generate

This command:

Reads your schema.ts file
Compares it to any existing migrations
Generates SQL files for the differences

Expected Output

drizzle-kit: v0.30.0
No config path provided, using default 'drizzle.config.ts'
Reading config file '/path/to/your/project/drizzle.config.ts'
2 tables
users 4 columns 1 indexes 0 fks
posts 5 columns 0 indexes 1 fks

[✓] Your SQL migration file ➜ migrations/0000_initial.sql

What’s in the Migration File?

The generated SQL looks something like this:

CREATE TABLE `users` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `created_at` integer NOT NULL
);

CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);

CREATE TABLE `posts` (
  `id` integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  `title` text NOT NULL,
  `content` text NOT NULL,
  `author_id` integer NOT NULL,
  `created_at` integer NOT NULL,
  FOREIGN KEY (`author_id`) REFERENCES `users`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);

Notice how Drizzle:

Created both tables with correct column types
Added the unique index on email
Set up the foreign key constraint on author_id

Apply Migrations Locally

To apply migrations to your local database:

pnpm wrangler d1 migrations apply YOUR_DATABASE_NAME --local

Replace YOUR_DATABASE_NAME with the database name from your wrangler.toml file.

Apply Migrations to Remote

For staging or production deployment:

pnpm wrangler d1 migrations apply YOUR_DATABASE_NAME --remote

Common Patterns and Variations

Here are some variations you might need for your own schemas:

Optional Columns

Remove .notNull() to make a column nullable:

content: text("content"),  // content can be null

Default Values

Use .default() for static defaults:

status: text("status").default("draft"),

Multiple Foreign Keys

A table can reference multiple other tables:

export const comments = sqliteTable("comments", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  postId: integer("post_id")
    .notNull()
    .references(() => posts.id),
  authorId: integer("author_id")
    .notNull()
    .references(() => users.id),
  content: text("content").notNull(),
});

Self-Referential Foreign Keys

A table can reference itself (e.g., for hierarchical data):

export const categories = sqliteTable("categories", {
  id: integer("id").primaryKey({ autoIncrement: true }),
  name: text("name").notNull(),
  parentId: integer("parent_id").references(() => categories.id),
});

Summary

In this chapter, you learned to:

Create a schema file with proper imports
Define the Users table with primary key, required fields, unique constraints, and defaults
Define the Posts table with a foreign key relationship to Users
Generate migrations using Drizzle Kit
Apply migrations to local and remote databases

Your schema is now ready to use. In the next chapters, you’ll learn how to query this data with full type safety using Drizzle’s query API.