Generate TypeScript definitions from PostgreSQL

I've been enjoying using Knex.js database client for quite some time when implementing GraphQL API backends. One thing that it currently lucks though, is the ability to generate strongly typed (TypeScript) models from the actual database schema.

Luckily, there is a solution! Assuming you have a database table that looks like this (Knex migration):

await db.raw(`
  CREATE DOMAIN short_id
    AS text CHECK(VALUE ~ '^[0-9a-z]{6}$')`);
await db.raw(`
  CREATE TYPE user_role
    AS ENUM ('consumer', 'provider')`);

await db.schema.createTable("user", (table) => {
  table.specificType("id", "short_id").notNullable().primary();
  table.specificType("email", "citext").unique();
  table.text("name").notNullable();
  table.jsonb("credentials").notNullable().defaultTo("{}");
  table.specificType("role", "user_role").notNullable();
  table.timestamps(false, true);
});

Having User (class) and UserRole (enum) data models in place, you would take full advantage of using Knex.js with TypeScript:

import db, { User, UserRole } from "./db";

const [user] = await db
  .table<User>("user")
  .insert({ id: "1", name: "John", role: UserRole.Provider })
  .onConflict()
  .merge()
  .returning("*");

TypeScript definitions for this particular database schema would look like this:

export enum UserRole {
  Consumer = "consumer",
  Provider = "provider",
}

export type User = {
  id: string;
  email: string | null;
  name: string;
  credentials: Record<string, unknown>;
  role: UserRole;
  created_at: Date;
  updated_at: Date;
}

Now, the interesting part, how to generate these types automatically. Here is the script that will do the trick:

const { knex } = require("knex");
const { updateTypes } = require("knex-types");
const db = knex(require("./knexfile"));

updateTypes(db, { output: "./types.ts" }).catch(err => {
  console.error(err);
  process.exit(1);
});

You would execute it as part of database migration workflow, e.g. by adding it to yarn db:migrate script in package.json:

{
  "dependencies": {
    "knex": "^0.95.4",
    "pg": "^8.6.0"
  },
  "devDependencies": {
    "knex-types": "^0.1.3"
  },
  "scripts": {
    "db:migrate": "knex migrate:latest && node ./update-types"
  }
}

Check out kriasoft/node-starter-kit containing a complete usage example.

Happy coding!