@earth-app/collegedb
    Preparing search index...

    @earth-app/collegedb

    CollegeDB

    Horizontal sharding router for SQL databases

    TypeScript GitHub Issues GitHub License NPM Version

    CollegeDB splits one logical table across many databases. It routes each key to the shard that owns it, either through a mapping store or by computing the shard from the key. It works over Cloudflare D1, PostgreSQL, MySQL, MariaDB and SQLite, directly or through Drizzle ORM, and runs on Cloudflare Workers, Node and Bun.

    Any shard database works with any mapping store. A deployment can also mix shard databases from different vendors.

    Role Backend Adapter
    Shard database Cloudflare D1 A D1 binding, or toProvider
    Shard database PostgreSQL createPostgreSQLProvider
    Shard database MySQL and MariaDB createMySQLProvider
    Shard database SQLite createSQLiteProvider
    Shard database Drizzle ORM over the above The matching adapter, with the Drizzle sql tag
    Shard database Hyperdrive (PostgreSQL, MySQL) createHyperdrivePostgresProvider and createHyperdriveMySQLProvider
    Shard database In-memory, for tests createInMemorySQLProvider
    Mapping store Workers KV createWorkersKVProvider
    Mapping store Redis createRedisKVProvider
    Mapping store Valkey createValkeyKVProvider
    Mapping store NuxtHub KV createNuxtHubKVProvider
    Mapping store In-memory, for tests createInMemoryKVProvider
    Coordinator Durable Object ShardCoordinator, optional and available on Cloudflare Workers only

    Providers lists what each adapter takes and which ones support atomic batches and shard moves.

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

    CollegeDB has no runtime dependencies. Install the client for your backend, such as pg, mysql2, redis or drizzle-orm. TypeScript 5.8 or later is the supported compiler. The declaration files import types from @cloudflare/workers-types, so add it as a dev dependency in a TypeScript project.

    createClient takes the mapping store, the shards and a strategy. run and first take the routing key before the SQL, so the insert and the read for user-1 reach the same shard. The example uses the in-memory providers so it runs without a database. Each snippet after it swaps in a real backend and leaves the calls unchanged.

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

    const client = createClient({
    	kv: createInMemoryKVProvider(),
    	shards: {
    		'shard-a': createInMemorySQLProvider(),
    		'shard-b': createInMemorySQLProvider()
    	},
    	strategy: 'hash'
    });

    await client.ensureSchema('CREATE TABLE IF NOT EXISTS users (id TEXT PRIMARY KEY, name TEXT NOT NULL)');

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

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

    Cloudflare Workers with two D1 databases and a Workers KV namespace:

    import type { D1Database, KVNamespace } from '@cloudflare/workers-types';
    import { createClient, createWorkersKVProvider, type CollegeDBClient } from '@earth-app/collegedb';

    interface Env {
    	KV: KVNamespace;
    	DB_EAST: D1Database;
    	DB_WEST: D1Database;
    }

    let client: CollegeDBClient | undefined;

    export default {
    	async fetch(request: Request, env: Env): Promise<Response> {
    		client ??= createClient({
    			kv: createWorkersKVProvider(env.KV),
    			shards: { 'db-east': env.DB_EAST, 'db-west': env.DB_WEST },
    			strategy: 'hash'
    		});

    		const id = new URL(request.url).searchParams.get('id') ?? '';
    		const user = await client.first(id, 'SELECT id, name FROM users WHERE id = ?', [id]);
    		return Response.json(user);
    	}
    };

    Node with two PostgreSQL pools and Redis:

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

    const redis = createRedisClient({ url: process.env.REDIS_URL });
    await redis.connect();

    const client = createClient({
    	kv: createRedisKVProvider(redis),
    	shards: {
    		'pg-a': createPostgreSQLProvider(new Pool({ connectionString: process.env.PG_A_URL })),
    		'pg-b': createPostgreSQLProvider(new Pool({ connectionString: process.env.PG_B_URL }))
    	},
    	strategy: 'hash'
    });

    Getting Started covers a first setup in full, and Providers has an example for each backend.

     application
     |  run(key, sql, bindings)
     v
 +-----------+   known key?   +-----------------------------+
 |  router   |--------------->|  mapping store              |
 |           |<---------------|  Workers KV, Redis, Valkey, |
 +-----------+   shard name   |  NuxtHub KV or in-memory    |
   |     |                    +-----------------------------+
   |     | new key
   |     +--> strategy: hash, round-robin, random or location
   |          (optional coordinator allocates instead)
   v
 +---------+    +---------+    +---------+
 | shard a |    | shard b |    | shard c |   D1, PostgreSQL, MySQL, ...
 +---------+    +---------+    +---------+

    Every keyed call carries a routing key. The router looks the key up in the mapping store, which holds the shard name for each key written so far. A key with no mapping gets a shard from the configured strategy, and a write records the result. With placement: 'computed', the hash strategy derives the shard from the key, and the mapping store holds only keys that were moved. The optional coordinator is a Durable Object that allocates new keys for every strategy, which makes round-robin exact across Workers isolates. Architecture describes the read and write paths.

    • Sharding strategies: hash, round-robin, random and location, with separate strategies for reads and writes. Sharding
    • Computed placement: rendezvous hashing finds a key's shard without a mapping lookup, and topology epochs keep existing keys resolvable when shards change. Sharding
    • Crash-safe shard moves: reassignShard, rebalance and recoverMigrations move rows through a journal, and a recovery lease lets one recovery run at a time. Shard Moves
    • Read consistency: a per-call consistency option selects primary, session or eventual reads. Reads and Writes
    • Shard eligibility: shardState and an eligible predicate keep draining, read-only or out-of-jurisdiction shards out of new allocations. Sharding
    • Instance clients: createClient gives each configuration its own state, so tenants and tests do not share caches. Getting Started
    • Batched and transactional writes: batch() groups statements by shard and sends one round trip per shard, inside one transaction where the adapter supports it. Reads and Writes
    • Routing without a key argument: query, queryFirst and queryAll take the routing key from the statement. Reads and Writes
    • Generated ids: insert, insertShard and nextId handle keys that the database assigns. Generated Ids
    • Drop-in integration: existing databases join as shards, and integration records a mapping for each existing key. Drop-in
    • In-memory providers: emulated shards and a mapping store for unit tests and local development. Testing

    A strategy picks the shard for a new key. After that, reads and writes follow the recorded mapping. strategy also takes an object with a read and a write strategy.

    Strategy New key goes to
    hash The eligible shard with the highest rendezvous score
    round-robin The next eligible shard in order
    random A random eligible shard
    location The eligible shard nearest targetRegion

    hash is the default and the only strategy that supports computed placement. round-robin splits keys exactly only through a coordinator. Sharding covers how to choose one.

    The API reference is generated from the source at earth-app.github.io/CollegeDB.

    Page Covers
    Getting Started A first setup, instance clients, one-call setup on Workers and CRUD helpers
    Providers Adapters, mapping stores, batch and journal support, Hyperdrive
    Configuration Every CollegeDBConfig field, its default and example configurations
    Sharding Strategies, computed placement, eligibility, size limits and lookup keys
    Reads and Writes Read helpers, consistency, routing without a key and batched writes
    Generated Ids Database-assigned keys and how rows with them are routed
    Shard Moves reassignShard, rebalance, recovery, topology epochs and reconciliation
    Migration Protocol The contract for moving one keyed record between shards
    Drop-in Adding CollegeDB in front of databases that already hold data
    Query Guidance Cross-shard pagination, indexes, query plans and SQL habits for fanout
    Architecture Components, the read and write paths and the coordinator
    Coordinator The ShardCoordinator Durable Object, its endpoints and its failure modes
    Cloudflare Bindings, wrangler.jsonc, the Worker entry point and operations
    Testing In-memory providers, unit and integration tests and the memory sandbox
    Benchmarks The sandbox benchmark runner and measuring your own deployment
    API Reference Every exported function, grouped by purpose, and every error code

    A ShardCoordinator Durable Object written by 1.3.x copies its state into its SQLite storage on its first requests, and Coordinator describes the dual-write window and POST /retire-legacy. Since 1.4.0, reassignShard, recoverMigrations and a rebalance that is not a dry run need journalShard on a multi-shard configuration, as Shard Moves explains.

    Fork the repository, create a branch, and open a pull request against master. CI runs Prettier, the TypeScript check and the tests. bun run prettier:check, bun run typecheck and bun run test run the same checks locally.

    CollegeDB is released under the MIT License. See the LICENSE file for details.

    Settings
    Member Visibility

    On This Page
    CollegeDB