CollegeDB

Universal Database Horizontal Sharding Router

A TypeScript library for horizontal scaling of SQL databases. CollegeDB splits a single logical table across many database instances by primary key, and routes each query to the instance that owns its key.

SQL backends: Cloudflare D1, PostgreSQL, MySQL, MariaDB, SQLite, and any Drizzle ORM instance over them. Key mappings live in Cloudflare Workers KV, Redis, Valkey, or NuxtHub KV. Runs on Cloudflare Workers, Node, and Bun.

Table of Contents

• Why CollegeDB
• Features
• Getting Started
• Benchmark Suite
• Provider Adapters
• NuxtHub + Drizzle Recipes
• Sandbox Benchmarks (Docker Compose)
• In-Memory Providers for Testing & Development
• Basic Usage
• Sharding Strategies
• Auto-Generated Primary Keys
• Utility Helpers
• Multi-Key Shard Mappings
• Drop-in Replacement for Existing Databases
• Troubleshooting
• Cross-Shard Pagination Behavior
• Query Guidance
• API Reference
• Architecture
• Cloudflare Setup
• Monitoring and Maintenance
• Performance Analysis
• Advanced Configuration
• Quick Reference
• Contributing
• License

Why CollegeDB

CollegeDB implements data distribution where a single logical table is physically stored across multiple D1 databases:

env.db-east (Shard 1)
┌────────────────────────────────────────────┐
│ table users: [user-1, user-3, user-5, ...] │
│ table posts: [post-2, post-7, post-9, ...] │
└────────────────────────────────────────────┘

env.db-west (Shard 2)
┌────────────────────────────────────────────┐
│ table users: [user-2, user-4, user-6, ...] │
│ table posts: [post-1, post-3, post-8, ...] │
└────────────────────────────────────────────┘

env.db-central (Shard 3)
┌────────────────────────────────────────────┐
│ table users: [user-7, user-8, user-9, ...] │
│ table posts: [post-4, post-5, post-6, ...] │
└────────────────────────────────────────────┘
Copy

This allows you to:

• Break through D1's single database limits by spreading data across many databases
• Improve query performance by reducing data per database instance
• Scale geographically by placing shards in different regions
• Increase write throughput by parallelizing across multiple database instances

Features

• Automatic query routing (primary key to shard mapping)
• Routing straight from the statement via query / queryFirst / queryAll, with no separate key argument
• Provider adapters for Redis/Valkey/NuxtHub/Workers KV plus PostgreSQL/MySQL/SQLite SQL
• Drizzle interop through existing SQL providers (createPostgreSQLProvider, createMySQLProvider, createSQLiteProvider)
• Auto-allocated generated-id inserts via insert() and direct-shard inserts via insertShard() for AUTOINCREMENT / RETURNING workflows
• Object-shaped CRUD helpers (insertInto, patch, updateRow, deleteById, upsert) so you never hand-align columns and bindings
• Cross-shard-safe id generation (nextId), one-call setup from a Worker env (initializeFromEnv), and pagination with totals (paginate)
• Shard-grouped batch writes (batch) that cost one round trip per shard instead of one per statement
• Rendezvous hashing, so adding a shard relocates about 1/N of keys instead of nearly all of them
• Optional computed placement that resolves a shard with no KV read and no mapping write
• Per-phase timing (onPhase, PhaseCollector) that separates hashing, KV, and SQL costs
• KV read-through cache (cached / invalidate) and secondary-index lookups (setLookup / getLookup / deleteLookup)
• Hyperdrive helpers for PostgreSQL and MySQL
• Multiple allocation strategies: round-robin, random, hash, location-aware, and mixed read/write strategies
• Durable Object shard coordination and shard statistics
• Migration helpers for integrating existing datasets, plus rebalance for redistributing them

Getting Started

Installation

bun add @earth-app/collegedb
# or
npm install @earth-app/collegedb
Copy

NuxtHub + Drizzle with CollegeDB Routing

Keep NuxtHub + Drizzle for schema/migrations and add CollegeDB as your routing layer.

import { db as hubDb } from '@nuxthub/db';
import { kv } from '@nuxthub/kv';
import { sql } from 'drizzle-orm';
import { drizzle } from 'drizzle-orm/d1';
import { createNuxtHubKVProvider, createSQLiteProvider, first, initialize, run } from '@earth-app/collegedb';

let initialized = false;

function ensureCollegeDB(env: { DB_SECONDARY: D1Database }) {
	if (initialized) return;

	initialize({
		kv: createNuxtHubKVProvider(kv),
		shards: {
			'db-primary': createSQLiteProvider(hubDb, sql),
			'db-secondary': createSQLiteProvider(drizzle(env.DB_SECONDARY), sql)
		},
		strategy: 'hash'
	});

	initialized = true;
}

export default defineEventHandler(async (event) => {
	const env = event.context.cloudflare.env;
	ensureCollegeDB(env);

	await run('post:123', 'INSERT OR REPLACE INTO blog_posts (id, title) VALUES (?, ?)', ['post:123', 'Hello from CollegeDB']);

	const post = await first<{ id: string; title: string }>('post:123', 'SELECT id, title FROM blog_posts WHERE id = ?', ['post:123']);

	return { post };
});
Copy

Drop-in Pattern for Existing hub:db + hub:kv Code

// before
import { eq } from 'drizzle-orm';
import { db } from 'hub:db';
import { kv } from 'hub:kv';
import { blogPosts } from '~/server/db/schema';

const cached = await kv.get('nuxtpress:post:slug');
if (cached) return cached;

const rows = await db.select().from(blogPosts).where(eq(blogPosts.slug, slug)).limit(1);
await kv.set('nuxtpress:post:slug', rows[0], { ttl: 3600 });
Copy

// after (CollegeDB routing + same NuxtHub KV cache)
import { kv } from '@nuxthub/kv';
import { sql } from 'drizzle-orm';
import { db } from 'hub:db';
import { createNuxtHubKVProvider, createSQLiteProvider, first, initialize } from '@earth-app/collegedb';

let initialized = false;

function setup() {
	if (initialized) return;
	initialize({
		kv: createNuxtHubKVProvider(kv),
		shards: {
			'db-primary': createSQLiteProvider(db, sql)
		},
		strategy: 'hash'
	});
	initialized = true;
}

setup();

const cacheKey = `nuxtpress:post:${slug}`;
const cached = await kv.get(cacheKey);
if (cached) return cached;

const row = await first<{ id: string; slug: string; title: string }>(
	cacheKey,
	'SELECT id, slug, title FROM blog_posts WHERE slug = ? LIMIT 1',
	[slug]
);

await kv.set(cacheKey, row, { ttl: 3600 });
Copy

Benchmark Suite

CollegeDB includes a benchmark runner that executes each SQL+KV combination across adapter profiles, then generates a report with profile-specific matrices.

Adapter Profiles

Profile	Lane	Purpose
native	both	Direct provider clients (Cloudflare bindings or driver-native adapters)
drizzle	both	Drizzle interop through SQL provider adapters
hyperdrive	Cloudflare	A real env.HYPERDRIVE binding, supplied by wrangler dev from local Postgres
per-statement-connection	local	A fresh driver connection per statement, off by default

The hyperdrive profile only runs in the Cloudflare lane, because a Hyperdrive binding needs a Workers runtime. wrangler dev supplies one from localConnectionString, which exercises the driver path, the binding shape, and the per-request connection lifecycle. Local Hyperdrive does no query caching or edge pooling, so that cell checks correctness and connection count rather than pooling latency; a pooling measurement needs wrangler dev --remote against a real Hyperdrive configuration.

per-statement-connection measures opening a connection per statement against a local server. It is not a Hyperdrive measurement and is excluded from the default matrix, where it accounted for 40.6% of the wall time.

KV adapters are not a profile axis, because the adapter wrapper is one function call per operation and the measured difference was indistinguishable from run-to-run variance. Adapter behaviour is covered by the KVStorage conformance spec instead.

Scenario Catalog

Scenario Key	Scenario	What Happens	Workload Per Run
basic_crud	Basic CRUD round-trip	Insert, read, update, and delete a user via routed queries.	20 iterations; 4 routed SQL ops per iteration
advanced_usage	Advanced lookup workflow	Writes user+post, adds lookup aliases, then validates join and alias-based lookup.	15 iterations; ~5 routed SQL ops + KV lookup-key updates per iteration
migration_mapping	Migration-style mapping creation	Inserts legacy records on a fixed shard, then builds shard mappings in batch and validates routing.	10 iterations; 20 legacy records mapped per iteration
bulk_crud	Bulk CRUD pressure	Performs bulk inserts, half updates, and full delete sweep, then validates shard-wide totals.	7 iterations; 160 inserts + 80 updates + 160 deletes per iteration
auto_increment	Auto-generated primary keys	Inserts rows with generated ids on an automatically selected shard, captures the generated key, then validates routed readback.	6 iterations; insert + generated-id readback per iteration
indexing	Indexed query scan	Creates an index on posts(user_id) and repeatedly queries the indexed path.	15 iterations after warmup dataset build
metadata_fetch	Metadata inspection	Reads table metadata/introspection rows from one shard.	14 iterations; 1 metadata query per iteration
pragma_or_info	PRAGMA / server info	Runs provider-specific PRAGMA/info query to sample low-level metadata latency.	14 iterations; 1 pragma/info query per iteration
counting	Cross-shard counting	Counts users across all shards to measure fanout aggregation overhead.	14 iterations; all-shard count aggregation per iteration
shard_fanout	Shard fanout query	Runs query fanout to all shards and aggregates shard-level responses.	14 iterations; 1 all-shards query per iteration
reassignment	Shard reassignment flow	Creates a record, reassigns it to another shard, and verifies routed reads still succeed.	10 iterations; insert + reassignment + verification per iteration

Report Matrices

Each generated report includes:

• Matrix: SQL x KV (Overall)
• Matrix: Adapter Profiles (Overall Avg)
• Matrix: Core Scenario Latency (avg/p95)
• Matrix: Introspection and Routing Latency (avg/p95)
• Cloudflare Worker (wrangler dev --local)
• Matrix: Cloudflare Adapter Profiles (Overall Avg)

Common Commands

bun run test:sandbox
bun run test:sandbox:drizzle
bun run test:sandbox:per-statement-connection
bun run test:sandbox:hyperdrive
Copy

For Docker-based benchmark details and filtering options, see Sandbox Benchmarks (Docker Compose).

Provider Adapters

CollegeDB can run with either native Cloudflare bindings or custom providers as long as they match the exported KVStorage and SQLDatabase interfaces.

Drizzle interop is enabled by passing a Drizzle sql tag as the optional second argument to createPostgreSQLProvider, createMySQLProvider, or createSQLiteProvider.

Supported adapters:

• createRedisKVProvider
• createValkeyKVProvider
• createNuxtHubKVProvider
• createPostgreSQLProvider
• createMySQLProvider
• createSQLiteProvider
• createDrizzleSQLProvider (compatibility helper)
• createHyperdrivePostgresProvider
• createHyperdriveMySQLProvider

import { createClient as createRedisClient } from 'redis';
import { Pool } from 'pg';
import { createPostgreSQLProvider, createRedisKVProvider, initialize, run, type CollegeDBConfig } from '@earth-app/collegedb';

const redisClient = createRedisClient({ url: process.env.REDIS_URL });
const pgPool = new Pool({ connectionString: process.env.POSTGRES_URL });

const config: CollegeDBConfig = {
	kv: createRedisKVProvider(redisClient),
	shards: {
		'pg-east': createPostgreSQLProvider(pgPool)
	},
	strategy: 'hash',
	disableAutoMigration: true
};

async function bootstrap() {
	await redisClient.connect();
	initialize(config);
	await run('user-1', 'INSERT INTO users (id, name) VALUES (?, ?)', ['user-1', 'Taylor']);
}

bootstrap().catch(console.error);
Copy

For Hyperdrive-backed SQL connections, use createHyperdrivePostgresProvider or createHyperdriveMySQLProvider with your database client factory.

NuxtHub Runtime Example (D1 + KV)

import { db } from '@nuxthub/db';
import { kv } from '@nuxthub/kv';
import { sql } from 'drizzle-orm';
import { createNuxtHubKVProvider, createSQLiteProvider, initialize, run, first } from '@earth-app/collegedb';

initialize({
	kv: createNuxtHubKVProvider(kv),
	shards: {
		'db-primary': createSQLiteProvider(db, sql)
	},
	strategy: 'hash'
});

await run('draft:home', 'INSERT OR REPLACE INTO drafts (id, content) VALUES (?, ?)', ['draft:home', '# Home']);

const draft = await first<{ id: string; content: string }>('draft:home', 'SELECT id, content FROM drafts WHERE id = ?', ['draft:home']);
Copy

Keep Drizzle Schema + Migrations, Route Queries with CollegeDB

CollegeDB does not replace your Drizzle schema or NuxtHub migration workflow.

npx nuxt db generate
npx nuxt db migrate
Copy

Use those migrations as-is, then route runtime reads/writes through CollegeDB adapters.

For a complete non-Cloudflare setup, see examples/provider-sandbox.ts.

NuxtHub + Drizzle Recipes

Multi-Vendor Shards (Cloudflare + Postgres/MySQL)

NuxtHub supports multiple deployment/database vendors. CollegeDB can shard across any SQL backends that Drizzle can connect to.

import { sql } from 'drizzle-orm';
import { drizzle as drizzlePg } from 'drizzle-orm/postgres-js';
import { drizzle as drizzleMySQL } from 'drizzle-orm/mysql2';
import { drizzle as drizzleD1 } from 'drizzle-orm/d1';
import { kv } from '@nuxthub/kv';
import postgres from 'postgres';
import mysql from 'mysql2/promise';
import {
	createMySQLProvider,
	createNuxtHubKVProvider,
	createPostgreSQLProvider,
	createSQLiteProvider,
	initialize,
	run
} from '@earth-app/collegedb';

const pgClient = postgres(process.env.POSTGRES_URL!);
const mysqlPool = mysql.createPool(process.env.MYSQL_URL!);

function setup(env: { DB_CF: D1Database }) {
	initialize({
		kv: createNuxtHubKVProvider(kv),
		shards: {
			'db-cf': createSQLiteProvider(drizzleD1(env.DB_CF), sql),
			'db-pg': createPostgreSQLProvider(drizzlePg(pgClient), sql),
			'db-mysql': createMySQLProvider(drizzleMySQL(mysqlPool), sql)
		},
		strategy: 'hash'
	});
}

export default defineEventHandler(async (event) => {
	setup(event.context.cloudflare.env);

	await run('tenant:acme:user:1', 'INSERT INTO users (id, name) VALUES (?, ?)', ['tenant:acme:user:1', 'Ada']);
});
Copy

Keep NuxtHub Cache/KV Semantics Intact

Use NuxtHub KV for app cache while CollegeDB uses its own key namespace for shard mappings:

import { kv } from '@nuxthub/kv';
import { first } from '@earth-app/collegedb';

const cacheKey = `nuxtpress:post:${slug}`;
const cached = await kv.get(cacheKey);
if (cached) return cached;

const post = await first(cacheKey, 'SELECT * FROM blog_posts WHERE slug = ? LIMIT 1', [slug]);
await kv.set(cacheKey, post, { ttl: 3600 });
Copy

Sandbox Benchmarks (Docker Compose)

CollegeDB ships with an integration sandbox runner that benchmarks real latency across provider combinations.

