Databases

Database per Tenant

Give every tenant its own SQLite database by running one Rivet Actor per tenant, with tables and migrations scoped to that tenant.

See the full example on GitHub. View on GitHub

The usual way to build a multi-tenant app is one shared database with a tenant_id column on every table, and every query has to remember to filter on it. With Rivet Actors you can give each tenant a database of its own instead.

What we’ll build:

A companyDatabase Actor per tenant, keyed by the company name, that owns all of that tenant’s data.
A SQLite database inside each Actor, with employees and projects tables and a migration that runs per tenant.
Actions that read and write the tables with SQL, and events that push every new row to the tenant’s open dashboards.
A React dashboard that switches tenants by switching the Actor key.

Install

npm add rivetkit @rivetkit/react

Create the tenant Actor

Each tenant is its own companyDatabase Actor:

import { actor, event, setup } from "rivetkit";
import { db } from "rivetkit/db";

export type Employee = {
	id: number;
	name: string;
	role: string;
	created_at: number;
};

export type ProjectStatus = "planning" | "active" | "done";

export type Project = {
	id: number;
	name: string;
	status: ProjectStatus;
	created_at: number;
};

export type CompanyStats = {
	employee_count: number;
	project_count: number;
	created_at: number;
	updated_at: number;
};

export const companyDatabase = actor({
	// Every tenant's Actor has its own SQLite database: https://rivet.dev/docs/actors/sqlite
	db: db({
		onMigrate: async (db) => {
			await db.execute(`
				CREATE TABLE IF NOT EXISTS company (
					id INTEGER PRIMARY KEY CHECK (id = 1),
					name TEXT NOT NULL,
					created_at INTEGER NOT NULL,
					updated_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS employees (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					role TEXT NOT NULL,
					created_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS projects (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					status TEXT NOT NULL CHECK (status IN ('planning', 'active', 'done')),
					created_at INTEGER NOT NULL
				)
			`);
		},
	}),

	// Runs once, the first time this tenant's Actor is created: https://rivet.dev/docs/actors/lifecycle
	onCreate: async (c) => {
		const now = Date.now();
		await c.db.execute(
			"INSERT INTO company (id, name, created_at, updated_at) VALUES (1, ?, ?, ?)",
			getCompanyName(c.key[0]),
			now,
			now,
		);
	},

	events: {
		employeeAdded: event<Employee>(),
		projectAdded: event<Project>(),
	},

	// Callable functions from clients: https://rivet.dev/docs/actors/actions
	actions: {
		listEmployees: (c) =>
			c.db.execute<Employee>(
				"SELECT id, name, role, created_at FROM employees ORDER BY id",
			),

		listProjects: (c) =>
			c.db.execute<Project>(
				"SELECT id, name, status, created_at FROM projects ORDER BY id",
			),

		getStats: async (c) => {
			const [stats] = await c.db.execute<CompanyStats>(`
				SELECT
					(SELECT COUNT(*) FROM employees) AS employee_count,
					(SELECT COUNT(*) FROM projects) AS project_count,
					created_at,
					updated_at
				FROM company
			`);
			return stats;
		},

		addEmployee: async (c, name: string, role: string) => {
			const now = Date.now();
			// Insert the row and bump updated_at together, or not at all
			const employee = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Employee>(
						"INSERT INTO employees (name, role, created_at) VALUES (?, ?, ?) RETURNING id, name, role, created_at",
						name.trim() || "New Employee",
						role.trim() || "Generalist",
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-employee" },
			);
			// Send events to all connected clients: https://rivet.dev/docs/actors/events
			c.broadcast("employeeAdded", employee);
			return employee;
		},

		addProject: async (c, name: string, status: ProjectStatus) => {
			const now = Date.now();
			const project = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Project>(
						"INSERT INTO projects (name, status, created_at) VALUES (?, ?, ?) RETURNING id, name, status, created_at",
						name.trim() || "New Project",
						status,
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-project" },
			);
			c.broadcast("projectAdded", project);
			return project;
		},
	},
});

// Register actors and start the server: https://rivet.dev/docs/setup
export const registry = setup({
	use: { companyDatabase },
});

registry.start();

const getCompanyName = (key: unknown) => {
	if (typeof key === "string" && key.trim()) {
		return key.trim();
	}

	return "Unknown Company";
};

