I am a person who, since I started developing my first projects (my OT Pokémon and my first websites for Habbo), I have always opted for Raw SQL. Honestly, I still really enjoy writing my own queries and having more precise control over this "low-level" layer. An ORM doesn't make me completely comfortable, as I've already spent days analyzing logs to identify and optimize inefficient queries.

However, in many codebases where I worked with Raw SQL, the vast majority did not have migration control, and the database was not monitored either. Everything worked on an improvisational basis: "Do you need a new field? Run ALTER TABLE and add a new column." This approach was extremely harmful in all scenarios, several questions arose, such as: "Which columns should we go up in the production environment?", "What new entities were created?", "Are the environments synchronized?" — and many other similar problems.

The solution to my problems

Faced with all these problems, I decided to adopt new tools to make my routine and those of the teams I worked with healthier. I didn't want to give up the flexibility I had, but I also wanted to better control the degrees of freedom of the application. After a lot of research, I found a tool that I consider the most complete for solving these problems: Kysely, it is a query builder for TypeScript that, in addition to being practical, is completely type-safe — a super important point for me. This lib caught my attention so much that I started actively contributing to the community, both directly and indirectly, creating plugins for other open source libraries integrated with Kysely.

However, one of the biggest difficulties when working with Kysely is that, unlike ORM's, it does not have an entity or automatic generation of types/interfaces. All this work needs to be done manually, which can be a bit exhausting. During my research for solutions, I found a tool that I ended up adopting in all my projects involving PostgreSQL: Kanel. Kanel automatically generates database typings, perfectly complementing Kysely.

In addition, Kanel has an additional feature for direct use with Kysely: Kanel-Kysely. I have been actively contributing to this repository, helping to develop new features, such as type filters for migration tables and the conversion of Zod objects to camelCase.

Configuring Kysely

I will be using NestJS to illustrate the following examples. So, if you don't understand some syntax or something in the code, I suggest reading the NestJS documentation. In my opinion, it is the best JavaScript framework — especially if you want to "escape" JavaScript. But that's a topic for another post of mine.

Beforehand, you will need to have a repository with NestJS initialized, if you want to follow the examples to the letter. However, you can also develop your own code.

At first, we will need to install Kysely itself, its CLI and the PostgreSQL module for Node.js.

npm i kysely pg && npm i kysely-ctl --save-dev

Next, we will need to create a configuration file in the root of the project for Kysely. I will also use the Knex prefix for our migrations and seeds files.

// kysely.config.ts

import "dotenv/config";

import { defineConfig, getKnexTimestampPrefix } from "kysely-ctl";
import { Pool } from "pg";

export default defineConfig({
  dialect: "pg",
  dialectConfig: {
    pool: new Pool({ connectionString: process.env.DATABASE_URL }),
  },
  migrations: {
    migrationFolder: "src/database/migrations",
    getMigrationPrefix: getKnexTimestampPrefix,
  },
  seeds: {
    seedFolder: "src/database/seeds",
    getSeedPrefix: getKnexTimestampPrefix,
  },
});

Next, we will run the command npx kysely migrate make create_user_table in our terminal. It will be responsible for creating our first migration. Next, we will create a new user table and, once done, we will run this migration in our database with the command npx kysely migrate latest.

// 20241225222128_create_user_table.ts

import { sql, type Kysely } from 'kysely'


export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
  .createTable("user")
  .addColumn("id", "serial", (col) => col.primaryKey())
  .addColumn("name", "text", (col) => col.notNull())
  .addColumn("email", "text", (col) => col.unique().notNull())
  .addColumn("password", "text", (col) => col.notNull())
  .addColumn("created_at", "timestamp", (col) =>
    col.defaultTo(sql`now()`).notNull(),
  )
  .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable("user").execute();
}

With all these steps completed, let's create a module for our database. Also notice that I'm using a Kysely plugin to convert our columns to camelCase.

