ERPForce DB SQL Core

Private production runtime SQL core for ERPForce services and other projects.

@erpforce/db-sql-core provides safe SQL builders, schema metadata, repository CRUD, driver adapters, transaction helpers, cache primitives, governance policies, observability helpers, introspection, and typed runtime errors.

Production Boundary

This package is runtime-only.

• Import from the package root only: require("@erpforce/db-sql-core").
• Do not import src, dist/*, ./tools, ./experimental, or private files.
• The package does not own database pool lifecycle.
• The package does not run migrations, code generation, or CLI workflows.
• Callers own pools, clients, logging, dependency injection, and service wiring.

For migration tooling, code generation, or dev-only review workflows, use a separate package. This package intentionally ships only the production root runtime surface.

Install

npm install @erpforce/db-sql-core

Install the driver used by the consuming service:

npm install mysql2
# or
npm install pg

Choose The Right API

| Need | Use | |---|---| | Standard CRUD, tenant policy, soft delete, pagination, streaming | BaseRepositoryV2 | | Custom SQL while preserving parameterization and schema policy | createQueryBuilder | | Run compiled SQL against an existing pool/client | createMysql2Adapter, createPgAdapter, executeCompiled | | Enforce field-level read/write rules | repository policies, applyFieldMasking*, applyFieldWritePolicy* | | Cache query/entity results safely | cache key helpers, identity map, L1/L2 caches | | Diagnose production SQL behavior | fingerprints, log metadata, metrics, slow query, explain helpers | | Inspect live table metadata | MySQL/PostgreSQL introspection helpers |

Prefer repository APIs for normal application code. Use builders for custom queries. Use trusted raw SQL only for reviewed internal fragments, never for untrusted request values.

Quick Start

const mysql = require("mysql2/promise");
const {
  BaseRepositoryV2,
  column,
  createMysql2Adapter,
  defineTable,
  normalizeConfig,
} = require("@erpforce/db-sql-core");

const pool = mysql.createPool({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DATABASE,
});

const adapter = createMysql2Adapter(pool);
const config = normalizeConfig({
  dialect: "mysql",
  tenant: { companyId: 7 },
});

const usersTable = defineTable("users", {
  id: column.number().primary().filterable().sortable().defaultSelect(),
  company_id: column.number().tenant("company"),
  name: column.string().filterable().sortable().searchable().defaultSelect(),
  email: column.string().filterable().classification("confidential"),
  deleted_at: column.datetime().softDelete("deletedAt").nullable(),
});

const users = new BaseRepositoryV2({
  table: usersTable,
  config,
  adapter,
});

const inserted = await users.insertOne({
  name: "Ada Lovelace",
  email: "[email protected]",
});

const found = await users.findOne({
  where: { id: inserted.metadata.insertedId },
  select: ["id", "name"],
});

Tenant columns and soft-delete columns are applied by schema/config policy. Write payloads should not manually concatenate SQL.

Direct Builder Example

const { createQueryBuilder, executeCompiled } = require("@erpforce/db-sql-core");

const query = createQueryBuilder(config)
  .from(usersTable)
  .select(["id", "name"])
  .where({ name: { like: "%Ada%" } })
  .orderBy("name", "asc")
  .limit(20)
  .compile();

const result = await executeCompiled(adapter, query, { timeoutMs: 1000 });

The compiled query contains parameter placeholders and values. Values from users, imports, jobs, reports, and filters must stay as values, not interpolated SQL strings.

Documentation

| File | Purpose | |---|---| | FEATURES.md | Capability catalog grouped by subsystem | | docs/api-guide.md | Compact root export reference | | docs/examples.md | Copyable examples for humans and AI agents |

Build And Test

npm run build
npm run test:unit

Production Artifact

Ship only:

• dist/**
• package.json
• README.md

Do not ship source maps, tests, local docs bundles, CLI files, migration files, or experimental review helpers unless a consuming release process explicitly requires them.