The key is the tenant id. companyDatabase["Acme"] always reaches the same Actor, and two tenants can never share one. Reaching another tenant’s data means building the wrong key, not forgetting a WHERE clause.
Keys are arrays, so the tenant id is passed as [tenantId] and never concatenated into a string such as "tenant:" + tenantId. There is nothing to escape.
onCreate runs once, the first time a tenant’s Actor is created, after the tables exist. It reads the tenant id from c.key[0] and inserts the single company row that tracks when the tenant’s database was created and last updated. See Lifecycle.
setup({ use: { companyDatabase } }) registers the Actor, and registry.start() serves it on port 6420.

Define the schema and migration

db({ onMigrate }) gives every companyDatabase Actor its own SQLite database and creates the tables in it:

import { actor, event, setup } from "rivetkit";
import { db } from "rivetkit/db";

export type Employee = {
	id: number;
	name: string;
	role: string;
	created_at: number;
};

export type ProjectStatus = "planning" | "active" | "done";

export type Project = {
	id: number;
	name: string;
	status: ProjectStatus;
	created_at: number;
};

export type CompanyStats = {
	employee_count: number;
	project_count: number;
	created_at: number;
	updated_at: number;
};

export const companyDatabase = actor({
	// Every tenant's Actor has its own SQLite database: https://rivet.dev/docs/actors/sqlite
	db: db({
		onMigrate: async (db) => {
			await db.execute(`
				CREATE TABLE IF NOT EXISTS company (
					id INTEGER PRIMARY KEY CHECK (id = 1),
					name TEXT NOT NULL,
					created_at INTEGER NOT NULL,
					updated_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS employees (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					role TEXT NOT NULL,
					created_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS projects (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					status TEXT NOT NULL CHECK (status IN ('planning', 'active', 'done')),
					created_at INTEGER NOT NULL
				)
			`);
		},
	}),

	// Runs once, the first time this tenant's Actor is created: https://rivet.dev/docs/actors/lifecycle
	onCreate: async (c) => {
		const now = Date.now();
		await c.db.execute(
			"INSERT INTO company (id, name, created_at, updated_at) VALUES (1, ?, ?, ?)",
			getCompanyName(c.key[0]),
			now,
			now,
		);
	},

	events: {
		employeeAdded: event<Employee>(),
		projectAdded: event<Project>(),
	},

	// Callable functions from clients: https://rivet.dev/docs/actors/actions
	actions: {
		listEmployees: (c) =>
			c.db.execute<Employee>(
				"SELECT id, name, role, created_at FROM employees ORDER BY id",
			),

		listProjects: (c) =>
			c.db.execute<Project>(
				"SELECT id, name, status, created_at FROM projects ORDER BY id",
			),

		getStats: async (c) => {
			const [stats] = await c.db.execute<CompanyStats>(`
				SELECT
					(SELECT COUNT(*) FROM employees) AS employee_count,
					(SELECT COUNT(*) FROM projects) AS project_count,
					created_at,
					updated_at
				FROM company
			`);
			return stats;
		},

		addEmployee: async (c, name: string, role: string) => {
			const now = Date.now();
			// Insert the row and bump updated_at together, or not at all
			const employee = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Employee>(
						"INSERT INTO employees (name, role, created_at) VALUES (?, ?, ?) RETURNING id, name, role, created_at",
						name.trim() || "New Employee",
						role.trim() || "Generalist",
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-employee" },
			);
			// Send events to all connected clients: https://rivet.dev/docs/actors/events
			c.broadcast("employeeAdded", employee);
			return employee;
		},

		addProject: async (c, name: string, status: ProjectStatus) => {
			const now = Date.now();
			const project = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Project>(
						"INSERT INTO projects (name, status, created_at) VALUES (?, ?, ?) RETURNING id, name, status, created_at",
						name.trim() || "New Project",
						status,
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-project" },
			);
			c.broadcast("projectAdded", project);
			return project;
		},
	},
});

// Register actors and start the server: https://rivet.dev/docs/setup
export const registry = setup({
	use: { companyDatabase },
});

registry.start();

const getCompanyName = (key: unknown) => {
	if (typeof key === "string" && key.trim()) {
		return key.trim();
	}

	return "Unknown Company";
};

The database belongs to the Actor, and the Actor belongs to the tenant, so every tenant has its own tables. No table has a tenant_id column.
onMigrate runs every time the Actor starts, before it serves any action, so every statement uses CREATE TABLE IF NOT EXISTS. RivetKit wraps it in a savepoint: if it throws, all of its SQL rolls back and the Actor does not start.
Migrations run per tenant. Each tenant’s database is migrated when its Actor next starts, instead of one large migration against a shared database. To change the schema later, add steps that are safe to run again, or use numbered migrations as shown in Versions.
The schema enforces its own rules. company can only hold the row with id = 1, and projects.status must be planning, active, or done.