// src/database/database.module.ts

import { EnvService } from "@/env/env.service";
import { Global, Logger, Module } from "@nestjs/common";
import { CamelCasePlugin, Kysely, PostgresDialect } from "kysely";
import { Pool } from "pg";

export const DATABASE_CONNECTION = "DATABASE_CONNECTION";

@Global()
@Module({
  providers: [
    {
      provide: DATABASE_CONNECTION,
      useFactory: async (envService: EnvService) => {
        const dialect = new PostgresDialect({
          pool: new Pool({
            connectionString: envService.get("DATABASE_URL"),
          }),
        });

        const nodeEnv = envService.get("NODE_ENV");

        const db = new Kysely({
          dialect,
          plugins: [new CamelCasePlugin()],
          log: nodeEnv === "dev" ? ["query", "error"] : ["error"],
        });

        const logger = new Logger("DatabaseModule");

        logger.log("Successfully connected to database");

        return db;
      },
      inject: [EnvService],
    },
  ],
  exports: [DATABASE_CONNECTION],
})
export class DatabaseModule {}

Configuring Kanel

Let's start by installing our dependencies.

npm i kanel kanel-kysely --save-dev

Next, let's create our configuration file for Kanel to start doing its work. Note that I will be using some plugins, such as camelCaseHook (to transform our interfaces into camelCase) and kyselyTypeFilter (to exclude Kysely's migration tables), one of these features I had the pleasure of being able to contribute and make the work we had even easier .

// .kanelrc.js

require("dotenv/config");

const { kyselyCamelCaseHook, makeKyselyHook, kyselyTypeFilter } = require("kanel-kysely");

/** @type {import('kanel').Config} */
module.exports = {
  connection: {
    connectionString: process.env.DATABASE_URL,
  },
  typeFilter: kyselyTypeFilter,
  preDeleteOutputFolder: true,
  outputPath: "./src/database/schema",
  preRenderHooks: [makeKyselyHook(), kyselyCamelCaseHook],
};

Once the file is created, we will run the command npx kanel in our terminal. Note that a directory was created in the path specified in the configuration file. This directory corresponds to the name of your schema, in our case, Public, and inside it we have two new files: PublicSchema.ts and User.ts. Your User.ts will probably look exactly like this:

// @generated
// This file is automatically generated by Kanel. Do not modify manually.

import type { ColumnType, Selectable, Insertable, Updateable } from 'kysely';

/** Identifier type for public.user */
export type UserId = number & { __brand: 'UserId' };

/** Represents the table public.user */
export default interface UserTable {
  id: ColumnType<UserId, UserId | undefined, UserId>;

  name: ColumnType<string, string, string>;

  email: ColumnType<string, string, string>;

  password: ColumnType<string, string, string>;

  createdAt: ColumnType<Date, Date | string | undefined, Date | string>;
}

export type User = Selectable<UserTable>;

export type NewUser = Insertable<UserTable>;

export type UserUpdate = Updateable<UserTable>;

However, the most important thing is the file outside this directory Public, the file Database.ts, because it is this that we will pass on so that Kysely can understand the entire structure of our database. Inside our file app.service.ts, we will inject our DatabaseModule provider and pass our type Database.
to Kysely

npm i kysely pg && npm i kysely-ctl --save-dev

Notice that the typing that Kanel generated is working correctly, because our code editor will suggest precisely the columns that we created in our first migration.

Final considerations

This is a duo that I really like to use in my personal projects and even at work (when I have the freedom to do so). A query builder is the essential tool for everyone who likes the flexibility that Raw SQL offers, but also opts for a "safer" path. Kanel has also saved me many hours of debugging and creating new typings. I strongly recommend that you create a project with these two, you definitely won't regret it.

Repository Link: frankenstein-nodejs

The above is the detailed content of Kisley Kanel: a perfect duo. For more information, please follow other related articles on the PHP Chinese website!