Skip to main content

DB Schema Management

This page provides implementation details for the declarative schema management feature of adapter.knex. See the concept overview for a high-level description and the rationale for design motivation.


Full adapter configuration​

// mysql/adapter/sql.ts
import {adapter} from '@feasibleone/blong';
import {dirname, join} from 'node:path';
import {fileURLToPath} from 'node:url';

const __dirname = dirname(fileURLToPath(import.meta.url));

export default adapter(({schema}) => ({
extends: 'adapter.knex',
activation: {
default: {
knex: {
connection: {database: 'demo', user: 'app', password: 'secret'},
},
namespace: 'sql', // prefix for the routed CRUD methods and procedure handlers
imports: ['mysql.sql'], // handler group name for dispatch routing

schema: {
sync: true, // run DDL — the deploy job, and a dev or upgrade run

tables: {
// table name → ISchemaTable spec (or plain TObject). The name is what the
// routed methods resolve to: the CRUD fallback derives the table from the
// method, `sql.item.get` → `sql_item`, so a key of `schema_item` under the
// `sql` namespace would never be found.
sql_item: {
definition: schema.mysql.item, // TypeBox TObject
order: 1, // creation order (FK dependencies)
dropColumns: true, // allow dropping removed columns
},
},

// Seed data (production)
seed: true,

// Preferred: scan a folder for *.sql files
procedurePaths: [join(__dirname, 'sql/schema')],

// Backward-compatible: inline SQL strings
// procedures: {
// sql_schema_list_active: `CREATE PROCEDURE ...`,
// },
},
},
integration: {
schema: {
// Test seed data (integration intent only)
dbTest: true,
},
},
},
}));

schema config fields​

FieldTypeDefaultDescription
syncbooleanfalseEnable schema sync (deployment-time job only, not normal startup)
tablesRecord<string, ISchemaTable|TObject>{}Tables to create/alter
seedbooleanfalseEnable production seed data from .db.asset YAML/JSON files
dbTestbooleanfalseEnable test seed data from .dbTest.asset YAML/JSON files
procedurePathsstring[][]Folders scanned for *.sql files (base name = procedure name)
proceduresRecord<string, string>{}Inline procedure SQL (backward compat; prefer procedurePaths)

namespace is a sibling field next to schema, not inside it. It controls the prefix of all auto-bound handler names.


Table schema definition​

Place the TypeBox TObject in the meta/type folder so it can be reused:

// mysql/meta/type/schema.ts
import {schema} from '@feasibleone/blong';

export default schema(async ({lib: {type}}) => ({
item: type.Object({
itemId: type.Integer({format: 'int64'}),
itemName: type.String(),
itemActive: type.Optional(type.Boolean()),
}),
}));

Column rules:

  • Property names are used as SQL column names verbatim (camelCase is preserved).
  • A column becomes a primary key by marker, not by name: type.increment() carries default: 'auto-increment' and becomes a BIGINT AUTO_INCREMENT, type.ulid() carries default: 'ulid' and becomes BINARY(16), and type.uuid() carries default: 'uuid' and becomes a binary UUID — the last two are declared PRIMARY KEY by the adapter itself. A plain Type.Integer() named itemId is an ordinary INT. A composite primary key, or a single-column key on a column that carries no marker, comes from the constraints second pass instead.
  • Properties listed in the schema's required array generate NOT NULL columns; all others are nullable.
  • Use dropColumns: true in the ISchemaTable spec to let the adapter remove columns that are no longer present in the TypeBox schema.

Structured JSON columns (*JSON suffix): any column whose name ends in JSON is auto-(de)serialized by the knex adapter — object/array values are JSON.stringify'd on insert/update and JSON.parse'd on read (lenient, non-JSON strings untouched). Declare such columns as plain strings (Type.String() / type.stringNull() → VARCHAR); no manual (de)serialization is needed in handlers. This is a name-based convention applied to every queryBuilder produced by the shared srv.db adapter — see core/blong-gogo/src/adapter/schema/knex/json.ts. Example: access_credential.credentialParamsJSON.

TypeBox → SQL type mapping​

TypeBox typeSQL column type
type.increment() — default: 'auto-increment'BIGINT AUTO_INCREMENT (the key is the marker)
type.ulid() — default: 'ulid'BINARY(16) PRIMARY KEY
type.uuid() — default: 'uuid'binary UUID PRIMARY KEY (useBinaryUuid)
Type.Integer()INT
Type.BigInt()BIGINT UNSIGNED
Type.Number()DOUBLE
Type.String({maxLength: N≤255})VARCHAR(N)
Type.String({maxLength: N>255})TEXT
Type.String() (no maxLength)VARCHAR(255) (default when no maxLength given)
Type.String({format: 'date-time'})DATETIME
Type.String({format: 'date'})DATE
Type.String({format: 'time'})TIME
Type.String({format: 'uuid'})binary UUID
Type.String({format: 'uid'})BINARY(16) (type.uidNull() / uidNotNull())
Type.Boolean()BOOLEAN
Type.Object() / Type.Array()JSON
Type.Unknown()TEXT — no type to map, so it falls to the default
Type.Optional(T) / Type.Null() in a unionnullable column

