@simplysm/orm-common

Platform-neutral ORM module for the Simplysm framework. Provides type-safe query building, schema definition, expression building, and DDL generation for MySQL, MSSQL, and PostgreSQL.

Installation

npm install @simplysm/orm-common
# or
pnpm add @simplysm/orm-common

API Overview

Core

| Export | Type | Description | |--------|------|-------------| | defineDbContext | Function | Create a DbContext definition (blueprint) with tables, views, procedures, and migrations | | createDbContext | Function | Create a DbContext instance from a definition and executor | | DbTransactionError | Class | Standardized database transaction error with DBMS-independent error codes | | DbErrorCode | Enum | Transaction error codes | | _Migration | Constant | System migration table definition (TableBuilder) |

Queryable / Executable

| Export | Type | Description | |--------|------|-------------| | Queryable | Class | Fluent query builder for SELECT, INSERT, UPDATE, DELETE, UPSERT | | queryable | Function | Factory to create Queryable accessors for tables/views | | Executable | Class | Stored procedure execution wrapper | | executable | Function | Factory to create Executable accessors for procedures | | parseSearchQuery | Function | Parse search query string into SQL LIKE patterns | | ParsedSearchQuery | Interface | Parsed search query result | | getMatchedPrimaryKeys | Function | Match FK columns with target table PK columns | | QueryableRecord | Type | Maps DataRecord fields to ExprUnit proxies | | QueryableWriteRecord | Type | Maps DataRecord fields to ExprInput for writes | | NullableQueryableRecord | Type | QueryableRecord with nullable primitives (for LEFT JOIN) | | UnwrapQueryableRecord | Type | Reverse-map QueryableRecord to DataRecord | | PathProxy | Type | Type-safe path proxy for include() |

Expression Builder

| Export | Type | Description | |--------|------|-------------| | expr | Object | Dialect-independent SQL expression builder (100+ methods) | | ExprUnit | Class | Type-safe expression wrapper | | WhereExprUnit | Class | WHERE clause expression wrapper | | ExprInput | Type | Union of ExprUnit or literal values | | SwitchExprBuilder | Interface | CASE WHEN builder (fluent API) |

Schema Builders

| Export | Type | Description | |--------|------|-------------| | Table | Function | Create a TableBuilder | | TableBuilder | Class | Fluent API for table definitions | | View | Function | Create a ViewBuilder | | ViewBuilder | Class | Fluent API for view definitions | | Procedure | Function | Create a ProcedureBuilder | | ProcedureBuilder | Class | Fluent API for stored procedure definitions | | ColumnBuilder | Class | Column definition builder | | createColumnFactory | Function | Column type factory | | IndexBuilder | Class | Index definition builder | | createIndexFactory | Function | Index factory | | ForeignKeyBuilder | Class | FK relation builder (N:1) | | ForeignKeyTargetBuilder | Class | FK reverse-reference builder (1:N/1:1) | | RelationKeyBuilder | Class | Logical relation builder (no DB FK) | | RelationKeyTargetBuilder | Class | Logical reverse-reference builder | | createRelationFactory | Function | Relation factory |

Query Builder & Result Parsing

| Export | Type | Description | |--------|------|-------------| | createQueryBuilder | Function | Create a dialect-specific QueryBuilder | | QueryBuilderBase | Abstract Class | Base for QueryDef to SQL rendering | | MysqlQueryBuilder | Class | MySQL query builder | | MssqlQueryBuilder | Class | MSSQL query builder | | PostgresqlQueryBuilder | Class | PostgreSQL query builder | | ExprRendererBase | Abstract Class | Base for Expr to SQL rendering | | MysqlExprRenderer | Class | MySQL expression renderer | | MssqlExprRenderer | Class | MSSQL expression renderer | | PostgresqlExprRenderer | Class | PostgreSQL expression renderer | | parseQueryResult | Function | Parse raw DB results into typed objects |

Types

See Types documentation for all type exports including:

• Database: Dialect, dialects, IsolationLevel, DataRecord, DbContextExecutor, ResultMeta, Migration, QueryBuildResult
• DbContext: DbContextDef, DbContextBase, DbContextStatus, DbContextInstance, DbContextConnectionMethods, DbContextDdlMethods
• Column: DataType, ColumnPrimitive, ColumnPrimitiveStr, ColumnPrimitiveMap, ColumnMeta, dataTypeStrToColumnPrimitiveStr, inferColumnPrimitiveStr, InferColumnPrimitiveFromDataType
• Column Builder: ColumnBuilderRecord, InferColumns, InferColumnExprs, InferInsertColumns, InferUpdateColumns, RequiredInsertKeys, OptionalInsertKeys, DataToColumnBuilderRecord
• Relation: RelationBuilderRecord, InferDeepRelations, ExtractRelationTarget, ExtractRelationTargetResult
• Expression: Expr (50+ variants), WhereExpr, DateUnit, WinFn, WinSpec, all Expr* interfaces
• QueryDef: QueryDef (30+ variants), QueryDefObjectName, DDL_TYPES, DdlType, all *QueryDef interfaces

Usage Examples

Define Schema and DbContext

import { Table, defineDbContext, createDbContext, expr } from "@simplysm/orm-common";

const User = Table("User")
  .database("mydb")
  .columns((c) => ({
    id: c.bigint().autoIncrement(),
    name: c.varchar(100),
    email: c.varchar(200).nullable(),
    companyId: c.bigint(),
  }))
  .primaryKey("id")
  .indexes((i) => [i.index("email").unique()])
  .relations((r) => ({
    company: r.foreignKey(["companyId"], () => Company),
    posts: r.foreignKeyTarget(() => Post, "author"),
  }));

const MyDb = defineDbContext({ tables: { user: User, post: Post, company: Company } });
const db = createDbContext(MyDb, executor, { database: "mydb" });

Query Data

await db.connect(async () => {
  const users = await db.user()
    .where((u) => [expr.eq(u.status, "active")])
    .orderBy((u) => u.name)
    .execute();

  const posts = await db.post().include((p) => p.author).execute();

  await db.user().insert([{ name: "New User", companyId: 1 }]);

  await db.user()
    .where((u) => [expr.eq(u.id, 1)])
    .update(() => ({ name: expr.val("string", "Updated") }));
});

Expression Builder

// Window functions
db.order().select((o) => ({
  ...o,
  rowNum: expr.rowNumber({ partitionBy: [o.userId], orderBy: [[o.createdAt, "DESC"]] }),
}));

// CASE WHEN
db.user().select((u) => ({
  grade: expr.switch<string>()
    .case(expr.gte(u.score, 90), "A")
    .default("C"),
}));

// Raw SQL
db.user().select((u) => ({
  data: expr.raw("string")`JSON_EXTRACT(${u.metadata}, '$.email')`,
}));