Database layer

At the end of the previous chapter, the app could already authenticate real users through the local auth provider, without requiring Supabase or a database. In this chapter, we install the MySQL database runtime, add the migration tooling, and explain what that changes immediately and what it still does not change yet.

This chapter is more infrastructural than the previous ones. That is intentional. There is no dramatic new screen in the browser. The important change is that the app gains a real database layer that later packages can depend on.

Recap from previous chapters

To get back to the same starting point as the end of the previous chapter, run:

npx @jskit-ai/create-app exampleapp --tenancy-mode none
cd exampleapp
npm install

npx jskit add package auth-provider-local-core
npx jskit add package auth-web
npm install

If you are already continuing from the previous chapter, you are already in the right place and can skip that setup.

MySQLCreate The Database First

Before installing the database runtime, make sure a real MySQL database already exists and that you know its connection details.

If you are using the ai-seed flow, this should already have been resolved during the seed-stage conversation. Do not promote the app scaffold or move into the runtime install step while the database is still hypothetical.

At minimum, keep these ready:

• host
• port
• database name
• database user
• database password

JSKIT installs the database runtime and writes those values into .env, but it does not create the MySQL server for you and it does not create the database itself.

If you are working locally, a very typical starting point is:

• host: 127.0.0.1
• port: 3306
• database name: exampleapp
• user: exampleapp

Using a normal app database user is usually a better guide setup than using the MySQL root account directly. It matches how production apps are normally configured, and it avoids local root-auth setups that work in the terminal but not through the Node driver.

If you are guiding someone interactively, ask plainly for these exact values:

• DB_NAME
• DB_USER
• DB_PASSWORD
• DB_HOST only if it is not the usual local default 127.0.0.1
• DB_PORT only if it is not the usual local default 3306

Do not hide behind vague language like "send the DB credentials later." In this local guide flow, these are routine setup values for the MySQL runtime install step.

Installing the database runtime

From inside exampleapp, run:

DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=exampleapp
DB_USER=exampleapp
DB_PASSWORD=secret

npx jskit add package database-runtime-mysql \
  --db-host "$DB_HOST" \
  --db-port "$DB_PORT" \
  --db-name "$DB_NAME" \
  --db-user "$DB_USER" \
  --db-password "$DB_PASSWORD"
npm install

The first command adds the MySQL driver package and its generic JSKIT database runtime dependency, using the explicit connection values from those DB_* variables:

• database host
• database port
• database name
• database user
• database password

The second command downloads the newly referenced runtime packages and the underlying Node dependencies, especially knex and mysql2.

If you open the app in the browser after this install, it still looks the same. That is expected.

• /home still renders the same shell
• /auth/login still renders the same login screen

This chapter changes the server-side infrastructure of the app, not the visible shell.

What changes immediately

Installing database-runtime-mysql gives the app three important things right away.

A real database runtime

The server can build a real Knex client from environment variables. That gives later packages a standard way to ask for a database connection instead of each package inventing its own wiring.

A migration workflow

The app gets three database scripts in package.json:

{
  "scripts": {
    "db:migrations:sync": "jskit migrations changed",
    "db:migrate": "npm run db:migrations:sync && knex --knexfile ./knexfile.js migrate:latest",
    "db:migrate:rollback": "knex --knexfile ./knexfile.js migrate:rollback",
    "db:migrate:status": "npm run db:migrations:sync && knex --knexfile ./knexfile.js migrate:list"
  }
}

That is the first time the scaffold can talk about schema migrations in a standard way.

If you run the status command immediately after this chapter:

npm run db:migrate:status

you should still see that there are no completed migrations and no pending migration files yet. The runtime and the Knex wiring exist at this point, but no package has added real schema files until the next chapter.

A place for future schema files

The app also gets a top-level migrations/ directory. Right now it only contains .gitkeep, which can look underwhelming at first, but that empty directory is actually useful. It means the migration toolchain is ready even before any package installs real schema files.

That is the key idea of this chapter:

• the database runtime provides the infrastructure
• later packages provide the actual schema

Two migration steps, two different tools

This chapter is the right place to make one distinction very explicit.