Constraint definitions​

Table-level constraints (composite primary keys, unique constraints, foreign keys, indexes) are declared via the second argument to type.Object():

type.Object(
{
itemId: type.Integer(),
itemName: type.String(),
categoryId: type.Integer(),
},
{
constraints: {
// Composite primary key
primaryKey: {columns: ['itemId', 'categoryId']},

// Unique constraint (key = constraint name, value = options)
unique: {
itemName: {},
},

// Foreign keys — string form (simple) or object form (with options)
foreign: {
categoryId: 'realmname.category.categoryId',
// — OR with cascade:
// categoryId: {
// references: 'realmname.category.categoryId',
// onDelete: 'CASCADE',
// },
},

// Indexes
index: {
itemName: {},
// — OR with index type:
// itemName: {indexType: 'FULLTEXT'},
},
},
},
);

Constraint reference​

ConstraintType formDescription
primaryKeystring (single column) or {columns, constraintName?}Composite primary key. Single-column PKs are auto-detected via *Id + Integer and do not need to be declared here.
uniqueRecord<string, {columns?}>Unique constraint. Key = constraint name (used to generate {table}_ux_{key}). Omit columns to apply to the key-named column.
foreignRecord<string, string|{references, columns?, onDelete?, onUpdate?}>Foreign key. references uses {table}.{column} format. Key = local column name.
indexRecord<string, {columns?, indexType?}>Index. indexType can be 'FULLTEXT', 'SPATIAL', etc. Key = index name (used to generate {table}_idx_{key}).

Two-pass constraint sync​

Constraints are applied in a separate pass after all tables have been created. This is necessary because foreign keys reference other tables that must already exist.

Both passes are idempotent. Already-present constraints (checked via information_schema.TABLE_CONSTRAINTS and information_schema.STATISTICS) are skipped. Running the sync multiple times on an unchanged schema produces zero SQL.

Constraint naming​

Constraint typeGenerated name patternExample
Primary key{table}_pkrealmname_item_pk
Unique{table}_ux_{key}realmname_item_ux_itemName
Index{table}_idx_{key}realmname_item_idx_itemName
Foreign key{table}_fk_{key}realmname_item_fk_categoryId

Seed data files​

Seed data is provided via YAML (.yaml) files placed in handler folders. The framework's Watch mechanism automatically wraps any .yaml/.yml/.json file in a handler folder as an asset module. The knex adapter's ready() hook processes these assets when the relevant config flags are enabled.

Production seeds (.db.asset)​

Place YAML files in the meta/db/ folder alongside db.ts. They are processed when schema.seed: true via processSeedAssets(ctx, /\.db\.asset$/).

realmname/meta/db/
├── db.ts
├── 0-coreTypeMerge.yaml ← .db.asset — production seed
└── realmnameRoleMerge.yaml ← .db.asset — production seed

Method name derivation: The method name is the part of the filename after the last - character (minus the extension). Examples:

FilenameMethod name
0-coreTypeMerge.yamlcoreTypeMerge
realmnameRoleMerge.yamlrealmnameRoleMerge
accessRoleMerge.yamlaccessRoleMerge

Processing: Each file is parsed (YAML or JSON) and dispatched via ctx.handle!(params, {method}). The YAML content becomes the params object:

# realmnameRoleMerge.yaml
resourceType: realmname.role
role:
- roleBit: 0
name: Admin
description: Full access

This dispatches to {handlerMethod}({resourceType: 'realmname.role', role: [...]}, {method: 'realmnameRoleMerge'}).

Because the method name comes from the file, a realm can override the merge for its own type: blong-access handles access.role.merge itself, so a seed row lists a role by name and the unique roleBit is allocated instead of declared (see the RBAC pattern).

Test seeds (.dbTest.asset)​

Place YAML files in the meta/dbTest/ folder. They are processed when both schema.seed: true AND schema.dbTest: true via processSeedAssets(ctx, /\.dbTest\.asset$/).

realmname/meta/dbTest/
└── realmnameEntityMerge.yaml ← .dbTest.asset — test seed only

Test seeds follow the same naming and processing rules as production seeds, but are only loaded in dev and integration intents — never in upgrade or microservice.

Intent-specific behaviour​

Intentschema.seedschema.dbTest
dev✅✅
integration✅✅
upgrade✅❌
microservice❌❌

Stored procedure files​

Place each procedure in its own .sql file inside a sub-folder named schema (by convention):

