Effect SQL: One Database per User

effect-database-per-user.ts

import { Config, Effect, LayerMap, ManagedRuntime, Schema } from "effect";
import { LibsqlClient } from "@effect/sql-libsql";
import { SqlClient, SqlSchema } from "@effect/sql";

//
// Database Manager which returns a different "SQLite database connection" for each user.
//
class DatabaseManager extends LayerMap.Service<DatabaseManager>()(
  "DatabaseManager",
  {
    lookup: (userId) => {
      console.log(`Creating database connection for user ${userId}`);

      return LibsqlClient.layerConfig({
        url: Config.succeed(`file:${userId}.db`),
      });
    },
    // Let the layer live for 5 seconds after the last use.
    idleTimeToLive: Duration.seconds(5),
  },
) {}

const runtime = ManagedRuntime.make(DatabaseManager.Default);

function createTablesForUser(userId: string) {
  const create = Effect.gen(function* () {
    const sql = yield* SqlClient.SqlClient;

    const query = SqlSchema.void({
      Request: Schema.Void,
      execute: () => sql`
        CREATE TABLE IF NOT EXISTS tasks (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          description TEXT NOT NULL,
          created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )
      `,
    });

    yield* query();
  }).pipe(
    Effect.provide(DatabaseManager.get(userId))
  );

  return runtime.runPromise(create);
}

//
// Create tables for each user (just for demo purposes).
//
await createTablesForUser("user-1");
await createTablesForUser("user-2");
await createTablesForUser("user-3");