The app has two different migration-related layers:

• JSKIT-managed migration files on disk
• Knex actually applying those files to the database

Those are not the same step.

The npm scripts hide the easy-to-miss first step for normal use. npm run db:migrate and npm run db:migrate:status run npm run db:migrations:sync first, then run Knex. That means package upgrades can add new JSKIT-managed migration files before Knex checks what is pending.

jskit migrations ... writes managed migration files

If you run the sync script directly:

npm run db:migrations:sync

JSKIT checks the installed package state in .jskit/lock.json and materializes any managed migration files that need to exist in migrations/.

That command is about the app scaffold on disk.

It does not run Knex against MySQL or Postgres.

At this point in the guide, that command will usually touch nothing yet, because database-runtime-mysql gives you the migration tooling and migrations/.gitkeep, but it does not add real schema files of its own.

npm run db:migrate runs Knex against the database

When you run:

npm run db:migrate

the Knex CLI reads knexfile.js, connects to the real database, and applies any pending migration files it finds.

That command is about the actual database.

So the clean mental model is:

1. JSKIT writes or refreshes managed migration files into migrations/
2. Knex applies those files to MySQL or Postgres

When you need each step

In many normal jskit add package ... flows, JSKIT already materializes a package's managed migration files while the package is being applied.

That means the most common flow is still:

npx jskit add package users-web
npm install
npm run db:migrate

But if you ever need JSKIT to re-materialize or refresh the managed migration files for installed packages, that is when you use:

npx jskit migrations changed
npm run db:migrate

So:

• use jskit migrations ... when you need JSKIT to write or refresh managed migration files
• use npm run db:migrate when you need Knex to apply pending migration files to the real database
• sometimes you need only npm run db:migrate
• sometimes, after repair or re-materialization work, you need both

Shared database helpers

The database layer also gives later server code a shared helper surface:

import {
  applyVisibility,
  applyVisibilityOwners,
  toIsoString,
  toInsertDateTime,
  toNullableDateTime,
  isDuplicateEntryError,
  whereJsonTextEquals,
  createWithTransaction
} from "@jskit-ai/database-runtime/shared";

This is worth calling out here because the database layer is not only "Knex plus migrations".

It also gives your later repositories and services a standard persistence toolbox so every package does not have to solve the same problems differently. The main point is consistency:

• one way to format timestamps for database writes
• one way to format timestamps for API output
• one way to recognize duplicate-entry errors across databases
• one way to apply ownership-aware visibility filters to queries
• one way to stamp owner columns onto new rows from the current visibility context
• one way to build some dialect-aware JSON filters
• one small transaction helper pattern that packages can reuse

That helper layer comes from @jskit-ai/database-runtime/shared. The MySQL package mainly contributes the actual driver and dialect registration. The shared helper surface comes from the generic runtime package.

Visibility and ownership helpers

This is the main helper pair that matters once repositories start dealing with owned records.

In JSKIT persistence code, visibility means "which rows should this request be allowed to see or create?" It is the data-layer version of the same ownership model used by routes and CRUD resources:

• public
  • the record is not scoped by owner columns
• workspace
  • the record belongs to one workspace through the exact reserved column workspace_id
• user
  • the record belongs to one user through the exact reserved column user_id
• workspace_user
  • the record belongs to one workspace and one user through both reserved columns

Only workspace_id and user_id carry this standard ownership contract. Specifically named foreign keys such as recipient_user_id, created_by_user_id, and assignee_user_id describe domain relationships; they are not alternate owner columns. Keep both fields when a row has an owner and a separate related actor, and never rename the relationship to an owner column merely to make a tool accept the schema.

The selected ownership filter must match the direct reserved columns exactly. A table with only workspace_id is workspace; a table with only user_id is user; and a table with both is workspace_user. A declaration cannot override or ignore either column.

That is why the shared helpers exist. Repositories should not have to re-implement the same ownership rules by hand every time they filter a query or build an insert payload.

The expected input is a visibility context. In practice this usually arrives from route/action execution context and gets passed down as queryOptions.visibilityContext or callOptions.visibilityContext.

The important shape looks like this:

