ts-sql-codegen
This utility generates table mapper classes for ts-sql-query by inspecting a database through tbls.
While it is possible to use ts-sql-query without any code-generation, we have to manually keep the table-mappings in sync with the database which is burdensome.
With this utility, this mapping can be derived from the database itself as part of build process, eliminating the manual effort, reducing room for erroneous mapping and enabling true end-to-end type-safety.
The code-generation process is highly customizable and almost all the defaults (which are sensible for most databases) can be configured if needed.
Features
- Generate clean typescript table/view mappers from database schema
- Ability to include/exclude tables/fields
- Ability to customize field types and type adapters
- Idiomatic pascal-cased/camel-cased table/field name mapping by default and ability to customize names if desired.
- Auto-detection & mapping of computed fields, primary key columns.
- Automatic documentation propagation from SQL comments
Installation:
Step 1: Install tbls and ensure it is available in path
Refer: https://github.com/k1LoW/tbls#install
Step 2: Install ts-sql-codegen
npm i --dev ts-sql-codegen
Note:
- Global installation (
npm i -g ts-sql-codegen) can be convenient, but is preferrable to have ts-sql-codegen as a project dependency to avoid versioning issues.
Usage (CLI):
After every database schema change:
Step 1: Generate yaml schema file from database using tbls
Example: tbls out postgres://postgres:password@localhost:5432/testdb -t yaml -o schema.yaml
Step 2: Pass this schema file to ts-sql-codegen
Example: ts-sql-codegen --schema ./schema.yaml --output-dir ./src/generated --connection-source ./src/db/connection-source
Note:
- All paths are relative to cwd
- Above options are default, so you can also just run ts-sql-codegen
Usage (Programmatic):
Programmatic usage enables a wider set of customization options.
Example:
const { Generator } = require('ts-sql-codegen');
const options = {
schemaPath: './schema.yaml',
connectionSourcePath: './connection-source.ts'
}
const generator = new Generator(options);
await generator.generate();
Refer to Generator and GeneratorOpts for available options.
The test suite also has examples of more complex customizations.
For advanced use-cases (eg. custom templates, pre/post processing of generated code and custom logic for table/column/field mapping) it is recommended to extend the Generator class in project. We intend to keep the customization options that the constructor accepts focussed on primary common use-cases.
Suggested workflow
This utility is expected to be used in a database-first workflow, where the developers first plan and implement the database level changes, and then adapt their code for the updated schema.
- Use a database migration system for versioned database schema evolution. You are free to choose a migration utility that you like (eg. dbmate, liquibase, flyway etc.) - if you are starting out we recommend dbmate, a simple and easy to use solution.
- Integrate the following steps into your build lifecycle
- Use migration utility to update database schema eg.
dbmate up - Dump yaml representation of schema through tbls eg.
tbls out postgres://localhost:5432/mydb -t yaml -o schema.yaml - Generate code using ts-sql-codegen eg.
ts-sql-codegen --schema ./schema.yaml # additional options
- Use migration utility to update database schema eg.
Recipies
Generating mappers for a subset of available tables or fields
const options = {
schemaPath,
connectionSourcePath,
outputDirPath,
tables: {
// Include only whitelisted tables
include: [/authors/, "books"],
// Similar exclude can be used to blacklist
},
fieldMappings: [
{
tableName: "authors",
columnName: "name",
// Ignore this field when generating table mapper
generatedField: false,
}
]
}
Custom DB types
ts-sql-query supports custom database types through type-adapters.
You can configure the code-generator to use type-adapters for specific columns or specific database types through field mappings.
const options = {
schemaPath: './schema.yaml',
connectionSourcePath: './connection-source.ts',
fieldMappings: [
{
// Field matching criteria:
//
// Match any column of any table where column type in database
// is the class_participation_policy custom type
columnType: "class_participation_policy",
// For fields matched by above criteria,
// use the ClassParticipationPolicyAdapter type adapter
// which you will have to implement.
//
// The import paths are resolved relative to cwd, and converted
// to relative paths wrt the generated file
//
// Generated code will include an import like this:
// import { ClassParticipationPolicyAdapter, ClassParticipationPolicy } from '../adapters';
generatedField: {
type: {
kind: 'custom',
adapter: {
name: "ClassParticipationPolicyAdapter",
importPath: "src/db/adapters",
},
tsType: {
name: "ClassParticipationPolicy",
importPath: "src/db/adapters",
},
dbType: {
name: 'class_participation_policy'
}
},
},
},
{
// Alternatively, the field matching criteria
// can point to specific column of a specific table
tableName: "classrooms",
columnName: 'participation_policy',
// Instead of exact strings above, we could also use regular expressions
generatedField: { ... }
}
]
}
Multiple databases/schema
The codegenerator does not have any special support for multi-db or multi-schema scenarios.
You can simply run ts-sql-codegen multiple times for different databases/different schema.
Filtering tables by schema
The tbls schema dump contains table names with schema prefix. We can target this prefix in table inclusion criteria:
const options = {
tables: {
include: [/^public\..*/]
}
}
This can be helpful, for instance, if we want tables from different schema to be generated with different configurations or different output directories.
Disambiguating tables in multi-schema scenarios
Use of tableMapping.useQualifiedTableName=true is recommended when the application can simultaneously use tables from multiple schema
const options = {
tableMapping: {
useQualifiedTableName: true
}
}
With this option the output looks like:
export class AuthorsTable extends Table<DBConnection, 'PublicAuthorsTable'> {
// ~~~~~~
// ^
// .. fields ...
constructor() {
super('public.authors')
// ~~~~~~~
// ^
}
}
Specifying id prefix for multi-db scenarios
Use of idPrefix is recommended to ensure that table ids passed to ts-sql-query is unique when application can connect to tables with same name from multiple databases.
const options = {
tableMapping: {
idPrefix: 'ReportingDB'
}
}
With this option the output looks like:
export class AuthorsTable extends Table<DBConnection, 'ReportingDBAuthorsTable'> {
// ~~~~~~~~~~~
// ^
// .. fields ...
constructor() {
super('authors')
}
}
This option will override the id prefix derived from schema name if tableMapping.useQualifiedTableName is true.
Known Limitations
- While ts-sql-codegen works with many databases and adapters, this utility has been tested only with postgresql & sqlite. Please report bugs if you face issues.
- Only databases which are supported by both ts-sql-query and tbls can be supported.
- Enum/custom type inspection support is currently limited - it is required to manually specify typescript types and adapters for now.
- Typescript is assumed - plain js projects are not supported currently
Contributing
Thanks for your interest in contributing to this project. Pull requests and feature enhancements are welcome.
This utility is being used in a project with hundreds of tables, so any backward incompatible changes in generated code are highly undesirable.
Feature flags are recommended for aspects which are not beneficial to all/most users.
Code-generation should be last resort - if some feature can be supported in ts-sql-query itself, we recommending creating a PR there.
49 Jan 1, 2023
10 Oct 16, 2022
28k Jan 2, 2023
6.8k Jan 3, 2023
6.1k Jan 9, 2023
2k Dec 30, 2022
6 Nov 15, 2022
15 Dec 22, 2022
7 Nov 2, 2022
8 Dec 15, 2022