Generate TypeScript definitions from a PostgreSQL database schema

Konstantin Tarkus
2 min readApr 14, 2021

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.3.0"
},
"scripts": {
"db:migrate": "knex migrate:latest && node ./update-types"
}
}

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

Happy coding!

Sign up to discover human stories that deepen your understanding of the world.

Free

Distraction-free reading. No ads.

Organize your knowledge with lists and highlights.

Tell your story. Find your audience.

Membership

Read member-only stories

Support writers you read most

Earn money for your writing

Listen to audio narrations

Read offline with the Medium app

Konstantin Tarkus
Konstantin Tarkus

Written by Konstantin Tarkus

Empowering startups with cutting-edge expertise: software architecture, optimal practices, database design, web infrastructure, and DevOps mastery.

No responses yet

What are your thoughts?