Database

Zap.ts uses a modern, type-safe database stack built on Drizzle ORM and PostgreSQL by default.

The setup is designed for flexibility, developer productivity, and end-to-end type safety.

Overview

• Drizzle ORM: Type-safe, SQL-friendly ORM for schema definition and queries.
• Extensible: Easily add new tables, relations, or swap out the database.
• Migrations: Simple, reliable schema migrations with Drizzle CLI.
• PostgreSQL: Production-ready, scalable relational database.
• Type Safety: All schemas and queries are fully typed with TypeScript.

How it works?

1. Schema definition

All database schemas are defined using Drizzle's SQL-like schema syntax in the src/db/schema/ directory.

Example:

// src/db/schema/auth.sql.ts
import { pgTable, serial, varchar, timestamp } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  createdAt: timestamp("created_at").defaultNow(),
});

2. Database client

The Drizzle client is set up in src/db/index.ts and used throughout your app for queries and mutations.

3. Running migrations

Migrations are managed with the Drizzle CLI. You can generate, run, and manage migrations using the following commands:

pnpm db:generate   # Create migration files
pnpm db:migrate    # Run pending migrations
pnpm db:push       # Apply schema changes to the database
pnpm db:studio     # Open Drizzle Studio to inspect your database

See Getting Started > Quick Scripts for more.

4. Querying the database

Use the Drizzle client (db) in your server actions, API routes, or procedures:

// Example: Fetch all users
import { db } from "@/db";
import { users } from "@/db/schema";

const allUsers = await db.select().from(users);

5. Prepare queries and execute them

For performance and security, you can use Drizzle's .prepare() and .execute() methods to optimize and safely reuse queries.

Prepared queries are compiled once and can be executed multiple times with different parameters, reducing overhead and risk of SQL injection.

Example:

// src/zap/db/queries/feedbacks.query.ts
import { eq, sql } from "drizzle-orm";
import { db } from "@/db";
import { feedback } from "@/db/schema";

// Prepare a query to fetch feedback for a user
export const getFeedbackForUserQuery = db
  .select()
  .from(feedback)
  .where(eq(feedback.userId, sql.placeholder("userId")))
  .limit(1)
  .prepare("getFeedbackForUser");

// Execute the prepared query
const feedbackResult = await getFeedbackForUserQuery.execute({ userId: "123" });

So, in short:

• Use .prepare("queryName") to define a reusable, named query.
• Use .execute(params) to run the query with specific parameters.

This pattern is recommended for queries used in API routes, server actions, and oRPC procedures.

Customizing the database

• Add a table: Create a new .sql.ts file in src/db/schema/ and export your table.
• Change database: Update your DATABASE_URL in .env and Drizzle config.
• Edit a table: Update the schema file and generate a migration.

For more, see the Drizzle ORM documentation.

WARNING

You may need to disable your VPN when applying migrations, as VPN connections can sometimes interfere with database connectivity and cause connection timeouts.