Multi-tenant with Drizzle ORM (multiple sqlite databases) - PoC

schema.ts

import { integer, sqliteTableCreator, text } from 'drizzle-orm/sqlite-core'

const sqliteTable = (tenant?: string) =>
	sqliteTableCreator(name => (tenant ? `${tenant}_${name}` : name))

export const users = sqliteTable()('users', {
	id: integer('id', { mode: 'number' }).primaryKey({ autoIncrement: true }),
	name: text('name'),
})

sqlite-pool.ts

import fs from 'fs'
import Database from 'better-sqlite3'
import { type BetterSQLite3Database, drizzle } from 'drizzle-orm/better-sqlite3'
import { migrate } from 'drizzle-orm/better-sqlite3/migrator'

const Pool = (maxPool = 10) => {
	const tenants: Record<string, BetterSQLite3Database> = {}

	return (tenant: string) => {
		if (tenants[tenant]) {
			return tenants[tenant]
		}

		if (Object.keys(tenants).length >= maxPool) {
			delete tenants[Object.keys(tenants)[0]]
		}

		const tenantFile = `${tenant}.db`
		let requiresMigration = true

		try {
			fs.accessSync(tenantFile)
			requiresMigration = false
		} catch (error) {
			requiresMigration = true
		}

		const db = new Database(tenantFile)
		tenants[tenant] = drizzle(db)

		if (requiresMigration) {
			migrate(tenants[tenant], { migrationsFolder: 'migrations' })
		}

		return tenants[tenant]
	}
}

export const pool = Pool()

usage.ts

import { pool } from './sqlite-pool'
import { users } from './schema'

const user = pool(tenant)
		.insert(users)
		.values({
			name: 'Gyo',
		})
		.run()

const results = pool(tenant)?.select().from(users).all()