Generating Typescript Types from Postgres

I’ve created a tool that extracts the schema of a Postgres database and generates Typescript types out of it. It’s called Kanel.

It is not an ORM. I simply wanted types in my editor so that I could get

1. autocomplete, and
2. error messages if I forget a field or try to access a field that doesn’t exist.

There are many ways to achieve this but I had a specific requirement that made me decide on this approach: I wanted my database schema to be the only source of truth. I wanted to write migrations in plain SQL and queries in almost-plain SQL (using Knex.js). Two things had inspired me to follow what you might call a database-driven design: a project called Postgrest and this blog post: Simplify: move code into database functions. With this philosophy, I reuse many of the generated types in frontend code as well as backend code.

It’s quite simple to get “reflection” data from a database, so establishing which tables, columns and views exist was easy. However, that raw information is often not quite sufficient for describing the semantics of the schema. I wanted store metadata of various sorts. For instance, some tables have columns that contain encrypted values and I wanted to mark those so I could automatically decrypt the data on the server and put some checks into place to make sure they were never accidentally transmitted to the frontend.

I considered having some sort of file that would complement the database which would contain such metadata, but that then meant maintaining things in two places. Not a complete showstopper as it would be pretty trivial to keep the two in sync but not great either. Fortunately, Postgres allows you to write comments on almost everything: tables, columns, procedures, etc. Not only could I use those to write actual comments that would go into my typescript types as JSDoc, I could add support for custom metadata with a tagging pattern. So I added a tiny parsing stage that handles comments like this:

COMMENT ON TABLE member 
 IS 'Member of the organization @cached @alias:person';

This results in the following comment and map of tags:

comment: 'Member of the organization',
tags: { cached: true, alias: 'person' }

Now, Kanel itself only looks for one tag at the moment: fixed which indicates that it shouldn’t create an initializer type for the table. But you can expand on this for any sort of metadata you might want.

It’s pretty easy to get Kanel running. You need to create a configuration file called .kanelrc.js and have access to a running database (which will typically be your local developer db). This is a minimal configuration file:

const path = require('path');

module.exports = {
 connection: {
 host: 'localhost',
 user: 'postgres',
 password: 'postgres',
 database: 'acme',
 },

schemas: [
 {
 name: 'public',
 modelFolder: path.join(\_\_dirname, 'src', 'models'),
 },
 ],
};

If you have added Kanel to your devDependencies, you can run it:

$ npx kanel

…and it will generate typescript files for your schema in the /src/models folder. They will look something like this:

As you can see, it has created two interfaces: Actor and ActorInitializer where the initializer contains optional fields for things that have default values. In addition to this, it’s created an id type called ActorId which uses a trick called flavoring (which is similar but not identical to branding) to simulate nominal typing. This means that you will not be allowed to assign an id belonging to one table to an instance of another.

If you are already using Typescript, you can just start using the types as you would anything else. If you are using Javascript, you still can with the help of JSDoc comments. This:

/\*\* @type {import('./models/Actor').default} \*/
const actor = fetchActor();

will mark the actor variable as a Actor instance. If you are using an editor that supports the typescript language server like VSCode, you should get autocomplete suggestions when you access it. You can also enable checkJs in your tsconfig.json or jsconfig.json file to get red squiggly lines if you mistype something.

Of course, now you will want a query library that works with these types and I am sorry to say but at the time of writing, I have not yet extracted that from the Submotion code base into open source. I plan to do so, though, so stay tuned!