---
title: Register users in a transaction
description: Add SQLite tables and a Drizzle service to an existing RELKIT app, customize the Users model, and verify the database and public query path.
pagination: false
sidebar:
  icon: database
---

Add a Users database to an existing RELKIT app using the executable data-model example. You will define
tables, generate migrations, add a custom query and an override, and use the generated CRUD schemas
in a member-registration transaction. You will expose a read operation and verify the result.

> **Before you begin**
>
> You'll need an existing RELKIT application, Bun 1.3.10 or later, and a new local SQLite database.
> The final test verifies that registration writes both records and rolls back on failure.

Use an installed app with the standard `@app/*` alias. If needed, follow [Create an app](/docs/learn/create-an-app).
If a database service already exists, extend it instead of adding a second one. Preserve existing schema,
environment fields, public service members, and routes.

1. ## 1. Scaffold the database baseline [#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:

    ```sh
    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](/docs/modules/database/define) 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](/docs/api/cli).

2. ## 2. Prepare SQLite and the environment [#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](/docs/modules/database/define) instead. This tutorial uses
    Bun's built-in SQLite driver and requires no external server.

    Add `DATABASE_PATH` to your environment contract:

    ```ts title="src/platform/env.ts"
    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. ## 3. Define tables and a custom model [#define-tables-and-a-custom-model]

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

    ```ts title="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](/docs/modules/database/schema) your application needs.

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

    ```ts title="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](/docs/modules/database/models)
    for the sample's pattern-matching, pagination, and atomicity limits before adapting it for public search.

4. ## 4. Register the database service [#register-the-database-service]

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

    ```ts title="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. ## 5. Generate and apply the initial migration [#generate-and-apply-the-initial-migration]

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

    ```ts title="drizzle.config.ts"
    /** 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:

    ```sh
    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:

    ```sh
    bun run check
    ```

6. ## 6. Create a member with generated CRUD [#create-a-member-with-generated-crud]

    Add the registration workflow:

    ```ts title="src/services/users/functions/register-member.function.ts"
    /** 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](/docs/modules/database/generated-schemas) and
    [Transactions](/docs/modules/database/transactions) for their contracts.

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

    ```ts title="src/services/users/functions/update-user-email.function.ts"
    /** 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. ## 7. Expose a Users operation [#expose-a-users-operation]

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

    ```ts title="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:

    ```ts title="src/services/users/service.ts"
    /** 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:

    ```ts title="src/routes/users/route.ts"
    /** 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. ## 8. Verify database behavior [#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.

    ```ts title="tests/registration.test.ts"
    /** 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:

    ```sh
    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:

    ```sh
    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](/docs/modules/operations/inspector),
    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](/docs/modules/database/better-auth)
    to plan authentication tables and session wiring.

{/* Generated by apps/docs/scripts/generate.ts. Do not edit. */}

**Reference and source**

- [`@relkit/drizzle` API](/docs/api/drizzle)
- [`@relkit/functions` API](/docs/api/functions)
- [`@relkit/routes` API](/docs/api/routes)

- [`examples/data-model/src/services/users/functions/register-member.function.ts`](https://github.com/rel-kit/relkit/blob/main/examples/data-model/src/services/users/functions/register-member.function.ts)

## Related tasks

Continue with [Add email and password sign-in](/guides/auth/email-password-sign-in). Review
[transaction limits](/docs/modules/database/transactions) when adapting the registration workflow.