sweetiq/schemats

Generate typescript interface definitions from SQL database schema

schemats's Issues

Fix broken osm.ts example in README

Hi! This looks like an awesome project and I'm excited to use it. I just wanted to share that currently the link in the README to a a sample osm.ts file is a dead link.

MySQL: TINYINT should be a number, not a boolean

The MySQL type TINYINT is a number between -128 and 127 (signed) or 0 and 255 (unsigned) so the corresponding type in Typescript should be number, not boolean.

It is true that booleans in MySQL are TINYINTs but the converse is not true. TINYINT is the correct type to use in MySQL for any integer that will fit inside of one byte, not just booleans.

In src/schemaMysql.ts there is a comment which states that the goal is to mirror the types from github.com/mysqljs. That library correctly maps TINYINTs to a number: https://github.com/mysqljs/mysql#type-casting

One more note: I thought it might be possible to look for TINYINT(1) to determine if a field is actually a boolean. Unfortunately, the 1 is merely a formatting hint and does not affect the values that the column can store. Even if it did, it refers to the decimal rather than binary length, so TINYINT(1) could still be any number between 0 and 9 and not a boolean.

npm run build fails if --strictNullChecks is set to true

If I set --strictNullChecks to true in tsconfig.json, npm run build fails :

$ npm run build

> [email protected] build C:\node-projects\schemats
> tsc

test/test.ts(49,9): error TS2345: Argument of type 'undefined' is not assignable to parameter of type 'string | null'.

The reason in that in the following line, we pass namespace as undefined :
https://github.com/SweetIQ/schemats/blob/master/test/test.ts#L49

while in https://github.com/SweetIQ/schemats/blob/master/src/index.ts#L37, only string is allowed for namespace parameter.

PR is coming.

neat tool - why use namespaces? just curious

This worked fine for me so far. Just curious why the use for namespaces?

when I ran this:

schemats generate -c 'postgres://postgres:postgres@localhost/postgres' -t 'user_table' -o schemas/user.ts

I got:

export namespace user_tableFields {
    export type id = number;
    export type handle = string;
    export type email = string;
    export type first_name = string;
    export type last_name = string;
    export type is_verified = boolean;

}

export interface user_table {
    id: user_tableFields.id;
    handle: user_tableFields.handle;
    email: user_tableFields.email;
    first_name: user_tableFields.first_name;
    last_name: user_tableFields.last_name;
    is_verified: user_tableFields.is_verified;

}

Add feature to customize ts types generated for some SQL types

By default, node-postgres parses bigint and numeric as strings, to preserve full information on the number since javascript doesn't support 64 bits integers.

Depending on the application, the developer can choose to parse big numbers (int8) as strings or integers.

Eg, for bigint, we used to change the parsing as following :

pg.types.setTypeParser(20, function (val) {
	// remember: by default, all values returned from the server are either NULL or a string
	return val === null ? null : parseInt(val);
});

So it would be useful to be able to define accordingly in schemats the ts type to generate.

Materialized view schema type definitions

Is there a way to generate materialized view schema type definitions? I know it does this for normal views, yet I don't see the output for materialized views.

Project maintainance

Follow up of #104 (comment)

Some background : @xiamx the original author of this module, and @crispmark another contributor, have both left SweetIQ and can't provide maintenance anymore (see #81 (comment)).

At @Arhia (my company), we use this module everyday in a very big application and we would want this project to be maintained, so I proposed to start a fork and ensure a (at least minimal) maintenance.

Contributors will of course be welcomed.

The goal is to maintain the project and merge the potential good PR pending here.

Some questions though :

1. Organization

