TypeScript Types Generation from PostgreSQL

In applications, inconsistencies and unexpected errors can arise from type mismatches, especially in languages like JavaScript that lack static type checking. TypeScript mitigates this issue by identifying type errors during compilation rather than runtime.

However, TypeScript does not inherently produce type definitions from databases. This is where tools like Kanel become invaluable. Kanel empowers developers to generate TypeScript types directly from PostgreSQL databases.

This article explores using Kanel to generate reusable type definitions for both front-end and back-end applications. It starts with an explanation of Kanel's functionality and how it distinguishes itself from similar tools. Finally, it demonstrates creating a fundamental to-do list application utilising Vue.js, NestJS, Kanel, and Kysely.

Kanel Database-Driven Development

Kanel simplifies the process of generating TypeScript types by analysing the schema of your PostgreSQL database. It connects to your database, examines its structure, and creates TypeScript types that precisely reflect the tables, columns, and relationships within it. With Kanel, there's no need for a manual definition of TypeScript types for your database entities. This not only saves time but also reduces the likelihood of human error. Moreover, as your database schema evolves, you can rerun Kanel to automatically update the generated types, ensuring your TypeScript code stays aligned with the database structure.

Contemporary ORMs typically adopt a code-driven approach, wherein the ORM constructs a database schema based on the structure of the code. Examples include Prisma, a well-known ORM that exemplifies this methodology. However, database-driven development gains relevance in scenarios where multiple applications rely on a pre-existing database schema. In database-driven development, the database schema holds precedence as the main point of reference for the application. Consequently, there's no need to define anything beyond the types generated by Kanel for interacting with the database.

Prerequisites

Before diving into the tutorial, ensure you have the following prerequisites ready:

1. Node.js: Verify that Node.js is installed on your system.
2. pnpm: We'll utilise pnpm as this tutorial's package manager.
3. Live PostgreSQL database: Ensure you have a PostgreSQL database set up and running with the required tables and columns already defined.
4. Docker and Docker Compose (optional): If you prefer, Docker can run the database, providing a consistent environment for development and testing.

Once you've confirmed these prerequisites, you can install Kanel and start generating TypeScript types from your PostgreSQL database.

Kanel Configuration

1. To set up Kanel and generate types, initiate a new Node.js project through the terminal using the command pnpm init. Follow the prompts to configure your project's package.json file. Subsequently, install Kanel by executing
   pnpm install kanel
2. Once Kanel is installed, create a .kanelrc.js file in the root directory of your project. This file will contain the configuration settings for connecting to your PostgreSQL database. Here's an example configuration:

const path = require('path');

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',
  },
};

3. Now, you can generate TypeScript types from your PostgreSQL database using Kanel. Navigate to the project's root directory and run the following command:

npx kanel

4. Assuming your PostgreSQL database is active, this command will prompt Kanel to generate the types in the specified output directory as configured in your .kanelrc.js file.

Creating a Monorepo With A Basic Structure

Let's create a new monorepo with a basic structure for our to-do list application. We'll have separate directories for the back-end (NestJS) and front-end (Vue.js), and we'll integrate Kanel and Kysely into the back-end later. Here's a simplified version of how we can set up the structure:

todo-list-monorepo/
│
├── backend/
│   ├── src/
│   │   ├── controllers/
│   │   ├── services/
│   │   ├── models/
│   │   ├── app.module.ts
│   │   └── main.ts
│   ├── .kanelrc.js
│   └── package.json
│
└── frontend/
    ├── public/
    ├── src/
    │   ├── components/
    │   ├── views/
    │   ├── App.vue
    │   └── main.js
    ├── .env
    └── package.json

In this structure:

• The backend/ directory contains all the backend-related code for the NestJS application.
• The frontend/ the directory contains the Vue.js front-end code.
• We have the necessary subdirectories and files to organise our code inside each directory.
• We'll later integrate Kanel and Kysely into the back-end by configuring .kanelrc.js and updating the code.

