JSKIT

Appearance

Database layer ​

At the end of the previous chapter, the app could already authenticate real users through Supabase, but JSKIT was still using its no-database fallback for the app-side user mirror. 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:

bash
SUPABASE_URL=...
SUPABASE_KEY=...

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

npx jskit add package auth-provider-supabase-core \
  --auth-supabase-url "$SUPABASE_URL" \
  --auth-supabase-publishable-key "$SUPABASE_KEY" \
  --app-public-url "http://localhost:5173"

npx jskit add bundle auth-base
npm install

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

Installing the database runtime ​

From inside exampleapp, run:

bash
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:

json
{
  "scripts": {
    "db:migrate": "knex --knexfile ./knexfile.js migrate:latest",
    "db:migrate:rollback": "knex --knexfile ./knexfile.js migrate:rollback",
    "db:migrate:status": "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:

bash
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.

jskit migrations ... writes managed migration files ​

If you run:

bash
npx jskit migrations changed

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:

bash
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:

bash
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:

bash
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:

js
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, usually through workspace_id
  • user
    • the record belongs to one user, usually through user_id
  • workspace_user
    • the record belongs to one workspace and one user together

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:

js
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.

js
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:

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

the helper turns that into the equivalent of:

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

That is the normal helper for workspace-owned rows.

The same pattern works for user-owned rows:

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

which applies:

js
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.

js
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:

js
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:

js
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:

js
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:

js
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:

js
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:

js
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 authentication is still only partly database-backed.

  • Supabase 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 tells auth to switch from the standalone in-memory profile mirror to the persistent users-backed one.

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.

Under the hood ​

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

text
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:

json
{
  "dependencies": {
    "@jskit-ai/database-runtime": "0.x",
    "@jskit-ai/database-runtime-mysql": "0.x",
    "knex": "^3.1.0",
    "mysql2": "^3.11.2"
  },
  "scripts": {
    "db:migrate": "knex --knexfile ./knexfile.js migrate:latest",
    "db:migrate:rollback": "knex --knexfile ./knexfile.js migrate:rollback",
    "db:migrate:status": "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 three new scripts are also worth reading carefully:

  • db:migrate applies all pending migrations
  • db:migrate:rollback rolls back the last migration batch
  • db:migrate:status 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:

dotenv
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:

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");

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

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:

bash
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 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:

js
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:

js
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-supabase-core is still talking to Supabase for the real auth work

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

Why auth still uses the standalone profile mirror ​

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

Inside AuthSupabaseServiceProvider, auth resolves its profile mode from server app config:

js
const authProfileMode = resolveAuthProfileMode(appConfig);
let userProfileSyncService = fallbackStandaloneProfileSyncService;

if (authProfileMode === PROFILE_MODE_USERS) {
  if (!scope.has("users.profile.sync.service")) {
    throw new Error(
      "AuthSupabaseServiceProvider requires users.profile.sync.service when config.auth.profileMode is \"users\"."
    );
  }
  userProfileSyncService = scope.make("users.profile.sync.service");
}

That snippet explains the whole consequence of this chapter.

  • The auth provider's server config still sets config.auth.profileMode = "standalone".
  • If profileMode is missing entirely, the runtime assumes users, but it is not missing in the auth-only scaffold.
  • The fallback service is still the in-memory profile sync service from the previous chapter.
  • Nothing in database-runtime-mysql changes config.auth.profileMode.
  • Nothing in database-runtime-mysql provides users.profile.sync.service.

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

  • Supabase still owns the real auth user and session
  • JSKIT still mirrors just enough profile data locally
  • that JSKIT-side mirror is still not persistent 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 standalone JSKIT-side mirror
  • 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:

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

JSKIT documentation

Copyright 2026 Mobily Enterprises