Register users in a transaction

1. Scaffold the database baseline

From an existing application root, the CLI can create the Drizzle service, dialect-specific items table, migration configuration, environment contract, ignore rules, dependencies, and database scripts:

bun run relkit add database --orm drizzle --dialect sqlite

Use postgresql or mysql for the other Bun-native Drizzle clients. The command installs packages and runs relkit check, but deliberately does not create or apply a migration. Run the reported db:generate and db:migrate next steps only after reviewing the schema and setting DATABASE_PATH or DATABASE_URL. To follow the richer Users tutorial below, replace the generated items placeholder with the shown Users and Memberships schema instead of adding a second database service.

The scaffold also registers the application-owned Drizzle declaration patch. If you selected --no-install, run bun install and bun run check before continuing. A skipped installation has not verified the project. The separate manual setup is for apps where you are creating the service yourself; it is not an extra prerequisite after this scaffold.

SQLite is the headless default and uses bun:sqlite; PostgreSQL and MySQL use drizzle-orm/bun-sql. --orm accepts only drizzle. Database is a singleton under the detected service root: src/services/database in grouped apps or src/database in existing flat apps. --service and --create-service do not select its location.

For every scaffolding flag, see the CLI reference.

2. Prepare SQLite and the environment

The scaffold has already installed the compatible adapter, ORM and migration tooling. For a manual installation, complete Define a Drizzle service instead. This tutorial uses Bun’s built-in SQLite driver and requires no external server.

Add DATABASE_PATH to your environment contract:

import { defineEnv, env } from "@relkit/app/config";

export default defineEnv({ DATABASE_PATH: env.string().default("./data-model.sqlite") });

Keep that contract referenced by relkit.config.ts. Do not overwrite an existing configuration with a new minimal one. This example defaults to ./data-model.sqlite, which retains data after restart. Start with a new local file, not an existing database with unrelated tables or data.

3. Define tables and a custom model

Create or extend src/services/database/schema/index.ts:

/** Registers SQLite users and organization memberships with unique email and organization/user membership identities. */
import { integer, sqliteTable, text, uniqueIndex } from "drizzle-orm/sqlite-core";

export const users = sqliteTable("users", {
  id: integer().primaryKey({ autoIncrement: true }),
  email: text().notNull().unique(),
  active: integer({ mode: "boolean" }).notNull().default(true),
});

export const memberships = sqliteTable(
  "memberships",
  {
    id: integer().primaryKey({ autoIncrement: true }),
    organizationId: text().notNull(),
    userId: integer().notNull(),
    role: text().notNull(),
  },
  (table) => [uniqueIndex("membership_identity").on(table.organizationId, table.userId)],
);

Users has an integer identity, unique email, and active flag. Memberships has its own identity and a composite unique key. Its userId is not a foreign-key constraint in this demonstration; add the relationships and constraints your application needs.

Add src/services/database/models/users.model.ts:

/** Extends the native users model with email-domain selection and an update that conceals missing or inactive rows. */
import { defineModel, type UpdateArgs } from "@relkit/drizzle/sqlite";
import { eq, like } from "drizzle-orm";
import { users } from "../schema/index.js";

export default defineModel({
  table: users,
  extend: {
    byEmailDomain: ({ table, database }, domain: string) =>
      database
        .select()
        .from(table)
        .where(like(table.email, `%@${domain}`)),
    findOneAndUpdate: async ({ table, database }, args: UpdateArgs<typeof users>) => {
      const id = args.where.id;
      if (id === undefined) return null;
      const [existing] = await database.select().from(table).where(eq(table.id, id));
      if (existing === undefined || existing.active === false) return null;
      const [updated] = await database
        .update(table)
        .set(args.data)
        .where(eq(table.id, id))
        .returning();
      return updated ?? null;
    },
  },
});

The custom methods receive { table, database } automatically. Read Custom models for the sample’s pattern-matching, pagination, and atomicity limits before adapting it for public search.

4. Register the database service

Create or merge src/services/database/service.ts:

/** Owns lazy SQLite registration and disposal, supplying users-model extensions and an inactive-user lookup override. */
import { Database } from "bun:sqlite";
import { defineDrizzleService } from "@relkit/drizzle";
import { drizzle } from "drizzle-orm/bun-sqlite";
import * as schema from "./schema/index.js";
import usersModel from "./models/users.model.js";