SQLite fits tenant data that is relational, grows over time, or needs queries. For a small dataset per tenant that always fits in memory, plain state is simpler: the same one Actor per tenant model works, but there are no tables or queries. If you want a typed schema and generated migration files, use SQLite with Drizzle on the same database.

Read and write with SQL

The actions are plain SQL against the tenant’s tables:

import { actor, event, setup } from "rivetkit";
import { db } from "rivetkit/db";

export type Employee = {
	id: number;
	name: string;
	role: string;
	created_at: number;
};

export type ProjectStatus = "planning" | "active" | "done";

export type Project = {
	id: number;
	name: string;
	status: ProjectStatus;
	created_at: number;
};

export type CompanyStats = {
	employee_count: number;
	project_count: number;
	created_at: number;
	updated_at: number;
};

export const companyDatabase = actor({
	// Every tenant's Actor has its own SQLite database: https://rivet.dev/docs/actors/sqlite
	db: db({
		onMigrate: async (db) => {
			await db.execute(`
				CREATE TABLE IF NOT EXISTS company (
					id INTEGER PRIMARY KEY CHECK (id = 1),
					name TEXT NOT NULL,
					created_at INTEGER NOT NULL,
					updated_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS employees (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					role TEXT NOT NULL,
					created_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS projects (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					status TEXT NOT NULL CHECK (status IN ('planning', 'active', 'done')),
					created_at INTEGER NOT NULL
				)
			`);
		},
	}),

	// Runs once, the first time this tenant's Actor is created: https://rivet.dev/docs/actors/lifecycle
	onCreate: async (c) => {
		const now = Date.now();
		await c.db.execute(
			"INSERT INTO company (id, name, created_at, updated_at) VALUES (1, ?, ?, ?)",
			getCompanyName(c.key[0]),
			now,
			now,
		);
	},

	events: {
		employeeAdded: event<Employee>(),
		projectAdded: event<Project>(),
	},

	// Callable functions from clients: https://rivet.dev/docs/actors/actions
	actions: {
		listEmployees: (c) =>
			c.db.execute<Employee>(
				"SELECT id, name, role, created_at FROM employees ORDER BY id",
			),

		listProjects: (c) =>
			c.db.execute<Project>(
				"SELECT id, name, status, created_at FROM projects ORDER BY id",
			),

		getStats: async (c) => {
			const [stats] = await c.db.execute<CompanyStats>(`
				SELECT
					(SELECT COUNT(*) FROM employees) AS employee_count,
					(SELECT COUNT(*) FROM projects) AS project_count,
					created_at,
					updated_at
				FROM company
			`);
			return stats;
		},

		addEmployee: async (c, name: string, role: string) => {
			const now = Date.now();
			// Insert the row and bump updated_at together, or not at all
			const employee = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Employee>(
						"INSERT INTO employees (name, role, created_at) VALUES (?, ?, ?) RETURNING id, name, role, created_at",
						name.trim() || "New Employee",
						role.trim() || "Generalist",
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-employee" },
			);
			// Send events to all connected clients: https://rivet.dev/docs/actors/events
			c.broadcast("employeeAdded", employee);
			return employee;
		},

		addProject: async (c, name: string, status: ProjectStatus) => {
			const now = Date.now();
			const project = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Project>(
						"INSERT INTO projects (name, status, created_at) VALUES (?, ?, ?) RETURNING id, name, status, created_at",
						name.trim() || "New Project",
						status,
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-project" },
			);
			c.broadcast("projectAdded", project);
			return project;
		},
	},
});

// Register actors and start the server: https://rivet.dev/docs/setup
export const registry = setup({
	use: { companyDatabase },
});

registry.start();

const getCompanyName = (key: unknown) => {
	if (typeof key === "string" && key.trim()) {
		return key.trim();
	}

	return "Unknown Company";
};

c.db.execute<Employee>(...) returns the rows as an array of Employee objects, ordered by id. getStats counts both tables with subqueries and reads the timestamps from the company row.
Every query uses ? placeholders, so names and roles from the client never end up inside the SQL string.
Each write runs inside c.db.transaction. The INSERT ... RETURNING and the UPDATE of company.updated_at either both happen or neither does. A project with an unknown status fails the CHECK constraint, the transaction rolls back, and the caller gets an error. The static name groups the transaction in SQLite profiling.
The Actor is the only writer to its tenant’s database, so the writes need no locks, and AUTOINCREMENT gives each tenant its own id sequence.
Queries only ever see one tenant. A query across tenants, such as an admin report, needs a separate path: call each tenant’s Actor with actor-to-actor calls, or copy the rows you need into a shared analytics store.

