Miss any of our Open RFC calls?Watch the recordings here! »

sql-ts-knex-generator

0.1.10 • Public • Published

sql-ts-knex-generator

Combine Abilities of Generate TypeScript definitions sql-ts and CRUD knex query helper functions from database schemas.

Supports the following databases: MySQL, Microsoft SQL Server, SQLite and Postgres.

Installation

npm install sql-ts-knex-generator

OR

After you clone the repository in your client

Run npm install

Install your relevant SQL driver. Refer to the knex documentation to determine which driver you should install.

For example npm install mssql.

Create a configuration file, for example mysql.json. This will mirror connection details from knex. The dialect or client property will determine the SQL type.

The most basic MSSQL setup is below, modify as appropriate. Additional options can be applied by referring to the Config.

{
  "client": "mssql",
  "schemas": ["dbo"],
  "interfaceNameFormat": "${table}Entity",
  "template": "node_modules/sql-ts-knex-generator/dist/templates/interfaces-and-functions.handlebars",
  "schemaAsNamespace": false,
  "tableAsNamespace": true,
  "createIndexFile": true,
  "separateTableFile": true,
  "connection": {
    "host": "***",
    "user": "***",
    "password": "***",
    "database": "***",
    "options": {
      "enableArithAbort": false
    }
  }
}

Usage

in root of your project after you installed your preferred driver:

node ./bin/sql-ts-knex-generator -c ./config.json

Based on your config file:

  • It a file will be exported with the filename Database.ts (or with the name specified in the configuration) at the current working directory. Warning: if this file exists, it will be overwritten.
  • It will generate a directory named generated in current working directory and put all files on that.

Alternatively, use as a node module, passing the configuration object as the first argument.

toObject

Retrieves the database schema as a simple object.

import sqlts from './index'
 
const config = {
  ...
}
 
const definitions = await sqlts.toObject(config)

fromObject

Converts the object returned from toObject into a TypeScript definition. This can be used to manipulate the database definitions before they are converted into strings or files, which allows for greater control over the generated typescript.

import sqlts from './index'
 
const config = {
  ...
}
 
const definitions = await sqlts.toObject(config)
 
const tsString = sqlts.fromObject(definitions, config)

For those using TypeScript, you can import the Config definition.

toTypeScript

Retrieves the raw string of the definition file.

import sqlts from './index'
 
const config = {
  ...
}
 
const definitions = await sqlts.toTypeScript(config)

Config

The configuration extends the knex configuration with some additional properties for table filtering and type overriding.

tables

Filter the tables to include only those specified. These must be in the format schema.table. See interfaceNameFormat for schema naming conventions.

{
  "dialect": "...",
  "connection": {},
  "tables": ["schema1.Table1", "schema2.Table2"]
}

excludedTables

Filter the tables to exclude those specified. These must be in the format schema.table. See interfaceNameFormat for schema naming conventions.

Excluding a table takes precedence over including it. Specifying a table in both configuration options will exclude it.

{
  "dialect": "...",
  "connection": {},
  "excludedTables": [
    "schema1.knex_migrations",
    "schema1.knex_migrations_lock",
    "schema2.android_metadata"
  ]
}

typeOverrides

Override the types on a per column basis. This requires the full name of the column in the format schema.table.column. See interfaceNameFormat for schema naming conventions. Omit the schema for databases that do not use them.

{
  "dialect": "...",
  "connection": {},
  "typeOverrides": {
    "dbo.Table_1.ColumnName": "string",
    "dbo.Table_1.Name": "number"
  }
}

typeMap

Adds additional types to the type resolution. The order in which types are resolved is typeOverrides (see above), this typeMap, then the global TypeMap file before defaulting to any.

{
  "dialect": "...",
  "connection": {},
  "typeMap": {
    "number": ["decimal", "float"],
    "string": ["nvarchar", "varchar"]
  }
}

filename

Specifies the name that the file should be saved as. Defaults to "Database.ts". The .ts extensions is not required.

