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
| Field | Type | Default | Description |
|---|---|---|---|
sync | boolean | false | Enable schema sync (deployment-time job only, not normal startup) |
tables | Record<string, ISchemaTable|TObject> | {} | Tables to create/alter |
seed | boolean | false | Enable production seed data from .db.asset YAML/JSON files |
dbTest | boolean | false | Enable test seed data from .dbTest.asset YAML/JSON files |
procedurePaths | string[] | [] | Folders scanned for *.sql files (base name = procedure name) |
procedures | Record<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()carriesdefault: 'auto-increment'and becomes aBIGINT AUTO_INCREMENT,type.ulid()carriesdefault: 'ulid'and becomesBINARY(16), andtype.uuid()carriesdefault: 'uuid'and becomes a binary UUID — the last two are declaredPRIMARY KEYby the adapter itself. A plainType.Integer()nameditemIdis an ordinaryINT. A composite primary key, or a single-column key on a column that carries no marker, comes from theconstraintssecond pass instead. - Properties listed in the schema's
requiredarray generateNOT NULLcolumns; all others are nullable. - Use
dropColumns: truein theISchemaTablespec 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 type | SQL 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 union | nullable 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
| Constraint | Type form | Description |
|---|---|---|
primaryKey | string (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. |
unique | Record<string, {columns?}> | Unique constraint. Key = constraint name (used to generate {table}_ux_{key}). Omit columns to apply to the key-named column. |
foreign | Record<string, string|{references, columns?, onDelete?, onUpdate?}> | Foreign key. references uses {table}.{column} format. Key = local column name. |
index | Record<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 type | Generated name pattern | Example |
|---|---|---|
| Primary key | {table}_pk | realmname_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:
| Filename | Method name |
|---|---|
0-coreTypeMerge.yaml | coreTypeMerge |
realmnameRoleMerge.yaml | realmnameRoleMerge |
accessRoleMerge.yaml | accessRoleMerge |
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
| Intent | schema.seed | schema.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 pattern | Example (namespace=sql, table=item) |
|---|---|
${ns}${Table}Get | sqlItemGet |
${ns}${Table}Find | sqlItemFind |
${ns}${Table}Add | sqlItemAdd |
${ns}${Table}Edit | sqlItemEdit |
${ns}${Table}Remove | sqlItemRemove |
${ns}${Table}Merge | sqlItemMerge |
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 method | Description |
|---|---|
schemaTableSync | Create or alter one table to match a TypeBox schema (table, schema, opt) |
schemaProcedureSync | Create or replace a list of stored procedures |
schemaProcedureBind | Discover procedures in the database and return callables + schemas |
schemaHandlersBind | Attach 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.