kysely-schema

Database Table Structure to Code Generation Workflow

This skill is used to convert MySQL CREATE TABLE statements into the Kysely table types required by this repository, and to continue completing the corresponding insert, update, or query methods when the user needs them.

Applicable Scenarios

Use when given a CREATE TABLE statement and needing to complete the table structure types in this repository, or to continue completing the corresponding insert/update methods.

If the user only asks to complete table types, finish the src/types/database.d.ts DB interface, explicit XxxDB、Selectable、Insertable、Updateable and then stop; do not additionally add service methods.

If the user continues to request adding insert, update, upsert, or query methods, then proceed to the corresponding *-modify.ts or *-query.ts。

Overall Process

读取 DDL
   │
   ▼
识别表名、字段名、类型、NULL、DEFAULT、自增、注释
   │
   ▼
转换成 camelCase 字段和 Kysely 字段类型
   │
   ▼
更新 src/types/database.d.ts 的 DB 接口和 XxxDB 类型
   │
   ├─ 用户只要表类型 ──► 停止
   │
   └─ 用户还要 service 方法
          │
          ▼
     选择对应领域的 *-modify.ts 或 *-query.ts
          │
          ▼
     导入 Insertable / Updateable / Selectable 类型
          │
          ▼
     生成插入、更新、upsert 或查询方法
          │
          ▼
     检查字段映射、JSDoc、import 合并和 SQL 名称

Mandatory Rules

| Rule | Correct Approach | Common Mistake | | -------------------- | ------------------------------------------------ | ---------------------------- | ------------------- | | New table DB shape | Write explicit XxxDB | Use ToGenerated to cut corners | | Default value field | Write Generated<type> | Also write as type | null | | Allows NULL and has no default value | Write type | null | Ignore NULL, write as required | | Auto-increment primary key | Write Generated<number> | Write as ordinary number | | Field naming | Use camelCase for TS fields, snake_case for SQL fields and table names | Mixing camelCase and snake_case | | Type location | Table types only contain src/types/database.d.ts | Temporarily assemble types in the service | | service location | Put create/delete/update in *-modify.ts, put queries in *-query.ts | Mix changes and queries into one entry point | | Comments | Add JSDoc to exported types, exported functions, and class static methods | Generated methods have no JSDoc | | Imports | Merge import / import type from the same path | Split multiple imports from the same path |

DDL to Kysely Type Mapping

For field judgment, prioritize looking at AUTO_INCREMENT、DEFAULT、NULL, do not guess the TS type based only on the MySQL type.

| DDL Feature | Kysely Field Type | Description | | --------------------------------- | ------------------------------------ | --------------------------------- | --------------------- | | AUTO_INCREMENT primary key | Generated<number> | Auto-increment field is generated by the database | | Any DEFAULT ... field | Generated<type> | Once there is a default value, no longer mark | null | | NOT NULL and has no default value | type | Caller must pass it when inserting | | NULL and has no default value | type | null | Only in this case mark null | | datetime / timestamp has default value | Generated<Date> or the project's existing time type | follow database.d.ts for similar table conventions | | decimal / numeric | string | Preserve decimal precision; do not write number directly | | tinyint / int / bigint | Follow the project's equivalent field | Do not casually create magic numbers for business status codes | | varchar / text | string | Comments may describe the field's business meaning | | json | Follow the project's existing JSON field type | Do not use any or unknown to muddle through |

Step 1: Add the table type to the DB interface

In src/types/database.d.ts of DB add the new table to the type interface and place it at the end of the corresponding business group.

export type DB = {
  // monitor
  m_monitor_evaluation: MonitorEvaluationDB;
  m_monitor_evaluation_list: MonitorEvaluationListDB;
  m_monitor_evaluation_dimension: MonitorEvaluationDimensionDB;
};

Step 2: Create the table structure type

Write the table structure type directly in an explicit Kysely style; do not wrap it in ToGeneratedagain. Handle field judgments according to the mapping table above.

A complete example is as follows.

/**
 * m_monitor_evaluation_list 课堂评价明细
 */
export type MonitorEvaluationListDB = {
  id: Generated<number>;
  monitorId: number; // 三率主记录ID
  dimensionId: number; // 维度ID
  score: string; // 课堂评价分数
  avgScore: string | null; // 平均分数
  type: Generated<number>; // 0 接口获取 1自己算
};
export type MonitorEvaluationList = Selectable<MonitorEvaluationListDB>;
export type InsertMonitorEvaluationListDB = Insertable<MonitorEvaluationListDB>;
export type UpdateMonitorEvaluationListDB = Updateable<MonitorEvaluationListDB>;

If the current task only requires adding types, you can stop here; there is no need to force adding the modify method.

Step 3: Choose where the service goes

When a service method needs to be added, first choose the file by responsibility; do not mix queries and mutations together.

Requirement	File location	Method tendency
Batch insert, insert-update, update by id, delete	The corresponding domain's *-modify.ts	dbInsert<Table>、dbUpdate<Table>ByIds、dbDelete<Table>ByIds
List, detail, aggregation, statistics, conditional query	The corresponding domain's *-query.ts	dbGet<Table>...、dbList<Table>...
Multi-step complex business orchestration	Corresponding domain logic/	Only split into a class when the flow is complex enough
Just a very thin pass-through	Keep it in the controller or the existing service	Do not add thin logic just for the sake of form

