@pylonts/mysql-schema — MySQL Schema DSL

Overview

@pylonts/mysql-schema is a MySQL driver for the standard schema type system defined in @pylonts/schema-core. It takes column definitions using standard FieldType (STRING / INT / REAL / ENUM / DATE / DATETIME) and translates them to MySQL DDL SQL.

It serves as the Single Source of Truth for database schemas, while also providing type information for mock data generation.

Docs

Design guidelines for using this DSL in projects (language: Chinese):

| Doc | Purpose | |---|---| | database-design-guideline.md | Generic database design rules: normalization, controlled redundancy, constraints, indexes, audit fields | | schema-dsl-guideline.md | Schema DSL authoring rules: file layout, column/enum definitions, FK patterns, indexes, table-level metadata | | schema-field-dictionary.md | Field naming conventions (id / code / no semantics) bridging business attributes to snake_case column names | | pay-naming-guideline.md | Payment-industry field naming rules: amt / rate / role-prefix conventions for transaction tables |

Architecture

| Layer | File | Responsibility | |---|---|---| | Type system | @pylonts/schema-core | FieldType + BaseFieldDef + Semantics + defineEnum — standard abstract types | | Column definition | column-def.ts | ColumnDef union type (FieldType-branch parameter locking) | | Column reference | column-ref.ts | ColumnRef class + col() factory | | Table definition | table-def.ts | TableSchema / ForeignKey / IndexDef interfaces | | Factory | define-table.ts | defineTable(name, input) → TableSchema (sets column identity) | | DDL generation | generate-ddl.ts | generateDdl(tables) → SQL string | | Schema build | build.ts | buildSchema(options) — scan + DDL + enum in one call | | Auto-scan | scan-tables.ts | scanTables(dir) auto-discovers *.table.ts files (defineTable form + legacy scatter form) | | Enum generation | generate-enums.ts | generateEnums({ schemaDir, outDir }) generates TS enums from defineEnum | | MySQL translation | mysql-type.ts | Internal MysqlType constants (not exported, not used by schema files) |

Core Types

FieldType (from @pylonts/schema-core)

Standard abstract data types that all layers share. MySQL is just one physical translation:

STRING → VARCHAR(max)
INT    → INT (autoIncrement, primaryKey, default)
REAL   → DECIMAL(precision, scale) — always requires precision + scale
ENUM   → VARCHAR(20) — fixed length for enum values
DATE   → DATE
DATETIME → DATETIME

Schema files import FieldType from @pylonts/schema-core and never reference MySQL types directly.

ColumnDef Union Type

type ColumnDef = IntColumn | RealColumn | StringColumn | EnumColumn | DateColumn | DateTimeColumn

Each interface restricts parameters by type branch, catching illegal combinations at compile time:

• IntColumn: no max, precision, scale
• StringColumn: no autoIncrement, precision
• DateTimeColumn: default can only be 'CURRENT_TIMESTAMP'
• EnumColumn: always requires enum: EnumDef, no max

ColumnRef

class ColumnRef {
  readonly def: ColumnDef;              // column definition
  _table: string = '';                  // table name (set by defineTable)
  _column: string = '';                 // column name (set by defineTable)
  toString(): string                    // returns _column, or '?' if uninitialized
}

_table / _column are identity fields set by defineTable() at definition time — every column ref passed to defineTable gets its table/column identity immediately, so cross-table FK references resolve without a separate pass.

Usage

Recommended: defineTable form

Each *.table.ts file exports one TableSchema built with defineTable(). The table name is explicit (first argument); enums local to the table are passed in as a field:

// schema/bd.table.ts
import { col, defineTable } from '@pylonts/mysql-schema';
import { defineEnum, FieldType } from '@pylonts/schema-core';
import { Semantics } from './semantics';

const enums = {
  BdStatus: defineEnum('BdStatus', {
    NORMAL: { value: 'normal', label: '正常' },
    FROZEN: { value: 'frozen', label: '已冻结' },
  }),
};

const columns = {
  id:     col({ type: FieldType.INT, autoIncrement: true }),
  name:   col({ type: FieldType.STRING, max: 50, unique: true, semantic: Semantics.BD_NAME }),
  phone:  col({ type: FieldType.STRING, max: 20, semantic: Semantics.BD_PHONE }),
  region: col({ type: FieldType.STRING, max: 50, semantic: Semantics.BD_REGION }),
  status: col({ type: FieldType.ENUM, default: 'normal', enum: enums.BdStatus }),
};