listEmployees and listProjects return every row with no limit, and the actions only trim their input. In production, add LIMIT with pagination, cap the length of names and roles, and check each caller’s role before addEmployee and addProject, not only when they connect. See Permissions.

Broadcast changes

After each write commits, the Actor pushes the new row to every dashboard that has the tenant open:

import { actor, event, setup } from "rivetkit";
import { db } from "rivetkit/db";

export type Employee = {
	id: number;
	name: string;
	role: string;
	created_at: number;
};

export type ProjectStatus = "planning" | "active" | "done";

export type Project = {
	id: number;
	name: string;
	status: ProjectStatus;
	created_at: number;
};

export type CompanyStats = {
	employee_count: number;
	project_count: number;
	created_at: number;
	updated_at: number;
};

export const companyDatabase = actor({
	// Every tenant's Actor has its own SQLite database: https://rivet.dev/docs/actors/sqlite
	db: db({
		onMigrate: async (db) => {
			await db.execute(`
				CREATE TABLE IF NOT EXISTS company (
					id INTEGER PRIMARY KEY CHECK (id = 1),
					name TEXT NOT NULL,
					created_at INTEGER NOT NULL,
					updated_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS employees (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					role TEXT NOT NULL,
					created_at INTEGER NOT NULL
				)
			`);
			await db.execute(`
				CREATE TABLE IF NOT EXISTS projects (
					id INTEGER PRIMARY KEY AUTOINCREMENT,
					name TEXT NOT NULL,
					status TEXT NOT NULL CHECK (status IN ('planning', 'active', 'done')),
					created_at INTEGER NOT NULL
				)
			`);
		},
	}),

	// Runs once, the first time this tenant's Actor is created: https://rivet.dev/docs/actors/lifecycle
	onCreate: async (c) => {
		const now = Date.now();
		await c.db.execute(
			"INSERT INTO company (id, name, created_at, updated_at) VALUES (1, ?, ?, ?)",
			getCompanyName(c.key[0]),
			now,
			now,
		);
	},

	events: {
		employeeAdded: event<Employee>(),
		projectAdded: event<Project>(),
	},

	// Callable functions from clients: https://rivet.dev/docs/actors/actions
	actions: {
		listEmployees: (c) =>
			c.db.execute<Employee>(
				"SELECT id, name, role, created_at FROM employees ORDER BY id",
			),

		listProjects: (c) =>
			c.db.execute<Project>(
				"SELECT id, name, status, created_at FROM projects ORDER BY id",
			),

		getStats: async (c) => {
			const [stats] = await c.db.execute<CompanyStats>(`
				SELECT
					(SELECT COUNT(*) FROM employees) AS employee_count,
					(SELECT COUNT(*) FROM projects) AS project_count,
					created_at,
					updated_at
				FROM company
			`);
			return stats;
		},

		addEmployee: async (c, name: string, role: string) => {
			const now = Date.now();
			// Insert the row and bump updated_at together, or not at all
			const employee = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Employee>(
						"INSERT INTO employees (name, role, created_at) VALUES (?, ?, ?) RETURNING id, name, role, created_at",
						name.trim() || "New Employee",
						role.trim() || "Generalist",
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-employee" },
			);
			// Send events to all connected clients: https://rivet.dev/docs/actors/events
			c.broadcast("employeeAdded", employee);
			return employee;
		},

		addProject: async (c, name: string, status: ProjectStatus) => {
			const now = Date.now();
			const project = await c.db.transaction(
				async (tx) => {
					const [row] = await tx.execute<Project>(
						"INSERT INTO projects (name, status, created_at) VALUES (?, ?, ?) RETURNING id, name, status, created_at",
						name.trim() || "New Project",
						status,
						now,
					);
					await tx.execute("UPDATE company SET updated_at = ?", now);
					return row;
				},
				{ name: "add-project" },
			);
			c.broadcast("projectAdded", project);
			return project;
		},
	},
});

// Register actors and start the server: https://rivet.dev/docs/setup
export const registry = setup({
	use: { companyDatabase },
});

registry.start();

const getCompanyName = (key: unknown) => {
	if (typeof key === "string" && key.trim()) {
		return key.trim();
	}

	return "Unknown Company";
};

