Kanel tutorial: Generating TypeScript types from PostgreSQL

Synchronizing types between the database and your source code is one of the most important steps in creating secure and maintainable software. But doing so can often be time-consuming and error-prone, especially when dealing with large databases.

A mismatch can lead to unexpected runtime errors and inconsistencies in the application, especially in dynamic languages like JavaScript that don’t have static type checking. TypeScript can help address these issues by catching the type errors at compile time rather than runtime.

But TypeScript won’t automatically generate type definitions from a database out of the box. That’s where tools like Kanel come into play. Kanel allows developers to generate TypeScript types from PostgreSQL databases.

In this article, you will learn more about using Kanel to generate types that you can reuse for your frontend or backend apps. We’ll first go into what Kanel is and how it differs from other tools, and in the end, we’ll create a basic to-do list app using Vue.js, NestJS, Kanel, and Kysely.

Jump ahead:

• What is Kanel?
  • What makes Kanel different from an ORM?
  • What is database-driven development?
• Installing prerequisites
• Configuring Kanel and generating types
• Creating a basic app with Kanel and Kysely
  • Copying the template repository
  • Configuring a shared Kanel and Kysely package
  • Generating the PostgreSQL schema
  • Creating the backend API using the generated types
• Summary, further reading, and resources

What is Kanel?

Kanel is a tool that helps you generate types for your TypeScript code based on the structure of your PostgreSQL database. By connecting to your Postgres database and analyzing its schema, Kanel can generate TypeScript types that accurately represent the tables, columns, and relationships within your database.

Using Kanel, you won’t need to manually define TypeScript types for your database entities, saving you time and reducing the chances of human error. Furthermore, as your database schema evolves and changes over time, you can rerun Kanel to update the generated types, ensuring that your TypeScript code remains in sync with your database structure.

What makes Kanel different from an ORM?

Don’t be mistaken: Kanel is not an ORM tool. Kanel focuses solely on generating TypeScript types based on the Postgres database schema. ORMs help you connect and query your databases; Kanel does only a subset of what an ORM can do by generating the types.

You cannot query or modify your database using Kanel, let alone run a migration. When using Kanel, you must use other tools or frameworks to interact with your PostgreSQL database, such as an SQL query builder like Kysely. Kanel provides support for multiple SQL query builders, including Kysely and Knex, which will help you provide the schemas required for type-safe SQL queries and modifications.

Kanel doesn’t support migrations, and it is more suitable for projects that follow database-driven development approaches where the database schema is the source of truth for the application.

What is database-driven development?

Modern ORMs follow a code-driven development pattern where the ORM generates a database schema based on the code structure. ORMs like Prisma come to mind when thinking about code-driven development. However, database-driven development becomes more suitable when accessing a predefined database schema that multiple applications use.

In database-driven development, the database schema is considered the primary source of truth for the application. You don’t need to define anything else other than the types that Kanel will generate to interact with the database.

Installing prerequisites

Before we continue with the tutorial, ensure that you have the following prerequisites installed:

1. Node.js: You’ll need to have Node.js installed on your system
2. pnpm: We’ll use pnpm as our package manager throughout this article
3. A live PostgreSQL database: Ensure that you have a Postgres database up and running with the necessary tables and columns already defined
4. Docker and Docker Compose (optional): You can use Docker to help you run the database and provide a consistent environment for development and testing

After confirming that you meet the prerequisites, you can install Kanel to start generating TypeScript types from your PostgreSQL database.

Configuring Kanel and generating types

Create a new Node.js project by running pnpm init in your terminal, and follow the prompts to set up your project’s package.json file. Then, install Kanel by running:

pnpm install kanel

Once you have Kanel installed, you need to create a .kanelrc.js file in the root directory of your project. This file will contain the configuration settings for connecting to your Postgres database:

const path = require('path');
/** @type {import('kanel').Config} */
module.exports = {
  connection: {
    host: 'localhost',
    user: 'myuser',
    password: 'mypassword',
    database: 'mydatabase',
  },
  preDeleteOutputFolder: true,
  outputPath: './src/schemas',
  customTypeMap: {
    'pg_catalog.tsvector': 'string',
    'pg_catalog.bpchar': 'string',
  },
};

Now you can generate TypeScript types from your PostgreSQL database using Kanel. To do so, run the following command in the project’s root directory:

npx kanel

Assuming that your Postgres database is active, this command will trigger Kanel to generate the types in the output directory specified in your .kanelrc.js file.

Creating a basic app with Kanel and Kysely

We will create a to-do list app using a NestJS backend and a Vue 3 frontend. We won’t be going into too much detail about the NestJS setup and the development of the Vue frontend; instead, we’ll focus on integrating Kanel and Kysely into a backend API to interact with the Postgres database.