{
  "dialect": "...",
  "connection": {},
  "filename": "DatabaseModels"
}

interfaceNameFormat

Specifies the pattern that the exported interface names will take. The token "${table}" will be replaced with the table name. Defaults to ${table}Entity.

The below will export interfaces with such names as UserModel and LogModel for tables with names User and Log respectively.

{
  "dialect": "...",
  "connection": {},
  "interfaceNameFormat": "${table}Model"
}

tableNameCasing

Determines the casing for table names before being passed into the name generator. Valid values are "pascal" for PascalCase and "camel" for camelCase. If the value is empty, missing or invalid, no case conversion will be applied to the table names.

{
  "dialect": "...",
  "connection": {},
  "tableNameCasing": "pascal"
}

columnNameCasing

Determines the casing for column names before being passed into the name generator. Valid values are "pascal" for PascalCase and "camel" for camelCase. If the value is empty, missing or invalid, no case conversion will be applied to the column names.

{
  "dialect": "...",
  "connection": {},
  "columnNameCasing": "camel"
}

singularTableNames

Removes the "s" from the end of table names before being passed into the name generator. Defaults false.

{
  "dialect": "...",
  "connection": {},
  "singularTableNames": true
}

schemaAsNamespace

Specifies whether the table schema should be used as a namespace. The functionality differs between database providers. Defaults to false.

Provider Source
Postgres The schema that the table belongs to.
SQL Server The schema that the table belongs to.
MySQL The database name.
SQLite 'main'
{
  "dialect": "...",
  "connection": {},
  "schemaAsNamespace": true
}

schemas

Specifies which schemas to import. This has no effect on SQLite databases. If MySQL is connected to without specifying a database, this can be used to import from multiple databases. Default [] (all schemas).

Note for Postgres users: The default schema on Postgres is public which is a reserved keyword in TypeScript. You may need to use the noImplicitUseStrict flag when transpiling.

This has no effect on SQLite as the concept of schemas do not exist.

{
  "dialect": "...",
  "connection": {},
  "schemas": ["dbo", "schema1"]
}

additionalProperties

Specifies additional properties to be assigned to the output TypeScript file. Key is in the format schema.table and the value is a list of raw strings.

{
  "dialect": "...",
  "connection": {},
  "additionalProperties": {
    "dbo.Table_1": ["propertyOne: string", "propertyTwo?: number | null"]
  }
}

extends

Specifies the superclass than should be applied to the generated interface. Key is in the format schema.table and the value is the extension to apply. The following would generate export interface Table_1 extends Extension, AnotherExtension { }

{
  "dialect": "...",
  "connection": {},
  "extends": {
    "dbo.Table_1": "Extension, AnotherExtension"
  }
}

template

Specifies the handlebars template to use when creating the output TypeScript file relative to the current working directory. See dist/templates/interfaces.handlebars for the default template.

{
  "dialect": "...",
  "connection": {},
  "template": "./templates/interfaces.handlebars"
}

separateTableFile

generate separate file for each table this is useful when generating CRUD functions alongside table interface in separate file

tableAsNamespace

use table name as name space. this is useful when generating CRUD functions alongside table interface in separate file

createIndexFile

generate index.ts file and export all generated table files this is useful when generating CRUD functions alongside table interface in separate file

Bespoke Configuration

mssql dialect with msnodesqlv8 driver (Windows only)

For instructions to setup the SQL Server Native client installed see mode-mssql/issue/338

You will need to get the connectionString correct as this configuration has only been tested using the connectionString.

Sample configuration (replace the HostName and DatabaseName accordingly).

{
  "dialect": "mssql",
  "connection": {
    "driver": "msnodesqlv8",
    "connectionString": "Driver={SQL Server Native Client 10.0};Server=HostName;Database=DatabaseName;Trusted_Connection=yes;"
  }
}

Credits:

Install

npm i sql-ts-knex-generator

DownloadsWeekly Downloads

28

Version

0.1.10

License

MIT

Unpacked Size

74.5 kB

Total Files

29

Last publish

Collaborators

  • avatar