DEV Community

Cover image for Generate TypeScript definitions from PostgreSQL
Konstantin Tarkus
Konstantin Tarkus

Posted on • Updated on • Originally published at koistya.Medium

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);
});
Enter fullscreen mode Exit fullscreen mode

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("*");
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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);
});
Enter fullscreen mode Exit fullscreen mode

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"
  }
}
Enter fullscreen mode Exit fullscreen mode

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

Happy coding!

Top comments (0)