In this setup, we’ll use a monorepo architecture, where the backend and frontend are housed in the same repository. This allows for easier coordination and sharing of code between the two components.

Copying the template repository

Let’s start by copying the monorepo template from the companion GitHub repository for this article. Copy the base branch of the repo, which already has the Vue 3 frontend application and a basic NestJS backend set up — without Kanel and Kysely integrated.

Using the template will allow you to follow along more easily because this article won’t detail how to set up the backend or frontend from scratch.

Configuring a shared Kanel and Kysely package

After successfully cloning the template repository, you can create a shared Kanel and Kysely package. You will need a shared package for compatibility between the frontend and backend applications. This shared package will contain the generated TypeScript types from your Postgres database.

For now, you will mainly use it in the backend, but you can reuse it later in the frontend app. To start, create a new directory:

mkdir packages/todo-shared

Then, initiate the project using pnpm:

cd packages/todo-shared
pnpm init

Once you’ve initiated the project in the todo-shared directory, you can proceed to install the necessary dependencies:

pnpm i kysely && pnpm i -D kanel kanel-kysely typescript

Assuming you have Docker installed, the next step is to create a docker-compose.yml file to start a PostgreSQL database instance to use for the application:

version: '3.8'
services:
  db:
    image: postgres:13
    restart: always
    environment:
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: mydatabase
    ports:
      - "5432:5432"

Finally, configure Kanel by creating a new .kanelrc.js file in the todo-shared directory and add the necessary configuration for connecting to your Postgres database:

const path = require('path');
const { makeKyselyHook } = require('kanel-kysely');
/** @type {import('kanel').Config} */
module.exports = {
  connection: {
    host: 'localhost',
    user: 'myuser',
    password: 'mypassword',
    database: 'mydatabase',
  },
  preDeleteOutputFolder: true,
  outputPath: './src/schemas',
  customTypeMap: {
    'pg_catalog.tsvector': 'string',
    'pg_catalog.bpchar': 'string',
  },
  preRenderHooks: [makeKyselyHook()],
};

Since you will use Kysely as the SQL query builder to access the database, add it to the preRenderHooks. You can visit this branch to see how the entire monorepo looks after the changes.

Now you’re ready to generate your database schema!

Generating the PostgreSQL schema

After configuring Kanel, you can continue by running the PostgreSQL instance and running an SQL query to create the necessary tables and schema in your database:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  userId INTEGER,
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);
CREATE TABLE todos (
  id SERIAL PRIMARY KEY,
  title VARCHAR(100) NOT NULL,
  completed BOOLEAN DEFAULT false,
  projectId INTEGER,
  FOREIGN KEY (projectId) REFERENCES projects(id) ON DELETE CASCADE
);

This example will have three tables: the user table, the projects table, and the todos table. Each will have a relation with the next. Execute the query against your PostgreSQL instance, then generate the TypeScript schema using Kanel, go to the root of the todo-shared project directory, and run the following command:

npx kanel

You will see the TypeScript schema generated in the ./src/schemas folder, as defined in your .kanelrc.js configuration file.

Next, other packages inside your workspace must be able to import your schemas. To do this, compile your application by running tsc. Now you can import and use the generated TypeScript types from your other projects. If you didn’t follow along, you can see the results of all the steps in this branch.

Creating the backend API using the generated types

Import the generated TypeScript types from your todo-shared project into your backend API by running the following command in the root of the todo-backend directory:

pnpm add todo-shared@workspace:*

After importing the package, create a new database.ts file inside the src folder to initiate your Kysely connection to the PostgreSQL database:

import Database from 'todo-shared/dist/Database';
import { Pool } from 'pg';
import { Kysely, PostgresDialect } from 'kysely';
const dialect = new PostgresDialect({
  pool: new Pool({
    database: 'mydatabase',
    host: 'localhost',
    user: 'myuser',
    password: 'mypassword',
    max: 10,
  }),
});
// Database interface is passed to Kysely's constructor, and from now on, Kysely
// knows your database structure.
// Dialect is passed to Kysely's constructor, and from now on, Kysely knows how
// to communicate with your database.
export const db = new Kysely<Database>({
  dialect,
});

Delete all the old app controllers and write new ones for the user, project, and to-do endpoints. You can start by writing the service file:

import { Injectable } from '@nestjs/common';
import { UsersId } from 'todo-shared/dist/public/Users';
import { db } from './database';
@Injectable()
export class UserService {
  async getAll() {
    return await db.selectFrom('users').selectAll().execute();
  }
  async get(id: UsersId) {
    return await db
      .selectFrom('users')
      .selectAll()
      .where('id', '=', id)
      .execute();
  }
  async create(name: string, email: string) {
    return await db
      .insertInto('users')
      .values({ name, email })
      .returning(['id', 'name', 'email'])
      .executeTakeFirst();
  }
}