const visibilityContext = {
  visibility: "workspace_user",
  scopeOwnerId: "42",
  userId: "7"
};

Those are the fields these helpers care about:

• visibility
  • one of public, workspace, user, workspace_user
• scopeOwnerId
  • the current workspace owner id
• userId
  • the current user id

There are a couple of extra normalized visibility fields elsewhere in JSKIT, but for applyVisibility(...) and applyVisibilityOwners(...), those three values are the main contract.

Use applyVisibility(...) when you are building a read/update/delete query that should only operate on rows visible to the current request.

import { applyVisibility } from "@jskit-ai/database-runtime/shared";

function listWorkspaceContacts(knex, queryOptions = {}) {
  const query = knex("contacts").select("*").orderBy("created_at", "desc");
  return applyVisibility(query, queryOptions.visibilityContext);
}

If the current request is workspace-scoped:

const visibilityContext = {
  visibility: "workspace",
  scopeOwnerId: "12"
};

the helper turns that into the equivalent of:

query.where("workspace_id", "12");

That is the normal helper for workspace-owned rows.

The same pattern works for user-owned rows:

const query = knex("saved_views").select("*");
applyVisibility(query, {
  visibility: "user",
  userId: "7"
});

which applies:

query.where("user_id", "7");

And if a workspace_user context is required but only one owner id is available, the helper intentionally makes the query match nothing rather than accidentally leaking records. That is why these helpers are safer than scattering raw where(...) calls by hand.

Use applyVisibilityOwners(...) when you are building a write payload for a new owned row and you want JSKIT to stamp the owner columns from the current visibility context.

import { applyVisibilityOwners, toInsertDateTime } from "@jskit-ai/database-runtime/shared";

async function createWorkspaceNote(knex, payload, queryOptions = {}) {
  const insertPayload = applyVisibilityOwners(
    {
      title: payload.title,
      body: payload.body,
      created_at: toInsertDateTime(),
      updated_at: toInsertDateTime()
    },
    queryOptions.visibilityContext
  );

  await knex("notes").insert(insertPayload);
}

If the request is workspace-scoped, the helper adds workspace_id automatically. If it is user-scoped, it adds user_id. If it is workspace_user, it adds both. That means repository code does not need to manually copy workspace_id and user_id out of the execution context for every insert.

That is especially useful for owned records such as:

• workspace-owned rows like contacts, workspace_invites, or assistant_config
• user-owned rows like personal settings or saved views
• combined workspace-and-user rows where both ids define ownership

So a workspace_user create can stay small:

const insertPayload = applyVisibilityOwners(
  {
    name: payload.name
  },
  {
    visibility: "workspace_user",
    scopeOwnerId: "4",
    userId: "9"
  }
);

// Result:
// {
//   name: "...",
//   workspace_id: "4",
//   user_id: "9"
// }

The important behavioral difference between the two helpers is:

• applyVisibility(...)
  • scopes a query, and falls back to "no rows" when required owner ids are missing
• applyVisibilityOwners(...)
  • stamps write payloads, and throws when required owner ids are missing

That split is deliberate. For reads, "match nothing" is the safe default. For writes, silently inserting an incorrectly owned row would be worse, so the helper fails fast instead.

If you want a slightly more structured repository shape, createRepositoryScope(...) from the same shared surface wraps these helpers into one small repository-scoping toolkit. But even when you write your own repositories manually, applyVisibility(...) and applyVisibilityOwners(...) are the key ownership helpers to reuse.

Date helpers

The date helpers are probably the first ones you are likely to reach for.

They exist because application code usually deals with dates in two different shapes:

• database write shape, such as a DATETIME(3)-style value
• API/read shape, usually an ISO timestamp string

Those are not the same thing, and it is easy for packages to become inconsistent if every repository hand-rolls the conversion.

Typical usage looks like this:

import {
  toIsoString,
  toInsertDateTime,
  toNullableDateTime
} from "@jskit-ai/database-runtime/shared";

function mapRow(row) {
  return {
    id: String(row.id),
    createdAt: toIsoString(row.created_at),
    updatedAt: toIsoString(row.updated_at),
    expiresAt: row.expires_at ? toIsoString(row.expires_at) : null
  };
}

