[ Data & storage ]

@sdxc/database-middleware

Middleware that publishes a remix/data-table database as ctx.db on a request, a job or a command

Installs with: @sdxc/result
Used by: uptime, auth-saas, blog
Source: packages/database-middleware

View as Markdown Open in GitHub Open in ChatGPT Open in Claude

Middleware that publishes a remix/data-table database as ctx.db on a request, a job or a command.

Installation

npm add @sdxc/database-middleware

It runs on a remix/router middleware chain, so remix installs alongside it. The ./jobs entry point runs on an @sdxc/jobs dispatcher; install that package when you use it. The ./cli entry point runs on an @sdxc/cli command line; install that package when you use it.

Usage

Publish the database on requests

import { database } from "@sdxc/database-middleware";
import { Database, sql } from "remix/data-table";
import { createRouter } from "remix/router";

let db = new Database(adapter);
let router = createRouter({ middleware: [database(db)] });

router.get("/posts", async (ctx) => {
	let posts = await ctx.db.exec(sql`select * from posts`);
	return Response.json(posts.rows);
});

database(db) stands in for a context key and a middleware setting it:

import type { Database } from "remix/data-table";
import type { Middleware } from "remix/router";

import { createContextKey } from "remix/router";

let DatabaseKey = createContextKey<Database>();

function publishDatabase(db: Database): Middleware {
	return (ctx, next) => {
		ctx.set(DatabaseKey, db, { property: "db" });
		return next();
	};
}

Read it by key

Code that receives a context without knowing which middleware ran reads the same instance through the Database key:

import type { RequestContext } from "remix/router";

import { Database } from "@sdxc/database-middleware";
import { sql } from "remix/data-table";

function countPosts(ctx: RequestContext) {
	return ctx.get(Database).exec(sql`select count(*) as total from posts`);
}

Publish it on jobs

@sdxc/database-middleware/jobs exports a database() for a job dispatcher, under the same key, so a job handler reads ctx.db the way a route does:

import { database } from "@sdxc/database-middleware/jobs";
import { createJobDispatcher } from "@sdxc/jobs";

let dispatcher = createJobDispatcher({ middleware: [database(db)] as const });

Publish it on a command line, with a SQL prompt

@sdxc/database-middleware/cli exports a database() for an @sdxc/cli command line, under the same key, and a db command to mount in your own contract under any name:

import { commands, createCLI } from "@sdxc/cli";
import { DB_COMMAND, database, dbHandler } from "@sdxc/database-middleware/cli";

let contract = commands({ db: DB_COMMAND, users: USERS_COMMANDS });

let cli = createCLI({
	name: "app",
	commands: contract,
	middleware: [database(() => openDatabase())],
});
cli.map(contract.db, dbHandler);

process.exitCode = await cli.run(process.argv.slice(2));

app db                                  # sql> prompt until .exit, .quit or Ctrl+C
app db --execute "SELECT id, email FROM users"
# id  email
# --  ---------------
# 1   ada@example.com
# (1 row)

API

`database(db: Database): Middleware`

Returns router middleware that sets db as ctx.db and under the Database key on every request it runs for. A router derived from the middleware list types ctx.db, and importing the package adds db to remix/router's RequestContext.

`database(db: Database | (() => Database)): JobMiddleware` from `./jobs`

Returns job middleware that sets the database as ctx.db and under the Database key on every job run. A function is called once per run, so each run reads the instance it returns.

`database(db: Database | (() => Database)): CommandMiddleware` from `./cli`

Returns command middleware that sets the database as ctx.db and under the Database key on every command run. A function is called once per run that reaches a handler, so --help opens nothing. Importing the entry adds db to @sdxc/cli's CommandContext, so a later middleware's callback reads ctx.db.

`DB_COMMAND` and `dbHandler` from `./cli`

The db command's declaration, to place in a contract under any name, and the action to map() onto it. With --execute <sql> (-e) it runs that one statement and exits, failing with the database's error; without it, a sql> prompt runs each statement until .exit, .quit or Ctrl+C, printing errors and carrying on. A read prints its rows as aligned columns with a row count, and a write prints its affected count. Without a terminal, it runs the SQL piped into it (app db < query.sql) the same way, and ends at once when nothing was piped. It reads the database database() published and fails when none was.

`Database`

The context key every middleware publishes under; ctx.get(Database) returns the same object as ctx.db. Every entry point exports it.

Pattern: In-memory database in tests

The middleware publishes whichever instance it is given, so a test builds the real router over an in-memory SQLite database.

import { DatabaseSync } from "node:sqlite";

import { database } from "@sdxc/database-middleware";
import { sql } from "remix/data-table";
import { createSqliteDatabase } from "remix/data-table/sqlite";
import { createRouter } from "remix/router";

let db = createSqliteDatabase(new DatabaseSync(":memory:"));
let router = createRouter({ middleware: [database(db)] });

router.get("/answer", async (ctx) => {
	let result = await ctx.db.exec(sql`select 41 + 1 as answer`);
	return Response.json(result.rows);
});

let response = await router.fetch("https://example.com/answer");

Pattern: One database per job run

A long-lived dispatcher takes a function, so each run opens the database its source returns, and a test swaps in a fresh one between deliveries.

import { DatabaseSync } from "node:sqlite";

import { database } from "@sdxc/database-middleware/jobs";
import { createJobDispatcher, createJobHandler, job, jobs } from "@sdxc/jobs";
import { sql } from "remix/data-table";
import { createSqliteDatabase } from "remix/data-table/sqlite";

let JOBS = jobs({ sweep: job({ cron: "0 0 * * *" }) });

let current = createSqliteDatabase(new DatabaseSync(":memory:"));

let dispatcher = createJobDispatcher({ middleware: [database(() => current)] as const });

dispatcher.map(JOBS.sweep, async () => ({
	default: createJobHandler(JOBS.sweep, async (ctx) => {
		await ctx.db.exec(sql`delete from sessions where expires_at < ${Date.now()}`);
	}),
}));

await dispatcher.deliver({ id: "m1", attempts: 1, body: { type: "sweep" } });
current = createSqliteDatabase(new DatabaseSync(":memory:"));
await dispatcher.deliver({ id: "m2", attempts: 1, body: { type: "sweep" } });

Pattern: One instance per request

A Worker whose database binding arrives with each request builds the router per request and hands the middleware the instance opened for it.

import { database } from "@sdxc/database-middleware";
import { Database } from "remix/data-table";
import { createRouter } from "remix/router";

export default {
	fetch(request: Request, env: Env) {
		let db = new Database(createAdapter(env.DB));
		let router = createRouter({ middleware: [database(db)] });
		return router.fetch(request);
	},
};

Pattern: Feeding later middleware

A middleware later in the chain reads ctx.db when it runs, so @sdxc/data-model binds its models to the published database.

import { database } from "@sdxc/database-middleware";
import { models } from "@sdxc/data-model/router";
import { Database } from "remix/data-table";
import { createRouter } from "remix/router";

let router = createRouter({
	middleware: [database(new Database(adapter)), models(registry, (ctx) => ({ db: ctx.db }))],
});