export default defineDrizzleService({
  schema,
  client: ({ env }) => drizzle({ client: new Database(env.DATABASE_PATH) }),
  models: { users: usersModel },
  overrides: {
    users: {
      findOne: async ({ args, base }) => {
        const user = await base(args);
        return user?.active === false ? null : user;
      },
    },
  },
  dispose: (database) => database.$client.close(),
});

This connects the schema and model, adds an inactive-user override for findOne, and closes SQLite on disposal. It does not create tables. The findOne override is not a global soft-delete policy.

5. Generate and apply the initial migration

The scaffold installed the compatible Drizzle Kit version. Add this configuration:

/** Points native Drizzle tooling at grouped model tables and rejects empty or in-memory migration targets. */
import { defineConfig } from "drizzle-kit";

const path = process.env.DATABASE_PATH;
if (path === undefined || path.trim() === "" || path === ":memory:") {
  throw new Error("Set DATABASE_PATH to the persistent SQLite file you intend to migrate");
}

export default defineConfig({
  schema: "./src/services/database/schema/index.ts",
  dialect: "sqlite",
  out: "./src/services/database/migrations",
  dbCredentials: { url: path },
});

From your application’s root, generate the migration from the table definitions and apply it:

export DATABASE_PATH=./data-model.sqlite
bun --bun drizzle-kit generate --name=initial
bun run relkit db migrate

Review the generated migration before applying it. In the repository example, the initial migration already exists, so set DATABASE_PATH and run bun run db:migrate instead of creating another one. Keep this environment value for the server, and commit generated SQL and required metadata under src/services/database/migrations with the schema. The installed command owns execution; scaffolding and service startup do not apply SQL.

Run the compiler now so the database context is available to the next function:

bun run check

6. Create a member with generated CRUD

Add the registration workflow:

/** Validates native user data and creates the user/membership in one borrowed database transaction, retaining rollback failures. */
import { defineFunction } from "@relkit/app/functions";
import { z } from "@relkit/app/schema";

export default defineFunction({
  input: z.object({ email: z.string().email(), organizationId: z.string().min(1) }),
  output: z.object({ userId: z.number(), membershipId: z.number() }),
  handler: async ({ email, organizationId }, context) => {
    const data = context.database.zodSchemas.users.insert.parse({ email });
    return context.database.transaction(async (tx) => {
      const user = await tx.users.insert({ data });
      const membership = await tx.memberships.insert({
        data: { organizationId, userId: user.id, role: "member" },
      });
      return { userId: user.id, membershipId: membership.id };
    });
  },
});

The table service provides users.insert, memberships.insert, and the insert validator without requiring a custom model for either operation. The transaction commits both records together. See Generated CRUD and schemas and Transactions for their contracts.

Add the email-change workflow, which uses the generated update/select schemas:

/** Validates native update/select data and returns the changed user's email or null when its row is unavailable. */
import { defineFunction } from "@relkit/app/functions";
import { z } from "@relkit/app/schema";

export default defineFunction({
  input: z.object({ userId: z.number().int().positive(), email: z.string().email() }),
  output: z.object({ userId: z.number(), email: z.string() }).nullable(),
  handler: async ({ userId, email }, context) => {
    const data = context.database.zodSchemas.users.update.parse({ email });
    const row = await context.database.users.update({ where: { id: userId }, data });
    if (row === null) return null;
    const user = context.database.zodSchemas.users.select.parse(row);
    return { userId: user.id, email: user.email };
  },
});

These are trusted internal examples, not authorized public write APIs. Add ownership/organization checks before exposing them to callers. This tutorial mounts only a read route.

7. Expose a Users operation

Add src/services/users/functions/list-users.function.ts:

/** Lists validated user fields through the database model's email-domain extension. */
import { defineFunction } from "@relkit/app/functions";
import { z } from "@relkit/app/schema";

export default defineFunction({
  input: z.object({ domain: z.string() }),
  output: z.array(z.object({ id: z.number(), email: z.string(), active: z.boolean() })),
  handler: ({ domain }, context) => context.database.users.byEmailDomain(domain),
});

