rmp135 / sql-ts Goto Github PK
View Code? Open in Web Editor NEWGenerate TypeScript interfaces from a SQL database.
License: MIT License
Generate TypeScript interfaces from a SQL database.
License: MIT License
Despite
Line 5 in 518986e
timestamptz
columns are typed as any
for me in all my tables.
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:
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:
and then the error:
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:
Line 23 in 64ea54c
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 :-) ?
{
"client": "pg",
"connection": {},
"tableNameCasing": "pascal",
"columnNameCasing": "camel",
"enumNameCasing": "pascal",
"schemas": ["public"]
}
export interface AddressEntity {
// Error: Cannot find name 'address_type'.ts(2304)
// I expect this also transform to pascal case
'type': address_type;
}
export enum AddressType {
RESIDENTIAL = 'RESIDENTIAL',
COMMERCIAL = 'COMMERCIAL',
}
CREATE TABLE address
(
type address_type NOT NULL
}
CREATE TYPE address_type AS ENUM ('RESIDENTIAL', 'COMMERCIAL');
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:
"
instead of '
@typescript-eslint/quotesThe 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:
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.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?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:
Line 23 in d60be87
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:
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.