How toCreate a Per-Request Database Instance with Middleware

Some database clients need explicit lifecycle management. You might need to open a connection at the start of a request and close it at the end, or you want to wrap all database operations in a transaction that commits on success and rolls back on error.

React Router middleware runs code both before and after your loaders and actions execute, making it the perfect place to handle this setup and teardown pattern. If you're new to middleware, see the middleware basics first.

Create the Database Context

app/context/database.ts
import { createContext } from "react-router";
import type { Database } from "~/lib/database";

export let databaseContext = createContext<Database>();

The context holds a typed reference to your database instance. Using createContext ensures type safety when getting and setting the value.

Build the Middleware with Cleanup

app/middleware/database.ts
import type { MiddlewareFunction } from "react-router";
import { databaseContext } from "~/context/database";
import { createDatabase } from "~/lib/database";

function createDatabaseMiddleware(): MiddlewareFunction<Response> {
	return async function databaseMiddleware({ context }, next) {
		let db = createDatabase();

		try {
			context.set(databaseContext, db);
			return await next();
		} finally {
			await db.close(); // or waitUntil(db.close()) if supported by your runtime
		}
	};
}

export const databaseMiddleware = createDatabaseMiddleware();

The middleware creates a fresh database instance, stores it in context, then calls next() to run the rest of the request. The finally block ensures db.close() runs whether the request succeeds or throws an error.

This pattern guarantees cleanup even when a loader throws, a validation fails, or an unexpected error occurs.

Add the Middleware to Routes

app/root.tsx
import { databaseMiddleware } from "~/middleware/database";

export let middleware = [databaseMiddleware];

export async function loader({ context }: Route.LoaderArgs) {
	let db = context.get(databaseContext);
	let users = await db.query("SELECT * FROM users");
	return { users };
}

Export the middleware array from any route that needs database access. The middleware runs before the loader, so context.get(databaseContext) always returns a valid instance.

For a related pattern that creates instances without cleanup logic, see how to create a per-request singleton with middleware.

Apply to All Routes

Most likely you want every route to have database access. Instead of adding the middleware to each route, add it to the root layout route so it applies to all routes:

app/root.tsx
import { databaseMiddleware } from "~/middleware/database";

export let middleware = [databaseMiddleware];

// ... rest of the root route code

Wrapping the middleware at the root means every request gets a database instance automatically. You can still add additional middleware to specific routes if needed, and they will run in the correct order.

Wrap Requests in a Transaction

app/middleware/transaction.ts
import type { MiddlewareFunction } from "react-router";
import { databaseContext } from "~/context/database";
import { createDatabase } from "~/lib/database";

function createTransactionMiddleware(): MiddlewareFunction<Response> {
	return async function transactionMiddleware({ context }, next) {
		let db = createDatabase();
		try {
			return await db.transaction((tx) => {
				context.set(databaseContext, tx);
				return next();
			});
		} finally {
			await db.close();
		}
	};
}

export const transactionMiddleware = createTransactionMiddleware();

This variants wraps next in a transaction and stores the tx object in the database context. If anything throws, the transaction rolls back automatically. The finally block still closes the connection regardless of the outcome.

Use this when you need atomic operations across multiple database calls within a single request.

Access the Database in Actions

app/routes/users.new.tsx
import { redirect } from "react-router";
import { databaseContext } from "~/context/database";
import { transactionMiddleware } from "~/middleware/transaction";

export let middleware = [transactionMiddleware];

export async function action({ request, context }: Route.ActionArgs) {
	let db = context.get(databaseContext);
	let formData = await request.formData();

	let user = await db.query("INSERT INTO users (name) VALUES (?)", [formData.get("name")]);

	await db.query("INSERT INTO audit_log (action, user_id) VALUES (?, ?)", [
		"user_created",
		user.id,
	]);

	return redirect(`/users/${user.id}`);
}

Both inserts happen within the same transaction. If the audit log insert fails, the user creation rolls back too. The redirect only happens after a successful commit.

This lifecycle pattern works for any resource that needs setup and teardown: database connections, external API clients with session tokens, or temporary files that need cleanup. To ensure your middleware behaves correctly, learn how to test middleware.