Requirements:

• Docker + Docker Compose plugin
• Bun
• Wrangler (installed as a dev dependency and invoked by scripts)

The Cloudflare benchmark path runs against the dedicated sandbox worker:

• Worker entry: sandbox/worker.ts
• Wrangler config: sandbox/wrangler.jsonc

Main commands:

# Run full SQL x KV matrix plus Cloudflare local benchmark
bun run test:sandbox

# Run full SQL x KV matrix only
bun run test:sandbox:all

# Run Cloudflare local benchmark only (wrangler dev --local)
bun run test:sandbox:cloudflare
Copy

Provider filters:

# One SQL provider against all KV providers (native profile by default)
bun run test:sandbox:mysql
bun run test:sandbox:postgres
bun run test:sandbox:mariadb
bun run test:sandbox:sqlite

# One KV provider against all SQL providers (native profile by default)
bun run test:sandbox:redis
bun run test:sandbox:valkey

# Run all SQL x KV combinations for one adapter profile
bun run test:sandbox:drizzle
bun run test:sandbox:per-statement-connection
bun run test:sandbox:hyperdrive

# Explicit pairwise combinations
bun run test:sandbox:postgres+redis
bun run test:sandbox:postgres+valkey
bun run test:sandbox:mysql+redis
bun run test:sandbox:mysql+valkey
bun run test:sandbox:mariadb+redis
bun run test:sandbox:mariadb+valkey
bun run test:sandbox:sqlite+redis
bun run test:sandbox:sqlite+valkey
Copy

Output behavior:

• Every run writes a timestamped Markdown report to sandbox/results/
• sandbox/results/latest.md is always updated to the newest report
• The runner prints the report in-terminal using Bun's Markdown renderer with ANSI formatting
• test:sandbox includes native, drizzle, hyperdrive, and nuxthub adapter profiles across supported SQL/KV combinations plus Cloudflare profile runs

Benchmark coverage includes:

• basic CRUD
• advanced lookup/routing workflows
• migration-style mapping creation
• bulk CRUD
• auto-generated primary key inserts and readback
• indexing queries
• metadata fetch
• pragma/info queries (provider-specific)
• counting across shards
• shard fanout aggregation
• shard reassignment workflow

How to read benchmark rows:

• Latency cells are formatted as average / p95 in milliseconds.
• FAILED means the scenario returned an error.
• N/A means the scenario was intentionally skipped in that environment.
• Use the detailed section for full avg, p50, p95, min, max, and sample count (n).

In-Memory Providers for Testing & Development

CollegeDB includes lightweight, zero-dependency in-memory mock implementations of the KVStorage and SQLDatabase interfaces. These are ideal for:

• Unit testing without external dependencies
• Integration testing with multiple shard combinations
• Local development and rapid iteration
• Sandboxed playtesting of routing logic

The in-memory providers work in Cloudflare Workers, Node.js, and Deno environments.

Quick Start with In-Memory Providers

import { createInMemoryKVProvider, createInMemorySQLProvider, initialize, run, first } from '@earth-app/collegedb';

// Create fresh in-memory providers for each test
const config = {
	kv: createInMemoryKVProvider(),
	shards: {
		'shard-1': createInMemorySQLProvider(),
		'shard-2': createInMemorySQLProvider(),
		'shard-3': createInMemorySQLProvider()
	},
	strategy: 'hash'
};

initialize(config);