Step 4: Import the new type in the modify file

In the corresponding src/service/**/modify.ts file, import the newly added type corresponding to this table. Type imports from the same path must be merged into one.

import type { InsertMonitorEvaluationListDB, UpdateMonitorEvaluationListDB } from "#types/database.d.ts";

Step 5: Create the insert or update method

Place the method in the corresponding business class, and keep the implementation consistent with the existing project.

Field mapping must check both updateField、values、MySQL VALUES(...) and the PostgreSQL-compatible branch EXCLUDED.... Field names use camelCase in TS objects, and real snake_case in raw SQL strings.

/**
 * 插入或更新课堂评价明细
 */
static async dbInsertMonitorEvaluationLists<T extends InsertMonitorEvaluationListDB>(
  params: DBInsertParams<T>,
) {
  const { dataList, tx = db } = params;
  if (isFalsy(dataList)) return [];

  const first = dataList[0];
  const updateField = {
    id: isValidValue(first.id),
    monitorId: isValidValue(first.monitorId),
    dimensionId: isValidValue(first.dimensionId),
    score: isValidValue(first.score),
    avgScore: isValidValue(first.avgScore),
    type: isValidValue(first.type),
  };

  const values = dataList.map((item) => ({
    id: item.id,
    monitorId: item.monitorId,
    dimensionId: item.dimensionId,
    score: item.score,
    avgScore: item.avgScore,
    type: item.type,
  }));

  const result = await DBUtil.upsert(
    tx.insertInto("m_monitor_evaluation_list").values(values),
    {
      id: updateField.id ? sql<number>`VALUES(id)` : undefined,
      monitorId: updateField.monitorId ? sql<number>`VALUES(monitor_id)` : undefined,
      dimensionId: updateField.dimensionId ? sql<number>`VALUES(dimension_id)` : undefined,
      score: updateField.score ? sql<string>`VALUES(score)` : undefined,
      avgScore: updateField.avgScore ? sql<string | null>`VALUES(avg_score)` : undefined,
      type: updateField.type ? sql<number>`VALUES(type)` : undefined,
    },
    {
      id: updateField.id ? sql.ref<number>("EXCLUDED.id") : undefined,
      monitorId: updateField.monitorId ? sql.ref<number>("EXCLUDED.monitor_id") : undefined,
      dimensionId: updateField.dimensionId ? sql.ref<number>("EXCLUDED.dimension_id") : undefined,
      score: updateField.score ? sql.ref<string>("EXCLUDED.score") : undefined,
      avgScore: updateField.avgScore ? sql.ref<string | null>("EXCLUDED.avg_score") : undefined,
      type: updateField.type ? sql.ref<number>("EXCLUDED.type") : undefined,
    },
  )
    .$call(debugSQL(false))
    .executeTakeFirst();

  return DBUtil.setInsertId(dataList, Number(result.insertId));
}

Field mapping checklist

| Checkpoint | What to check | | ------------------------------------------ | -------------------------------------------------- | -------------------------------------- | | DB interface | Whether the new table name is placed in the appropriate group, and whether the table name remains snake_case | | XxxDB | Whether every DDL field exists, and whether camelCase is correct | | Generated<> | Whether auto-increment fields and default fields are all covered | | | null | Whether it appears only in fields that allow NULL and have no default value | | Selectable / Insertable / Updateable | Whether all three exported types are present | | updateField | Whether all fields that allow upsert updates are covered | | values | Whether no fields are missing, and snake_case was not written into TS object keys | | VALUES(...) | Whether raw SQL fields use real snake_case | | EXCLUDED... | Whether field names match the real SQL fields | | JSDoc | Whether exported functions or static methods explain the business purpose |

Common mistakes

| Mistake | Correction | | -------------------------------------------- | ------------------------------------------------ | ---------------------- | | Directly applying to a new table ToGenerated | Change to explicit XxxDB | | DDL has default, but the field type is still written as | null | Change to Generated<type> | | updateField、values、upsert Missing fields in parameters | Check against the DDL and field mapping checklist and fill them in | | Mixing camelCase and snake_case | Use camelCase for TS fields, snake_case for SQL strings | | Exported functions and static methods have no JSDoc | Add a brief JSDoc above the definition | | Adding an enum just for status codes | First determine whether it is reused across files; do not over-enumerate simple single-point semantics | | Using any、unknown or assertions to bypass types | Add accurate types or follow the existing JSON field style |

Verification checklist

| Item | Pass criteria | | ---------------- | ----------------------------------------------- | ----- | | DB interface | The new table type has been added | | Table structure type | Use explicit XxxDB style | | Exported type | Already exported Selectable、Insertable、Updateable | | Default values | Fields with default values already use Generated<> | | null | default fields are not mistakenly marked | null | | service type import | The corresponding file has imported the new type, and the import has been merged | | Insert or update method | Field mapping is complete, and raw SQL field names are correct | | Naming | TS camelCase / SQL snake_case conventions are consistent | | Comments | JSDoc has been filled in |