I created schemats organization today : https://github.com/schemats
I will invite people later (I'm still learning how to manage Collaborators rights)

2. Start point for the fork

Should the repository be forked from here or from https://github.com/xiamx/schemats/ ?
The latter is 12 commits ahead so I would like to start from it, @xiamx can you confirm it's a good idea ?

3. Forking or cloning

Forking keep the link with the original repository, but it's unlikely here that we will be able to merge back into SweetIQ/schemats so I would rather clone the repo, for clarity.

4. Npm

It looks like @xiamx owns npm account (see #104 (comment)) so we would be able to publish new version under the same name, providing a smooth transition.

Use typescript 2.1 index access types

Typescript 2.1 introduces index access to types, which can be used to access type of interface property:

interface a {
    prop1: string
    prop2: number
}

let b: a["prop1"] = "321"

This s definetely could be used in schemats to avoid to generate XXXfields namespace.

Schemats generates empty file

My env: Windows x64, MySQL 5.5
I try cli with command
schemats generate -c mysql://root:1@localhost/db -t users -o db.ts
and the output file content is

export namespace usersFields {

}

export interface users {

}

Database has table users with data, what am I doing wrong?

Some characters are missing in generated output

Nodejs : 7.4.0
Postgres : 9.6.1
schemats : 1.0.1

While using last version 1.0.1 of this module, I run into a strange bug : in the generated file, some characters are missing, always in the third line of first interface declaration.

Eg, in the following example, the file contains export type sal_matriculetring; instead of export type sal_matricule = string;
4 characters = s are missing

Excerpt of output :

/**
 * AUTO-GENERATED FILE @ 2017-01-24 13:27:02 - DO NOT EDIT!
 *
 * This file was generated with schemats node package:
 * $ schemats generate -c postgres://username:password@localhost:5432/testpayroll -t pay_salarie_sal -o schema.ts
 *
 * Re-run the command above.
 *
 */

    
export namespace pay_salarie_salFields {
    export type sal_id = number;
    export type sal_matriculetring;

With another table, the missing characters are in nearly the same place :

/**
 * AUTO-GENERATED FILE @ 2017-01-24 13:33:18 - DO NOT EDIT!
 *
 * This file was generated with schemats node package:
 * $ schemats generate -c postgres://username:password@localhost:5432/testpayroll -t pay_contrat_cnt -o schema.ts
 *
 * Re-run the command above.
 *
 */

    
export namespace pay_contrat_cntFields {
    export type cnt_soldetc_mdp_id = number | null;
    exporte tex_id = number | null;

Of course, expected : export type tex_id instead of exporte tex_id
4 characters typ are missing

It's the only issue in the output file : all other lines are correct.

I don't reproduce the issue with osm artifact test file, which is weird.

Object should be object instead?

I used this command:

schemats generate -c 'postgres://postgres:postgres@localhost/postgres' -t 'user_map_table' -o schemas/user-map.ts

and I got:

but shouldn't it be object not Object?

it's more consistent I think

Improve the header details

This is a feature request for this code: https://github.com/SweetIQ/schemats/blob/master/src/index.ts#L37

Any typical header like this is expected to include the library's name + exact version that was used for generating the file, so you know when it is worthwhile regenerating it, and which tool to use for it, if newer versions are available. Just providing Date/Time doesn't give enough information.

Here's an example I used within pg-promise:

/////////////////////////////////////////////////////////////////////////
// This file was automatically generated by pg-promise v.4.3.8
//
// Generated on: 6/2/2016, at 2:15:23 PM
// Total files: 15
//
// API: http://vitaly-t.github.io/pg-promise/utils.html#.buildSqlModule
/////////////////////////////////////////////////////////////////////////

The version should be injected as the value from require('./package.json').version.

Comments support?

How about retrieving comments for all tables + columns, and adding them as TypeScript comments? ;)

See:

• Retrieving Comments from a PostgreSQL DB
• How to retrieve the comment of a PostgreSQL database?

PSQL bytea should be typed as a buffer not a string

The bytea datatype should be typed as a buffer not a string. All psql drivers i know of (mainly node-postgres and pg-promise) automatically convert the datatypes.

Support nullable type

Hi and thx a lot for this module.

I just started to use this module, and I wonder if it's possible to generate typescript nullable type ?
Indeed, for ones who use typescript with --strictNullChecks flag, it would be very useful to generate types nullable with union type.

For example, for a SQL nullable string, it would generate type : string | null, etc.

I think it would be better to have this optional.

Is it possible to get types from queries?

Is it possible to get types from queries? I mean one query may contain two or more tables. I'd like to get types of such query's result, not type of just single tables.

Small updates

• Instructions to setup the project
• Instructions on how to consume
• Examples folder ?
• .editorconfig file
• Ability to name the global namespace, table namespace, and interface

Name conflict in generated types

I have a table document with a column type of type document_type:

CREATE TYPE document_type AS ENUM (
    'NOTE',
    'PAGE'
);
CREATE TABLE document (
    id uuid NOT NULL,
    document_type document_type NOT NULL
);

This causes a name conflict in the generate types:

export type document_type = "NOTE" | "PAGE";

// ...

export namespace documentFields {
  export type id = string;
  export type document_type = document_type;
  //          ^^^^^^^^^^^^^
  // Type alias 'note_type' circularly references itself
}

Support passing pg-promise initialization options during the library's initialization

It would be nice to be able to pass initialization options or a configuration object directly to pg-promise.

This could be fairly easily be possible if you

1. Allow either a PgPromise.IDatabase or a string directly to be passed to the PostgresDatabase constructor.
2. Export PostgresDatabase from index (should also maybe export MysqlDatabase too)

In general, it might be worth double checking to see if there are other useful classes/functions that should be exported too.

article

Great article, well done!

I just wanted to point out that one can write even more elegant code, by replacing db.query(..).then(...each) with simply db.each. See methods each and map ;)

add mapping for text search types

A powerfull feature in postgres is the full text search, which can be perfectly used as a replacement of nosql similar features (elastic search, etc).

For this, postgres provides several types :

• tsquery
• tsvector
• tsrange

see https://www.postgresql.org/docs/current/static/datatype-textsearch.html

The udt names are the sames as the postgres type names :

SELECT column_name, udt_name
            FROM information_schema.columns
            WHERE table_name = 'test' and table_schema = 'public'

gives

I don't know exactly which typescript type to map to these types, as usually theses types are manipulated with built-in functions/casts, like ::tsvector or to_tsvector and can not be inserted like simple strings.

Perhaps the any type is the most appropriate for this ?

Change json/jsonb type

Currently, a json or jsonb db field is mapped with typescript Object type.
Though Object would seems appropriate at first sight, it leads to following type errors :

Property 'foo' does not exist on type 'Object'.

...sinceObject type (same with object and {} ts types) doesn't accept more properties.

It should be more appropriate to type these fields with a specific type which accepts any properties, something like :

interface JsonParsed extends Object {
    [k: string]: any
}

or more precise :

interface JsonParsed {
        [k: string]: string|number|boolean|Date|JsonParsed|JsonParsedArray|null;
    }
interface JsonParsedArray extends Array<string|number|boolean|Date|JsonParsed|JsonParsedArray|null> { }

What do you think about it ?

When runned locally, tests are looking for osm.ts at wrong location

Hi and thx again for this module !

While running tests locally before submitting a PR, I ran into the following pb : tests are looking for file osm.ts in test/artifacts/osm.ts, but this file is actually located at test/example/osm.ts

The culprit seems to be https://github.com/SweetIQ/schemats/blob/master/test/test.ts#L59

bigint should be typed as "string" instead of "number" by default

node-postgres returns Bigint columns as strings instead of numbers (by default), because they can be larger than javascript numbers. Could you please modify schemats to use "string" for bigint columns instead of "number"?

Maybe project can be merged with https://github.com/typeorm/typeorm

Maybe project can be merged with https://github.com/typeorm/typeorm
combined efforts,

Angular 2 Kitchen sink: http://ng2.javascriptninja.io
and source@ https://github.com/born2net/Angular-kitchen-sink
Regards,

Sean

Support Enum Type

Add support for Postgres Enum Type https://www.postgresql.org/docs/current/static/datatype-enum.html

Mark serial types as optional

Hi,

I attempted to use the generated schemas in knex to insert rows, but ran into an (obvious?) issue that the interface for the rows require the primary key, which of course I don't yet have. Does this make sense for schemats?

Can I work around this somehow?

Just to fiddle with the idea I made a POC for PostgreSQL that does this: https://github.com/ration/schemats/tree/optional_serial

pg-promise updates

To my recollection, this library was kind of inspired from using pg-promise, and it still uses it as the main reference, although a very old version of it.

Version 9.3 of pg-promise kind-of opened the floodgate to supporting BigInt natively all the way through. And a library such as schemats should somehow keep up with that, in order to stay relevant.

Just a thought 😄

add feature to generate output without namespace

Currently, namespace parameter is required.

If one runs schemats without argument -n (or with empty one) like that :
schemats generate -c postgres://postgres@localhost/osm -t users -o osm.ts

It ends up with the following output in osm.ts :

export namespace undefined {

    export namespace usersFields {
...

Using a namespace for a whole module in typescript is ok, but a bit outdated.
With es2015 modules, it seems preferable to generate directly :

    export namespace usersFields {
...

wich allows to import as following :

import * as myNamespaceInConsumerModule from 'osm.ts'
...

It would be nice to make namespace optional.

Negation support

A schema can include service tables, either special for security or for PostGIS ones.

One needs to be able to say: generate for all tables in the schema, except for... and list the tables to be excluded, possibly with a reg-ex pattern for exclusion. Might also add support for reg-ex pattern for inclusion as well.

At this point it might be best to introduce profiling support also, i.e. a default .schemats.json file in the app's root to configure everything that's generated, to avoid too long command lines 😉

Type [undefined] has been mapped to [any] because no specific type has been found.

when I run this command, it always show the message that you have seen in the title.

schemats generate -c mysql://root:12345678@localhost:3306/mydb -o ./test.ts -t article -t book -t subscription -t test_article -t test_book -t test_toc -t test_version -t toc -t tool -t tool_type -t version -s mydb

And the file which has been generated is like this:

export namespace articleFields {
    export type undefined = any;

}

export interface article {
    undefined: articleFields.undefined;

}

export namespace bookFields {
    export type undefined = any;

}

export interface book {
    undefined: bookFields.undefined;

}

export namespace subscriptionFields {
    export type undefined = any;

}

export interface subscription {
    undefined: subscriptionFields.undefined;

}

export namespace test_articleFields {
    export type undefined = any;

}

export interface test_article {
    undefined: test_articleFields.undefined;

}

export namespace test_bookFields {
    export type undefined = any;

}

export interface test_book {
    undefined: test_bookFields.undefined;

}

export namespace test_tocFields {
    export type undefined = any;

}

export interface test_toc {
    undefined: test_tocFields.undefined;

}

export namespace test_versionFields {
    export type undefined = any;

}

export interface test_version {
    undefined: test_versionFields.undefined;

}

export namespace tocFields {
    export type undefined = any;

}

export interface toc {
    undefined: tocFields.undefined;

}

export namespace toolFields {
    export type undefined = any;

}

export interface tool {
    undefined: toolFields.undefined;

}

export namespace tool_typeFields {
    export type undefined = any;

}

export interface tool_type {
    undefined: tool_typeFields.undefined;

}

export namespace versionFields {
    export type undefined = any;

}

export interface version {
    undefined: versionFields.undefined;

}

How can I fix this problem?

node version is 8.9.1
schemats version is 3.0.3

Camelized column names

In case you want automatically camelized column names, see event receive, and the example that follows 😉 Could make for a nicer TypeScript style 😉

Caramelized, LOL 😸

Support more data types

I open this issue just for the record : in PR #21, I didn't add all sql types, since there so many, and I think the mapping still lacks some common ones :

• inet type
• timestamptz

If I a had some time, I will submit a PR for this.

If anyone would suggest more types to add ?

Add MSSQL support

I am using mssql and would really like to see something like this for mssql.

I do think it might be very similar to the mysql implementation :-) !

Auto-generated comment

Hey! Thanks for this cool project!

I'm guessing all the generated .ts files shouldn't be edited by user in most cases and it might make sense to add a top most comment about it. Something like:

/**
 * AUTO-GENERATED FILE - DO NOT EDIT!
 *
 * This file was generated with schemats node package:
 * $ schemats generate -c postgresql://user@localhost:5432/db -all used args-
 * 
 * Re-run command above if your DB schema was changed.
 * 
 */
export namespace novatus {
    // ...generated interfaces and types ...
}

what do you think?

Q: what about generating types for procedures/functions in schema

I've not found info about generating type definitions for functions/procedures in schema!
Is it possible with it?

Nested jsonb

Great and useful project. Looking for native json support where the json object is typed once definitions are generated. Is that possible?

Unsafe table names generate invalid ts file

If we have table names that, on the db side, need to be quoted, the generated type names end up being invalid typescript names. Not certain what the ideal end result should be, but just figured this was worth documenting.

for instance:

CREATE TABLE "has spaces" (id int not null);
CREATE TABLE "has-dashes" (id int not null);

Produces

export namespace has spacesFields {
  export type id = number;
}
export interface has spaces {
  id: has spacesFields.id;
}
export namespace has-dashesFields {
  export type id = number;
}
export interface has-dashes {
  id: has-dashes.id;
}

Would be nice to be able to generate all/multiple tables at once.

Generates invalid ts file if a table has column named

Bad line endings

When installing with -g, it won't work on Mac. The problem seems to be the file is using CRLF instead of LF. I converted the file and it worked again.

Support numeric type

I just started to use this module, but unfortunately, only a few types seem to be mapped :

TypeError: do not know how to convert type [numeric]

Would it be possible to add numeric type mapping ?

Thx by advance.

Add constraint information to generated interfaces

First of all, thanks for writing this library, it is great.

I'm trying to write a type-safe SQL query builder and having information on the row constraints would be useful.

As an example, this table:

field	type	constraint
id	text	NOT NULL PRIMARY KEY
email	text	NOT NULL UNIQUE
pwd_hash	char(60)	NOT NULL
deleted	boolean	NOT NULL DEFAULT false
name	text

could generate something like this:

export interface users {
   id: { type: string, primary: true }
   email: { type: string, unique: true }
   pwd_hash: { type: string }
   deleted: { type: boolean,  default: false }
   name: { type: string | null }
}

I understand that this feature would be overkill to most users, so it could be hidden behind a flag --mode verbose or something like that.

If there's interest in this feature, I'd be willing to work on it.

Parse default schema from connection string

Need to parse default schema from MySQL connection string so we can use

schemats generate -c mysql://root:1@localhost/db -t users -o db.ts

instead of schemats generate -c mysql://root:1@localhost/db -s db -t users -o db.ts

related to #57

Generates invalid ts file if a table has column named "default"

Hello,
I noticed this on postgres.
Ex:

I think it should rename it to _default

Overlap/Collaboration Potential: pogi

Kudos on schemats! I wanted to point out a related library with some functional overlap: https://github.com/holdfenytolvaj/pogi

db types

This is not an issue, just a thought...

I have seen so many projects at this point that keep re-doing the same thing - trying to map database types into TypeScript types.

So I've just started scratching something with db-types, in an attempt to bring it under one roof.

Any thoughts are welcome!

int8 type

TypeError: do not know how to convert type int8
    at lodash_1.mapValues.udt_name (/Users/pat/Documents/Projects/sweetiq/schemats/schema.js:55:27)
    at /Users/pat/Documents/Projects/sweetiq/schemats/node_modules/lodash/lodash.js:13047:23
    at /Users/pat/Documents/Projects/sweetiq/schemats/node_modules/lodash/lodash.js:4738:15
    at baseForOwn (/Users/pat/Documents/Projects/sweetiq/schemats/node_modules/lodash/lodash.js:2879:24)
    at Function.mapValues (/Users/pat/Documents/Projects/sweetiq/schemats/node_modules/lodash/lodash.js:13046:7)
    at Database.mapDBSchemaToType (/Users/pat/Documents/Projects/sweetiq/schemats/schema.js:35:25)
    at Database.<anonymous> (/Users/pat/Documents/Projects/sweetiq/schemats/schema.js:31:25)
    at next (native)
    at fulfilled (/Users/pat/Documents/Projects/sweetiq/schemats/schema.js:4:58)
    at process._tickCallback (internal/process/next_tick.js:103:7)

TypeError: Cannot read property 'replace' of undefined

I get the following error:

$ schemats generate -c mysql://local:local@localhost/Signing -t Logs -o Logs.ts
TypeError: Cannot read property 'replace' of undefined
    at Function.MysqlDatabase.parseMysqlEnumeration (/Users/ap/.nvm/versions/node/v8.11.3/lib/node_modules/schemats/src/schemaMysql.js:119:26)
    at /Users/ap/.nvm/versions/node/v8.11.3/lib/node_modules/schemats/src/schemaMysql.js:149:60
    at Array.forEach (<anonymous>)
    at MysqlDatabase.<anonymous> (/Users/ap/.nvm/versions/node/v8.11.3/lib/node_modules/schemats/src/schemaMysql.js:147:40)
    at step (/Users/ap/.nvm/versions/node/v8.11.3/lib/node_modules/schemats/src/schemaMysql.js:32:23)
    at Object.next (/Users/ap/.nvm/versions/node/v8.11.3/lib/node_modules/schemats/src/schemaMysql.js:13:53)
    at fulfilled (/Users/ap/.nvm/versions/node/v8.11.3/lib/node_modules/schemats/src/schemaMysql.js:4:58)
    at <anonymous>
    at process._tickCallback (internal/process/next_tick.js:188:7)

I just installed schemats with npm install -g schemats

$ type schemats
schemats is hashed (/Users/ap/.nvm/versions/node/v8.11.3/bin/schemats)

and my table looks like this:

mysql> use Signing;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe Logs;
+---------+------------------------------------------------+------+-----+-------------------+----------------+
| Field   | Type                                           | Null | Key | Default           | Extra          |
+---------+------------------------------------------------+------+-----+-------------------+----------------+
| id      | int(10) unsigned                               | NO   | PRI | NULL              | auto_increment |
| tstamp  | timestamp                                      | YES  | MUL | CURRENT_TIMESTAMP |                |
| user    | char(8)                                        | YES  | MUL | NULL              |                |
| session | int(10) unsigned                               | YES  | MUL | NULL              |                |
| level   | enum('debug','info','warn','error','critical') | NO   |     | NULL              |                |
| text    | text                                           | YES  |     | NULL              |                |
+---------+------------------------------------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

My database installation:

mysql> status
--------------
mysql  Ver 8.0.11 for osx10.13 on x86_64 (Homebrew)

Connection id:		23
Current database:	signing
Current user:		local@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.11 Homebrew
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/tmp/mysql.sock
Uptime:			2 hours 38 min 35 sec

Update the example to use TypeScript 2.1 index access types

Update the example in readme and in the article to use TypeScript 2.1 index access types as suggested in #46