events declares employeeAdded and projectAdded as typed events carrying the same Employee and Project rows the list actions return.
c.broadcast runs after the transaction has committed, so a row other clients have seen is already saved. The action also returns the row to the caller.
A broadcast only reaches the connections of this tenant’s Actor. Dashboards open on another company never receive it.

Connect the dashboard

The React dashboard connects to the Actor for the signed-in company, loads its data, then applies events as they arrive:

const { useActor } = createRivetKit<typeof registry>("http://localhost:6420");

type CompanyDashboardProps = {
	companyName: string;
	onSwitch: (companyName: string | null) => void;
};

function CompanyDashboard({ companyName, onSwitch }: CompanyDashboardProps) {
	// The company name is the Actor key, so each tenant reaches its own Actor and database
	const company = useActor({
		name: "companyDatabase",
		key: [companyName],
	});

	const [employees, setEmployees] = useState<Employee[]>([]);
	const [projects, setProjects] = useState<Project[]>([]);
	const [stats, setStats] = useState<CompanyStats | null>(null);
	const [employeeName, setEmployeeName] = useState("");
	const [employeeRole, setEmployeeRole] = useState("");
	const [projectName, setProjectName] = useState("");
	const [projectStatus, setProjectStatus] =
		useState<ProjectStatus>("planning");
	const [switchName, setSwitchName] = useState(companyName);

	useEffect(() => {
		setSwitchName(companyName);
	}, [companyName]);

	const refreshStats = async () => {
		if (!company.connection) return;
		const latestStats = await company.connection.getStats();
		setStats(latestStats);
	};

	useEffect(() => {
		let canceled = false;
		setEmployees([]);
		setProjects([]);
		setStats(null);

		const loadData = async () => {
			if (!company.connection) return;
			const [employeeRows, projectRows, latestStats] = await Promise.all([
				company.connection.listEmployees(),
				company.connection.listProjects(),
				company.connection.getStats(),
			]);

			if (canceled) return;
			setEmployees(employeeRows);
			setProjects(projectRows);
			setStats(latestStats);
		};

		loadData();

		return () => {
			canceled = true;
		};
	}, [company.connection]);

	company.useEvent("employeeAdded", (employee: Employee) => {
		setEmployees((prev) => [...prev, employee]);
		void refreshStats();
	});

	company.useEvent("projectAdded", (project: Project) => {
		setProjects((prev) => [...prev, project]);
		void refreshStats();
	});

	const companyInitials = useMemo(
		() => getInitials(companyName),
		[companyName],
	);

	const handleAddEmployee = async (event: FormEvent) => {
		event.preventDefault();
		if (!company.connection) return;
		await company.connection.addEmployee(employeeName, employeeRole);
		setEmployeeName("");
		setEmployeeRole("");
	};

	const handleAddProject = async (event: FormEvent) => {
		event.preventDefault();
		if (!company.connection) return;
		await company.connection.addProject(projectName, projectStatus);
		setProjectName("");
	};

createRivetKit<typeof registry> returns hooks typed against the backend. See the React client.
useActor({ name: "companyDatabase", key: [companyName] }) connects to that company’s Actor and creates it, with its database, if it does not exist yet. Switching companies connects to a different Actor, so the whole dataset swaps.
Once company.connection is ready, the dashboard calls listEmployees, listProjects, and getStats together. After that, useEvent appends each new row and refreshes the stats.
addEmployee and addProject only wait for the action. The new row shows up through the same event that every other open dashboard receives.

In the example, the visitor types a company name and that string becomes the Actor key, so anyone can read and write any tenant’s data. In production, the tenant id must come from your auth, never from the client:

Derive the tenant id from a verified credential, such as a claim in a JWT.
In onBeforeConnect or createConnState, check that the credential’s tenant matches c.key[0] and reject the connection if it does not. Keep the verified user in connection state for per-action checks.
Never build a key from one tenant’s input to reach another tenant’s Actor.

Try it

git clone https://github.com/rivet-dev/rivet.git
cd rivet/examples/per-tenant-database
pnpm install
pnpm dev

Open http://localhost:5173, continue as one company, and add a few employees and projects. Switch to another company name and the dashboard is empty, because that company has its own Actor and its own database. Open the same company in a second window, add an employee in one, and it appears in both.

pnpm test runs the example’s test, which writes to companyDatabase["Alpha Co"] and companyDatabase["Beta Co"] and checks that neither sees the other’s rows.

Next steps

SQLite: queries, transactions, and migrations in the Actor’s database.
Authentication: verify who connects before they reach a tenant’s Actor.
Chat Room: the same one Actor per key pattern with SQLite history and broadcasts.