Add it to the Users service:

/** Exposes user listing, transactional registration and email updates through the immediate users service boundary. */
import { defineService } from "@relkit/app/services";
import listUsers from "./functions/list-users.function.js";
import registerMember from "./functions/register-member.function.js";
import updateUserEmail from "./functions/update-user-email.function.js";

export default defineService({ functions: { listUsers, registerMember, updateUserEmail } });

Connect the public operation to HTTP if you want an endpoint:

/** Maps users GET requests to the service's email-domain model query through the inferred HTTP contract. */
import { defineRoute } from "@relkit/app/routes";
import users from "@app/services/users/service.js";

export const GET = defineRoute({ target: users.listUsers });

The route invokes the Users service, not the private database model. Function validation and output contracts remain the public boundary; the database service does not automatically expose CRUD routes.

8. Verify database behavior

Create tests/registration.test.ts. It uses a separate in-memory SQLite database and the migrations you generated above. The second case makes membership creation fail after its insert, then checks that the transaction removes both writes. Your application’s database is untouched.

/** Verifies the guide's registration commits both records or rolls both back. */
import { expect, test } from "bun:test";
import { Database } from "bun:sqlite";
import { activateDrizzleService, defineDrizzleService } from "@relkit/drizzle";
import { invokeFunction } from "@relkit/testing";
import { drizzle } from "drizzle-orm/bun-sqlite";
import { migrate } from "drizzle-orm/bun-sqlite/migrator";
import * as schema from "@app/services/database/schema/index.js";
import usersModel from "@app/services/database/models/users.model.js";
import registerMember from "@app/services/users/functions/register-member.function.js";

for (const failMembership of [false, true]) {
  test(
    failMembership ? "registration rolls back both records" : "registration saves both records",
    async () => {
      const definition = defineDrizzleService({
        schema,
        client: () => drizzle({ client: new Database(":memory:") }),
        models: { users: usersModel },
        overrides: {
          memberships: {
            insert: async ({ args, base }) => {
              const membership = await base(args);
              if (failMembership) throw new Error("membership unavailable");
              return membership;
            },
          },
        },
        dispose: (database) => database.$client.close(),
      });
      const handle = await activateDrizzleService(definition, {});
      try {
        migrate(handle.client, { migrationsFolder: "src/services/database/migrations" });
        const registration = invokeFunction(
          registerMember,
          {
            email: "member@example.com",
            organizationId: "bookshop",
          },
          { context: ({ signal }) => ({ signal, database: handle.context }) },
        );
        if (failMembership) {
          await expect(registration).rejects.toThrow();
          expect(await handle.context.users.findMany()).toEqual([]);
          expect(await handle.context.memberships.findMany()).toEqual([]);
        } else {
          const result = await registration;
          expect(
            await handle.context.users.findOne({ where: { id: result.userId } }),
          ).toMatchObject({ email: "member@example.com" });
          expect(
            await handle.context.memberships.findOne({ where: { id: result.membershipId } }),
          ).toMatchObject({ userId: result.userId, organizationId: "bookshop", role: "member" });
        }
      } finally {
        await handle.close();
      }
    },
  );
}

From your application root:

bun test tests/registration.test.ts
bun run check
bun run typecheck
export DATABASE_PATH=./data-model.sqlite
bun run dev

Tests use an independent in-memory connection; they do not seed the running server. In a second terminal, query the migrated application:

curl http://localhost:3000/users\?domain=example.com

Use the actual backend port if different. An empty, migrated database returns HTTP 200 with []. If you also complete the separate Inspector setup, you can invoke registerMember (function ID users.register-member) with { "email": "member@example.com", "organizationId": "demo" }, then repeat the request. Expect a row containing that email, its generated numeric ID, and active: true. Use a different email for another registration; email uniqueness is enforced by the database.

Inspect the checked database service and Users function in the Inspector, normally at http://localhost:3210, and inspect .relkit/generated/context-registry.d.ts for the typed table methods. The listUsers function is also directly invokable from the Inspector with { "domain": "example.com" }.

Before deployment, add application-specific authorization, bounded queries, driver configuration, and tested backup and migration procedures. Continue with Integrate Better Auth to plan authentication tables and session wiring.