Database

Ploy Start provides built-in Drizzle ORM integration for databases, giving you type-safe database queries with automatic schema inference.

Setup

1. Install Drizzle

pnpm add drizzle-orm

2. Configure DB Binding

Add a database binding to your ploy.yaml:

ploy.yaml

kind: worker
build: pnpm build
out: dist
db:
  DB: default

3. Generate Types

Run the Ploy CLI to generate environment types:

pnpm types

This creates env.d.ts with your DB binding typed.

4. Add Migrations

Create SQL migrations in migrations/. Ploy runs them in filename order and tracks applied files in ploy_internal_db_migrations inside the same database.

For a single DB binding:

migrations/

migrations/
└── 20260403171410_init/
    ├── migration.sql
    └── snapshot.json

Only .sql files are executed, so sibling files such as snapshot.json are ignored.

For multiple DB bindings, scope migrations by binding name:

migrations/

migrations/
├── DB/
│   └── 001_create_users.sql
└── ANALYTICS_DB/
    └── 20260403171410_init/
        └── migration.sql

Production deploys on the default branch apply pending migrations before upload and log them in the build output. During local development, ploy dev applies project migrations on startup.

Schema Definition

Define your database schema using Drizzle:

src/schema.ts

import { integer, sqliteTable, text } 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" }).$defaultFn(
		() => new Date(),
	),
});

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

Using withDrizzle

The withDrizzle state factory adds a typed db instance to your handler context:

src/index.ts

import { ploy, withDrizzle, z } from "@meetploy/start";
import { eq } from "drizzle-orm";
import * as schema from "./schema.js";
import { users } from "./schema.js";

const worker = ploy<PloyEnv>()
	.state(withDrizzle("DB", schema))
	.get(
		"/users",
		{
			response: z.object({
				users: z.array(
					z.object({
						id: z.number(),
						name: z.string(),
						email: z.string(),
					}),
				),
			}),
		},
		async (ctx) => {
			// ctx.state.db is typed with your schema
			const allUsers = await ctx.state.db.select().from(users);
			return { users: allUsers };
		},
	)
	.build();

export default worker;

The withDrizzle function takes two arguments: 1. The binding name (must match your ploy.yaml db key) 2. Your schema object (optional, but recommended for typed queries)

Query Examples

Select All

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

Select with Where

import { eq } from "drizzle-orm";

const user = await ctx.state.db
	.select()
	.from(users)
	.where(eq(users.id, 1))
	.limit(1);

Select Specific Columns

const userNames = await ctx.state.db.select({ name: users.name }).from(users);

Insert

await ctx.state.db.insert(users).values({
	name: "Alice",
	email: "alice@example.com",
});

Update

await ctx.state.db
	.update(users)
	.set({ name: "Alice Smith" })
	.where(eq(users.id, 1));

Delete

await ctx.state.db.delete(users).where(eq(users.id, 1));

Raw DB Access

For operations not supported by Drizzle, access the DB directly via ctx.env:

const result = await ctx.env.DB.exec(`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
  PRAGMA optimize;
`);

Prepared Statements

const result = await ctx.env.DB.prepare("SELECT * FROM users WHERE id = ?")
	.bind(userId)
	.first();

Batch Operations

const results = await ctx.env.DB.batch([
	ctx.env.DB.prepare("INSERT INTO users (name, email) VALUES (?, ?)").bind(
		"Alice",
		"alice@example.com",
	),
	ctx.env.DB.prepare("INSERT INTO users (name, email) VALUES (?, ?)").bind(
		"Bob",
		"bob@example.com",
	),
]);

Using withRawDB

For raw DB access as state, use withRawDB:

import { ploy, withRawDB } from "@meetploy/start";

const worker = ploy<PloyEnv>()
  .state(withRawDB("DB"))
  .get("/users", {...}, async (ctx) => {
    const users = await ctx.state.rawDb.query("SELECT * FROM users");
    return { users };
  })
  .build();

Raw DB Interface

interface RawDBHelpers {
	query<T>(sql: string, ...params: unknown[]): Promise<T[]>;
	first<T>(sql: string, ...params: unknown[]): Promise<T | null>;
	execute(sql: string, ...params: unknown[]): Promise<void>;
	batch(
		statements: { sql: string; params?: unknown[] }[],
	): Promise<unknown[][]>;
}

Full CRUD Example

This example assumes your users table already exists from a migration in migrations/.

import { ploy, withDrizzle, z } from "@meetploy/start";
import { eq } from "drizzle-orm";

import * as schema from "./schema.js";
import { users } from "./schema.js";

const userSchema = z.object({
	id: z.number(),
	name: z.string(),
	email: z.string(),
});

const worker = ploy<PloyEnv>()
	.state(withDrizzle("DB", schema))

	// List users
	.get(
		"/users",
		{
			response: z.object({ users: z.array(userSchema) }),
		},
		async (ctx) => {
			const allUsers = await ctx.state.db.select().from(users);
			return { users: allUsers };
		},
	)

	// Get user by ID
	.get(
		"/users/:id",
		{
			params: z.object({ id: z.string() }),
			response: z.object({ user: userSchema.nullable() }),
		},
		async (ctx) => {
			const [user] = await ctx.state.db
				.select()
				.from(users)
				.where(eq(users.id, parseInt(ctx.params.id)))
				.limit(1);
			return { user: user ?? null };
		},
	)

	// Create user
	.post(
		"/users",
		{
			body: z.object({
				name: z.string(),
				email: z.string().email(),
			}),
			response: z.object({ user: userSchema }),
		},
		async (ctx) => {
			const [user] = await ctx.state.db
				.insert(users)
				.values(ctx.body)
				.returning();
			return { user };
		},
	)

	// Update user
	.put(
		"/users/:id",
		{
			params: z.object({ id: z.string() }),
			body: z.object({
				name: z.string().optional(),
				email: z.string().email().optional(),
			}),
			response: z.object({ user: userSchema }),
		},
		async (ctx) => {
			const [user] = await ctx.state.db
				.update(users)
				.set(ctx.body)
				.where(eq(users.id, parseInt(ctx.params.id)))
				.returning();
			return { user };
		},
	)

	// Delete user
	.delete(
		"/users/:id",
		{
			params: z.object({ id: z.string() }),
			response: z.object({ success: z.boolean() }),
		},
		async (ctx) => {
			await ctx.state.db
				.delete(users)
				.where(eq(users.id, parseInt(ctx.params.id)));
			return { success: true };
		},
	)

	.build();

export default worker;

Best Practices

Use migrations - Keep schema changes in migrations/ instead of request handlers
Index frequently queried columns - Add indexes for better performance
Validate input - Use Zod schemas to validate data before inserting
Handle errors - Wrap database operations in try/catch
Use transactions - Group related operations when needed

Database

On this page