Building The Structure

1. Begin by creating a fresh directory for your monorepo: use the command mkdir todo-list-mono repo.
2. Move into the newly created directory: utilise cd todo-list-monorepo.
3. Set up the required directories and files for the back-end code structure, including src/, .kanelrc.js, etc.
4. Return to the root directory: move back with cd.
5. Proceed with setting up your Vue.js project by following the provided prompts.
6. Once the Vue.js project setup is complete, create the necessary directories and files for the front-end code structure, such as src/, public/, and others.
7. Create the front-end directory and initialise a Vue.js project inside it:

mkdir frontend
cd frontend
vue create 

8. Create the back-end directory and initialise a Node.js project inside it:

mkdir backend
cd backend
npm init -y

Once you've completed these steps, you'll have a basic monorepo structure for your to-do list application. You can continue integrating Kanel and Kysely into the back-end and developing the front-end components.

Creating A Shared Package for Kanel And Kysely

The next step is to create a shared package for Kanel and Kysely. This package will ensure compatibility between the front-end and back-end applications and contain the TypeScript types generated from your PostgreSQL database.

1. To get started, create a new directory named todo-shared:

mkdir packages/todo-shared

2. Navigate into the todo-shared directory and initialise a new Node.js project using. pnpm:

cd packages/todo-shared
pnpm init

3. Once the project is initialised, install the necessary dependencies:

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

4. If you have Docker installed, proceed to create a docker-compose.yml File to set up a PostgreSQL database instance 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"

5. Finally, configure Kanel by creating a .kanelrc.js file in the todo-shared directory and specify the connection settings for your PostgreSQL database:

const path = require('path');
const { makeKyselyHook } = require('kanel-kysely');

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()],
};

Don't forget to include kysely as a pre-render hook since it will be used as the SQL query builder to access the database.

Once these configurations are in place, your shared package for Kanel and Kysely will be ready to use in both the back-end and front-end applications.

Table Config

1. After configuring Kanel, create the necessary tables and schema in your PostgreSQL database using SQL queries. Below is the SQL code to create the tables:

-- Create the 'users' table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

-- Create the 'projects' table
CREATE TABLE projects (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  userId INTEGER,
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
);

-- Create the 'todos' table
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
);

Executing this command will produce a TypeScript schema by analysing the structure of your PostgreSQL database. The resulting schema will be stored in the designated directory, ./src/schemas, as outlined in your .kanelrc.js setup.

npx kanel

This command will generate the TypeScript schema based on the database structure defined in your PostgreSQL instance and save it in the ./src/schemas folder, as specified in your .kanelrc.js configuration file.

2. Once the TypeScript schema is generated, compile your application by running tsc to enable other packages within your workspace to import and use the generated TypeScript types from your projects.
3. To integrate the generated TypeScript types into your back-end API, import them from the todo-shared project. Run the following command in the root of the todo-backend directory to import the package:

pnpm add todo-shared@workspace:*

4. After importing the package, create a new file named database.ts inside the src folder of your back-end project. This file will establish the connection to the PostgreSQL database using Kysely:

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,
  }),
});

export const db = new Kysely<Database>({
  dialect,
});

This database.ts file initialises the Kysely connection to the PostgreSQL database using the specified credentials.

5. Next, you'll create service and controller files for handling user, project, and to-do endpoints. Below is an example of the UserService and UserController:

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();
  }
}

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);
  }
}

These files handle the logic related to user operations and expose corresponding API endpoints.

6. Similarly, you'll create service and controller files for projects and to-dos and then add them to the AppModule. Additionally, enable CORS inside the main.ts file.
7. To test your app, open two terminals from the root of the monorepo and run the following commands separately:

pnpm run backend start:dev

pnpm run frontend serve

8. This setup allows you to access the front-end app on your local machine by navigating to http://localhost:8080 in a web browser.

Thank you for reading this article. Please consider subscribing if you liked it.