async function insertInvite(knex, payload) {
  await knex("workspace_invites").insert({
    email: payload.email,
    expires_at: toNullableDateTime(payload.expiresAt),
    created_at: toInsertDateTime(),
    updated_at: toInsertDateTime()
  });
}

The reasoning behind each helper is:

• toIsoString(...)
  • use this when data is coming out of the database and you want a stable ISO timestamp for application code or API responses
• toInsertDateTime(...)
  • use this when you need "now" in the database's datetime text format for insert/update timestamps
• toNullableDateTime(...)
  • use this when a field is optional and should become either a normalized database datetime or null

There is also a lower-level helper:

toDatabaseDateTimeUtc(value)

That one is useful when you already know you are converting one specific date value into the database write format and you want to be explicit about it.

The main benefit is not convenience by itself. The real benefit is that packages stop inventing slightly different timestamp formats and timezone assumptions.

Duplicate-entry helpers

Another practical problem is unique-constraint handling.

MySQL and Postgres do not report duplicate-entry errors in exactly the same way. If every package checks raw driver error codes by hand, the code becomes repetitive and easy to get wrong.

The shared helper gives you one check:

import { isDuplicateEntryError } from "@jskit-ai/database-runtime/shared";

try {
  await knex("users").insert({
    email: payload.email
  });
} catch (error) {
  if (!isDuplicateEntryError(error)) {
    throw error;
  }

  // Handle the "already exists" case here.
}

Why this is useful:

• it keeps repository code cleaner
• it avoids scattering vendor-specific numeric/string error codes everywhere
• it makes later database portability less painful

JSON query helpers

Some databases expose different SQL syntax for reading JSON fields.

The shared helpers smooth over a small but useful part of that difference. For example:

import { whereJsonTextEquals } from "@jskit-ai/database-runtime/shared";

const query = knex("contacts");
whereJsonTextEquals(query, {
  column: "metadata",
  path: "status",
  value: "active"
});

That helper uses the right JSON text expression for the active client instead of forcing every package to write separate MySQL and Postgres raw SQL.

This is not a full ORM abstraction layer, and it should not be described that way. It is just a small set of helpers for the database differences JSKIT packages actually care about.

Transaction and repository helpers

The shared surface also includes a few small helpers for transaction-friendly repository code:

import { createWithTransaction } from "@jskit-ai/database-runtime/shared";

function createRepository(knex) {
  const withTransaction = createWithTransaction(knex);

  return {
    withTransaction
  };
}

That pattern shows up in JSKIT packages because it gives repositories a simple standard way to say, "run this unit of work inside a transaction".

You do not need to memorize all of these helpers yet. The important thing to understand in this chapter is just that the database layer gives you:

• runtime wiring
• migration tooling
• a shared persistence utility surface for later packages and app code

What this still does not change yet

Installing the database runtime is important, but it is not the same thing as installing the full users/account data model.

Right now, after this chapter:

• the app can resolve database settings from .env
• the server can create a Knex client
• the app can run migration commands
• later packages are allowed to depend on runtime.database

But the app still does not have:

• JSKIT user tables
• JSKIT user settings tables
• persistent account/profile rows on the JSKIT side
• workspace tables
• CRUD tables of its own

That means the app's account model still is not database-backed.

• local auth is still the real source of truth for auth users and sessions.
• JSKIT still has a database runtime available.
• But JSKIT still has no installed package yet that projects auth identities into persistent users/account tables.

So this chapter is an infrastructure step. It makes the database layer available, but it does not yet install the package that uses that layer for persistent JSKIT-side user data.

ImportantAuth Is Not Users-Backed Yet

This is the most important thing to keep straight:

• adding database-runtime-mysql does not automatically change where auth stores credentials or sessions
• it also does not create JSKIT user rows yet

That only changes later, when a package such as users-core is installed and registers the persistent users-backed auth.profile.projector.

So after this chapter the app has a database layer, but authentication still behaves like:

• real local auth
• provider identity in the auth session

not yet:

• real local auth plus a persistent JSKIT-side users layer

Under the hood