export const bd = defineTable('bd', {
  description: 'BD (Business Developer)',
  enums,
  columns,
  paginated: true,   // large data volume → paginated lists
  actor: true,       // this table represents an Actor (initiates use cases)
});

columns and enums are extracted to local const objects so FK/index references can point at them by name.

Cross-table FK references use the exported table schema:

// schema/store.table.ts
import { col, defineTable } from '@pylonts/mysql-schema';
import { merchant } from './merchant.table';

const columns = {
  id:          col({ type: FieldType.INT, autoIncrement: true }),
  merchant_id: col({ type: FieldType.STRING, max: 12 }),
  // ...
};

export const store = defineTable('store', {
  description: 'Store',
  columns,
  foreignKeys: {
    fk_store_merchant: { columns: [columns.merchant_id], ref: merchant.columns.id, onDelete: 'CASCADE' },
  },
  paginated: true,
});

Manual (small schemas, programmatic use)

import { FieldType } from '@pylonts/schema-core';
import { col, defineTable, generateDdl } from '@pylonts/mysql-schema';

const merchantCols = {
  id:   col({ type: FieldType.STRING, max: 12, primaryKey: true }),
  name: col({ type: FieldType.STRING, max: 100 }),
};
const merchant = defineTable('merchant', { columns: merchantCols, description: 'Merchant' });

const orderCols = {
  id:     col({ type: FieldType.INT, autoIncrement: true }),
  mer_id: col({ type: FieldType.STRING, max: 20 }),
  status: col({ type: FieldType.ENUM, enum: OrderStatusEnum }),
};
const order = defineTable('order', {
  columns: orderCols,
  indexes: [{ columns: [orderCols.status] }],
  foreignKeys: {
    fk_order_merchant: { columns: [orderCols.mer_id], ref: merchant.columns.id, onDelete: 'CASCADE' },
  },
  paginated: true,         // large data volume → paginated lists
  generator: 'snowflake',   // non-autoIncrement PK ID generator
});

const sql = generateDdl([merchant, order]);

Auto-scan

Organize *.table.ts files by naming convention and let scanTables() discover them:

schema/
├── merchant.table.ts
├── order.table.ts
├── coupon.table.ts
└── ...

const tables = await scanTables('./schema');
const sql = generateDdl(tables);

scanTables() is dual-compatible:

• defineTable form (recommended): the module exports a TableSchema (e.g. export const bd = defineTable(...)) — it is used as-is; identities are already set.
• legacy scatter form (compatible): the module exports columns / indexes / foreignKeys / primaryKey / description / paginated / generator / actor separately — scanTables() runs a two-phase init (set _table/_column, then defineTable()) and still works.

Files without a columns export / TableSchema export are skipped with a warning.

Foreign Key Patterns

Direct reference (cross-table)

import { merchant } from './merchant.table';

export const store = defineTable('store', {
  columns,
  foreignKeys: {
    fk_store_merchant: {
      columns: [columns.merchant_id],
      ref: merchant.columns.id,   // direct ColumnRef reference
      onDelete: 'CASCADE',
    },
  },
});

Lazy reference (self/circular references)

export const category = defineTable('category', {
  columns,
  foreignKeys: {
    fk_category_parent: {
      columns: [columns.parent_id],
      ref: () => columns.id,      // lazy evaluation to avoid circular dependency
      onDelete: 'CASCADE',
    },
  },
});

When ref is a () => ColumnRef, it is evaluated during DDL generation.

Composite primary key

export const couponStore = defineTable('coupon_store', {
  columns,
  primaryKey: [columns.coupon_id, columns.store_id],   // composite PK
});

Current limitation: composite FK ref syntax is not finalized. Only single-column FKs are supported.

Table-level Metadata

enums

Table-local enum definitions from defineEnum(). Not included in DDL output as MySQL ENUM — enums are stored as VARCHAR/INT columns; enums feeds the enum product generator (generateEnums). Shared enums across tables should live in _common.ts and be referenced by the same EnumDef object (the generator re-exports instead of duplicating).

paginated

paginated is table-level metadata indicating data volume. Not included in DDL output — it guides downstream components on UI behavior:

| Value | Data Volume | Frontend Behavior | Selector | |---|---|---|---| | true | Large | Admin: paginated list; Mini-program: waterfall | auto-complete (searchable) | | false / unset | Small | Full list | Simple dropdown |

