DEV Community

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

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

3 3

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!

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up