mysql/adapter/sql/schema/sql_item_list_active.sql
CREATE PROCEDURE `sql_item_list_active`()
BEGIN
SELECT * FROM `item` WHERE `itemActive` = 1;
END

The file's base name (without .sql) becomes the SQL procedure name. The adapter compares the procedure body against information_schema.ROUTINES.ROUTINE_DEFINITION and only re-creates the procedure when the body differs — so repeated restarts with an unchanged schema produce no SQL.

Private procedures (underscore prefix)​

Name a procedure _my_helper to mark it as a private DB helper. It will be synced to the database but not exposed as a synthetic handler on the API surface.


Auto-bound synthetic handlers​

After ready(), every procedure whose name does not start with _ is callable as a synthetic own-property handler on the adapter. For a procedure named sql_item_list_active:

sql_item_list_active → sqlItemListActive (camelCase, for super calls)
→ sqlitemlistactive (methodId, for framework dispatch)

Call it from any orchestrator or test step exactly like a registered handler:

const rows = await handler.sqlItemListActive({}, $meta);

Input parameters are mapped from camelCase object keys matching the procedure's IN/INOUT parameters (declared order is respected). For a procedure parameter named item_status, the caller passes {itemStatus: value}.


Routed CRUD methods​

When namespace is set, every declared table answers nine methods — get, find, add, edit, remove, merge, and the bulk insert, update, delete — resolved as <namespace>.<table>.<method>:

Method name patternExample (namespace=sql, table=item)
${ns}${Table}GetsqlItemGet
${ns}${Table}FindsqlItemFind
${ns}${Table}AddsqlItemAdd
${ns}${Table}EditsqlItemEdit
${ns}${Table}RemovesqlItemRemove
${ns}${Table}MergesqlItemMerge

There is no handler object per table: the call falls through to the adapter's generic exec, which derives the table name from the method and runs the query. That is why a declared table needs no handler files, why the method list above is the whole surface, and why the way to change one operation is a handler named after the method that delegates the standard work to super.exec.

To use them from a test or orchestrator, name them in the handler:{} proxy:

handler: {
(sqlItemAdd, sqlItemFind, sqlItemRemove);
}

Overriding a synthetic handler​

A handler file placed in the adapter layer whose name matches a synthetic handler takes precedence because registered realm handlers sit higher in the prototype chain than synthetic own-property handlers. Three things can answer a call, and the order between them is fixed:

To delegate back to the synthetic version, use super with the camelCase name stored on the adapter object:

// mysql/adapter/sql/sqlItemListActive.ts
import {handler} from '@feasibleone/blong';

export default handler(
() => ({
async sqlItemListActive(params, $meta) {
// Delegate to the synthetic procedure handler, then post-filter
const rows = await (super as unknown as Record<
string,
(p: object, m: object) => Promise<unknown[]>
>).sqlItemListActive(params, $meta);
return (rows as Array<{itemActive: boolean}>).filter(r => r.itemActive);
},
}),
);

Note: the super key is the camelCase name (not the methodId lowercase form), because the framework stores synthetic handlers under both keys to support this pattern.


Explicit schema helper methods​

The adapter exposes four schema operations as methods on the port itself — these are what the ready() hook calls, and they are reachable from a handler as this.<name> when imperative control is wanted (integration-test cleanup, a one-off):

Adapter methodDescription
schemaTableSyncCreate or alter one table to match a TypeBox schema (table, schema, opt)
schemaProcedureSyncCreate or replace a list of stored procedures
schemaProcedureBindDiscover procedures in the database and return callables + schemas
schemaHandlersBindAttach a group's handlers to the port

test/blong-int-adapter/mysql/adapter/sql/ shows how a realm wraps one: sqlSchemaTableSync is a handler that calls this.schemaTableSync('sql_item', schema.mysql.item, {dropColumns}), and sqlSchemaTableDrop is a handler that drops the tables through the query builder directly — there is no schemaTableDrop on the adapter, because dropping is a test concern rather than a deployment one. The declarative configuration path remains the preferred route over any of these.


Integration test pattern​

The blong-int-adapter mysql suite demonstrates the full declarative path in mysql/server/test/test/testMysqlSchema.ts. The table and procedure are created by the adapter's ready() hook before any test step runs — the test does not call sqlSchemaTableSync or sqlSchemaProcedureSync for setup.

Step 1 — cleanData: sqlItemFind → sqlItemRemove (auto-bound CRUD)
Step 2 — addActiveItem: sqlItemAdd (auto-bound CRUD)
Step 3 — callSyntheticProc: sqlItemListActive (synthetic procedure handler)
Step 4 — verifyIdempotency: sqlSchemaTableSync → assert created:false, added:[]
Step 5 — dropTable: sqlSchemaTableDrop (cleanup so next run exercises CREATE)

Step 4 calls sqlSchemaTableSync explicitly only because it tests the idempotency guarantee — in production code you would never need to call it manually.