defineTable('order', {
  columns: orderCols,
  paginated: true,
});

Accessible via table._schema.paginated.

generator

Declares an ID generator for non-autoIncrement primary keys. Not included in DDL output — it tells downstream tools which generator to use.

defineTable('pay', {
  columns: payCols,
  primaryKey: payCols.id,
  generator: 'snowflake',
});

Auto-increment PK tables do not need generator.

actor

actor is table-level metadata indicating that the table represents an Actor — a role that can initiate use cases in the system. Not included in DDL output — it signals downstream tools (code generators, validators, flow test scanners) about the semantic role of this table.

| Value | Meaning | Examples | |---|---|---| | true | Rows are autonomous subjects — they initiate actions | admin_user, bd, merchant, user | | false / unset | Rows are objects — they are acted upon | coupon, store, pay |

defineTable('admin_user', {
  columns: userCols,
  actor: true,
});

Accessible via table._schema.actor.

Downstream consumers (e.g. Pylon toolchain) can use this flag to:

• Verify actor tables have required fields (status, authentication columns)
• Generate login/auth DTOs and controller skeletons
• Inject identity into flow tests
• Trace use case diagrams to physical database tables

Business Semantics

Declares the business meaning of a column — not its database type, but what it represents in the domain model. Not included in DDL output — it guides downstream tools (mock generators, test generators, DTO validators, UI components) to understand the column's semantic role.

Usage

import { Semantics } from '@pylonts/schema-core';

col({ type: FieldType.STRING, max: 100, semantic: Semantics.NAME })
col({ type: FieldType.STRING, max: 20, semantic: Semantics.PHONE })
col({ type: FieldType.STRING, max: 50, semantic: Semantics.EMAIL })
col({ type: FieldType.DATETIME, semantic: Semantics.CREATED_AT })

Standard Semantics

The Semantics constant object (from @pylonts/schema-core) provides cross-industry universal meanings:

| Constant | Value | Description | |---|---|---| | Semantics.NAME | 'name' | Person name (natural person) | | Semantics.USERNAME | 'username' | Login username | | Semantics.NICKNAME | 'nickname' | User nickname (may differ from real name) | | Semantics.PHONE | 'phone' | Phone number | | Semantics.EMAIL | 'email' | Email address | | Semantics.PASSWORD_HASH | 'password_hash' | Password hash | | Semantics.ADDRESS | 'address' | Address (general) | | Semantics.CREATED_AT | 'created_at' | Record creation time | | Semantics.UPDATED_AT | 'updated_at' | Record last update time | | Semantics.DATE | 'date' | Date only (no time) | | Semantics.LABEL | 'label' | General label/name (fallback) | | Semantics.DESCRIPTION | 'description' | Text description | | Semantics.SORT_ORDER | 'sort_order' | Display sort order | | Semantics.STATUS | 'status' | General status (discrete values) | | Semantics.ID | 'id' | Numeric ID (fallback) | | Semantics.IMAGE_URL | 'image_url' | Image/icon URL | | Semantics.IP | 'ip' | IP address | | Semantics.CODE | 'code' | Arbitrary string code | | Semantics.URL | 'url' | URL link |

These are intentionally minimal and cross-industry. Domain-specific semantics (merchant, payment, order, etc.) should be defined in dedicated @pylonts/xxx-semantics packages — see ts-libs/README.md for the conventions.

Project-level extension

// schema/semantics.ts
import { Semantics as Base } from '@pylonts/schema-core';
import { PaySemantics } from '@pylonts/pay-semantics';

export const Semantics = {
  ...Base,
  ...PaySemantics,
  // project-specific
  MY_FIELD: 'my_field',
};

// schema/xxx.table.ts
import { col } from '@pylonts/mysql-schema';
import { FieldType } from '@pylonts/schema-core';
import { Semantics } from './semantics';
col({ type: FieldType.STRING, max: 100, semantic: Semantics.MERCHANT_NAME })

The semantic parameter accepts any string — it does not need to come from the Semantics constant. The constants are strictly for developer convenience and cross-project standardization.

Indexes & Constraints

Single-column index (inline in column definition)

col({ type: FieldType.STRING, max: 20, index: true })
// → KEY `idx_status` (`status`)

Single-column unique (inline)

col({ type: FieldType.STRING, max: 50, unique: true })
// → UNIQUE KEY `uk_name` (`name`)

Composite index

