Generate TypeScript definitions from a PostgreSQL database schema

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: "xxxxxx", 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 a small 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:migratescript 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!

Bringing the technical edge to early-stage companies with advice on software architecture, best practices, database design, web infrastructure, and DevOps.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store