The user.service.ts file is responsible for handling the logic related to the user database operations. Then, to open the logic to the public, create a controller:

import {
  Controller,
  Get,
  Post,
  Body,
  Param,
} from '@nestjs/common';
import { UserService } from './user.service';
import { UsersId } from 'todo-shared/dist/public/Users';
@Controller('users')
export class UserController {
  constructor(private readonly userService: UserService) {}
  @Get()
  async getAllUsers() {
    return await this.userService.getAll();
  }
  @Get(':id')
  async getUser(@Param('id') id: UsersId) {
    return await this.userService.get(id);
  }
  @Post()
  async createUser(
    @Body('name') name: string,
    @Body('email') email: string,
  ) {
    return await this.userService.create(name, email);
  }
}

The user.controller.ts file is responsible for handling API requests and processing the parameters before sending them to the UserService. Next, create the business logic for the project endpoints:

import { Injectable } from '@nestjs/common';
import { db } from './database';
import { UsersId } from 'todo-shared/dist/public/Users';
import { ProjectsId } from 'todo-shared/dist/public/Projects';
@Injectable()
export class ProjectService {
  async getAll() {
    return await db.selectFrom('projects').selectAll().execute();
  }
  async get(id: ProjectsId) {
    return await db
      .selectFrom('projects')
      .selectAll()
      .where('id', '=', id)
      .execute();
  }
  async getByUserId(id: UsersId) {
    return await db
      .selectFrom('projects')
      .selectAll()
      .where('projects.userid', '=', id)
      .execute();
  }
  async create(name: string, userid: UsersId) {
    return await db
      .insertInto('projects')
      .values({ name, userid })
      .returning(['id', 'name', 'userid'])
      .executeTakeFirst();
  }
}

After creating the ProjectService, create the project controller to open the endpoints to the public:

import {
  Controller,
  Get,
  Post,
  Body,
  Param,
  Query,
} from '@nestjs/common';
import { ProjectService } from './project.service';
import { UsersId } from 'todo-shared/dist/public/Users';
import { ProjectsId } from 'todo-shared/dist/public/Projects';
@Controller('projects')
export class ProjectController {
  constructor(private readonly projectService: ProjectService) {}
  @Get()
  async getAllProjects(@Query('userId') userId: UsersId) {
    if (userId !== null) {
      return await this.projectService.getByUserId(userId);
    }
    return await this.projectService.getAll();
  }
  @Get(':id')
  async getProject(@Param('id') id: ProjectsId) {
    return await this.projectService.get(id);
  }
  @Post()
  async createProject(
    @Body('name') name: string,
    @Body('userId') userid: UsersId,
  ) {
    return await this.projectService.create(name, userid);
  }
}

Repeat this step one more time to create the TodoService and TodoController. Then, add the created services and controllers to the main app.module.ts file:

import { Module } from '@nestjs/common';
import { ProjectService } from './project.service';
import { TodoService } from './todo.service';
import { UserService } from './user.service';
import { ProjectController } from './project.controller';
import { TodoController } from './todo.controller';
import { UserController } from './user.controller';
@Module({
  imports: [],
  controllers: [ProjectController, TodoController, UserController],
  providers: [ProjectService, TodoService, UserService],
})
export class AppModule {}

Also, since you’ll be calling this API from the frontend, enable CORS inside the main.ts file:

import { NestFactory } from '@nestjs/core';
import { AppModule } from './app.module';
async function bootstrap() {
  const app = await NestFactory.create(AppModule);
  // Enable CORS
  app.enableCors();
  await app.listen(3000);
}
bootstrap();

Finally, to test your app, open two terminals from the root of the monorepo and run the following commands separately:

pnpm run backend start:dev

Then, run:

pnpm run frontend serve

You can then access the frontend app on your local machine by opening a web browser and navigating to http://localhost:8080.

The final state of the repository can be seen in the master branch.

Summary, further reading, and resources

Kanel is a tool for generating TypeScript types from PostgreSQL databases. It allows developers to define and use application-specific data types across modules, ensuring static typing and improving code quality and maintainability.

The provided code demonstrates the implementation of a NestJS backend application with controllers, services, and modules for managing projects, to-dos, and users. By leveraging Kanel, we can easily generate TypeScript types from our Postgres schema, which promotes type safety and eliminates manual type declarations.

This simplifies the development process by reducing the risk of type-related errors and providing autocomplete support in IDEs. Kanel is a good tool, but like anything else, it’s not usable under every scenario. It will be very handy for scenarios where the database is already developed and acts as the single source of truth. Otherwise, it may not be as useful.

You can review further documentation regarding Kanel here:

• Kanel docs: https://kristiandupont.github.io/kanel/
• Monorepo example: https://github.com/agustinustheo/vue-nest-kanel-kysely