Despite

Sample table:

CREATE TABLE groups_securities (
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    updated_at timestamp with time zone NOT NULL DEFAULT now(),
    group_id uuid NOT NULL REFERENCES groups(id),
    security_id uuid REFERENCES securities(id),
    portfolio_id uuid REFERENCES portfolios(id),
    CONSTRAINT groups_securities_pkey PRIMARY KEY (portfolio_id, security_id)
);

My config:

  {
    client: 'pg',
    connection: {
      host,
      user,
      password,
      database,
    },
    excludedTables: ['public.schema_version'],
    tableNameCasing: 'pascal',
    typeMap: {
      string: ['numeric'],
    },
  }

When I add Date: ['timestamptz'] to typeMap, everything works (but I'm assuming it should work without that).

I use sqlts.toTypeScript because I generate the config programmatically.

nice work,but i have one question is that how can i get the column's length

what's different between optional and nullable property for each column?
the only thing I can imagine fields in DB that has a default value that is optional.
is that correct or anything else?

Is there support for materialized views for postgres? If so how do I use it?

I was using schemats before but it also doesn't support it. It will be great if can do it.

Enums are exported without linebreaks between them

export enum lang_enum {
  en = "en",
  es = "es",
  de = "de",
  fr = "fr"
}export enum foo_enum {
  foo = "foo"
}export enum bar_enum {
  bar = "bar"
{

In sql-ts 1.5.2

Is it possible to use a module like pluralize to singularize table names rather than simply removing the 's'?

Just removing the 's' works well in a lot of cases, but for a word like, say 'Addresses', it requires further manual configuration.

If not, maybe providing a custom API for generating table names?

Hi,

table name "dbo.test-tbl01" is perfectly valid in SQL server but not in typescript, we can't have an interface name with "-" in the middle of the name so the generated code is broken.
I tried "tableNameCasing": "pascal" flag but it didn't help.

I'm sure I'm doing something very very foolish, I apologize if that turns out the case, but with the latest release, it seems the -c and --config command-line arguments don't work unless they're both present, in which case, config is used, per https://github.com/rmp135/sql-ts/blob/master/src/cli.ts#L13?

For example:

$ npx @rmp135/sql-ts --version
1.11.0

$ npx @rmp135/sql-ts -c sqlite.json
node:internal/fs/utils:670
    throw new ERR_INVALID_ARG_TYPE(propName, ['string', 'Buffer', 'URL'], path);
    ^

TypeError [ERR_INVALID_ARG_TYPE]: The "path" argument must be of type string or an instance of Buffer or URL. Received undefined
    at Object.openSync (node:fs:577:10)
    at Object.readFileSync (node:fs:453:35)
    at Object.<anonymous> (/Users/fasih/Dropbox/src/yamanote/node_modules/@rmp135/sql-ts/dist/cli.js:49:28)
    at Module._compile (node:internal/modules/cjs/loader:1101:14)
    at Object.Module._extensions..js (node:internal/modules/cjs/loader:1153:10)
    at Module.load (node:internal/modules/cjs/loader:981:32)
    at Function.Module._load (node:internal/modules/cjs/loader:822:12)
    at Module.require (node:internal/modules/cjs/loader:1005:19)
    at require (node:internal/modules/cjs/helpers:102:18)
    at Object.<anonymous> (/Users/fasih/Dropbox/src/yamanote/node_modules/@rmp135/sql-ts/bin/sql-ts:3:1) {
  code: 'ERR_INVALID_ARG_TYPE'
}

$ npx @rmp135/sql-ts --config sqlite.json 
Options:
      --help         Show help                                         [boolean]
      --version      Show version number                               [boolean]
  -c, --config       Config file.                                     [required]
      ----config
      --sqlite.json

Missing required argument: c

$ npx @rmp135/sql-ts --config sqlite.json -c blablabla
Definition file written as DbTables.ts

I know most likely I'm being an ignoramus but if not, perhaps something changed in yargs?

First, thanks for making this library. It fills a huge gap and worked right out of the box.

A summary:

• sql-ts makes any column without a default value optional in the generated interface.
• I'm using the generated interfaces in my app's model layer.
• Most columns don't have defaults, so they end up optional.
• This means that every attribute access from TypeScript has to be conditional. I have to narrow the column's type from (e.g.) string | undefined to just string.

Here's a concrete example. I have this table (irrelevant columns elided):

                                           Table "public.users"
      Column      |            Type             | Collation | Nullable |              Default
------------------+-----------------------------+-----------+----------+-----------------------------------
 id               | integer                     |           | not null | nextval('users_id_seq'::regclass)
 email            | text                        |           | not null |
 auth_token       | text                        |           |          |
[...]

which generates this interface, using sql-ts 1.1.0:

export interface Iusers {
  id: number 
  email?: string 
  auth_token?: string | null 
  [...]
}

The optionality of email and auth_token is surprising to me at a conceptual level. In order to successfully insert, we have to provide an auth_token, so I want it to be required in the interface. Likewise, any record that we pull from the database will definitely have an auth_token, so I want it to be non-optional in the interface there too.

On the read side, this causes practical problems. For example, the email column above is NOT NULL in the database, so it's always present. But this code:

    const email: string = user.email

fails to type check:

src/backend/db/user.ts(187,11): error TS2322: Type 'string | undefined' is not assignable to type 'string'.

If anything, I'd expect the opposite of this optional behavior: if a column has a default value, then it's not required on insert, so it can be optional in the inserting interface. But that still doesn't help with the reading problem shown above.

It looks like this behavior was intentional, because the postgres/mysql/mssql adapters all do it. But I'm curious about the motivation and whether you'd consider a change to it.

I see that this optionality issue was brought up in #9, where the propertyOptionality option was introduced. However, that option was later removed in commit 18fc667.

Using SQL Server given a following query:

CREATE TABLE SampleTable (
	testID varchar(100) NOT NULL,
	[0000] varchar(100),
	[1800] numeric(38,0)
);

creates a table with column name that begins with a number.

However this line removes the entire column's property name to blank string resulting in duplicate type name of '' as such:

export interface SampleTableEntity {
	'testID': string;
	''?: string | null;
	''?: number | null;
}

The commit that added the line was titled - fix: removed illegal characters allowed in SQL server but not in type…

Isn't following type names allowed in TypeScript?

export interface SampleTableEntity {
	'testID': string;
	'0000'?: string | null;
	'1800'?: number | null;
}

The following types are returned as a string by node-postgres because JavaScript's number does not have enough precision:

• numeric
• decimal
• bigserial
• money

Postgres schemas should be exported as namespaces to allow for the same table over two different schemas to be differentiated.

export namespace SchemaOne {
  export interface TableOne { }
}

export namespace SchemaTwo {
  export interface TableOne { }
}

Perhaps a config option to specify which schemas should be exported.

Dear sql-ts community,

I'm having this issue but cannot figure out how to fix it.

To reproduce the error:

1. npm install sql-ts
2. I verified sql-ts was installed inside node_modules
3. Created a configuration file: mysql.json
   {
   "dialect":"mysql",
   "connection": {
   "host": "localhost",
   "user": "root",
   "password": "myPassword",
   "database" : "my_database"
   }
   }
4. I ran in the terminal the following command:

sql-ts -c ./config.json

and then the error:

sql-ts: command not found

Thanks so much for your help with this issue.

I'm using this package to generate types of DB from Microsoft SQL server. Some of tables have data type as timestamp which are being mapped with JavaScript Date type into TS interfaces. Those columns are not optional and when not passed value during insert, MSSQL automatically inserts default value. But when I use knex insert interface, I must pass value as date since column isn't optional and has Date as type. That ends up with error statement

Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column

To fix this, I must update handlebar template and make column optional into TS interface when type is timestamp. This is only possible with handlebar custom helpers since type is of type string while handlebar compile time.

handlebars is direct dependency of @rmp135/sql-ts and doesn't provide any ability to register any handlebars custom helpers.

Currently I have done workaround to get this working.
I added handlebars as direct dependency of my project. I import handlebars before @rmp135/sql-is and register custom helpers before I start configuring and using sqlts.toTypeScript method.

import * as handlebars from "handlebars";
import sqlts, { Config } from "@rmp135/sql-ts";
import * as fs from "fs";
import * as path from "path";
import process from "process";

handlebars.registerHelper({
  ifEquals: function (arg1, arg2, options) {
    return arg1 == arg2 ? options.fn(this) : options.inverse(this);
  },
});

const config: Config = {
...

Is it a proper way of doing it?
Can we get an ability to register custom handlebar helpers using @rmp135/sql-ts Config object?

If the global map has a type that I want to override then there is no way for me to do that.

The order of keys within an enum is not consistent when running sql-ts on different postgres databases even if they have the same schema.

For example, one database might generate

export enum MyEnumType {
  'Value1': 'value1',
  'Value2': 'value2',
}

while another generates

export enum MyEnumType {
  'Value2': 'value2',
  'Value1': 'value1',
}

Postgres enums have a natural sort order, and it would be nice if the types followed that order. I quickly hacked up a temporary fix here: SocketDev@e048dc2
but it's not very clean. Better would be to pass through the enumsortorder column and sort later, like with tables here:

The order of enums in the output should also be deterministic, although I haven't run into a problem with that yet.

Currently the type override system only takes the name of the table / column into account. It should be modified to include the name of the schema.

I was trying to run sql-ts against a ms sql 2008 server, but I was not able to go past the adapter query where it gets all the table info for a database Adapters/mssql.ts#L6 and #L14.

I successfully passed through that by changing the case to upper case:

#L6 const query = db('INFORMATION_SCHEMA.TABLES') and #L14 const query = db('INFORMATION_SCHEMA.COLUMNS')

I do not now how this change affects other versions of SQL server.

Hi, thanks a lot for this great work!

I was wondering if there was a way to somehow include the original SQL type in the generated files as a comment alongside the type?

i.e.

interface Table {
MyCol : string; // varchar(20)
MyCol2 : number; // smallint
...
}

Hi,
maybe we should cooperate: I recently wrote a typed SQL builder (live demo) and I am still missing some code generation features which you seem to have implemented.

Maybe you noticed as well that knex does not work very well together with typescript and are looking for a better alternative to knex?

Consider to add working example with input output,
Currently the code parts in the readme has some ... I cannot follow from the first time reading

This error happens on version 1.5.0, I tried installing version 1.3.0 and it worked.
Tried using both npx and as a module, using toTypescript and toObject, and all cases printed the same error:

(node:11591) UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'split' of undefined

Hello.

Thank you for a very useful tool! 🙏 It seems that type generation for regular Postgres views is not supported yet. Is this due to a technical limitation or complexity?

Only the current schema is being retrieved.

Feature request: allow specifying the location of the Types file after generation.

In Postgres, I have a NOT NULL column. This column is also PRIMARY KEY.

     Column     |            Type             | Collation | Nullable |      Default       | Storage  | Stats target | Description
----------------+-----------------------------+-----------+----------+--------------------+----------+--------------+-------------
 id             | uuid                        |           | not null | uuid_generate_v4() | plain    |              |

The generated typescript is correctly turning the uuid into a string, but not catching the not null bit.

id?: string

This is strange because other columns which are also not null are being marked correctly. Any idea how I might work around the problem?

Thanks!

Does this library support SQL functions? For my workflow the typings of the function inputs and outputs are more important that the tables.

Thanks for the awesome work.
I have extended a little the functionality of this library to generate some functions to query data with knex alongside interfaces for each table. here is the link to the repository please let me know if you are interested to merge that to yours.
I can create a pull request with my changes.

Nice work on this library.

I'm working with a local sqlite3 db which has db views. None of the npm releases include the latest code (written around 3months) to include both tables and views in the output. Do you mind creating another release on npm which reflects what's on master?

When i tried to run the interface generation i discovered that alle the interface keys are optional in the types even though they aren't in the database :-) ?

I love this tool, however I can't pass an absolute path to my config file. It seems the tool always assumes the path will be relative and prepends the current working directory.

I'm super happy I found this project because I am using a very similar script in my projects. Here is it if you are interested in having a look.

I would love to get rid of it and use sql-ts instead but I'm missing a feature I have in my script which is to know which columns are the primary keys.

Do you think this is something that could be added as part of the getAllColumns function?

for this postgresql table definition:

                                        Table "seeds"
   Column    |           Type           |                         Modifiers                          
-------------+--------------------------+------------------------------------------------------------
 id          | bigint                   | not null default nextval('seeds_id_seq'::regclass)
 created     | timestamp with time zone | not null default now()
 user_id     | bigint                   | not null
 server_seed | text                     | not null
 client_seed | text                     | not null
 last_nonce  | integer                  | not null
 active      | boolean                  | not null

sql-ts outputs the following typescript:

  export interface seeds {
    id?: string | null
    created?: string | null
    user_id?: string | null
    server_seed?: string | null
    client_seed?: string | null
    last_nonce?: string | null
    active?: boolean | null
  }

Even though none of the columns should be able to be undefined or null.

Some (especially older) databases have poor naming conventions e.g. tableid and TableID. It'd be great to homogenize this with a "lowercase" option (particularly for columns).

Looks like a change to SharedTask.convertCase?

sql-ts -c ./config.json
tedious deprecated The default value for `options.encrypt` will change from `false` to `true`. Please pass `false` explicitly if you want to retain current behaviour. ../../../../../.nvm/versions/node/v11.0.0/lib/node_modules/mssql/lib/tedious.js:230:23 (node:7455) UnhandledPromiseRejectionWarning: ConnectionError: Failed to connect to localhost:1433 - Could not connect (sequence)

These don't break things, but they annoy my linter:

1. Keys are wrapped in " instead of ' @typescript-eslint/quotes
2. Lines don't end with a semicolon @typescript-eslint/member-delimiter-style
3. Lines end with spaces no-trailing-spaces

The linter I'm using is ESLint.

Hi,

My MSSQL Server is case sensitive and I am getting the error Invalid object name 'information_schema.table_constraints'.
to fix that I have created a pull request #64
it's just capitalizing the SQL statements.
please merge or let me know what you want me to change.

Regards.

This is so udeful :-)

I really like typescript and will really like to have my tables in MSSQL typed. This tool is exactly what i am looking for :-) However, most of the types in my table is not supported. Would it be possible to find a way for user to map SQL types to typescript types :-) ?

Thanks for this tool :D !

Although not a part of the SQL standard, enumerated values are a common feature in domain models, are supported in some ways by most databases, and can be expressed with the TypeScript typing system. Enums in databases currently supported by sql-ts come at least in three forms:

1. The PostgreSQL TYPE ... AS ENUM, which is a named, stand-alone data type that can be shared by multiple columns in different tables. This pretty clearly maps to a TypeScript string enum, which can then be referred to in the individual table interfaces.
2. The MySQL ENUM(...) column type, which is anonymous. It is conceptually similar to a TypeScript string union (ENUM('a', 'b', 'c') -> 'a' | 'b' | 'c'), but perhaps using a string enum for these as well would be desirable for consistency?
3. Using a check constraint to limit the column to certain values – the only enum implementation available for SQL Server and SQLite, and preferred by some with PostgreSQL. I don’t know if handling these would be feasible, as check constraints are a very expressive construct, and detecting whether one is to be considered an “enum” or not would probably be difficult.

Example table:

CREATE TABLE `example` (
	`nullableField` int(10) UNSIGNED DEFAULT NULL
)

Results in type:

type Example {
	nullableField: number | null
}

Since it is nullable and has a default null, shouldn't it be optional?

I think the issue is the way isOptional is calculated in the Adapter here:

Should that be changed to:

CASE WHEN LOCATE('auto_increment', extra) <> 0 OR COLUMN_DEFAULT IS NOT NULL OR (COLUMN_DEFAULT IS NULL AND IS_NULLABLE = 'Yes') THEN 1 ELSE 0 END isOptional,

The current camelcase will convert ID -> to iD, it would be nice to allow user provider custom change-case function in config,

{
  "columnNameCasing": name => customCamelCase(name),
}

Is there currently a way to provide a list of imports to append to the top of the generated file? I have a JSONB column called data in a postgres database that I would like to type. Providing this to the config works:

  "typeOverrides": {
    "public.table.data": "{someValue: number}"
  }

but it would be more elegant to supply a type via an import at the top of the generated file so that the typeOverrides could read:

  "typeOverrides": {
    "public.table.data": "MyCustomType"
  }

In my postgres db I've got an "environment" enum defined with the values "TEST" and "LIVE".
After I generated the ts types, the output file shows me the enum like this:

export enum environment { 0 = '[object Object]', 1 = '[object Object]', }

The desired output would be the following:
export enum environment { TEST = 0, LIVE = 1, }

Also, acccording to the TypeScript documentation ENUMs do not need quotes.

I think that exporting an enum for table names is useful. However it's very difficult to make everyone happy because the format varies too much depending on your software configuration.

For instance, let's say that the configuration is extended to provide the following extra properties for a table.

• keyedName: human friendly name for the table (possibly camelCase transformed, possibly including the schema to avoid table name collisions)
• exportedName: table name used for queries (possibly camelCase transformed)
• exportedSchema: schema name used for queries (possibly camelCase transformed)

Knex requires schema and table_name to be exported separately and possibly with camelCase transformations.

export const TableNames = {
  {{#each grouped as |group key|}}
  {{#each tables as |table|}}
  {{keyedName}}: {
    schema: "{{schema}}",
    name: "{{name}}"
  }{{#if @last}}{{else}},{{/if}}
  {{/each}}
  {{/each}}
}

However slonik and plain sql queries use the "schema.table_name" format.

export enum TableNames {
  {{#each grouped as |group key|}}
  {{#each tables as |table|}}
  {{keyedName}} = "{{schema}}.{{name}}"{{#if @last}}{{else}},{{/if}}
  {{/each}}
  {{/each}}
}

I also think that the documentation needs a simpler example of a handlebars template for those of us that are not familiar with the format. It believe it can help users to better visualize a solution to their problem.

TLDR: I would like to contribute a useful and illustrative example for a handlebars template in #50

NVM

Docs: https://www.postgresql.org/docs/current/sql-comment.html

Given this table:

create table foo (
  bar text
)
comment on table foo is 'Table Foo has everything.
Even bars.'
comment on column foo.bar is 'The bar is closed.'

I would like this generated:

/**
 * Table Foo has everything.
 * Even bars.
 */
export interface FooEntity {
  /** The bar is closed. */
  bar: string
}

I believe that https://github.com/kristiandupont/kanel supports this.

Actually, looking at their examples, It would also be awesome to print comments with more information about the field (like index, default value, etc). This way when you are in your application code in your IDE, you can easily see whether a column has an index, for example.

Given this table in a PostgreSQL database:

CREATE TABLE my_table (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    value_one numeric NOT NULL,
    value_two numeric
);

I can run the following query:

INSERT INTO my_table (value_one) VALUES (123);

However, sql-ts will generate types that look like this:

export interface MyTableEntity {
  id?: string;
  value_one: string;
  value_two: string | null;
}

which makes specifying value_two mandatory. I think a more correct type would be:

export interface MyTableEntity {
  id?: string;
  value_one: string;
  value_two?: string | null;
}

I can get around it by modifying the handlebars template:

"{{propertyName}}"{{#if optional}}?{{else}}{{#if nullable}}?{{/if}}{{/if}}: {{propertyType}} {{#if nullable}}| null {{/if}}

It seems though that perhaps optional should be true for column value_two since I don't need to provide a value for this column in an INSERT. Is there something I might be missing?

Hi, I am working on a very big DB and find out some problems with column names that are valid in the SQL server but not in js/ts.
Here is a list of generated problems:

• SubCO/POCONum: number | null
• P-UN: string
• Receiver#: string | null
• L-%Comp: number | null

I think there are a few solutions:

• validate field names before generating them
• generate all fields in string format "FIELD_NAME" (better performance)
  and then maybe we can run prettier on the generated file to standardize the output.