The interesting files for this chapter are mostly at the top level:

exampleapp/
  .env
  knexfile.js
  migrations/
    .gitkeep
  package.json

This is the first chapter where the new behavior is mostly about server infrastructure rather than pages or client layouts.

package.json gains database dependencies and scripts

After installing the MySQL runtime, the important new pieces in package.json look like this:

{
  "dependencies": {
    "@jskit-ai/database-runtime": "0.x",
    "@jskit-ai/database-runtime-mysql": "0.x",
    "knex": "^3.1.0",
    "mysql2": "^3.11.2"
  },
  "scripts": {
    "db:migrations:sync": "jskit migrations changed",
    "db:migrate": "npm run db:migrations:sync && knex --knexfile ./knexfile.js migrate:latest",
    "db:migrate:rollback": "knex --knexfile ./knexfile.js migrate:rollback",
    "db:migrate:status": "npm run db:migrations:sync && knex --knexfile ./knexfile.js migrate:list"
  }
}

Those new dependencies divide into two roles:

• @jskit-ai/database-runtime is the generic JSKIT database runtime
• @jskit-ai/database-runtime-mysql is the MySQL-specific driver package
• knex is the database toolkit used by both runtime code and migration commands
• mysql2 is the actual Node driver that speaks to MySQL

The migration scripts are also worth reading carefully:

• db:migrations:sync writes or refreshes JSKIT-managed migration files in migrations/
• db:migrate syncs JSKIT-managed migration files, then applies all pending Knex migrations
• db:migrate:rollback rolls back the last migration batch
• db:migrate:status syncs JSKIT-managed migration files, then lists applied and pending migrations

They are not special JSKIT commands. They are ordinary project scripts, which makes them easy to run in any environment.

.env owns the database connection settings

The package install also writes the database settings into .env:

DB_CLIENT=mysql2
DB_HOST=127.0.0.1
DB_PORT=3306
DB_NAME=exampleapp
DB_USER=exampleapp
DB_PASSWORD=secret

That small block is doing two jobs.

• DB_CLIENT tells the generic database runtime which dialect was installed.
• the rest of the variables describe the real connection to MySQL.

This matters because the generic runtime is written to support more than one driver package. The runtime does not hard-code MySQL. It reads the configured client and the installed driver and checks that they agree.

knexfile.js is for migration commands, not normal page code

The migration scripts in package.json work because the app has a top-level knexfile.js:

import path from "node:path";
import dotenv from "dotenv";
import {
  normalizeText,
  toKnexClientId,
  resolveDatabaseClientFromEnvironment,
  resolveKnexConnectionFromEnvironment
} from "@jskit-ai/database-runtime/shared";

const appRoot = process.cwd();
dotenv.config({
  path: path.join(appRoot, ".env"),
  quiet: true
});

const dialectId = resolveDatabaseClientFromEnvironment(process.env);
const client = toKnexClientId(dialectId);
const defaultPort = dialectId === "pg" ? 5432 : 3306;
const migrationsDirectory = path.resolve(appRoot, normalizeText(process.env.DB_MIGRATIONS_DIR) || "migrations");
const deferredConstraintsDirectory = path.join(migrationsDirectory, "constraints");

export default {
  client,
  connection: resolveKnexConnectionFromEnvironment(process.env, {
    client: dialectId,
    defaultPort,
    context: "knex migrations"
  }),
  migrations: {
    directory: [migrationsDirectory, deferredConstraintsDirectory],
    extension: "cjs",
    sortDirsSeparately: true
  }
};

The important thing to understand is what this file is for.

It is not the main runtime API that your app code imports during a request. It is the configuration file the Knex CLI reads when you run commands such as:

npm run db:migrate

So there are really two separate database entry points:

• knexfile.js for migration commands
• the JSKIT server provider runtime for application code

That separation is good. It keeps the operational CLI workflow and the app runtime wiring clear.

The two migration directories are one ordered migration plan. Knex completes the ordinary migrations/ files before it reads migrations/constraints/. CRUD scaffolding uses that second phase for foreign keys, so two tables may validly reference one another without either table-creation migration depending on a table that has not been created yet. Rollback reverses the order and removes those constraints before dropping tables.