indexes: [
  { columns: [columns.status, columns.deleted] },
  // → KEY `idx_status_deleted` (`status`, `deleted`)
]

Composite unique

indexes: [
  { columns: [columns.user_id, columns.order_no], unique: true },
  // → UNIQUE KEY `uk_user_id_order_no` (`user_id`, `order_no`)
]

Naming conventions

| Source | Auto-name | |---|---| | Column index: true | idx_{column} | | Column unique: true | uk_{column} | | Composite index (no name) | idx_{col1}_{col2}_... | | Composite unique (no name) | uk_{col1}_{col2}_... |

Custom index name via IndexDef.name:

{ columns: [columns.status, columns.deleted], name: 'idx_order_status' }

Column Definition Reference

INT (maps to MySQL INT)

| Param | Type | Description | |---|---|---| | type | typeof FieldType.INT | Required | | autoIncrement | true | Optional | | primaryKey | true | Optional (shorthand, can also be declared at table level) | | index | true | Optional, generates single-column index | | unique | true | Optional, generates unique index | | default | number | Optional | | description | string | Optional, human-readable comment describing the field's purpose | | semantic | string | Optional, business meaning — see Business Semantics |

REAL (maps to MySQL DECIMAL(p,s))

| Param | Type | Description | |---|---|---| | type | typeof FieldType.REAL | Required | | precision | number | Required | | scale | number | Required | | default | string | Optional (e.g. '0.00') | | description | string | Optional, human-readable comment describing the field's purpose | | semantic | string | Optional, business meaning — see Business Semantics |

STRING (maps to MySQL VARCHAR(max))

| Param | Type | Description | |---|---|---| | type | typeof FieldType.STRING | Required | | max | number | Required | | primaryKey | true | Optional | | index | true | Optional | | unique | true | Optional | | default | string | Optional | | description | string | Optional, human-readable comment describing the field's purpose | | semantic | string | Optional, business meaning — see Business Semantics |

ENUM (maps to MySQL VARCHAR(20))

| Param | Type | Description | |---|---|---| | type | typeof FieldType.ENUM | Required | | enum | EnumDef | Required — enum definition from defineEnum() | | default | string | Optional | | index | true | Optional | | description | string | Optional, human-readable comment describing the field's purpose | | semantic | string | Optional, business meaning — see Business Semantics |

DATE (maps to MySQL DATE)

| Param | Type | Description | |---|---|---| | type | typeof FieldType.DATE | Required | | description | string | Optional, human-readable comment describing the field's purpose | | semantic | string | Optional, business meaning — see Business Semantics |

DATETIME (maps to MySQL DATETIME)

| Param | Type | Description | |---|---|---| | type | typeof FieldType.DATETIME | Required | | default | 'CURRENT_TIMESTAMP' | Optional | | description | string | Optional, human-readable comment describing the field's purpose | | semantic | string | Optional, business meaning — see Business Semantics |

DDL Generation

generateDdl(tables: TableSchema[]) outputs SQL with:

• CREATE TABLE IF NOT EXISTS for each table
• Default ENGINE=InnoDB, CHARSET=utf8mb4
• Output in definition order, separated by SQL comments (-- comment)
• Foreign keys automatically resolve referenced table/column identities

Compile-time Checks

• Column definitions: type-branch parameter locking (e.g. VARCHAR cannot have autoIncrement)
• Column references: primaryKey / indexes.columns / foreignKeys.columns must use ColumnRef objects, not strings
• FK references: ref must be ColumnRef or () => ColumnRef
• Optional exports: scanTables warns and skips files without a columns export (legacy form) or a TableSchema export (defineTable form)

Enum Generation (generateEnums)

Generates TypeScript enum + label Record files from defineEnum() definitions.

Usage

CLI (same command as DDL generation):

# Generate DDL SQL
pylon-mysql-schema --src ./schema --dest ./001_init.sql

# Generate TS enum files
pylon-mysql-schema generate-enums --src ./schema --dest ./enums

API:

import { generateEnums } from '@pylonts/mysql-schema';

const files = await generateEnums({
  schemaDir: './schema',
  outDir: './enums',
});
// → ['_common', 'coupon', 'merchant', ...]

Output Structure

Each *.table.ts file produces one output file, named after the table:

enums/
├── _common.ts       # shared enum definitions from _common.ts
├── coupon.ts        # enums defined in coupon.table.ts
├── merchant.ts
├── pay.ts
└── ...

