Database

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

Setup

1. Install Drizzle

pnpm add drizzle-orm

2. Configure D1 Binding

Add a D1 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 D1 binding typed.

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 type { Env } from "./env.js";
import * as schema from "./schema.js";
import { users } from "./schema.js";

const worker = ploy<Env>()
	.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 D1 Access

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

.get("/init", {
  response: z.object({ success: z.boolean() })
}, async (ctx) => {
  // Raw SQL execution
  await ctx.env.DB.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL
    )
  `);
  return { success: true };
})

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 withRawD1

For raw D1 access as state, use withRawD1:

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

const worker = ploy<Env>()
  .state(withRawD1("DB"))
  .get("/users", {...}, async (ctx) => {
    // ctx.state.d1.query, ctx.state.d1.first, ctx.state.d1.execute
    const users = await ctx.state.d1.query("SELECT * FROM users");
    return { users };
  })
  .build();

Raw D1 Interface

interface RawD1 {
	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

import { ploy, withDrizzle, z } from "@meetploy/start";
import { eq } from "drizzle-orm";
import type { Env } from "./env.js";
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<Env>()
	.state(withDrizzle("DB", schema))

	// Initialize database
	.get(
		"/init",
		{
			response: z.object({ success: z.boolean() }),
		},
		async (ctx) => {
			await ctx.env.DB.exec(`
      CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE NOT NULL,
        created_at INTEGER
      )
    `);
			return { success: true };
		},
	)

	// 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 - Track schema changes with Drizzle migrations
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