The MySQL package registers the driver, and the generic runtime builds the Knex client

On the server side, the two installed packages split responsibilities very deliberately.

The MySQL-specific package registers a driver token:

class DatabaseRuntimeMysqlServiceProvider {
  static id = "runtime.database.driver.mysql";

  register(app) {
    app.singleton("runtime.database.driver.mysql", () => MYSQL_DATABASE_DRIVER_API);
  }
}

That does not create the database client yet. It only tells the app, "a MySQL driver is available, and here is its dialect metadata."

The generic runtime then uses that driver to create the real Knex wiring:

class DatabaseRuntimeServiceProvider {
  static id = "runtime.database";

  register(app) {
    app.singleton("runtime.database", () => DATABASE_RUNTIME_SERVER_API);

    if (!app.has("runtime.database.driver")) {
      app.singleton("runtime.database.driver", (scope) => resolveSingleRegisteredDriver(scope));
    }

    if (!app.has("jskit.database.knex")) {
      app.singleton("jskit.database.knex", (scope) => createKnexInstance(scope));
    }

    if (!app.has("jskit.database.transactionManager")) {
      app.singleton("jskit.database.transactionManager", (scope) => {
        const knex = scope.make("jskit.database.knex");
        return createTransactionManager({ knex });
      });
    }
  }
}

That one provider is the real center of this chapter. It gives later server code a standard set of container tokens:

• runtime.database
• runtime.database.driver
• jskit.database.knex
• jskit.database.transactionManager

This is why later packages can simply say "I require runtime.database" instead of building their own database bootstrap.

Why the browser still feels unchanged

At first glance it can feel strange that the database layer is installed but the app still behaves almost exactly like the previous chapter.

The reason is simple:

• the runtime is available
• but almost no installed package is using it yet

Right now:

• shell-web is still a shell/layout package
• auth-web is still a web auth package
• auth-provider-local-core is still handling credentials and sessions through auth.local.backend

So the app has gained a new capability, but no visible part of the UI depends on that capability yet.

Why auth is not users-backed yet

This is the most important code path to read in this chapter.

Inside the local provider, auth only projects provider identities into the app users layer when something registers the provider-neutral auth.profile.projector token:

const profileProjector = scope.has("auth.profile.projector")
  ? {
      async syncIdentityProfile(profile, options = {}) {
        const projector = scope.make("auth.profile.projector");
        return projector.syncIdentityProfile(profile, options);
      }
    }
  : null;

That snippet explains the whole consequence of this chapter. The provider checks whether the token exists, but it does not resolve the real projector until an auth payload actually needs profile projection.

• The auth provider can authenticate users without an app database.
• Nothing in database-runtime-mysql registers auth.profile.projector.
• Nothing in database-runtime-mysql provides users.profile.sync.service.
• The database runtime only provides the database foundation that later packages can use.

So the auth layer keeps behaving the same way it did before:

• local auth still owns the auth user and session
• JSKIT can still display the provider identity from the auth session
• there is still no persistent JSKIT users/account model yet

The database runtime is ready, but the users layer that will actually use it has not been installed yet.

Why the empty migrations/ directory is important

The new migrations/ directory can look almost silly at first because it only contains .gitkeep. But that empty directory is the cleanest signal of what this chapter really does.

It means:

• the app has a migration system
• the app does not yet have a schema of its own

That is exactly the right state at this stage of the guide.

The database runtime chapter should give the app a database foundation first. The next data-heavy chapters can then install actual schema migrations on top of that foundation.

Summary

This chapter did not make the app feel dramatically different in the browser, but it changed the server foundation in an important way.

• the app has a real JSKIT database runtime
• the app has a standard Knex migration workflow
• the app has a place for future schema files

But just as importantly, this chapter also defined what has not changed yet:

• auth still uses the local provider's own backend
• JSKIT still has no persistent users layer of its own
• no feature package has started storing real app data yet

So the right mental model at the end of this chapter is:

• local auth already handles real authentication
• MySQL is wired up and ready
• the persistent JSKIT-side user model arrives in the next chapter