_common.ts Handling

If _common.ts exists in the schema directory, its top-level export const Xxx = defineEnum(...) definitions will:

1. Be generated into the _common.ts output file
2. Auto-deduplicate: table files referencing the same EnumDef object (imported from _common) will use export { Xxx } from './_common' instead of regenerating them

Example

Input _common.ts:

export const AcquiringTypeEnum = defineEnum('AcquiringType', {
  WECHAT: { value: 'wechat', label: 'WeChat' },
  UNIONPAY: { value: 'unionpay', label: 'UnionPay' },
});

Input coupon.table.ts (defineTable form):

import { AcquiringTypeEnum } from './_common';

const enums = {
  CouponStatus: defineEnum('CouponStatus', {
    PENDING: { value: 'pending', label: 'Pending' },
    LISTED: { value: 'listed', label: 'Listed' },
  }),
  AcquiringType: AcquiringTypeEnum,  // same EnumDef object as _common → re-exported
};

export const coupon = defineTable('coupon', {
  description: 'Coupon',
  enums,
  columns,
  paginated: true,
});

Output enums/_common.ts:

export enum AcquiringType {
  WECHAT = 'wechat',
  UNIONPAY = 'unionpay',
}
export const ACQUIRING_TYPE_LABEL: Record<AcquiringType, string> = {
  [AcquiringType.WECHAT]: 'WeChat',
  [AcquiringType.UNIONPAY]: 'UnionPay',
};

Output enums/coupon.ts:

export enum CouponStatus {
  PENDING = 'pending',
  LISTED = 'listed',
}
export const COUPON_STATUS_LABEL: Record<CouponStatus, string> = {
  [CouponStatus.PENDING]: 'Pending',
  [CouponStatus.LISTED]: 'Listed',
};

export { AcquiringType, ACQUIRING_TYPE_LABEL } from './_common';

Nullable Report (nullable-report)

Prints a per-table column table showing each column's nullability — a quick review aid for the nullable vs default discipline (see @pylonts/schema-core README, Empty-string default rule).

Usage

CLI:

pylon-mysql-schema nullable-report --src ./schema

No --dest needed — output goes to stdout.

API:

import { generateNullableReport } from '@pylonts/mysql-schema';

const report = await generateNullableReport('./schema');
console.log(report);

Output

One section per table; each row is a column with its SQL type, nullability, and default:

=== merchant — Merchant ===
Column         Type          Nullable  Default
-------------  ------------  --------  ---------
id             VARCHAR(12)   NO
name           VARCHAR(100)  NO
status         VARCHAR(20)   NO        'settled'
uscc           VARCHAR(50)   YES
settle_cycle   VARCHAR(20)   NO        'T+1'

Nullability rule

The Nullable column mirrors the DDL generator exactly (generate-ddl.ts renderNullable):

• autoIncrement columns → NO (always NOT NULL)
• nullable: true → YES
• everything else → NO

Default shows the DDL-rendered default ('T+1', 0, CURRENT_TIMESTAMP, AUTO_INCREMENT) so "nullable without default" and "NOT NULL with a real default" are distinguishable at a glance.

Phrase Check (phrase-check)

Verifies that column names use the project's entity phrase map — long entity names (merchant) must be shortened to their phrase (mer) everywhere, globally and consistently.

The phrase map is project-owned, not hardcoded: copy src/phrase-map.template.ts into the project schema dir as phrase-map.ts and customize it. Only entity concepts that need shortening belong in the map; field-level words (name, phone, status) and short entities (user, store) do not.

Usage

# CLI
pylon-mysql-schema phrase-check --src ./schema
# exit code: 0 = OK, 1 = violations/duplicate phrases found

// API
import { phraseCheck } from '@pylonts/mysql-schema';

const result = await phraseCheck({ schemaDir: './schema' });
console.log(result.violations);
if (!result.ok) process.exit(1);

Map file

schema/phrase-map.ts (project-owned):

export const PhraseMap = {
  merchant: 'mer',
  platform: 'plat',
  category: 'cat',
} as const;

Column names are tokenized by _; a token matching a full-name key is a violation (merchant_id → must be mer_id), tokens matching phrase values pass, unknown tokens are skipped.

Development

# Type check
cd pylon-mysql-schema
npm run typecheck

# Run tests with tsx
npx tsx test-scan/verify.ts

Commit

git add -A && git commit -m "feat: add generateEnums API and CLI subcommand"
git push