Schema

Creating a schema for your data is one of the primary tasks when building a new application.

You can find the schema of each table in packages/db/src/db/schema directory. The schema is basically organized by entity and each file is a separate table.

Defining schema

The schema is defined using SQL-like utilities from drizzle-orm.

It supports all the SQL features, such as enums, indexes, foreign keys, extensions and more.

Example

Let's take a look at the customers table, where we store information about our customers.

export const customers = pgTable("customers", {
  userId: uuid("userId")
    .references(() => usersTable.id, {
      onDelete: "cascade",
    })
    .primaryKey(),
  customerId: text("customerId").notNull(),
  status: billingStatusEnum("status"),
  plan: pricingPlanTypeEnum("plan"),
  createdAt: timestamp("createdAt").notNull().defaultNow(),
  updatedAt: timestamp("updatedAt")
    .notNull()
    .$onUpdate(() => new Date()),
});
 
export type InsertCustomer = typeof customers.$inferInsert;
export type SelectCustomer = typeof customers.$inferSelect;

We're using a few native SQL utilities here, such as:

• pgTable - a table definition.
• timestamp - a timestamp.
• text - a text.
• uuid - a unique identifier which is used as a primary key.
• unique - a unique constraint.
• references - a reference to another table.

What's more, Drizzle gives us the ability to export the TypeScript types for the table, which we can reuse e.g. for the API calls.

Also, we can use the drizzle extension drizzle-zod to generate the Zod schemas for the table.

import { createInsertSchema, createSelectSchema } from "drizzle-zod";
 
export const insertCustomerSchema = createInsertSchema(customers);
export const selectCustomerSchema = createSelectSchema(customers);

Then we can use the generated schemas in our API handlers and frontend forms to validate the data.