TypebaseTypebase
Database

Schema

Define your database tables using Drizzle ORM.

Last updated on

If your project doesn't need a database, you can delete the typebase/db/ folder entirely and skip this section. Typebase will work just fine with actions that don't touch a database.

Typebase uses Drizzle ORM under the hood for everything database-related: schema definitions, relations, and queries. Schema changes are applied to your database with a push-based workflow (npx typebase-io-cli db dev push). If you run into anything not covered here, the Drizzle documentation has you covered.

New to databases?

A database is where your app stores its data: users, posts, orders, anything that needs to persist between sessions. Think of it as a collection of spreadsheets, where each spreadsheet is a table and each row is a record (e.g. one user, one post).

A schema is the blueprint that describes the shape of those tables: what columns each table has, what type of data each column holds (text, number, date, etc.), and any rules like "this field is required" or "this value must be unique."

Relations describe how tables connect to each other. For example, a user has many posts, and each post belongs to one user. Defining these connections lets you query related data together (e.g. "give me this user and all their posts").

Schema

Your entire database schema lives in a single file: typebase/db/schema.ts. Each table is defined using p.pgTable() from the typebase-io/db package.

Here's the example schema generated by npx typebase-io-cli init. It creates a todos table with four columns: an auto-generated id, a value, a completed flag, and a createdAt timestamp.

typebase/db/schema.ts
import { p } from 'typebase-io/db';

export const todos = p.pgTable('todos', {
  id: p.integer().primaryKey().generatedAlwaysAsIdentity(),
  value: p.varchar({ length: 255 }).notNull(),
  completed: p.boolean().notNull(),
  createdAt: p.timestamp().notNull().defaultNow(),
});

The p object re-exports all of Drizzle's column types and helpers, so you can use any column type Drizzle supports: text, boolean, timestamp, json, serial, and so on. See the Drizzle column types documentation for the full list.

Adding more tables

Define all your tables in the same schema.ts file. For example, adding a users table alongside todos:

typebase/db/schema.ts
import { p } from 'typebase-io/db';

export const users = p.pgTable('users', {
  id: p.integer().primaryKey().generatedAlwaysAsIdentity(),
  name: p.varchar({ length: 255 }).notNull(),
  email: p.varchar({ length: 255 }).notNull().unique(),
});

export const todos = p.pgTable('todos', {
  id: p.integer().primaryKey().generatedAlwaysAsIdentity(),
  value: p.varchar({ length: 255 }).notNull(),
  completed: p.boolean().notNull(),
  createdAt: p.timestamp().notNull().defaultNow(),
  userId: p
    .integer()
    .notNull()
    .references(() => users.id),
});

.references(() => users.id) is what creates the database-level foreign key. relations.ts (covered in the next page) only sets up typed query helpers for joining tables. It does not add constraints in Postgres. Put constraints here; put query ergonomics there.

Common column types

Here are the most commonly used column types:

import { p } from 'typebase-io/db';

p.integer(); // Integer number
p.text(); // Unlimited text
p.varchar({ length: 255 }); // Variable-length text with max length
p.boolean(); // true/false
p.timestamp(); // Date and time
p.json(); // JSON data
p.uuid(); // UUID string
p.serial(); // Auto-incrementing integer
p.real(); // Floating-point number

Common column modifiers:

.primaryKey()                  // Mark as primary key
.notNull()                     // Field is required
.unique()                      // Value must be unique across all rows
.default(value)                // Set a default value
.defaultNow()                  // Default to current timestamp
.generatedAlwaysAsIdentity()   // Auto-generated sequential ID
.references(() => otherTable.id)  // Foreign key reference

For the full list, see the Drizzle column types documentation.

The p and q objects

Typebase re-exports Drizzle ORM through two objects from the typebase-io/db package:

ObjectSourceUse for
pdrizzle-orm/pg-coreDefining tables and columns (p.pgTable, p.integer, p.varchar, etc.)
qdrizzle-ormQuery helpers and relation definitions (q.eq, q.and, q.defineRelations, etc.)

You use p in your schema file and q in your relations file and action handlers.

Generated types

When you run npx typebase-io-cli codegen or npx typebase-io-cli init, Typebase generates a _generated/db.d.ts file with TypeScript types inferred from your schema:

// ⚠️ AUTO-GENERATED FILE — DO NOT EDIT

import type { InferDB } from 'typebase-io/db';
import type * as schema from '../db/schema.ts';

export type DB = InferDB<typeof schema>;

In most cases you don't need to use this file directly. The types flow through the action builder and client automatically. But if you ever need the database types explicitly (for example, to type a helper function), you can import DB from it:

import type { DB } from './_generated/db';

type Todo = DB['todos']['select'];
type NewTodo = DB['todos']['insert'];

Next steps

Now that you know how to define tables, learn how to set up relations between them.

On this page

Schema
Adding more tables
Common column types
The p and q objects
Generated types
Next steps