Database Developer Guide

This guide covers working with Drizzle ORM, database migrations, and Row-Level Security (RLS) in the Refresh App Web.

Overview

Database: Neon Postgres (serverless) ORM: Drizzle ORM 0.44 Migration Tool: Drizzle Kit Connection: HTTP-based (no persistent connections)

Database Configuration

Location: drizzle.config.ts

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
	schema: './src/lib/server/db/schema',
	dbCredentials: { url: process.env.WRITE_DATABASE_URL },
	dialect: 'postgresql',
	entities: {
		roles: {
			provider: 'neon',
			include: ['refresh_ai_db_admin']
		}
	}
});

Schema Organization

Schemas are organized by domain in src/lib/server/db/schema/. Each file represents a logical grouping of related tables. For the complete list of tables and their relationships, see Architecture: Database Architecture - Core Tables.

Creating a Table

Example schema file:

// src/lib/server/db/schema/projects.ts
import { pgTable, text, timestamp, boolean } from 'drizzle-orm/pg-core';
import { tenants } from './tenants';
import { users } from './users';
import { sql } from 'drizzle-orm';

export const projects = pgTable('projects', {
	id: text('id')
		.primaryKey()
		.default(sql`gen_random_uuid()`),
	tenantId: text('tenant_id')
		.notNull()
		.references(() => tenants.id),
	name: text('name').notNull(),
	description: text('description'),
	ownerId: text('owner_id')
		.notNull()
		.references(() => users.id),
	active: boolean('active').default(true),
	createdAt: timestamp('created_at').defaultNow(),
	updatedAt: timestamp('updated_at').defaultNow()
});

export type Project = typeof projects.$inferSelect;
export type NewProject = typeof projects.$inferInsert;

Database Client

Two client types based on read/write needs:

// src/lib/server/db/index.ts
import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';

// Write client (uses root credentials)
export const db = drizzle(neon(WRITE_DATABASE_URL), { schema });

// Read client with JWT (enforces RLS)
export function createReadDbClient(event: RequestEvent, jwt: string) {
	const sql = neon(READ_DATABASE_URL, {
		authToken: async () => jwt
	});
	return drizzle(sql, { schema });
}

Querying

Basic Queries

import { db } from '$lib/server/db';
import { users } from '$lib/server/db/schema';
import { eq } from 'drizzle-orm';

// Find one
const user = await db.query.users.findFirst({
	where: eq(users.id, userId)
});

// Find many
const allUsers = await db.query.users.findMany();

// Find with conditions
const activeUsers = await db.query.users.findMany({
	where: eq(users.active, true)
});

Relational Queries

// Define relations in schema
export const usersRelations = relations(users, ({ many }) => ({
	tenants: many(tenantUsers)
}));

// Query with relations
const userWithTenants = await db.query.users.findFirst({
	where: eq(users.id, userId),
	with: {
		tenants: {
			with: {
				tenant: true
			}
		}
	}
});

Complex Filters

import { and, or, eq, gt, like } from 'drizzle-orm';

const results = await db.query.projects.findMany({
	where: and(
		eq(projects.tenantId, tenantId),
		or(like(projects.name, '%search%'), like(projects.description, '%search%')),
		gt(projects.createdAt, startDate)
	)
});

Mutations

Insert

// Single insert
const [newProject] = await db
	.insert(projects)
	.values({
		tenantId: 'tenant-1',
		name: 'New Project',
		ownerId: 'user-1'
	})
	.returning();

// Multiple insert
await db.insert(projects).values([
	{ tenantId: 'tenant-1', name: 'Project 1', ownerId: 'user-1' },
	{ tenantId: 'tenant-1', name: 'Project 2', ownerId: 'user-2' }
]);

Update

await db
	.update(projects)
	.set({ name: 'Updated Name', updatedAt: new Date() })
	.where(eq(projects.id, projectId));

Delete

await db.delete(projects).where(eq(projects.id, projectId));

Row-Level Security (RLS)

Enabling RLS

-- In migration file
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON projects
  USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO authenticated;

Using RLS in Code

export const load: PageServerLoad = async ({ locals }) => {
	const session = await locals.auth();
	const jwt = session.user.neonJwt;

	// Create client with JWT
	const dbClient = createReadDbClient(event, jwt);

	// Query automatically filtered by tenant_id
	const projects = await dbClient.query.projects.findMany();

	// Only returns projects for session.user.activeTenant.id
	return { projects };
};

Migrations

Creating Migrations

1. Edit schema file (e.g., add new column)

2. Generate migration:

pnpm db:generate

1. Review generated SQL in drizzle/migrations/

2. Apply migration:

pnpm db:migrate

Custom Migrations

For complex changes, create custom migration:

pnpm db:generate:custom my_migration_name

Edit the generated file:

-- drizzle/migrations/0012_my_migration.sql

-- Add custom logic
CREATE INDEX idx_projects_tenant ON projects(tenant_id);

-- Add RLS policy
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_policy ON projects
  USING (tenant_id = current_setting('request.jwt.claims', true)::json->>'tenant_id');

Best Practices

1. Always use RLS for tenant data - Prevents data leaks

2. Use read client in load functions - Enforces RLS

3. Use write client sparingly - Only for system operations

4. Index foreign keys - Improves query performance

5. Add timestamps - Track created_at and updated_at

Troubleshooting

RLS Denying Access

Problem: Queries return no results

Solution:

1. Verify JWT contains correct tenant_id

2. Check RLS policy is enabled

3. Ensure user has granted permissions

Migration Conflicts

Problem: Migration fails due to conflicts

Solution:

1. Review migration SQL

2. Manually fix conflicts

3. Re-run migration

Related Documentation

• Architecture: Database Architecture

• Architecture: Multi-Tenancy

• Authentication Guide

---

Last updated: October 2025