// Use as normal - all operations happen in-memory
await run('user-1', 'INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['user-1', 'Alice', 'alice@example.com']);

const user = await first<{ id: string; name: string }>('user-1', 'SELECT id, name FROM users WHERE id = ?', ['user-1']);
console.log(user); // { id: 'user-1', name: 'Alice' }
Copy

Unit Testing Example

import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import { createInMemoryKVProvider, createInMemorySQLProvider, initialize, resetConfig, run, first } from '@earth-app/collegedb';

describe('User Shard Routing', () => {
	beforeEach(() => {
		// Fresh providers for each test
		initialize({
			kv: createInMemoryKVProvider(),
			shards: {
				'shard-1': createInMemorySQLProvider(),
				'shard-2': createInMemorySQLProvider(),
				'shard-3': createInMemorySQLProvider()
			},
			strategy: 'hash'
		});
	});

	afterEach(() => {
		resetConfig();
	});

	it('should insert and retrieve a user', async () => {
		await run('user-1', 'INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['user-1', 'Alice', 'alice@example.com']);

		const user = await first<{ name: string }>('user-1', 'SELECT name FROM users WHERE id = ?', ['user-1']);

		expect(user?.name).toBe('Alice');
	});

	it('should distribute users across shards', async () => {
		// Insert multiple users
		for (let i = 0; i < 9; i++) {
			await run(`user-${i}`, 'INSERT INTO users (id, name) VALUES (?, ?)', [`user-${i}`, `User ${i}`]);
		}

		// Verify each can be retrieved
		for (let i = 0; i < 9; i++) {
			const user = await first(`user-${i}`, 'SELECT id FROM users WHERE id = ?', [`user-${i}`]);
			expect(user).toBeDefined();
		}
	});

	it('should handle updates correctly', async () => {
		await run('user-1', 'INSERT INTO users (id, name) VALUES (?, ?)', ['user-1', 'Alice']);

		await run('user-1', 'UPDATE users SET name = ? WHERE id = ?', ['Alice Updated', 'user-1']);

		const user = await first<{ name: string }>('user-1', 'SELECT name FROM users WHERE id = ?', ['user-1']);

		expect(user?.name).toBe('Alice Updated');
	});
});
Copy

Integration Testing with Multiple Providers

Test different combinations without Docker or external services:

import { describe, it, expect, beforeEach, afterEach } from 'vitest';
import {
	createInMemoryKVProvider,
	createInMemorySQLProvider,
	initialize,
	resetConfig,
	run,
	first,
	KVShardMapper
} from '@earth-app/collegedb';

describe('Multi-Provider Integration', () => {
	it('should work with different KV/SQL combinations', async () => {
		const combinations = [{ kvName: 'memory', sqlName: 'memory' }];

		for (const combo of combinations) {
			resetConfig();

			initialize({
				kv: createInMemoryKVProvider(),
				shards: {
					'shard-1': createInMemorySQLProvider(),
					'shard-2': createInMemorySQLProvider()
				},
				strategy: 'hash'
			});

			// Test basic operations
			await run('key-1', 'INSERT INTO data (id, value) VALUES (?, ?)', ['key-1', 'test-value']);

			const row = await first('key-1', 'SELECT value FROM data WHERE id = ?', ['key-1']);

			expect(row?.value).toBe('test-value');
		}
	});

	it('should support lookup key mapping', async () => {
		initialize({
			kv: createInMemoryKVProvider(),
			shards: { 'shard-1': createInMemorySQLProvider() },
			strategy: 'hash'
		});

		const mapper = new KVShardMapper(createInMemoryKVProvider());

		// Add lookup keys
		await mapper.addLookupKeys('user-123', ['email:alice@example.com', 'username:alice']);

		// Retrieve via lookup key
		const mapping = await mapper.getShardMapping('email:alice@example.com');
		expect(mapping?.shard).toBeDefined();
	});
});
Copy

Performance Testing with In-Memory Providers

Run quick performance tests locally without external dependencies:

import { createInMemoryKVProvider, createInMemorySQLProvider, initialize, run } from '@earth-app/collegedb';

async function benchmarkInserts(iterations: number): Promise<number> {
	initialize({
		kv: createInMemoryKVProvider(),
		shards: {
			'shard-1': createInMemorySQLProvider(),
			'shard-2': createInMemorySQLProvider(),
			'shard-3': createInMemorySQLProvider()
		},
		strategy: 'hash'
	});

	const startTime = performance.now();

	for (let i = 0; i < iterations; i++) {
		const id = `perf-user-${i}`;
		await run(id, 'INSERT INTO users (id, name) VALUES (?, ?)', [id, `User ${i}`]);
	}

	return performance.now() - startTime;
}

const duration = await benchmarkInserts(1000);
console.log(`1000 inserts: ${duration.toFixed(2)}ms (${((1000 / duration) * 1000).toFixed(0)} ops/sec)`);
Copy

Running the Memory Provider Sandbox

CollegeDB includes a ready-made sandbox example demonstrating multiple scenarios:

bun run test:memory
Copy

This runs benchmarks covering:

• Basic CRUD operations
• Multi-shard data distribution
• KV storage operations
• Round-robin strategy testing
• JOIN query performance

Supported Operations

Both in-memory providers support the complete CollegeDB API:

SQLDatabase features:

• CREATE TABLE / DROP TABLE
• INSERT / UPDATE / DELETE
• SELECT with WHERE clauses
• COUNT(*) queries
• JOIN queries
• PRAGMA queries (basic support)

KVStorage features:

• get() / put() / delete()
• list() with prefix filtering and cursor-based pagination
• TTL/expiration support

Limitations

The in-memory providers are intentionally simple to avoid dependencies:

• SQL Parser: Basic pattern matching instead of full SQL parsing; works well for standard CollegeDB patterns but may not handle complex SQL edge cases
• Joins: Supported at application level; cross-shard joins work via multiple routed queries
• Transactions: Not supported; operations are atomic per-statement
• Indexes: Created but not actually used for query optimization
• Schema: Inferred from CREATE TABLE statements; dynamic column detection based on binding order

For production use, migrate to appropriate providers (D1, Redis, PostgreSQL, etc.). For testing/development, these limitations are intentional to keep the implementation lightweight and zero-dependency.

Advanced In-Memory Example: run, all, insert, and Aggregates

The in-memory SQL emulator supports a useful subset of SQLite syntax, enough to drive routing tests for ORM-style code. The example below exercises the full routing stack (run, all, first, insert, insertShard, runShard, countAllShards, allAllShardsGlobal) entirely in-process without spinning up a database container.

import {
	allAllShardsGlobal,
	countAllShards,
	createInMemoryKVProvider,
	createInMemorySQLProvider,
	first,
	initialize,
	insert,
	insertShard,
	resetConfig,
	run,
	runShard
} from '@earth-app/collegedb';

resetConfig();

initialize({
	kv: createInMemoryKVProvider(),
	shards: {
		'db-east': createInMemorySQLProvider(),
		'db-west': createInMemorySQLProvider(),
		'db-central': createInMemorySQLProvider()
	},
	strategy: 'hash',
	hashShardMappings: false,
	disableAutoMigration: true
});

// 1. Schema setup: replicate the same DDL on every shard.
for (const shard of ['db-east', 'db-west', 'db-central']) {
	await runShard(
		shard,
		`CREATE TABLE IF NOT EXISTS users (
			id TEXT PRIMARY KEY,
			name TEXT NOT NULL,
			email TEXT UNIQUE,
			created_at INTEGER
		)`
	);
	await runShard(
		shard,
		`CREATE TABLE IF NOT EXISTS tickets (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			user_id TEXT NOT NULL,
			subject TEXT NOT NULL,
			created_at INTEGER
		)`
	);
}

// 2. Routed inserts: CollegeDB picks the shard from a stable hash of the
//    primary key, then records the mapping in KV.
await run('user-1', 'INSERT INTO users (id, name, email, created_at) VALUES (?, ?, ?, ?)', [
	'user-1',
	'Alice',
	'alice@example.com',
	Date.now()
]);

// 3. AUTOINCREMENT inserts: `insert()` allocates the shard and captures the
//    generated id, storing it as a mapping so routed reads find the row.
const ticketA = await insert('INSERT INTO tickets (user_id, subject, created_at) VALUES (?, ?, ?)', [
	'user-1',
	'Cannot log in',
	Date.now()
]);

// 4. Pinned inserts: `insertShard()` writes to a specific shard, still
//    capturing the generated id mapping.
const ticketB = await insertShard('db-west', 'INSERT INTO tickets (user_id, subject, created_at) VALUES (?, ?, ?) RETURNING id', [
	'user-1',
	'Mobile sync is slow',
	Date.now()
]);

// 5. Routed reads: `first` and `all` resolve the shard from the mapping.
const user = await first<{ id: string; name: string; email: string }>('user-1', 'SELECT id, name, email FROM users WHERE id = ?', [
	'user-1'
]);

const ticket = await first<{ id: number; subject: string }>(String(ticketA.generatedId), 'SELECT id, subject FROM tickets WHERE id = ?', [
	ticketA.generatedId
]);

// 6. Cross-shard aggregates: the emulator evaluates COUNT/MAX/MIN/SUM/AVG and
//    COALESCE during SELECT, so utility queries like `SELECT COALESCE(MAX(id),
//    0) + 1 AS next FROM tickets` work the same way they do in production.
const totals = await countAllShards('tickets');

const recentTickets = await allAllShardsGlobal<{ id: number; subject: string; created_at: number }>(
	'SELECT id, subject, created_at FROM tickets WHERE user_id = ?',
	['user-1'],
	{ sortBy: 'created_at', sortDirection: 'desc', limit: 10 }
);

console.log({ user, ticket, ticketB: ticketB.generatedId, totals, recent: recentTickets.results });
Copy

What this example demonstrates:

• Routing through run, first, all, and insert works end-to-end without a real database. The hash strategy assigns each new primary key to a shard the same way it would in production.
• Auto-increment + RETURNING are honored. insert() captures the generated id from either provider metadata or RETURNING id rows.
• Aggregate SQL is evaluated (COUNT(*), MIN, MAX, SUM, AVG, COALESCE, simple arithmetic), and compound WHERE with AND / OR / parens / LIKE / IS NULL is honored on UPDATE, DELETE, and SELECT.
• Cross-shard fanout helpers (countAllShards, allAllShardsGlobal) operate over the in-memory store exactly as they would over D1/Postgres/MySQL.
• No data leaks between tests: call resetConfig() (and instantiate fresh providers) to start each test with a clean state.

Migrating from In-Memory to Production Providers

When ready to migrate from testing to production:

// Before (testing)
import { createInMemoryKVProvider, createInMemorySQLProvider } from '@earth-app/collegedb';

const config = {
	kv: createInMemoryKVProvider(),
	shards: { 'shard-1': createInMemorySQLProvider() }
};

// After (production)
import { createRedisKVProvider, createPostgreSQLProvider } from '@earth-app/collegedb';

const config = {
	kv: createRedisKVProvider(redisClient),
	shards: { 'shard-1': createPostgreSQLProvider(pgPool) }
};

// Rest of configuration stays the same!
Copy

The API remains identical - only the provider initialization changes.

Basic Usage

import { collegedb, createSchema, run, first } from '@earth-app/collegedb';

// Initialize with your Cloudflare bindings (existing databases work automatically!)
collegedb(
	{
		kv: env.KV,
		coordinator: env.ShardCoordinator,
		shards: {
			'db-east': env['db-east'], // Can be existing DB with data
			'db-west': env['db-west'] // Can be existing DB with data
		},
		strategy: 'hash'
	},
	async () => {
		// Create schema on new shards only (existing shards auto-detected)
		await createSchema(env['db-new-shard'], 'CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, name TEXT, email TEXT)');

		// Insert data (automatically routed to appropriate shard)
		await run('user-123', 'INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['user-123', 'Johnson', 'alice@example.com']);

		// Query data (automatically routed to correct shard, works with existing data!)
		const result = await first<User>('existing-user-456', 'SELECT * FROM users WHERE id = ?', ['existing-user-456']);

		console.log(result); // User data from existing database
	}
);
Copy

Sharding Strategies

The strategy decides which shard a key lands on the first time it is written. After that the key stays put: every later read and write goes to the shard it was assigned, whatever the strategy says. So a strategy shapes distribution, not per-query routing.

Strategy	New key goes to	Recomputable from the key	Needs a coordinator
hash	Rendezvous hash of the key over the shards	Yes	No
round-robin	The next shard in order	No	For cross-isolate fairness
random	A uniformly random shard	No	No
location	The shard nearest targetRegion	No	No

hash is the default and the only one that supports computed placement, because it is the only one that is a function of the key.

import { getShardStats, initialize, run } from '@earth-app/collegedb';

initialize({
	kv: env.KV,
	shards: { 'db-a': env['db-a'], 'db-b': env['db-b'], 'db-c': env['db-c'] },
	strategy: 'hash'
});

for (let i = 0; i < 300; i++) {
	await run(`user-${i}`, 'INSERT INTO users (id, name) VALUES (?, ?)', [`user-${i}`, `User ${i}`]);
}

// Roughly 100 keys per shard
console.log(await getShardStats());
Copy

Choosing Between Them

hash spreads keys deterministically and needs nothing else running. Distribution is even in aggregate but not exact, since it is a hash: expect a few percent of variance across shards.

round-robin is the only strategy that produces an exactly even split, which is why it is worth using when shard sizes must track each other closely. Without a coordinator the counter is per-isolate, so a Worker that starts many isolates gets even distribution per isolate rather than globally. Configure a coordinator to share one counter.

random needs no shared state and converges on even over enough keys, at the cost of more variance than hash at low key counts.

location sends new keys to the shard nearest targetRegion, which concentrates rather than spreads. That is the point when data residency or write locality matters, but it means one shard absorbs everything for a given region. On D1 specifically, read latency is a weaker reason to reach for it than it looks: D1 already creates read replicas in every supported region, so location earns its place on write locality and residency rather than read speed. Pair it with a mixed strategy when you want placement by region and even reads.

Adding or Removing a Shard

Existing keys keep their recorded mapping, so adding a shard only affects keys written afterwards. Nothing needs to be migrated for reads to keep working:

// Before: two shards. After: three. Existing rows stay where they are.
initialize({
	kv: env.KV,
	shards: { 'db-a': env['db-a'], 'db-b': env['db-b'], 'db-c': env['db-c'] },
	strategy: 'hash'
});
Copy

To move existing keys onto the distribution the new shard set implies, run rebalance. It reports what it would do before it does anything:

import { rebalance } from '@earth-app/collegedb';

const preview = await rebalance('users', { dryRun: true });
console.log(`${preview.moved} of ${preview.examined} keys would move`);

const applied = await rebalance('users', { concurrency: 10 });
console.log(`moved ${applied.moved}, failed ${applied.failed.length}`);
Copy

rebalance moves rows as well as mappings, one key at a time, so it is safe to interrupt and re-run. Keys it fails to move are listed in failed with the reason rather than aborting the pass.

Because hash uses rendezvous hashing, adding an Nth shard relocates about 1/N of the keyspace and leaves the rest alone. The scheme it replaced (hash(key) % shardCount) relocated nearly everything on any change in shard count, so a rebalance after adding a shard is now a small operation rather than a full reshuffle.

Geographic Distribution Example

import { collegedb, first, run } from '@earth-app/collegedb';

// Optimize for North American users with geographic sharding
collegedb(
	{
		kv: env.KV,
		strategy: 'location',
		targetRegion: 'wnam', // Western North America
		shardLocations: {
			'db-west': { region: 'wnam', priority: 2 }, // SF - Preferred for target region
			'db-east': { region: 'enam', priority: 1 }, // NYC - Secondary
			'db-europe': { region: 'weur', priority: 0.5 } // London - Fallback
		},
		shards: {
			'db-west': env.DB_WEST,
			'db-east': env.DB_EAST,
			'db-europe': env.DB_EUROPE
		}
	},
	async () => {
		// New users will be allocated to db-west (closest to target region)
		await run('user-west-123', 'INSERT INTO users (id, name, location) VALUES (?, ?, ?)', [
			'user-west-123',
			'West Coast User',
			'California'
		]);

		// Queries are routed to the correct geographic shard
		const user = await first<User>('user-west-123', 'SELECT * FROM users WHERE id = ?', ['user-west-123']);
		console.log(`User found in optimal shard: ${user?.name}`);
	}
);
Copy

Mixed Strategy Example

import { collegedb, first, run, type MixedShardingStrategy } from '@earth-app/collegedb';

// Use location strategy for writes (optimal data placement) and hash for reads (optimal performance)
const mixedStrategy: MixedShardingStrategy = {
	write: 'location', // New data goes to geographically optimal shards
	read: 'hash' // Reads use consistent hashing for best performance
};

collegedb(
	{
		kv: env.KV,
		strategy: mixedStrategy,
		targetRegion: 'wnam', // Western North America for writes
		shardLocations: {
			'db-west': { region: 'wnam', priority: 2 },
			'db-east': { region: 'enam', priority: 1 },
			'db-central': { region: 'enam', priority: 1 }
		},
		shards: {
			'db-west': env.DB_WEST,
			'db-east': env.DB_EAST,
			'db-central': env.DB_CENTRAL
		}
	},
	async () => {
		// Write operations use location strategy - new users placed optimally
		await run('user-california-456', 'INSERT INTO users (id, name, location) VALUES (?, ?, ?)', [
			'user-california-456',
			'California User',
			'Los Angeles'
		]);

		// Read operations use hash strategy - consistent and fast routing
		const user = await first<User>('user-california-456', 'SELECT * FROM users WHERE id = ?', ['user-california-456']);

		// Different operations can route to different shards based on strategy
		// This optimizes both data placement (writes) and query performance (reads)
		console.log(`User: ${user?.name}, Location: ${user?.location}`);
	}
);
Copy

This approach provides:

• Optimal data placement: New records are written to geographically optimal shards using location strategy
• Optimal read performance: Queries use hash strategy for consistent, high-performance routing
• Flexibility: Each operation type can use the most appropriate routing strategy

Auto-Generated Primary Keys

When your table assigns the primary key during insert, use insert() for the automatic shard-allocation path or insertShard() when you already know the target shard. Both helpers capture the generated id from provider metadata or RETURNING rows, then store the generated-id mapping so the normal routed first() / all() helpers can read the row back.

A database-generated id is only unique within its own shard. Every shard runs its own AUTOINCREMENT or SERIAL sequence, so db-a and db-b both hand out 1, then 2, then 3. Spread a generated-key table across shards and the same id eventually arrives twice; the second mapping would overwrite the first and leave that row on a shard nothing routes to.

CollegeDB throws GENERATED_KEY_COLLISION when it sees that instead:

import { CollegeDBError, insertShard } from '@earth-app/collegedb';

await insertShard('db-a', 'INSERT INTO auto_users (name) VALUES (?)', ['Ada']); // id 1 on db-a

try {
	await insertShard('db-b', 'INSERT INTO auto_users (name) VALUES (?)', ['Grace']); // also id 1
} catch (error) {
	if (error instanceof CollegeDBError && error.code === 'GENERATED_KEY_COLLISION') {
		// The row was written to db-b, but id 1 already routes to db-a. Reconcile
		// it with a cluster-unique id rather than leaving it unreachable.
	}
}
Copy

Three Ways to Key a Generated-Id Table

Approach	Spans shards	Concurrency-safe	Cost per id
nextId() + explicit id	Yes	With a coordinator	One coordinator call
insertShard() on one shard	No	Yes, the database guarantees it	None
insert() across shards	Yes	No, throws on collision	None

nextId() for a table that spans shards. The id is allocated before the insert, so it is unique across the cluster and you route on it directly:

import { insertInto, nextId } from '@earth-app/collegedb';

const id = await nextId('tickets');

await insertInto(String(id), 'tickets', {
	id,
	title: 'Printer is on fire',
	created_at: Math.floor(Date.now() / 1000)
});
Copy

insertShard() to keep the database's own sequence. Pin the table to one shard and its AUTOINCREMENT stays authoritative, with no coordinator and no extra round trip. This is the right call for an append-only table that does not need to scale past one instance:

import { first, insertShard } from '@earth-app/collegedb';

const created = await insertShard('db-east', 'INSERT INTO audit_log (action, at) VALUES (?, ?)', ['login', Date.now()]);
const row = await first(String(created.generatedId), 'SELECT * FROM audit_log WHERE id = ?', [created.generatedId]);
Copy

insert() when a single shard holds the table anyway. Convenient while there is one shard, and it fails loudly rather than silently the moment there are two.

Naming the Id Column

CollegeDB looks for id or rowid in the returned row, then for the driver's own last-insert metadata. If your primary key is called something else, say so, because it will not guess which returned column is the key:

const created = await insert('INSERT INTO things (uuid, label) VALUES (?, ?) RETURNING uuid, label', ['abc', 'Widget'], {
	idColumn: 'uuid'
});

console.log(created.generatedId); // 'abc'
Copy

Without idColumn, a statement whose RETURNING row contains no recognizable id column throws GENERATED_KEY_UNAVAILABLE. It does not fall back to the row's first value or to the driver's rowid, because for a TEXT PRIMARY KEY table the rowid is a different value than the key and routing on it puts the row out of reach.

import { first, insert, insertShard } from '@earth-app/collegedb';

// SQLite / D1
await createSchema(
	env['db-east'],
	`
	CREATE TABLE IF NOT EXISTS auto_users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		email TEXT UNIQUE,
		created_at INTEGER
	)
`
);

const created = await insert('INSERT INTO auto_users (name, email, created_at) VALUES (?, ?, ?)', ['Ada', 'ada@example.com', Date.now()]);

const row = await first(String(created.generatedId), 'SELECT * FROM auto_users WHERE id = ?', [created.generatedId]);
Copy

// Direct shard insert when you want to pin the write to a specific shard
const directCreated = await insertShard('db-east', 'INSERT INTO auto_users (name, email, created_at) VALUES (?, ?, ?)', [
	'Ada',
	'ada@example.com',
	Date.now()
]);

console.log(directCreated.generatedId);
Copy

// PostgreSQL / MySQL 8.0.19+ RETURNING path
await createSchema(
	env['db-east'],
	`
	CREATE TABLE IF NOT EXISTS auto_users (
		id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
		name VARCHAR(255) NOT NULL,
		email VARCHAR(255) UNIQUE,
		created_at BIGINT
	)
`
);

const created = await insert('INSERT INTO auto_users (name, email, created_at) VALUES (?, ?, ?) RETURNING id', [
	'Ada',
	'ada@example.com',
	Date.now()
]);

const row = await first(String(created.generatedId), 'SELECT * FROM auto_users WHERE id = ?', [created.generatedId]);
Copy

If your SQL dialect uses RETURNING, include it in the insert statement. The helper will use the returned row instead of provider metadata when present.

Utility Helpers

CollegeDB ships helpers that remove the boilerplate most consumers otherwise rewrite per table. Every helper routes through the same shard map as run/first, and every generated statement uses positional bindings with validated, quoted identifiers.

Initialization on Workers

initialize() starts two background tasks: a known-shard sync and, unless disableAutoMigration is set, auto-migration detection. On Workers, work not attached to a request is cancelled when that request ends, so pass ctx.waitUntil to let them finish:

export default {
	async fetch(request: Request, env: Env, ctx: ExecutionContext) {
		initialize({
			kv: env.KV,
			shards: { 'db-east': env.DB_EAST, 'db-west': env.DB_WEST },
			strategy: 'hash',
			waitUntil: (promise) => ctx.waitUntil(promise)
		});

		// ... handle the request
	}
};
Copy

initializeAsync() awaits both tasks instead, which suits a script or a test but adds their latency to the request that calls it.

One-Call Setup From a Worker env

initializeFromEnv discovers D1 bindings on env (any name matching DB_, DB-, or db-, plus a primary env.DB), resolves each with toProvider (D1 / Drizzle / SQLite), wires the KV store (raw Workers KV is auto-wrapped), and calls initialize. Shard names are the binding name lowercased with _ replaced by - (DB_EAST -> db-east).

import { initializeFromEnv, isInitialized } from '@earth-app/collegedb';
import { sql } from 'drizzle-orm';

export default {
	async fetch(request: Request, env: Env) {
		if (!isInitialized()) {
			// Pass `sql` so Drizzle bindings can be wrapped; omit it for raw D1.
			initializeFromEnv(env, { sql, strategy: { read: 'location', write: 'hash' } });
		}
		// ... routed queries
	}
};
Copy

Lower-level building blocks are exported too:

• toProvider(binding, { sql? }) - detect and wrap a single binding, or null if unrecognized.
• createWorkersKVProvider(env.KV) - adapt a raw Cloudflare Workers KVNamespace to CollegeDB's KVStorage.
• isInitialized() - replaces the ad-hoc let initialized = false guard.

Object-Shaped CRUD

Build routed statements from plain objects instead of hand-aligned SQL strings:

import { insertInto, insertReturning, patch, updateRow, deleteById, deleteRow, upsert } from '@earth-app/collegedb';

// INSERT INTO "users" ("id", "username", "created_at") VALUES (?, ?, ?)
await insertInto('user-123', 'users', { id: 'user-123', username: 'ada', created_at: nowSeconds });

// Insert and read the row back in one call
const created = await insertReturning('user-123', 'users', { id: 'user-123', username: 'ada' });

// Partial UPDATE by id: UPDATE "tickets" SET "status" = ?, "priority" = ? WHERE "id" = ?
await patch('42', 'tickets', 42, { status: 'closed', priority: 'high' });

// General UPDATE / DELETE scoped by a where map (an empty where throws, never a full-table write)
await updateRow('user-123', 'users', { username: 'ada2' }, { id: 'user-123' });
await deleteById('user-123', 'users', 'user-123');
await deleteRow('user-123', 'sessions', { user_id: 'user-123' });

// INSERT ... ON CONFLICT ("key") DO UPDATE SET "value" = excluded."value"
await upsert('settings:theme', 'settings', { key: 'theme', value: 'dark' }, 'key');
Copy

The pure builders (buildInsert, buildUpdate, buildDelete, buildUpsert) are exported as well when you want the { sql, bindings } pair without executing it.

Cross-Shard-Safe Id Generation

nextId replaces the common but broken SELECT COALESCE(MAX(id), 0) + 1 on a single shard. Because a generated id lands on the shard it hashes to, a per-shard MAX never sees rows on the other shards and hands out colliding ids. nextId reads MAX across every shard, then uses the Durable Object's atomic sequence when a coordinator is configured.

import { nextId, insertInto } from '@earth-app/collegedb';

const id = await nextId('tickets'); // atomic when a coordinator is configured
await insertInto(String(id), 'tickets', { id, title, created_at: nowSeconds });
Copy

With a coordinator the sequence is race-free across concurrent callers and isolates. Without one, nextId returns a cross-shard-correct MAX + 1 that is not concurrency-safe on its own; pair it with a coordinator or a unique constraint when writers race.

The cross-shard MAX is only paid once. With a coordinator, nextId asks the sequence first, and the coordinator answers directly whenever the sequence already exists, which it does after the first call. Only an unseeded sequence triggers the MAX sweep, so a table's first id costs one query per shard and every id after it costs one coordinator call and no shard queries.

Options cover a non-default column and a floor:

// Scan a column other than `id`
const next = await nextId('tickets', { column: 'ticket_number' });

// Never return below a known watermark, for example after importing legacy rows
const safe = await nextId('tickets', { min: 100_000 });
Copy

Resilient Reads and Pagination

import { firstResilient, paginate } from '@earth-app/collegedb';

// Routed read, falling back to a global scan when the key->shard mapping has not been created yet
const user = await firstResilient<User>('user-123', 'SELECT * FROM users WHERE id = ?', ['user-123']);

// One call returns the page plus the total match count for a UI
const { results, total, page, pages } = await paginate<User>('SELECT * FROM users WHERE username LIKE ?', ['%ada%'], {
	page: 2,
	limit: 25,
	sortBy: 'created_at',
	sortDirection: 'desc'
});
Copy

Idempotent Schema Across Shards

ensureSchema runs DDL on every configured shard with an in-process once guard and an optional KV-backed version gate.

import { ensureSchema } from '@earth-app/collegedb';

await ensureSchema(
	[
		'CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, username TEXT NOT NULL)',
		'CREATE INDEX IF NOT EXISTS idx_users_username ON users (username)'
	],
	{ versionKey: 'schema:version', version: '3' }
);
Copy

Routing Without a Key Argument

query, queryFirst, and queryAll read the primary key out of the statement, so you do not pass it twice:

import { query, queryFirst } from '@earth-app/collegedb';

await query('INSERT INTO users (id, name) VALUES (?, ?)', ['user-1', 'Ada']);
await query('UPDATE users SET name = ? WHERE id = ?', ['Ada L.', 'user-1']);

const user = await queryFirst<User>('SELECT * FROM users WHERE id = ?', ['user-1']);
Copy

A statement whose key spans shards is grouped and issued once per shard:

await query('DELETE FROM users WHERE id IN (?, ?, ?)', ['user-1', 'user-2', 'user-3']);
Copy

Recognized shapes are INSERT INTO t (cols) VALUES (...) including multi-row inserts, and UPDATE/DELETE/SELECT whose whole WHERE clause is key = ? or key IN (?, ...). The key column defaults to id; declare others per table:

initialize({
	kv: env.KV,
	shards: { 'db-east': env.DB_EAST },
	keyColumns: { tickets: 'ticket_id', sessions: 'session_uuid' }
});
Copy

Anything else is unroutable, and CollegeDB throws rather than guessing, because a mis-routed write lands a row on a shard no reader queries. The error names the explicit-key alternative. Set onUnroutable: 'fanout' to query every shard instead.

The planner reads the SQL you hand to query, so a Drizzle query builder chain does not go through it. Use the key-first API for those.

Batched Writes

batch groups routed statements by shard and issues one round trip per shard:

import { batch } from '@earth-app/collegedb';

const groups = await batch([
	{ key: 'user-1', sql: 'INSERT INTO users (id, name) VALUES (?, ?)', bindings: ['user-1', 'Ada'] },
	{ key: 'user-2', sql: 'INSERT INTO users (id, name) VALUES (?, ?)', bindings: ['user-2', 'Grace'] }
]);

for (const group of groups) {
	if (group.error) console.error(`${group.shard} failed: ${group.error}`);
}
Copy

Statements on the same shard share that shard's transaction and run in submission order. Statements on different shards do not. A batch spanning three shards is three independent transactions and can leave one shard updated and another not; key the whole unit of work to one shard when that matters. The result is reported per shard for the same reason.

D1 caps a Worker invocation at 1,000 queries on the paid plan and 50 on the free plan, so a bulk write of one statement per row is not merely slow there.

Routing the batch costs one KV read for the whole set and one KV write for the keys that were not mapped yet, rather than a read and a write per key, on any store that implements getMany and putMany. The Redis, Valkey and in-memory adapters do; the rest fall back to bounded concurrent single-key calls and return the same answer.

Transactions Inside a Batch

A shard runs its group in one transaction when its provider can give the batch a connection to run on. D1 and Drizzle-on-D1 use the native batch. The PostgreSQL, MySQL and SQLite adapters need to know where a connection comes from, because BEGIN, the statements, and COMMIT are only one transaction if they travel down the same connection:

// A pool is recognized and leased from per batch.
createPostgreSQLProvider(new Pool({ connectionString }));
createMySQLProvider(mysql.createPool({ uri }));

// A single connection has to say so; a pool must not.
createPostgreSQLProvider(new Client({ connectionString }), { singleConnection: true });
createSQLiteProvider(new Database('app.db'), { singleConnection: true });

// Anything else supplies its own lease.
createPostgreSQLProvider(handle, {
	lease: async () => {
		const client = await myPool.acquire();
		return { client, release: () => myPool.release(client) };
	}
});
Copy

A handle CollegeDB cannot place runs its statements one at a time, exactly as before. It never guesses: pg's Client and Pool both expose connect(), and either can be wrapped in something that exposes only query(), so sending BEGIN to an unidentified handle risks bracketing a different connection than the statements it is meant to cover. A failed statement rolls the group back and rethrows.

Computed Placement

For the hash strategy the shard is already a function of the key, so placement: 'computed' skips the KV round trip entirely and stops recording a mapping per key:

initialize({
	kv: env.KV,
	shards: { 'db-east': env.DB_EAST, 'db-west': env.DB_WEST },
	strategy: 'hash',
	placement: 'computed'
});
Copy

Placement uses rendezvous hashing, so adding or removing a shard moves only the keys that must move, and the result does not depend on the order the bindings are declared in.

It is opt-in because it does not store mappings, and three things read the keyspace back out of those mappings: getShardStats key counts, KVShardMapper.getKeysForShard, and the migration helpers that enumerate mapped keys. Under computed placement the assignment is implied by the hash over a keyspace nothing enumerates, so those cannot answer.

round-robin and random are not functions of the key, and location depends on the requesting region rather than the key, so all three keep using KV.

For a deployment created before 1.4.0, run rebalance() first. It moves stored mappings onto their computed shard and reports how many already agreed:

const result = await rebalance('users', { dryRun: true });
console.log(`${result.agreed}/${result.examined} keys already agree, ${result.moved} would move`);
Copy

Once a pass reports no moves and no failures, computed placement resolves every key where the stored mapping already pointed. Keys moved by reassignShard stay recorded as exceptions and are still read from KV.

Measuring Where Time Goes

onPhase reports each cost inside a routed operation separately, so a change to the KV path can be judged rather than guessed at. It allocates nothing when unset:

import { PhaseCollector, initialize, run } from '@earth-app/collegedb';

const phases = new PhaseCollector();

initialize({
	kv: env.KV,
	shards: { 'db-east': env.DB_EAST, 'db-west': env.DB_WEST },
	strategy: 'hash',
	onPhase: phases.observer
});

await run('user-1', 'INSERT INTO users (id, name) VALUES (?, ?)', ['user-1', 'Ada']);

console.table(phases.stats());
// phase           count  avgMs  p50Ms  p95Ms  totalMs
// sql.exec            1   0.21   0.21   0.21     0.21
// kv.put              1   0.17   0.17   0.17     0.17
// shard.select        1   0.00   0.00   0.00     0.00
Copy

Phases are hash, kv.get, kv.put, kv.delete, kv.list, shard.select, coordinator.fetch, sql.prepare, and sql.exec. SQL spans carry the shard binding in detail and KV spans carry the key prefix, so you can tell which shard or which kind of key is slow.

For a stream rather than a summary, pass a function:

initialize({
	kv: env.KV,
	shards: { 'db-east': env.DB_EAST },
	onPhase: (span) => {
		if (span.durationMs > 50) {
			console.warn(`slow ${span.phase} on ${span.detail}: ${span.durationMs.toFixed(1)}ms`);
		}
	}
});
Copy

An observer that throws is swallowed rather than failing the query it was measuring.

Configuration Edge Cases

Reads That Find Nothing

A read for a key with no row does not record a mapping, so looking up something that does not exist costs no KV write and leaves nothing behind. That is the common shape for a public API, and it used to be the most expensive path in the library.

If you depend on the old behavior, where any routed read pinned its key to a shard, turn it back on:

import { initialize } from '@earth-app/collegedb';

initialize({
	kv: env.KV,
	shards: { 'db-east': env.DB_EAST },
	allocateOnRead: true
});
Copy

Reassignment and the Mapping Cache

mappingCacheTtlMs (default 30s) is a consistency window, not only a latency knob. The process that calls reassignShard is correct immediately, because it clears its own cache entry. Other processes keep routing that key to its old shard until their own entry expires:

import { reassignShard } from '@earth-app/collegedb';

await reassignShard('user-123', 'db-west', 'users');
// This isolate now routes user-123 to db-west.
// Other isolates may route it to db-east for up to mappingCacheTtlMs.
Copy

Lower the TTL when reassignment has to take effect quickly across isolates, at the cost of more KV reads. Set it to 0 to read the mapping every time:

initialize({ kv: env.KV, shards, mappingCacheTtlMs: 0 });
Copy

Under placement: 'computed', a reassignment also bumps the manifest's exception version, so other isolates discard their cached exception set on their next manifest refresh.

Upgrading Mappings Written Before 1.0.3

A mapping miss reads one KV key. Versions before 1.0.3 stored multi-key mappings in a second record that later versions no longer need, and probing for it doubled the cost of every miss. If your KV store still holds mappings from that era, enable the probe while you migrate:

initialize({ kv: env.KV, shards, legacyMultiKeyLookup: true });
Copy

Bulk KV Round Trips

Backends with a native multi-get or multi-set are used automatically when the adapter exposes them, which matters for multi-key mappings: those otherwise cost one round trip per lookup key. Redis and Valkey clients with mGet/mSet/unlink are detected at adapter construction; adapters without them fall back to concurrent single operations, so nothing needs checking at the call site.

import { createRedisKVProvider } from '@earth-app/collegedb';

const kv = createRedisKVProvider(redisClient);

// Present only when the client supports it
await kv.putMany?.([
	{ key: 'a', value: '1' },
	{ key: 'b', value: '2' }
]);
Copy

Workers KV Edge Caching

Shard mappings are close to write-once, so the Workers KV adapter asks for a one-hour edge cache by default rather than Cloudflare's 60-second default. Override it when reassignments must propagate faster, or pass 0 to use Cloudflare's default:

import { createWorkersKVProvider, initialize } from '@earth-app/collegedb';

initialize({
	kv: createWorkersKVProvider(env.KV, { cacheTtl: 300 }),
	shards: { 'db-east': env.DB_EAST }
});
Copy

Hyperdrive Connection Lifetime

The Hyperdrive adapters create one client per request and reuse it for every statement in that request, which is the pattern Cloudflare documents. A client cached in module scope throws in Workers, so either give the provider a way to identify the current request, or dispose it yourself.

Scoped, for a configuration shared across requests:

import { Client } from 'pg';
import { createHyperdrivePostgresProvider } from '@earth-app/collegedb';

let currentRequest: Request | undefined;

const shard = createHyperdrivePostgresProvider(env.HYPERDRIVE, (connectionString) => new Client({ connectionString }), {
	scope: () => currentRequest
});

export default {
	async fetch(request: Request, env: Env) {
		currentRequest = request; // a new request transparently gets a new client
		// ... handle the request
	}
};
Copy

Explicit, when you build the provider per request:

import { Client } from 'pg';
import { createHyperdrivePostgresProvider } from '@earth-app/collegedb';

export default {
	async fetch(request: Request, env: Env, ctx: ExecutionContext) {
		const shard = createHyperdrivePostgresProvider(env.HYPERDRIVE, (cs) => new Client({ connectionString: cs }));
		ctx.waitUntil(shard.dispose());
		// ... handle the request
	}
};
Copy

Hyperdrive does not support D1, which needs no connection pooling from Workers.

Unroutable Statements

The planner throws by default. Switch to a fanout when you would rather query every shard than handle the error, accepting that a typo in a column name becomes an N-shard query instead of a failure:

import { initialize, query } from '@earth-app/collegedb';

initialize({ kv: env.KV, shards, onUnroutable: 'fanout' });

// Routed on `id`
await query('SELECT * FROM users WHERE id = ?', ['user-1']);

// Not routable on a key, so this runs on every shard
await query('SELECT * FROM users WHERE email = ?', ['ada@example.com']);
Copy

To decide per call rather than globally, plan it yourself:

import { allAllShards, first, planQuery } from '@earth-app/collegedb';

const plan = planQuery(sql, bindings, { keyColumns: { tickets: 'ticket_id' } });

if (plan?.keys.length === 1) {
	await first(plan.keys[0]!, sql, bindings);
} else {
	await allAllShards(sql, bindings);
}
Copy

KV Cache and Secondary-Index Lookups

cached wraps a read with the configured KV store (TTL enforced in-band, so it works on any KV backend); invalidate clears a key prefix. setLookup / getLookup / deleteLookup maintain a namespaced secondary index (for example email hash -> customer id), distinct from the shard-routing lookup keys used by firstByLookupKey.

import { cached, invalidate, setLookup, getLookup, deleteLookup } from '@earth-app/collegedb';

const user = await cached(`user:${id}`, () => loadUser(id), { ttl: 3600 });
await invalidate('tickets:list:'); // after a write

await setLookup(emailHash, String(customerId));
const customerId = await getLookup(emailHash);
await deleteLookup(oldEmailHash);
Copy

Multi-Key Shard Mappings

CollegeDB supports multiple lookup keys for the same record, allowing you to query by username, email, ID, or any unique identifier. Keys are automatically hashed with SHA-256 for security and privacy.

import { collegedb, first, run, KVShardMapper } from '@earth-app/collegedb';

collegedb(
	{
		kv: env.KV,
		shards: { 'db-east': env.DB_EAST, 'db-west': env.DB_WEST },
		hashShardMappings: true, // Default: enabled for security
		strategy: 'hash'
	},
	async () => {
		// Create a user with multiple lookup keys
		const mapper = new KVShardMapper(env.KV, { hashShardMappings: true });

		await mapper.setShardMapping('user-123', 'db-east', ['username:john_doe', 'email:john@example.com', 'id:123']);

		// Now you can query by ANY of these keys
		const byId = await first('user-123', 'SELECT * FROM users WHERE id = ?', ['user-123']);
		const byUsername = await first('username:john_doe', 'SELECT * FROM users WHERE username = ?', ['john_doe']);
		const byEmail = await first('email:john@example.com', 'SELECT * FROM users WHERE email = ?', ['john@example.com']);

		// All queries route to the same shard (db-east)
		console.log('All queries find the same user:', byId?.name);
	}
);
Copy

Adding Lookup Keys to Existing Mappings

const mapper = new KVShardMapper(env.KV);

// User initially created with just ID
await mapper.setShardMapping('user-456', 'db-west');

// Later, add additional lookup methods
await mapper.addLookupKeys('user-456', ['email:jane@example.com', 'username:jane']);

// Now works with any key
const user = await first('email:jane@example.com', 'SELECT * FROM users WHERE email = ?', ['jane@example.com']);
Copy

Lookup-Key Read Helpers With Fanout Fallback

When you query by a secondary key and want safe behavior even when a lookup mapping is missing or stale, use the router-level helpers:

import { allByLookupKey, firstByLookupKey } from '@earth-app/collegedb';

// Uses lookup-key mapping when present, then falls back to all-shard fanout if needed
const user = await firstByLookupKey('email:john@example.com', 'SELECT * FROM users WHERE email = ? LIMIT 1', ['john@example.com']);

// Same resolution flow, but returns merged row sets
const matches = await allByLookupKey('username:john_doe', 'SELECT id, username FROM users WHERE username = ?', ['john_doe']);
Copy

This avoids accidentally creating a new primary-key mapping for secondary identifiers while still returning results when mappings are unavailable.

Security and Privacy

SHA-256 Hashing (Enabled by Default): Sensitive data like emails are hashed before being stored as KV keys, protecting user privacy:

// With hashShardMappings: true (default)
// KV stores: "shard:a1b2c3d4..." instead of "shard:email:user@example.com"

const config = {
	kv: env.KV,
	shards: {/* ... */},
	hashShardMappings: true, // Hashes keys with SHA-256
	strategy: 'hash'
};
Copy

⚠️ Performance Trade-off: When hashing is enabled, operations like getKeysForShard() cannot return original key names, only hashed versions. For full key recovery, disable hashing:

const config = {
	hashShardMappings: false // Disables hashing - keys stored in plain text
};
Copy

Multi-Key Management

const mapper = new KVShardMapper(env.KV);

// Get all lookup keys for a mapping
const allKeys = await mapper.getAllLookupKeys('email:user@example.com');
console.log(allKeys); // ['user-123', 'username:john', 'email:user@example.com']

// Update shard assignment (updates all keys)
await mapper.updateShardMapping('username:john', 'db-central');

// Delete mapping (removes all associated keys)
await mapper.deleteShardMapping('user-123');
Copy

Drop-in Replacement for Existing Databases

CollegeDB integrates with databases that already contain data. Add them as shards in the configuration; CollegeDB detects the existing rows and creates the shard mappings for them, with no manual migration step.

Requirements for Drop-in Replacement

1. Primary Keys: All tables must have a primary key column (typically named id)
2. Schema Compatibility: Tables should use standard SQLite data types
3. Access Permissions: CollegeDB needs read/write access to existing databases
4. KV Namespace: A Cloudflare KV namespace for storing shard mappings

import { collegedb, first, run } from '@earth-app/collegedb';

// Add your existing databases as shards - that's it!
collegedb(
	{
		kv: env.KV,
		shards: {
			'db-users': env.ExistingUserDB, // Your existing database with users
			'db-orders': env.ExistingOrderDB, // Your existing database with orders
			'db-new': env.NewDB // Optional new shard for growth
		},
		strategy: 'hash'
	},
	async () => {
		// Existing data works immediately!
		const existingUser = await first('user-from-old-db', 'SELECT * FROM users WHERE id = ?', ['user-from-old-db']);

		// New data gets distributed automatically
		await run('new-user-123', 'INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['new-user-123', 'New User', 'new@example.com']);
	}
);
Copy

That's it! No migration scripts, no manual mapping creation, no downtime. Your existing data is immediately accessible through CollegeDB's sharding system.

Manual Validation (Optional)

You can manually validate databases before integration if needed:

import { validateTableForSharding, listTables } from '@earth-app/collegedb';

// Check database structure
const tables = await listTables(env.ExistingDB);
console.log('Found tables:', tables);

// Validate each table
for (const table of tables) {
	const validation = await validateTableForSharding(env.ExistingDB, table);
	if (validation.isValid) {
		console.log(`✅ ${table}: ${validation.recordCount} records ready`);
	} else {
		console.log(`❌ ${table}: ${validation.issues.join(', ')}`);
	}
}
Copy

Manual Data Discovery (Optional)

If you want to inspect existing data before automatic migration:

import { discoverExistingPrimaryKeys } from '@earth-app/collegedb';

// Discover all user IDs in existing users table
const userIds = await discoverExistingPrimaryKeys(env.ExistingDB, 'users');
console.log(`Found ${userIds.length} existing users`);

// Custom primary key column
const orderIds = await discoverExistingPrimaryKeys(env.ExistingDB, 'orders', 'order_id');
Copy

Manual Integration (Optional)

For complete control over the integration process:

import { integrateExistingDatabase, KVShardMapper } from '@earth-app/collegedb';

const mapper = new KVShardMapper(env.KV);

// Integrate your existing database
const result = await integrateExistingDatabase(
	env.ExistingDB, // Your existing D1 database
	'db-primary', // Shard name for this database
	mapper, // KV mapper instance
	{
		tables: ['users', 'posts', 'orders'], // Tables to integrate
		primaryKeyColumn: 'id', // Primary key column name
		strategy: 'hash', // Allocation strategy for future records
		addShardMappingsTable: true, // Add CollegeDB metadata table
		dryRun: false // Set true for testing
	}
);

if (result.success) {
	console.log(`✅ Integrated ${result.totalRecords} records from ${result.tablesProcessed} tables`);
} else {
	console.error('Integration issues:', result.issues);
}
Copy

After integration, initialize CollegeDB with your existing databases as shards:

import { initialize, first } from '@earth-app/collegedb';

// Include existing databases as shards
initialize({
	kv: env.KV,
	coordinator: env.ShardCoordinator,
	shards: {
		'db-primary': env.ExistingDB, // Your integrated existing database
		'db-secondary': env.AnotherExistingDB, // Another existing database
		'db-new': env.NewDB // Optional new shard for growth
	},
	strategy: 'hash'
});

// Existing data is now automatically routed!
const user = await first('existing-user-123', 'SELECT * FROM users WHERE id = ?', ['existing-user-123']);
Copy

Complete Drop-in Example

The simplest possible integration - just add your existing databases:

import { initialize, first, run } from '@earth-app/collegedb';

export default {
	async fetch(request: Request, env: Env): Promise<Response> {
		// Step 1: Initialize with existing databases (automatic migration happens here!)
		initialize({
			kv: env.KV,
			shards: {
				'db-users': env.ExistingUserDB, // Your existing database with users
				'db-orders': env.ExistingOrderDB, // Your existing database with orders
				'db-new': env.NewDB // New shard for future growth
			},
			strategy: 'hash'
		});

		// Step 2: Use existing data immediately - no migration needed!
		// Supports typed queries, inserts, updates, deletes, etc.
		const existingUser = await first<User>('user-from-old-db', 'SELECT * FROM users WHERE id = ?', ['user-from-old-db']);

		// Step 3: New data gets distributed automatically
		await run('new-user-123', 'INSERT INTO users (id, name, email) VALUES (?, ?, ?)', ['new-user-123', 'New User', 'new@example.com']);

		return new Response(
			JSON.stringify({
				existingUser: existingUser.results[0],
				message: 'Automatic drop-in replacement successful!'
			})
		);
	}
};
Copy

Manual Integration Example

If your tables use different primary key column names:

// For tables with custom primary key columns
const productIds = await discoverExistingPrimaryKeys(env.ProductDB, 'products', 'product_id');
const sessionIds = await discoverExistingPrimaryKeys(env.SessionDB, 'sessions', 'session_key');
Copy

Integrate only specific tables from existing databases:

const result = await integrateExistingDatabase(env.ExistingDB, 'db-legacy', mapper, {
	tables: ['users', 'orders'] // Only integrate these tables
	// Skip 'temp_logs', 'cache_data', etc.
});
Copy

Test integration without making changes:

const testResult = await integrateExistingDatabase(env.ExistingDB, 'db-test', mapper, {
	dryRun: true // No actual mappings created
});

console.log(`Would process ${testResult.totalRecords} records from ${testResult.tablesProcessed} tables`);
Copy

Performance Impact

• One-time Setup: Migration detection runs once per shard
• Minimal Overhead: Only scans table metadata and sample records
• Cached Results: Subsequent operations have no migration overhead
• Async Processing: Doesn't block application startup or queries

// Simple rollback - clear all mappings
import { KVShardMapper } from '@earth-app/collegedb';
const mapper = new KVShardMapper(env.KV);
await mapper.clearAllMappings(); // Returns to pre-migration state

// Or clear cache to force re-detection
import { clearMigrationCache } from '@earth-app/collegedb';
clearMigrationCache(); // Forces fresh migration check
Copy

Troubleshooting

Tables without Primary Keys

// Error: Primary key column 'id' not found
// Solution: Add primary key to existing table
await db.prepare(`ALTER TABLE legacy_table ADD COLUMN id TEXT PRIMARY KEY`).run();
Copy

Large Database Integration

// For very large databases, integrate in batches
const allTables = await listTables(env.LargeDB);
const batchSize = 2;

for (let i = 0; i < allTables.length; i += batchSize) {
	const batch = allTables.slice(i, i + batchSize);
	await integrateExistingDatabase(env.LargeDB, 'db-large', mapper, {
		tables: batch
	});
}
Copy

Mixed Primary Key Types

// Handle different primary key column names per table
const customIntegration = {
	users: 'user_id',
	orders: 'order_number',
	products: 'sku'
};

for (const [table, pkColumn] of Object.entries(customIntegration)) {
	const keys = await discoverExistingPrimaryKeys(env.DB, table, pkColumn);
	await createMappingsForExistingKeys(keys, ['db-shard1'], 'hash', mapper);
}
Copy

Cross-Shard Pagination Behavior

allAllShards and firstAllShards execute the exact SQL on each shard independently. That means SQL LIMIT/OFFSET applies per shard, not globally.

// With two shards, this can return up to 20 total rows (10 per shard)
const perShard = await allAllShards('SELECT * FROM posts ORDER BY created_at DESC LIMIT 10');
Copy

If you need true global merge/sort/pagination across all shard results, use allAllShardsGlobal / firstAllShardsGlobal and pass sort/pagination options to the library:

import { allAllShardsGlobal, firstAllShardsGlobal } from '@earth-app/collegedb';

const page = await allAllShardsGlobal<{ id: string; created_at: number }>('SELECT id, created_at FROM posts', [], {
	sortBy: 'created_at',
	sortDirection: 'desc',
	offset: 20,
	limit: 10
});

const newest = await firstAllShardsGlobal<{ id: string; created_at: number }>('SELECT id, created_at FROM posts', [], {
	sortBy: 'created_at',
	sortDirection: 'desc'
});
Copy

Memory Ceiling

A global page merges and sorts in one isolate, so it holds every matching row from every shard at once. When sortBy names a column and the statement has no LIMIT, OFFSET, or set operator, CollegeDB appends LIMIT offset + limit to each shard query, which bounds that. A JavaScript filter or comparator, or includeTotal, can promote a row the bound would have discarded, so the rewrite does not apply in those cases and every matching row is fetched.

Workers cap an isolate at 128 MB. For a large result set, filter in SQL rather than in a filter callback, or page with count plus keyed ranges instead of a global sort.

Query Guidance

Use Library Utility Operations for DDL and Inspection

CollegeDB now exposes utility helpers for operational tasks that need shard awareness:

import { countAllShards, explainAllShards, getDatabaseSizesAllShards, indexAllShards } from '@earth-app/collegedb';

// Create index across all shards
await indexAllShards('posts', [{ name: 'user_id' }, { name: 'created_at', order: 'DESC' }], {
	ifNotExists: true
});

// Inspect query plan across all shards
const plans = await explainAllShards('SELECT * FROM posts WHERE user_id = ? ORDER BY created_at DESC LIMIT 20', ['user-123']);

// Count rows globally
const counts = await countAllShards('posts');

// Get per-shard size measurements
const sizes = await getDatabaseSizesAllShards();
Copy

Recommended pattern:

• Use indexAllShards for schema/index consistency.
• Use explain/explainAllShards before adding indexes or changing query shapes.
• Use countAllShards and getDatabaseSizesAllShards for operational dashboards and rebalancing thresholds.

Create Targeted Indexes

CREATE INDEX IF NOT EXISTS idx_posts_user_id_created_at ON posts(user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
Copy

Inspect Query Plans and Statistics

EXPLAIN QUERY PLAN SELECT * FROM posts WHERE user_id = ? ORDER BY created_at DESC LIMIT 20;
PRAGMA optimize;
ANALYZE;
Copy

Use Bound Parameters (SQL Injection Protection)

// Safe: parameterized query
await first('user-123', 'SELECT * FROM users WHERE email = ?', [email]);

// Avoid string interpolation with user input
// BAD: `... WHERE email = '${email}'`
Copy

Design Search for Scale

• Exact-match or prefix search fields should be indexed.
• Prefer bounded result sets (LIMIT) and stable sorting.
• For global search pages, pair lightweight per-shard SQL with allAllShardsGlobal for final merge/sort/pagination.

Pagination Guidance

• For routed single-key reads (first, all), SQL pagination is naturally shard-local and predictable.
• For fanout (allAllShards, firstAllShards), SQL pagination is per-shard.
• For user-facing global pages, use allAllShardsGlobal so offset/limit apply once after merge.

API Reference

Function	Description	Parameters
collegedb(config, callback)	Initialize CollegeDB, then run a callback	CollegeDBConfig, () => T
initialize(config)	Initialize CollegeDB with configuration	CollegeDBConfig
createSchema(db, schema)	Create schema on a shard database	SQLDatabase, string
prepare(key, sql)	Prepare a SQL statement for execution	string, string
run(key, sql, bindings)	Execute a SQL query with primary key routing	string, string, any[]
insert(sql, bindings)	Insert on an automatically selected shard and capture the generated id	string, any[]
insertShard(shard, sql, bindings)	Insert directly on a specific shard and capture the generated id	string, string, any[]
first(key, sql, bindings)	Execute a SQL query and return first result	string, string, any[]
all(key, sql, bindings)	Execute a SQL query and return all results	string, string, any[]
index(key, table, columns, options)	Create an index on routed shard	string, string, string or index-column array, CreateIndexOptions
indexShard(shard, table, columns, options)	Create an index on one shard	string, string, string or index-column array, CreateIndexOptions
indexAllShards(table, columns, options)	Create an index on all shards	string, string or index-column array, CreateIndexOptions
firstByLookupKey(key, sql, bindings, batchSize)	Resolve secondary-key mapping, fallback to fanout	string, string, any[], number
allByLookupKey(key, sql, bindings, batchSize)	Resolve secondary-key mapping, fallback to fanout	string, string, any[], number
runShard(shard, sql, bindings)	Execute a query directly on a specific shard	string, string, any[]
allShard(shard, sql, bindings)	Execute a query on specific shard, return all results	string, string, any[]
firstShard(shard, sql, bindings)	Execute a query on specific shard, return first result	string, string, any[]
explain(key, sql, bindings, options)	Inspect query plan on routed shard	string, string, any[], ExplainOptions
explainShard(shard, sql, bindings, options)	Inspect query plan on one shard	string, string, any[], ExplainOptions
explainAllShards(sql, bindings, options)	Inspect query plan on all shards	string, any[], ExplainOptions
count(key, table)	Count rows on routed shard	string, string
countShard(shard, table)	Count rows on a specific shard	string, string
countAllShards(table, batchSize)	Count rows per shard and global total	string, number
runAllShards(sql, bindings, batchSize)	Execute query on all shards	string, any[], number
allAllShards(sql, bindings, batchSize)	Execute query on all shards (SQL pagination applies per shard)	string, any[], number
firstAllShards(sql, bindings, batchSize)	Execute query on all shards, return first row per shard	string, any[], number
allAllShardsGlobal(sql, bindings, options)	Execute query on all shards, then globally merge/sort/paginate	string, any[], GlobalAllShardsOptions
firstAllShardsGlobal(sql, bindings, options)	Return first row after global merge/sort/paginate	string, any[], GlobalAllShardsOptions
reassignShard(key, newShard)	Move primary key to different shard	string, string
listKnownShards()	Get list of available shards	void
getShardStats()	Get statistics for all shards	void
getDatabaseSizeForKey(key)	Get size of key-routed shard in bytes	string
getDatabaseSizeForShard(shard)	Get size of a specific shard in bytes	string
getDatabaseSizesAllShards(batchSize)	Get per-shard size data	number
getTotalDatabaseSize(batchSize)	Get total size across all shards	number
flush()	Clear all shard mappings (development only)	void

Provider Adapter Functions

Function	Description	Parameters
createRedisKVProvider(client, options?)	Adapt a Redis client to CollegeDB's KVStorage contract	RedisLikeClient, { scanCount?: number }
createValkeyKVProvider(client, options?)	Adapt a Valkey client to CollegeDB's KVStorage contract	RedisLikeClient, { scanCount?: number }
createNuxtHubKVProvider(client)	Adapt NuxtHub/Unstorage-style KV clients to KVStorage	NuxtHubKVLike
createPostgreSQLProvider(client, sqlTag?)	Adapt PostgreSQL or Drizzle PostgreSQL clients	PostgresClientLike, sqlTag?
createMySQLProvider(client, sqlTag?)	Adapt MySQL/MariaDB or Drizzle MySQL/MariaDB clients	MySQLClientLike, sqlTag?
createSQLiteProvider(client, sqlTag?)	Adapt SQLite/D1 or Drizzle SQLite/D1 clients	SQLiteClientLike, sqlTag?
createDrizzleSQLProvider(client, sqlTag)	Generic Drizzle adapter (optional helper)	DrizzleClientLike, DrizzleSqlTagLike
createHyperdrivePostgresProvider(binding, clientFactory)	Create a PostgreSQL SQLDatabase adapter using a Hyperdrive binding	HyperdriveBindingLike, HyperdrivePostgresClientFactory
createHyperdriveMySQLProvider(binding, clientFactory)	Create a MySQL SQLDatabase adapter using a Hyperdrive binding	HyperdriveBindingLike, HyperdriveMySQLClientFactory
isKVStorage(value)	Runtime guard for KVStorage	unknown
isSQLDatabase(value)	Runtime guard for SQLDatabase	unknown

Drop-in Replacement Functions

Function	Description	Parameters
autoDetectAndMigrate(d1, shard, config)	Automatically detect and migrate existing data	SQLDatabase, string, config
checkMigrationNeeded(d1, shard, config)	Check if database needs migration	SQLDatabase, string, config
validateTableForSharding(d1, table)	Check if table is suitable for sharding	SQLDatabase, string
discoverExistingPrimaryKeys(d1, table)	Find all primary keys in existing table	SQLDatabase, string
integrateExistingDatabase(d1, shard)	Complete drop-in integration of existing DB	SQLDatabase, string, mapper
createMappingsForExistingKeys(keys)	Create shard mappings for existing keys	string[], string[], strategy
listTables(d1)	Get list of tables in database	SQLDatabase
clearMigrationCache()	Clear automatic migration cache	void

Error Handling

Class	Description	Usage
CollegeDBError	Custom error class for CollegeDB operations	throw new CollegeDBError(msg, code)

The CollegeDBError class extends the native Error class and includes an optional error code for better error categorization:

Code	Raised when
NOT_INITIALIZED	A routed helper ran before initialize
NO_SHARDS	No shards are configured, or none are eligible for allocation
SHARD_NOT_FOUND	A mapping names a shard this configuration does not hold
MAPPING_NOT_FOUND	reassignShard or updateShardMapping found no mapping for the key
QUERY_FAILED	The backend reported the statement as unsuccessful
GENERATED_KEY_UNAVAILABLE	An insert returned no recognizable id; pass idColumn
GENERATED_KEY_COLLISION	A generated id is already mapped to a different shard
UNROUTABLE_QUERY	The planner could not prove a routing key and onUnroutable is throw
SIZE_QUERY_FAILED	No sizing statement worked against the backend
INVALID_IDENTIFIER	A table or column name is not a bare SQL identifier
EMPTY_WHERE	A built UPDATE or DELETE had no WHERE conditions
KV_JSON_PARSE_FAILED	A KV value could not be parsed as JSON

try {
	await run('invalid-key', 'SELECT * FROM users WHERE id = ?', ['invalid-key']);
} catch (error) {
	if (error instanceof CollegeDBError) {
		console.error(`CollegeDB Error (${error.code}): ${error.message}`);
	}
}
Copy

ShardCoordinator (Durable Object) API

The ShardCoordinator is an optional Durable Object that provides centralized shard allocation and statistics management. All endpoints return JSON responses.

HTTP API Endpoints

Endpoint	Method	Description	Request Body	Response
/shards	GET	List all registered shards	None	["db-east", "db-west"]
/shards	POST	Register a new shard	{"shard": "db-new"}	{"success": true}
/shards	DELETE	Unregister a shard	{"shard": "db-old"}	{"success": true}
/stats	GET	Get shard statistics	None	[{"binding":"db-east","count":1542}]
/stats	POST	Update shard statistics	{"shard": "db-east", "count": 1600}	{"success": true}
/allocate	POST	Allocate shard for primary key	{"primaryKey": "user-123"}	{"shard": "db-west"}
/allocate	POST	Allocate with specific strategy	{"primaryKey": "user-123", "strategy": "hash"}	{"shard": "db-west"}
/sequence	POST	Allocate next atomic sequence id	{"name": "tickets", "min": 42}	{"value": 42}
/flush	POST	Clear all state (development only)	None	{"success": true}
/health	GET	Health check	None	"OK"

Programmatic Methods

Method	Description	Parameters	Returns
new ShardCoordinator(state)	Create coordinator instance	DurableObjectState	ShardCoordinator
fetch(request)	Handle HTTP requests	Request	Promise<Response>
incrementShardCount(shard)	Increment key count for shard	string	Promise<void>
decrementShardCount(shard)	Decrement key count for shard	string	Promise<void>

Usage Example

import { ShardCoordinator } from '@earth-app/collegedb';

// Export for Cloudflare Workers runtime
export { ShardCoordinator };

// Use in your worker
export default {
	async fetch(request: Request, env: Env) {
		const coordinatorId = env.ShardCoordinator.idFromName('default');
		const coordinator = env.ShardCoordinator.get(coordinatorId);

		// Allocate shard for user
		const response = await coordinator.fetch('http://coordinator/allocate', {
			method: 'POST',
			headers: { 'Content-Type': 'application/json' },
			body: JSON.stringify({ primaryKey: 'user-123', strategy: 'hash' })
		});

		const { shard } = await response.json();
		// Use allocated shard for database operations...
	}
};
Copy

Configuration Types

CollegeDBConfig

The main configuration interface supports both single strategies and mixed strategies:

interface CollegeDBConfig {
	kv: KVStorage;
	coordinator?: DurableObjectNamespace;
	shards: Record<string, SQLDatabase>;
	strategy?: ShardingStrategy | MixedShardingStrategy;
	targetRegion?: D1Region;
	shardLocations?: Record<string, ShardLocation>;
	disableAutoMigration?: boolean; // Default: false
	hashShardMappings?: boolean; // Default: true
	maxDatabaseSize?: number; // Default: undefined (no limit)
	mappingCacheTtlMs?: number; // Default: 30000
	knownShardsCacheTtlMs?: number; // Default: 10000
	sizeCacheTtlMs?: number; // Default: 30000
	migrationConcurrency?: number; // Default: 25
	placement?: 'computed' | 'kv'; // Default: 'kv'
	allocateOnRead?: boolean; // Default: false
	legacyMultiKeyLookup?: boolean; // Default: false
	keyColumns?: Record<string, string>; // Default: every table keyed on `id`
	onUnroutable?: 'throw' | 'fanout'; // Default: 'throw'
	onPhase?: (span: PhaseSpan) => void;
	waitUntil?: (promise: Promise<unknown>) => void;
}
Copy

Option	Purpose
placement	Resolve the shard by computing it rather than reading KV. hash strategy only
allocateOnRead	Record a mapping when a read finds no row
legacyMultiKeyLookup	Probe the pre-1.0.3 multi-key record on a miss, at the cost of a second KV read
keyColumns	Primary-key column per table, used by the planner to recover the routing key
onUnroutable	Whether an unprovable routing key throws or fans out
onPhase	Per-phase timing observer. Allocates nothing when unset
waitUntil	Keeps initialize's background work alive past the request that started it, on Workers

When hashShardMappings is enabled (default), original keys cannot be recovered during shard operations like getKeysForShard(). This is intentional for privacy but means you'll get fewer results from such operations. For full key recovery, set hashShardMappings: false, but be aware this may expose sensitive data in KV keys.

Strategy Types

// Single strategy for all operations
type ShardingStrategy = 'round-robin' | 'random' | 'hash' | 'location';

// Mixed strategy for different operation types
interface MixedShardingStrategy {
	read: ShardingStrategy; // Strategy for SELECT operations
	write: ShardingStrategy; // Strategy for INSERT/UPDATE/DELETE operations
}

// Operation types for internal routing
type OperationType = 'read' | 'write';
Copy

Example Configurations

// Single strategy configuration (traditional)
const singleStrategyConfig: CollegeDBConfig = {
	kv: env.KV,
	strategy: 'hash', // All operations use hash strategy
	shards: {/* ... */}
};

// Mixed strategy configuration (new feature)
const mixedStrategyConfig: CollegeDBConfig = {
	kv: env.KV,
	strategy: {
		read: 'hash', // Fast, consistent reads
		write: 'location' // Optimal data placement
	},
	targetRegion: 'wnam',
	shardLocations: {/* ... */},
	shards: {/* ... */}
};
Copy

Database Size Management

CollegeDB supports automatic size-based shard exclusion to prevent individual shards from becoming too large. This feature helps maintain optimal performance and prevents hitting database storage limits.

Configuration

const config: CollegeDBConfig = {
	kv: env.KV,
	shards: {
		'db-east': env.DB_EAST,
		'db-west': env.DB_WEST,
		'db-central': env.DB_CENTRAL
	},
	strategy: 'hash',
	maxDatabaseSize: 500 * 1024 * 1024 // 500 MB limit per shard
};
Copy

How It Works

When maxDatabaseSize is configured:

1. Allocation Phase: Before allocating new records, CollegeDB checks each shard's size using efficient SQLite pragmas
2. Size Filtering: Shards exceeding the limit are excluded from new allocations
3. Fallback Protection: If all shards exceed the limit, allocation continues to prevent complete failure
4. Existing Records: Records already mapped to oversized shards remain accessible

Size Check Implementation

The size check uses SQLite's PRAGMA page_count and PRAGMA page_size for accurate, low-overhead size calculation:

-- Efficient size calculation (used internally)
PRAGMA page_count;  -- Returns number of database pages
PRAGMA page_size;   -- Returns size of each page in bytes
-- Total size = page_count × page_size
Copy

Usage Examples

// Conservative limit for high-performance scenarios
const performanceConfig: CollegeDBConfig = {
	// ... other config
	maxDatabaseSize: 100 * 1024 * 1024, // 100 MB per shard
	strategy: 'round-robin' // Ensures even distribution
};

// Standard production limit
const productionConfig: CollegeDBConfig = {
	// ... other config
	maxDatabaseSize: 1024 * 1024 * 1024, // 1 GB per shard
	strategy: 'hash' // Consistent allocation
};

// Check individual shard sizes
import { getDatabaseSizeForShard } from '@earth-app/collegedb';

const eastSize = await getDatabaseSizeForShard('db-east');
console.log(`East shard: ${Math.round(eastSize / 1024 / 1024)} MB`);
Copy

Debug Logging

Enable debug logging to monitor size-based exclusions:

const config: CollegeDBConfig = {
	// ... other config
	maxDatabaseSize: 500 * 1024 * 1024,
	debug: true // Logs when shards are excluded due to size
};

// Console output example:
// "Excluded 2 shards due to size limits: db-east, db-central"
Copy

Size-Limit Performance Impact

• Size Check Frequency: Only performed during new allocations (not on reads)
• Query Efficiency: Uses fast SQLite pragmas (microsecond execution time)
• Parallel Execution: Size checks run concurrently across all shards
• Caching: Size checks are cached in-memory (controlled by sizeCacheTtlMs, default 30000)

Types

CollegeDB exports TypeScript types for better development experience and type safety:

Type	Description	Example
CollegeDBConfig	Main configuration object	{ kv, shards, strategy }
KVStorage	Provider-agnostic KV contract	createRedisKVProvider(redisClient)
SQLDatabase	Provider-agnostic SQL contract	createPostgreSQLProvider(pgPool)
NuxtHubKVLike	NuxtHub/Unstorage KV contract	createNuxtHubKVProvider(kv)
DrizzleClientLike	Minimal Drizzle DB contract	createPostgreSQLProvider(drizzleDb, sql)
DrizzleSqlTagLike	Drizzle SQL tag contract	createSQLiteProvider(drizzleDb, sql)
QueryResult	Standard query response shape	{ success, results, meta }
QueryResultMeta	Query execution metadata	{ duration, changes?, last_row_id? }
ShardingStrategy	Single strategy options	'hash' | 'location' | 'round-robin' | 'random'
MixedShardingStrategy	Mixed strategy configuration	{ read: 'hash', write: 'location' }
OperationType	Database operation types	'read' | 'write'
D1Region	Cloudflare D1 regions	'wnam' | 'enam' | 'weur' | ...
ShardLocation	Geographic shard configuration	{ region: 'wnam', priority: 2 }
ShardStats	Shard usage statistics	{ binding: 'db-east', count: 1542 }
IndexColumnDefinition	Index column definition	{ name: 'created_at', order: 'DESC' }
CreateIndexOptions	Index creation options	{ ifNotExists: true, unique: false }
ExplainOptions	Explain mode options	{ mode: 'query-plan' }
ShardTableCount	Per-shard row-count result	{ shard: 'db-east', count: 100, success: true }
ShardSizeResult	Per-shard size result	{ shard: 'db-east', size: 10485760, success: true }

Mixed Strategy Configuration

import type { MixedShardingStrategy, CollegeDBConfig } from '@earth-app/collegedb';

// Type-safe mixed strategy configuration
const mixedStrategy: MixedShardingStrategy = {
	read: 'hash', // Fast, deterministic reads
	write: 'location' // Geographically optimized writes
};

const config: CollegeDBConfig = {
	kv: env.KV,
	strategy: mixedStrategy, // Type-checked
	targetRegion: 'wnam',
	shardLocations: {
		'db-west': { region: 'wnam', priority: 2 },
		'db-east': { region: 'enam', priority: 1 }
	},
	shards: {
		'db-west': env.DB_WEST,
		'db-east': env.DB_EAST
	}
};
Copy

Architecture

┌─────────────────────────────────────────────────────────────┐
│                    Cloudflare Worker                        │
├─────────────────────────────────────────────────────────────┤
│                     CollegeDB Router                        │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │     KV      │  │  Durable    │  │   Query Router      │  │
│  │  Mappings   │  │  Objects    │  │                     │  │
│  │             │  │ (Optional)  │  │                     │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
├─────────────────────────────────────────────────────────────┤
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │   D1 East   │  │  D1 West    │  │    D1 Central       │  │
│  │   Shard     │  │   Shard     │  │     Shard           │  │
│  │             │  │             │  │   (Optional)        │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
└─────────────────────────────────────────────────────────────┘
Copy

Data Flow

Without ShardCoordinator (Hash/Random/Location strategies)

1. Query Received: Application sends query with primary key
2. Shard Resolution: CollegeDB checks KV for existing mapping or calculates shard using strategy
3. Direct Allocation: For new keys, shard selected using hash/random/location algorithm
4. Query Execution: SQL executed on appropriate D1 database
5. Response: Results returned to application

With ShardCoordinator (Round-Robin strategy)

1. Query Received: Application sends query with primary key
2. Shard Resolution: CollegeDB checks KV for existing mapping
3. Coordinator Allocation: For new keys, coordinator allocates shard using round-robin
4. State Update: Coordinator updates round-robin index and shard statistics
5. Query Execution: SQL executed on appropriate D1 database
6. Response: Results returned to application

ShardCoordinator Internal Flow

┌─────────────────────────────────────────────────────────────┐
│              ShardCoordinator (Durable Object)             │
├─────────────────────────────────────────────────────────────┤
│  ┌─────────────────┐  ┌─────────────────────────────────┐  │
│  │  HTTP API       │  │       Persistent Storage        │  │
│  │  - /allocate    │  │  - knownShards: string[]        │  │
│  │  - /shards      │  │  - shardStats: ShardStats{}     │  │
│  │  - /stats       │  │  - strategy: ShardingStrategy   │  │
│  │  - /health      │  │  - roundRobinIndex: number      │  │
│  └─────────────────┘  └─────────────────────────────────┘  │
│                                  │                         │
│  ┌─────────────────────────────────────────────────────┐   │
│  │           Allocation Algorithms                     │   │
│  │  - Round-Robin: state.roundRobinIndex               │   │
│  │  - Hash: consistent hash(primaryKey)                │   │
│  │  - Random: Math.random() * shards.length            │   │
│  │  - Location: region proximity + priority            │   │
│  └─────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────┘
Copy

Shard Allocation Strategies

• Hash: Consistent hashing for deterministic shard selection
• Round-Robin: Evenly distribute new keys across shards
• Random: Random shard selection for load balancing
• Location: Geographic proximity-based allocation for optimal latency

Cloudflare Setup

1. Create D1 Databases

# Create multiple D1 databases for sharding
wrangler d1 create collegedb-east
wrangler d1 create collegedb-west
wrangler d1 create collegedb-central
Copy

2. Create KV Namespace

# Create KV namespace for shard mappings
wrangler kv namespace create "KV"
Copy

3. Configure wrangler.jsonc

{
	"$schema": "./node_modules/wrangler/config-schema.json",
	"name": "collegedb-app",
	"main": "src/index.ts",
	"compatibility_date": "2026-04-15",
	"d1_databases": [
		{
			"binding": "db-east",
			"database_name": "collegedb-east",
			"database_id": "your-east-database-id"
		},
		{
			"binding": "db-west",
			"database_name": "collegedb-west",
			"database_id": "your-west-database-id"
		}
	],
	"kv_namespaces": [
		{
			"binding": "KV",
			"id": "your-kv-namespace-id",
			"preview_id": "your-kv-preview-id"
		}
	],
	"durable_objects": {
		"bindings": [
			{
				"name": "ShardCoordinator",
				"class_name": "ShardCoordinator"
			}
		]
	},
	"migrations": [
		{
			"tag": "v1",
			"new_sqlite_classes": ["ShardCoordinator"]
		}
	]
}
Copy

Complete wrangler.jsonc with ShardCoordinator

{
	"$schema": "./node_modules/wrangler/config-schema.json",
	"name": "collegedb-app",
	"main": "src/index.ts",
	"compatibility_date": "2026-04-15",
	"d1_databases": [
		{
			"binding": "db-east",
			"database_name": "collegedb-east",
			"database_id": "your-east-database-id"
		},
		{
			"binding": "db-west",
			"database_name": "collegedb-west",
			"database_id": "your-west-database-id"
		},
		{
			"binding": "db-central",
			"database_name": "collegedb-central",
			"database_id": "your-central-database-id"
		}
	],
	"kv_namespaces": [
		{
			"binding": "KV",
			"id": "your-kv-namespace-id",
			"preview_id": "your-kv-preview-id"
		}
	],
	"durable_objects": {
		"bindings": [
			{
				"name": "ShardCoordinator",
				"class_name": "ShardCoordinator"
			}
		]
	},
	"migrations": [
		{
			"tag": "v1",
			"new_sqlite_classes": ["ShardCoordinator"]
		}
	],
	"env": {
		"production": {
			"d1_databases": [
				{
					"binding": "db-east",
					"database_name": "collegedb-prod-east",
					"database_id": "your-prod-east-id"
				},
				{
					"binding": "db-west",
					"database_name": "collegedb-prod-west",
					"database_id": "your-prod-west-id"
				}
			],
			"kv_namespaces": [
				{
					"binding": "KV",
					"id": "your-prod-kv-namespace-id"
				}
			],
			"durable_objects": {
				"bindings": [
					{
						"name": "ShardCoordinator",
						"class_name": "ShardCoordinator"
					}
				]
			}
		}
	}
}
Copy

3.1. Worker Script Setup (Required for ShardCoordinator)

Create your main worker file with ShardCoordinator export:

// src/index.ts
import { collegedb, ShardCoordinator, first, run } from '@earth-app/collegedb';

// IMPORTANT: Export ShardCoordinator for Cloudflare Workers runtime
export { ShardCoordinator };

interface Env {
	KV: KVNamespace;
	ShardCoordinator: DurableObjectNamespace;
	'db-east': D1Database;
	'db-west': D1Database;
	'db-central': D1Database;
}

export default {
	async fetch(request: Request, env: Env): Promise<Response> {
		return await collegedb(
			{
				kv: env.KV,
				coordinator: env.ShardCoordinator, // Optional: only needed for round-robin
				strategy: 'hash', // or 'round-robin', 'random', 'location'
				shards: {
					'db-east': env['db-east'],
					'db-west': env['db-west'],
					'db-central': env['db-central']
				}
			},
			async () => {
				// Your application logic here
				const url = new URL(request.url);

				if (url.pathname === '/user') {
					const userId = url.searchParams.get('id');
					if (!userId) {
						return new Response('Missing user ID', { status: 400 });
					}

					const user = await first(userId, 'SELECT * FROM users WHERE id = ?', [userId]);
					return Response.json(user);
				}

				return new Response('CollegeDB API', { status: 200 });
			}
		);
	}
};
Copy

4. Deploy

# Deploy to Cloudflare Workers
wrangler deploy

# Deploy with environment
wrangler deploy --env production
Copy

Monitoring and Maintenance

Shard Statistics

Using CollegeDB Functions

import { getShardStats, listKnownShards } from '@earth-app/collegedb';

// Get detailed statistics
const stats = await getShardStats();
console.log(stats);
// [
//   { binding: 'db-east', count: 1542 },
//   { binding: 'db-west', count: 1458 }
// ]

// List available shards
const shards = await listKnownShards();
console.log(shards); // ['db-east', 'db-west']
Copy

Using ShardCoordinator Directly

// Get coordinator instance
const coordinatorId = env.ShardCoordinator.idFromName('default');
const coordinator = env.ShardCoordinator.get(coordinatorId);

// Get real-time shard statistics
const statsResponse = await coordinator.fetch('http://coordinator/stats');
const detailedStats = await statsResponse.json();
console.log(detailedStats);
/* Returns:
[
  {
    "binding": "db-east",
    "count": 1542,
    "lastUpdated": 1672531200000
  },
  {
    "binding": "db-west",
    "count": 1458,
    "lastUpdated": 1672531205000
  }
]
*/

// List registered shards
const shardsResponse = await coordinator.fetch('http://coordinator/shards');
const allShards = await shardsResponse.json();
console.log(allShards); // ['db-east', 'db-west', 'db-central']
Copy

Advanced Monitoring Dashboard

async function createMonitoringDashboard(env: Env) {
	const coordinatorId = env.ShardCoordinator.idFromName('default');
	const coordinator = env.ShardCoordinator.get(coordinatorId);

	// Get metrics
	const [shardsResponse, statsResponse, healthResponse] = await Promise.all([
		coordinator.fetch('http://coordinator/shards'),
		coordinator.fetch('http://coordinator/stats'),
		coordinator.fetch('http://coordinator/health')
	]);

	const shards = await shardsResponse.json();
	const stats = await statsResponse.json();
	const isHealthy = healthResponse.ok;

	// Calculate distribution metrics
	const totalKeys = stats.reduce((sum: number, shard: any) => sum + shard.count, 0);
	const avgKeysPerShard = totalKeys / stats.length;
	const maxKeys = Math.max(...stats.map((s: any) => s.count));
	const minKeys = Math.min(...stats.map((s: any) => s.count));
	const distributionRatio = maxKeys / (minKeys || 1);

	// Check for stale statistics (>5 minutes)
	const now = Date.now();
	const staleThreshold = 5 * 60 * 1000; // 5 minutes
	const staleShards = stats.filter((shard: any) => now - shard.lastUpdated > staleThreshold);

	return {
		healthy: isHealthy,
		totalShards: shards.length,
		totalKeys,
		avgKeysPerShard: Math.round(avgKeysPerShard),
		distributionRatio: Math.round(distributionRatio * 100) / 100,
		isBalanced: distributionRatio < 1.5, // Less than 50% difference
		staleShards: staleShards.length,
		shardDetails: stats.map((shard: any) => ({
			...shard,
			loadPercentage: Math.round((shard.count / totalKeys) * 100),
			isStale: now - shard.lastUpdated > staleThreshold
		}))
	};
}

// Usage in monitoring endpoint
export default {
	async fetch(request: Request, env: Env) {
		if (new URL(request.url).pathname === '/monitor') {
			const dashboard = await createMonitoringDashboard(env);
			return Response.json(dashboard);
		}
		// ... rest of your app
	}
};
Copy

Shard Rebalancing

import { reassignShard } from '@earth-app/collegedb';

// Move a primary key to a different shard
await reassignShard('user-123', 'db-west');
Copy

Health Monitoring

Monitor your CollegeDB deployment by tracking:

• Shard distribution balance
• Query latency per shard
• Error rates and failed queries
• KV operation metrics
• ShardCoordinator health and availability

Automated Health Checks

async function performHealthChecks(env: Env): Promise<HealthReport> {
	const results: HealthReport = {
		overall: 'healthy',
		timestamp: new Date().toISOString(),
		checks: {}
	};

	// 1. Test KV availability
	try {
		await env.KV.put('health-check', 'ok', { expirationTtl: 60 });
		const kvTest = await env.KV.get('health-check');
		results.checks.kv = kvTest === 'ok' ? 'healthy' : 'degraded';
	} catch (error) {
		results.checks.kv = 'unhealthy';
		results.overall = 'unhealthy';
	}

	// 2. Test ShardCoordinator availability
	if (env.ShardCoordinator) {
		try {
			const coordinatorId = env.ShardCoordinator.idFromName('default');
			const coordinator = env.ShardCoordinator.get(coordinatorId);
			const healthResponse = await coordinator.fetch('http://coordinator/health');
			results.checks.coordinator = healthResponse.ok ? 'healthy' : 'unhealthy';

			if (!healthResponse.ok) {
				results.overall = 'degraded';
			}
		} catch (error) {
			results.checks.coordinator = 'unhealthy';
			results.overall = 'degraded'; // Can fallback to hash allocation
		}
	}

	// 3. Test each D1 shard
	const shardTests = Object.entries(env)
		.filter(([key]) => key.startsWith('db-'))
		.map(async ([shardName, db]: [string, any]) => {
			try {
				// Simple query to test connectivity
				await db.prepare('SELECT 1 as test').first();
				results.checks[shardName] = 'healthy';
			} catch (error) {
				results.checks[shardName] = 'unhealthy';
				results.overall = 'unhealthy';
			}
		});

	await Promise.all(shardTests);

	// 4. Check shard distribution balance
	if (results.checks.coordinator === 'healthy') {
		try {
			const coordinatorId = env.ShardCoordinator.idFromName('default');
			const coordinator = env.ShardCoordinator.get(coordinatorId);
			const statsResponse = await coordinator.fetch('http://coordinator/stats');
			const stats = await statsResponse.json();

			const totalKeys = stats.reduce((sum: number, shard: any) => sum + shard.count, 0);
			if (totalKeys > 0) {
				const avgKeys = totalKeys / stats.length;
				const maxKeys = Math.max(...stats.map((s: any) => s.count));
				const distributionRatio = maxKeys / avgKeys;

				results.checks.distribution = distributionRatio < 2 ? 'healthy' : 'degraded';
				results.distributionRatio = distributionRatio;

				if (distributionRatio >= 3 && results.overall === 'healthy') {
					results.overall = 'degraded';
				}
			}
		} catch (error) {
			results.checks.distribution = 'unknown';
		}
	}

	return results;
}

interface HealthReport {
	overall: 'healthy' | 'degraded' | 'unhealthy';
	timestamp: string;
	checks: Record<string, 'healthy' | 'degraded' | 'unhealthy' | 'unknown'>;
	distributionRatio?: number;
}

// Health endpoint example
export default {
	async fetch(request: Request, env: Env) {
		if (new URL(request.url).pathname === '/health') {
			const health = await performHealthChecks(env);
			const statusCode = health.overall === 'healthy' ? 200 : health.overall === 'degraded' ? 206 : 503;
			return Response.json(health, { status: statusCode });
		}
		// ... rest of your app
	}
};
Copy

Alerting and Monitoring Integration

// Integration with external monitoring services
async function sendAlert(severity: 'warning' | 'critical', message: string, env: Env) {
	// Example: Slack webhook
	if (env.SLACK_WEBHOOK_URL) {
		await fetch(env.SLACK_WEBHOOK_URL, {
			method: 'POST',
			headers: { 'Content-Type': 'application/json' },
			body: JSON.stringify({
				text: `🚨 CollegeDB ${severity.toUpperCase()}: ${message}`,
				username: 'CollegeDB Monitor'
			})
		});
	}

	// Example: Custom webhook
	if (env.MONITORING_WEBHOOK_URL) {
		await fetch(env.MONITORING_WEBHOOK_URL, {
			method: 'POST',
			headers: { 'Content-Type': 'application/json' },
			body: JSON.stringify({
				service: 'collegedb',
				severity,
				message,
				timestamp: new Date().toISOString()
			})
		});
	}
}

// Scheduled monitoring (using Cron Triggers)
export default {
	async scheduled(event: ScheduledEvent, env: Env, ctx: ExecutionContext): Promise<void> {
		const health = await performHealthChecks(env);

		if (health.overall === 'unhealthy') {
			await sendAlert('critical', `System unhealthy: ${JSON.stringify(health.checks)}`, env);
		} else if (health.overall === 'degraded') {
			await sendAlert('warning', `System degraded: ${JSON.stringify(health.checks)}`, env);
		}

		// Check for severe shard imbalance
		if (health.distributionRatio && health.distributionRatio > 5) {
			await sendAlert('warning', `Severe shard imbalance detected: ${health.distributionRatio}x difference`, env);
		}
	}
};
Copy

Performance Analysis

Numbers come from the benchmark runner in scripts/sandbox/run.ts. Reports land in sandbox/results/, and CI publishes the full-matrix run.

Reading the Report

Section	What it answers
Where a Routed Operation Spends Its Time	Cost of hashing, each KV round trip, shard selection, and SQL execution, measured separately
SQL x KV (Overall)	Per-operation and per-scenario averages per backend pair
Core Scenario Latency	End-to-end latency per scenario
Strategy Key Distribution	How evenly each allocation strategy spreads keys

Use Per-Op Avg to compare backends. Overall Avg is the unweighted mean of the scenario averages, so bulk_crud at 400 routed operations per iteration dominates basic_crud at 4.

Measuring Your Own Deployment

Attach the phase observer to get the same breakdown for your own traffic:

import { PhaseCollector, initialize } from '@earth-app/collegedb';

const phases = new PhaseCollector();

initialize({
	kv: env.KV,
	shards: { 'db-east': env.DB_EAST, 'db-west': env.DB_WEST },
	strategy: 'hash',
	onPhase: phases.observer
});

// ... run some queries, then read the breakdown
console.table(phases.stats());
Copy

onPhase costs nothing when unset. Timings use performance.now(), so sub-millisecond phases are visible; Date.now() reports every one of them as 0 or 1.

What Costs What

Per routed operation, counted from the call graph rather than estimated:

Path	KV reads	KV writes	SQL round trips
Warm mapping cache	0	0	1
Cold cache, mapping exists	1	0	1
Key not yet mapped, write	1	1	1
Key not yet mapped, read	1	0	1
placement: 'computed'	0	0	1
nextId(), sequence seeded	0	0	0
nextId(), first call	0	0	N
batch() of M statements	1	1	one per shard
paginate()	0	0	N

N is the shard count. A read that finds no row no longer writes a mapping; set allocateOnRead: true to restore the old behavior.

Platform Limits

Cloudflare's own published limits shape what a deployment can do, independent of CollegeDB:

• Workers KV allows 1,000 writes per day on the free plan. One new primary key costs one KV write, so placement: 'computed' is the difference between a bounded and an unbounded number of new keys per day there.
• D1 allows 1,000 queries per Worker invocation on the paid plan and 50 on the free plan. Use batch() for bulk writes; one statement per row exceeds the free limit at 50 rows.
• Smart Placement helps when a request makes several sequential queries and the shards sit in one region. Cloudflare puts the per-query difference at 20-30 ms from a distant region against 1-3 ms when placed nearby. It does not help a one-query request or a genuinely geo-distributed shard set.

Fault Tolerance

A single database is a single point of failure. Across N shards, losing one affects 1/N of the data, and firstResilient falls back to a cross-shard scan when a routed read comes up empty.

When to Use CollegeDB

Worth it for:

• Datasets approaching a single database's size or throughput limits
• Write throughput beyond what one instance sustains
• Geographic placement of data
• Isolating failures to a fraction of the dataset

Not worth it for:

• Small datasets served fine by one database
• Workloads that mostly run cross-shard queries, which fan out to every shard
• Tables whose primary key is a database-generated sequence and which must span shards, unless ids come from nextId()

Advanced Configuration

Custom Allocation Strategy

initialize({
	kv: env.KV,
	shards: { 'db-east': env['db-east'], 'db-west': env['db-west'] },
	strategy: 'hash' // Shard selection based on primary key hash
});
Copy

Environment-Specific Setup

const config = {
	kv: env.KV,
	shards: env.NODE_ENV === 'production' ? { 'db-prod-1': env['db-prod-1'], 'db-prod-2': env['db-prod-2'] } : { 'db-dev': env['db-dev'] },
	strategy: 'round-robin' // Shard selection is evenly distributed, regardless of size
};

initialize(config);
Copy

Durable Objects Integration (ShardCoordinator)

CollegeDB includes an optional ShardCoordinator Durable Object that provides centralized shard allocation and statistics management. This is particularly useful for round-robin allocation strategies and monitoring shard utilization across your application.

Durable Object Setup

First, configure the Durable Object in your wrangler.jsonc:

{
	"durable_objects": {
		"bindings": [
			{
				"name": "ShardCoordinator",
				"class_name": "ShardCoordinator"
			}
		]
	},
	"migrations": [
		{
			"tag": "v1",
			"new_sqlite_classes": ["ShardCoordinator"]
		}
	]
}
Copy

Basic Usage with ShardCoordinator

import { collegedb, ShardCoordinator } from '@earth-app/collegedb';

// Export the Durable Object class for Cloudflare Workers
export { ShardCoordinator };

export default {
	async fetch(request: Request, env: Env): Promise<Response> {
		// Initialize CollegeDB with coordinator support
		await collegedb(
			{
				kv: env.KV,
				coordinator: env.ShardCoordinator, // Add coordinator binding
				strategy: 'round-robin',
				shards: {
					'db-east': env.DB_EAST,
					'db-west': env.DB_WEST,
					'db-central': env.DB_CENTRAL
				}
			},
			async () => {
				// Your application logic here
				const user = await first('user-123', 'SELECT * FROM users WHERE id = ?', ['user-123']);
				return Response.json(user);
			}
		);
	}
};
Copy

ShardCoordinator HTTP API

The ShardCoordinator exposes an HTTP API for managing shards and allocation:

Shard Management

// Get coordinator instance
const coordinatorId = env.ShardCoordinator.idFromName('default');
const coordinator = env.ShardCoordinator.get(coordinatorId);

// List all registered shards
const shardsResponse = await coordinator.fetch('http://coordinator/shards');
const shards = await shardsResponse.json();
// Returns: ["db-east", "db-west", "db-central"]

// Register a new shard
await coordinator.fetch('http://coordinator/shards', {
	method: 'POST',
	headers: { 'Content-Type': 'application/json' },
	body: JSON.stringify({ shard: 'db-new-region' })
});

// Remove a shard
await coordinator.fetch('http://coordinator/shards', {
	method: 'DELETE',
	headers: { 'Content-Type': 'application/json' },
	body: JSON.stringify({ shard: 'db-old-region' })
});
Copy

Statistics and Monitoring

// Get shard statistics
const statsResponse = await coordinator.fetch('http://coordinator/stats');
const stats = await statsResponse.json();
/* Returns:
[
  {
    "binding": "db-east",
    "count": 1542,
    "lastUpdated": 1672531200000
  },
  {
    "binding": "db-west",
    "count": 1458,
    "lastUpdated": 1672531205000
  }
]
*/

// Update shard statistics manually
await coordinator.fetch('http://coordinator/stats', {
	method: 'POST',
	headers: { 'Content-Type': 'application/json' },
	body: JSON.stringify({
		shard: 'db-east',
		count: 1600
	})
});
Copy

Shard Allocation

// Allocate a shard for a primary key
const allocationResponse = await coordinator.fetch('http://coordinator/allocate', {
	method: 'POST',
	headers: { 'Content-Type': 'application/json' },
	body: JSON.stringify({
		primaryKey: 'user-123',
		strategy: 'round-robin' // Optional, uses coordinator default if not specified
	})
});

const { shard } = await allocationResponse.json();
// Returns: { "shard": "db-west" }

// Hash-based allocation (consistent for same key)
const hashAllocation = await coordinator.fetch('http://coordinator/allocate', {
	method: 'POST',
	headers: { 'Content-Type': 'application/json' },
	body: JSON.stringify({
		primaryKey: 'user-456',
		strategy: 'hash'
	})
});
Copy

Health Check and Development

// Health check endpoint
const healthResponse = await coordinator.fetch('http://coordinator/health');
// Returns: "OK" with 200 status

// Clear all coordinator state (DEVELOPMENT ONLY!)
await coordinator.fetch('http://coordinator/flush', {
	method: 'POST'
});
// WARNING: This removes all shard registrations and statistics
Copy

Programmatic Shard Management

The ShardCoordinator also provides methods for direct programmatic access:

// Get coordinator instance
const coordinatorId = env.ShardCoordinator.idFromName('default');
const coordinator = env.ShardCoordinator.get(coordinatorId);

// Increment shard count (when adding new keys)
await coordinator.incrementShardCount('db-east');

// Decrement shard count (when removing keys)
await coordinator.decrementShardCount('db-west');
Copy

Advanced Monitoring Setup

Monitor your shard distribution:

async function monitorShardHealth(env: Env) {
	const coordinatorId = env.ShardCoordinator.idFromName('default');
	const coordinator = env.ShardCoordinator.get(coordinatorId);

	// Get current statistics
	const statsResponse = await coordinator.fetch('http://coordinator/stats');
	const stats = await statsResponse.json();

	// Calculate distribution balance
	const totalKeys = stats.reduce((sum: number, shard: any) => sum + shard.count, 0);
	const avgKeysPerShard = totalKeys / stats.length;

	// Check for imbalanced shards (>20% deviation from average)
	const imbalancedShards = stats.filter((shard: any) => {
		const deviation = Math.abs(shard.count - avgKeysPerShard) / avgKeysPerShard;
		return deviation > 0.2;
	});

	if (imbalancedShards.length > 0) {
		console.warn('Shard imbalance detected:', imbalancedShards);
		// Trigger rebalancing logic or alerts
	}

	// Check for stale statistics (>1 hour old)
	const now = Date.now();
	const staleShards = stats.filter((shard: any) => {
		return now - shard.lastUpdated > 3600000; // 1 hour in ms
	});

	if (staleShards.length > 0) {
		console.warn('Stale shard statistics detected:', staleShards);
	}

	return {
		totalKeys,
		avgKeysPerShard,
		balance: imbalancedShards.length === 0,
		freshStats: staleShards.length === 0,
		shards: stats
	};
}
Copy

Error Handling with ShardCoordinator

When using the ShardCoordinator, ensure you handle potential errors gracefully:

try {
	const coordinator = env.ShardCoordinator.get(coordinatorId);
	const response = await coordinator.fetch('http://coordinator/allocate', {
		method: 'POST',
		headers: { 'Content-Type': 'application/json' },
		body: JSON.stringify({ primaryKey: 'user-123' })
	});

	if (!response.ok) {
		const error = await response.json();
		throw new Error(`ShardCoordinator error: ${error.error}`);
	}

	const { shard } = await response.json();
	return shard;
} catch (error) {
	console.error('Failed to allocate shard:', error);
	// Fallback to hash-based allocation without coordinator
	return hashFunction('user-123', availableShards);
}
Copy

Performance Considerations

• Coordinator Latency: Round-robin allocation adds ~10-20ms latency due to coordinator communication
• Scalability: Single coordinator instance can handle thousands of allocations per second
• Fault Tolerance: Design fallback allocation strategies when coordinator is unavailable
• Caching: Consider caching allocation results for frequently accessed keys

// Fallback allocation when coordinator is unavailable
function fallbackAllocation(primaryKey: string, shards: string[]): string {
	// Use hash-based allocation as fallback
	const hash = simpleHash(primaryKey);
	return shards[hash % shards.length];
}

async function allocateWithFallback(coordinator: DurableObjectNamespace, primaryKey: string, shards: string[]): Promise<string> {
	try {
		const coordinatorId = coordinator.idFromName('default');
		const instance = coordinator.get(coordinatorId);

		const response = await instance.fetch('http://coordinator/allocate', {
			method: 'POST',
			headers: { 'Content-Type': 'application/json' },
			body: JSON.stringify({ primaryKey })
		});

		if (response.ok) {
			const { shard } = await response.json();
			return shard;
		}
	} catch (error) {
		console.warn('Coordinator unavailable, using fallback allocation:', error);
	}

	// Fallback to hash-based allocation
	return fallbackAllocation(primaryKey, shards);
}
Copy

Quick Reference

Strategy Selection Guide

Strategy	Use Case	Latency	Distribution	Coordinator Required
hash	High-volume apps, consistent performance	Lowest	Excellent	No
round-robin	Guaranteed even distribution	Medium	Perfect	Yes
random	Simple setup, good enough distribution	Low	Good	No
location	Geographic optimization, reduced latency	Region-optimized	Good	No
mixed	Optimized read/write performance	Strategy-dependent	Variable	Strategy-dependent

Mixed Strategy Recommendations by Scenario

Scenario	Recommended Mix	Read Strategy	Write Strategy	Benefits
Large Databases (>10M records)	{read: 'hash', write: 'round-robin'}	Hash	Round-Robin	Fastest reads, even data distribution
Global Applications	{read: 'hash', write: 'location'}	Hash	Location	Fast queries, optimal geographic placement
High Write Volume	{read: 'location', write: 'hash'}	Location	Hash	Regional read optimization, fast write routing
Analytics Workloads	{read: 'random', write: 'location'}	Random	Location	Load-balanced queries, optimal data placement
Multi-Tenant SaaS	{read: 'hash', write: 'hash'}	Hash	Hash	Consistent performance, predictable routing

Configuration Templates

Hash Strategy (Recommended for most apps):

{
  kv: env.KV,
  strategy: 'hash',
  shards: { 'db-1': env.DB_1, 'db-2': env.DB_2 }
}
Copy

Location Strategy (Geographic optimization):

{
  kv: env.KV,
  strategy: 'location',
  targetRegion: 'wnam',
  shardLocations: {
    'db-west': { region: 'wnam', priority: 2 },
    'db-east': { region: 'enam', priority: 1 }
  },
  shards: { 'db-west': env.DB_WEST, 'db-east': env.DB_EAST }
}
Copy

Round-Robin Strategy (Even distribution):

{
  kv: env.KV,
  coordinator: env.ShardCoordinator,
  strategy: 'round-robin',
  shards: { 'db-1': env.DB_1, 'db-2': env.DB_2, 'db-3': env.DB_3 }
}
Copy

Mixed Strategy (Global applications):

{
  kv: env.KV,
  strategy: {
    read: 'hash',      // Fast, consistent reads
    write: 'location'  // Optimal geographic placement
  },
  targetRegion: 'wnam',
  shardLocations: {
    'db-west': { region: 'wnam', priority: 2 },
    'db-east': { region: 'enam', priority: 1 }
  },
  shards: { 'db-west': env.DB_WEST, 'db-east': env.DB_EAST }
}
Copy

Mixed Strategy (Large databases):

{
  kv: env.KV,
  coordinator: env.ShardCoordinator,
  strategy: {
    read: 'hash',         // Fastest possible reads
    write: 'round-robin'  // Perfect distribution
  },
  shards: { 'db-1': env.DB_1, 'db-2': env.DB_2, 'db-3': env.DB_3 }
}
Copy

Mixed Strategy (High-performance consistent):

{
  kv: env.KV,
  strategy: {
    read: 'hash',   // Predictable read performance
    write: 'hash'   // Predictable write performance
  },
  shards: { 'db-1': env.DB_1, 'db-2': env.DB_2 }
}
Copy

Region Codes Reference

Code	Region	Typical Location
wnam	Western North America	San Francisco
enam	Eastern North America	New York
weur	Western Europe	London
eeur	Eastern Europe	Berlin
apac	Asia Pacific	Tokyo
oc	Oceania	Sydney
me	Middle East	Dubai
af	Africa	Johannesburg

Contributing

1. Fork the repository
2. Create a feature branch: git checkout -b feature/amazing-feature
3. Commit changes: git commit -m 'Add amazing feature'
4. Push to branch: git push origin feature/amazing-feature
5. Submit a pull request

License

This project is licensed under the MIT License - see the LICENSE file for details.

Links

• Cloudflare D1 Documentation
• Cloudflare KV Documentation
• Cloudflare Workers Documentation
• Durable Objects Documentation

Support

• 📖 Documentation
• 🐛 Report Issues
• 💬 Discussions