Schemats

Using Schemats, you can generate TypeScript interface definitions from (Postgres, MySQL) SQL database schema automatically.

Start with a database schema:

Users
id	SERIAL
username	VARCHAR
password	VARCHAR
last_logon	TIMESTAMP

Automatically have the following TypesScript Interface generated

interface Users {
    id: number;
    username: string;
    password: string;
    last_logon: Date;
}

For an overview on the motivation and rational behind this project, please take a look at Statically typed PostgreSQL queries in Typescript .

Quick Start

Installing Schemats

npm install -g schemats

Generating the type definition from schema

schemats generate -c postgres://postgres@localhost/osm -t users -o osm.ts
schemats generate -c mysql://mysql@localhost/osm -t users -o osm.ts

The above commands will generate typescript interfaces for osm database with table users. The resulting file is stored as osm.ts.

Generating the type definition for all the tables in a postgres schema

To generate all type definitions for all the tables within the schema 'public':

Note: MySQL does not have a default public schema, but should it have a schema named public, this will still work.

schemats generate -c postgres://postgres@localhost/osm -s public -o osm.ts
schemats generate -c mysql://mysql@localhost/osm -s public -o osm.ts

If neither the table parameter nor the schema parameter is provided, all tables in schema 'public' will be generated, so the command above is equivalent to:

schemats generate -c postgres://postgres@localhost/osm -o osm.ts
schemats generate -c mysql://mysql@localhost/osm -o osm.ts

Using schemats.json config file

Schemats supports reading configuration from a json config file (defaults to schemats.json). Instead of passing configuration via commandline parameter like done above, it is also possible to supply the configuration through a config file. The config file supports the same parameters as the commandline arguments.

For example, if a schemats.json exists in the current working directory with the following content:

{
    "conn": "postgres://postgres@localhost/osm",
    "table": ["users"]
}

Running schemats generate here is equivalent to running schemats generate -c postgres://postgres@localhost/osm -t users -o osm.ts.

Writing code with typed schema

We can import osm.ts directly

// imports the _osm_ namespace from ./osm.ts

import * as osm from './osm'


// Now query with pg-promise and have a completely typed return value
  
let usersCreatedAfter2013: Array<osm.users>
   = await db.query("SELECT * FROM users WHERE creation_time >= '2013-01-01'");

// We can decide to only get selected fields

let emailOfUsersCreatedAfter2013: Array<{
    email: osm.users['email'],
    creation_time: osm.users['creation_time']
}> = await db.query("SELECT (email, creation_time) FROM users WHERE creation_time >= '2013-01-01'");

With generated type definition for our database schema, we can write code with autocompletion and static type checks.

Using schemats as a library

Schemats exposes a few high-level functions for generating typescript definition from a database schema. They can be used by a build tool such as grunt and gulp.

typescriptOfTable (db, table, schema, options)

db - database connection string or database object. If you are calling the function multiple times in your script, use a database object.

table - database table you want to generate definitions for

schema - database schema

options - options object

import { typescriptOfTable, Options, getDatabase } from 'schemats'
import fs from 'fs'

//optional - defaults shown
const options = new Options({
    camelCase: true,
    writeHeader: true
})

typescriptOfTable('postgres://username:password@hostname/osm', 'users', 'public', options)
    .then(types => fs.writeFile('osm.ts', types, (err) => { if (err) console.log(err) }))

typescriptOfSchema (db, table, schema, options)

db - database connection string or database object. If you are calling the function multiple times in your script, use a database object.

table - array of database tables you want to generate definitions for

schema - database schema, defaults to default schema in the db

options - options object - just a plain object, not an instance of Options

import { typescriptOfSchema, getDatabase } from 'schemats'

//optional - defaults shown
const options = {
    camelCase: true,
    writeHeader: true
}

const schemas = ['osm', 'foo', 'bar']

/* 
    If you just passed in the connection string instead of getting a 
    connection like this you would get an error like
    WARNING: Creating a duplicate database object for the same connection
    and you may even get errors if you use all your configured connections on your db
*/
const database = getDatabase('postgres://username:password@hostname/dbname')

schemas.forEach(async (schema) => {
    const types = await typescriptOfSchema(
        database,
        [], //this will generate types for all tables in the schema unless you put table names in here
        schema
    )
    fs.writeFile(`types/${schema}.ts`, types, (err) => { if (err) console.log(err) })
})

Upgrading to v1.0

Deprecation of Namespace

Version 1.0 deprecates generating schema typescript files with namespace.

Instead of generating schema typescript files with

schemats generate -c postgres://postgres@localhost/db -n yournamespace -o db.ts

and import them with

import {yournamespace} from './db'

It is now encouraged to generate without namespace

schemats generate -c postgres://postgres@localhost/db -o db.ts

and import them with

import * as yournamespace from './db'
// or
import {table_a, table_b} from './db'

As TypeScript's documentation describes, having a top level namespace is needless. This was discussed in #25.

Generating schema typescript files with namespace still works in v1.0, but it is discouraged and subjected to removal in the future.

Support Strict Null-Checking

Version 1.0 supports strict null-checking and reflects the NOT NULL constraint defined in PostgreSQL schema.