Kysely.js типизированный sql builder

Kysely.js – это библиотека, позволяющая писать типизированные SQL запросы. Библиотека делает работу с SQL в вашем проекте более безопасной, избавляя от таких ошибок как опечатки в названиях колонок или таблиц и неправильное использование SQL операторов в коде (код не скомпилируется). Ко всему прочему она делает работу с SQL более удобной, предоставляя при написании запросов автодополнения для таблиц, колонок, алиасов и других сущностей. Kysely имеет незначительный слой абстракции над SQL для того чтобы можно было пользоваться всей мощью SQL и при этом не изучать множество дополнительных сущностей. Библиотека поддерживает MySQL, PostgreSQL, SQLite, PlanetScale, D3, SurrealDB и другие.

Теперь погрузимся в наш кисель ?.

Схема БД

Установим сам модуль:

npm i kysely -S

Затем необходимо установить диалект. В наших примерах мы будем работать с MySQL. Но есть официальная поддержка PostgreSQL и SQLite:

# MySQL
npm i mysql2 -S 

# PostgreSQL
# npm install pg -S 

# SQLite
# npm install better-sqlite3

Список доступных диалектов. После этого необходимо задекларировать схему нашей базы и таблиц:

// schema.ts

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

export interface UserTable {
  id: Generated<number>;
  name: string;
  gender: 'man' | 'woman' | null;
  last_name: string | null;
  created_at: ColumnType<Date, string | Date | undefined, never>
}

export type User = Selectable<UserTable>
export type NewUser = Insertable<UserTable>
export type UserUpdate = Updateable<UserTable>

export interface OrderTable {
  id: Generated<number>;
  user_id: number;
  amount: number;
  status: 'pending' | 'approved' | 'canceled' | 'paid';
  created_at: ColumnType<Date, string | undefined, never>;
  updated_at: ColumnType<Date, string | undefined, Date>;
}

export type Order = Selectable<OrderTable>
export type NewOrder = Insertable<OrderTable>
export type OrderUpdate = Updateable<OrderTable>

export interface Database {
  user: UserTable;
  order: OrderTable;
}

Generated это тип, который необходимо применять для колонок, которые генерит сама база данных, например, поле с автоинкрементом. И таким образом при update/insert это поле станет опциональным.

Если поле в БД может быть null, то не надо делать его опциональным (last_name?). Kysely автоматически сделает это за вас.

C помощью ColumnType можно указать разные типы для колонок в зависимости от того делаем мы select, insert или update. Тип ColumnType<SelectType, InsertType, UpdateType> принимает на вход 3 дженерик типа для каждого типа операции соответственно. Это может быть удобно, например, для колонок с типом datetime или timestamp. Для таблицы user мы определили, что колонка created_at для select будет иметь тип Date, для insert она будет опциональной и будет иметь тип Date или string (время в формате строки), а операцияupdateнад ней будет запрещена с помощью типа never.

Вам может потребоваться использовать напрямую интерфейсы таблиц вашей базы данных. Не стоит этого делать, kysely предоставляет типы обертки Selectable, Insertable, Updateableдля соответствующих операций. Они гарантируют, что будут использоваться корректные типы для каждого типа операций.

Теперь необходимо создать подключение к БД:

// db.ts

// Схема БД
import { Database } from './schema' 
// do not use 'mysql2/promises'!
import * as mysql from 'mysql2' 
import { Kysely, MysqlDialect } from 'kysely'

const dialect = new MysqlDialect({
  pool: mysql.createPool({
    database: 'test',
    host: 'localhost',
    user: 'test',
    password: 'test',
    port: 3306,
    connectionLimit: 10,
  })
})


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

export default db;

Мы создаем инстанс БД, передав диалект, а также схему базы данных как дженерик параметр для корректной типизации.

Пример user repository c kysely:

import { Kysely } from 'kysely';
import { Database, User, NewUser, UserUpdate } from './schema';

class UserRepo {
  constructor(private readonly db: Kysely<Database>) {}

  async getAll() {
    return await this.db
      .selectFrom('user')
      .select(['id', 'name'])
      .execute();
  }

  async getById(id: number) {
    return await this.db
      .selectFrom('user')
      .select(['id', 'name'])
      .where('id', '=', id)
      .executeTakeFirst()
    ;
  }

  async search(params: Partial<User>) {
    return await this.db
      .selectFrom('user')
      .select([ 'id', 'name', 'last_name' ])
      .where((eb) => eb.and(params)).executeTakeFirst();
  }

  async create(data: NewUser) {
    const { insertId } = await this.db
      .insertInto('user')
      .values(data)
      .executeTakeFirst();
    
    if (!insertId) {
      throw new Error(`Not found id ${insertId}`);
    }
    return Number(insertId);
  }

  async updateById(id: number, data: UserUpdate) {
    return await this.db
      .updateTable('user')
      .set(data)
      .where('id', '=', id)
      .execute();
  }

  async delete(id: number) {
    return await this.db
      .deleteFrom('user')
      .where('id', '=', id)
      .execute();
  }
}


const userRepo = new UserRepo(db);
const userId = await userRepo.create({
  name: 'Олег',
  last_name: 'Иванов',
  gender: 'man',
});

const users = await userRepo.getAll();
// [ { id: 2, name: 'Олег' } ]  
console.log(users);

const user = await userRepo.getById(userId);
// { id: 2, name: 'Олег' }
console.log(user);

await userRepo.updateById(userId, { last_name: '' });

const foundedUser = await userRepo.search({ name: 'Олег', last_name: '' });
// { id: 2, name: 'Олег', last_name: '' }
console.log(foundedUser);

Теперь подробнее рассмотрим возможности kysely.

Insert/Update/Delete

Вставка:

const result = await db.insertInto('user').values({
  name: 'Иван',
  last_name: 'Иванов',
  gender: 'man',
  created_at: new Date().toISOString(),
}).executeTakeFirst();
  
console.log({ userId: result.insertId });

Множественная вставка:

const result = await db.insertInto('user').values([{
  name: 'Иван',
  last_name: 'Иванов',
  gender: 'man',
  created_at: new Date(),
}, {
  name: 'Алена',
  last_name: 'Петрова',
  gender: 'woman',
  created_at: new Date(),
}]).executeTakeFirstOrThrow();
  
console.log(result.numInsertedOrUpdatedRows); // 2

Для выполнения запросов используются следующие методы: execute (который возвращает массив значений), executeTakeFirst (который возвращает первое значение), executeTakeFirstOrThrow (аналог предыдущего метода, но бросающий ошибку, если результат не найден).

Имеется поддержка БД зависимого синтаксиса:

// Only MySQL: игнорим ошибку при вставке в таблицу дубликата
const result = await db.insertInto('user').ignore().values({
  id: 2,
  name: 'Алена',
  last_name: 'Петрова',
  gender: 'woman',
  created_at: new Date(),
}).executeTakeFirst();
  
console.log(result.numInsertedOrUpdatedRows); // 0


// Only MySQL: в случае ошибки дублирования при вставке, 
// то last_name делаем пустым
const result = await db.insertInto('user').values({
  id: 2,
  name: 'Алена',
  last_name: 'Петрова',
  gender: 'woman',
  created_at: new Date(),
}).onDuplicateKeyUpdate({ last_name: '' }).executeTakeFirst();

console.log(result.numInsertedOrUpdatedRows); // 2


// Only PostgreSQL: в случае ошибки дублирования при вставке, 
// то last_name делаем пустым
const result = await db.insertInto('user').values({
  id: 2,
  name: 'Алена',
  last_name: 'Петрова',
  gender: 'woman',
  сreated_at: new Date(),
}).onConflict((oc) => 
    oc.column('id').doUpdateSet({ last_name: '' })
).executeTakeFirst();

console.log(result.numInsertedOrUpdatedRows); // 2

// Only PostgreSQL: получить значения колонок, которые были вставлены  
const result = await db.insertInto('user').values({
  name: 'Алена',
  last_name: 'Петрова',
  gender: 'woman',
  created_at: new Date(),
}).returning([ 'id', 'name' ]).executeTakeFirst();

console.log(result); // { id: 3, name: 'Алена' }

Обновление:

const result = await db
  .updateTable('user')
  .set({
    last_name: 'Иванова'
  })
  .where('id', '=', 2)
  .executeTakeFirst()

console.log(result.numUpdatedRows) // 1

Удаление:

const result = await db
  .deleteFrom('user')
  .where('user.id', '=', 1).executeTakeFirst();

console.log(result.numDeletedRows); // 1

Select

Выборка пользователей с использованием alias:

const users = await db
  .selectFrom('user')
  .select(['id', 'user.name as name', 'created_at as createdAt'])
  .where('gender', '!=', 'man')
  .execute();

// [{ id: 2, name: 'Алена', createdAt: 2023-09-07T07:38:49.000Z }]
console.log(users); 

Выборка одной строки с использованием limit и order by :

const user = await db
  .selectFrom('user')
  .select(['id', 'user.name as name', 'created_at as createdAt'])
  .where('gender', '!=', 'man')
  .orderBy('id', 'desc')
  .limit(1)
  .executeTakeFirst();

// { id: 2, name: 'Алена', createdAt: 2023-09-07T07:38:49.000Z }
console.log(user); 

Разумеется есть возможность делать подзапросы, и хотя kysely не является ORM, он поддерживает автоматическую сериализацию данных в объект или массив:

// mysql
import { jsonObjectFrom, jsonArrayFrom } from 'kysely/helpers/mysql'
// postgres
// import { jsonObjectFrom, jsonArrayFrom } from 'kysely/helpers/postgres'

// Получить всех пользователей вместе с оплаченным заказом
const result = await db
  .selectFrom('user')
  // подзапрос
  .select((eb) => [
    'id',
    jsonObjectFrom(
      eb.selectFrom('order')
        .select(['order.id as orderId', 'order.amount'])
        .whereRef('order.user_id', '=', 'user.id')
        .where('order.status', '=', 'paid')
        .where('order.amount', '>', 100)
        .limit(1)
    ).as('paid_order')
  ])
  .execute();

/*
[
  { id: 1, paid_order: { amount: 546, orderId: 2 } },
  { id: 2, paid_order: null }
]
*/
console.log(result);

// Получить всех пользователей вместе с оплаченными заказами
const result = await db
  .selectFrom('user')
  // подзапрос
  .select((eb) => [
    'id',
    jsonArrayFrom(
      eb.selectFrom('order')
        .select(['order.id as orderId', 'order.amount'])
        .whereRef('order.user_id', '=', 'user.id')
        .where('order.status', '=', 'paid')
        .where('order.amount', '>', 100)
    ).as('paid_orders')
  ])
 .execute();
/*
[
  { id: 1, paid_orders: [ { amount: 546, orderId: 2 } ] },
  { id: 2, paid_orders: [] }
]
*/
console.dir(result, { depth: 10 });

whereRef позволяет в подзапросах использовать в условии колонки из другой таблицы.

Разумеется можно использовать sql функции:

const result = await db.selectFrom('order')
  .select(({ fn }) => [
    // `fn` содержит все основные функции
    fn.count<number>('order.id').as('order_count'),

    // С помощью `agg` можно вызвать любую функцию.
    // Первым аргументом передаем тип колонки
    fn.agg<string>('GROUP_CONCAT', ['order.id']).as('order_ids')
  ])
  .groupBy('order.user_id')
  .execute()
;
/*
[
  { order_count: 2, order_ids: '1,2' },
  { order_count: 1, order_ids: '3' }
]
*/
console.log(result);

Если запрос достаточно сложный и необходимо глянуть, что за sql на выходе получается, то это можно сделать с помощью метода compile

const sql = db.selectFrom('order')
  .select(({ fn }) => [
    fn.count<number>('order.id').as('order_count'),
    fn.agg<string>('GROUP_CONCAT', ['order.id']).as('order_ids')
  ])
  .groupBy('order.user_id')
  .compile();

/*{
  query: {
    kind: 'SelectQueryNode',
    from: { kind: 'FromNode', froms: [Array] },
    selections: [ [Object], [Object] ],
    groupBy: { kind: 'GroupByNode', items: [Array] }
  },
  sql: 'select count(`order`.`id`) as `order_count`, GROUP_CONCAT(`order`.`id`) as `order_ids` from `order` group by `order`.`user_id`',
  parameters: []
}*/
console.log(sql);

Благодаря chaining в js вы можете поэтапно добавлять операторы sql в зависимости от условий. Но это может породить следующую проблему:

async function getUser(id: number, withLastName: boolean) {
  let query = db.selectFrom('user').select('name').where('id', '=', id);

  if (withLastName) {
    query = query.select('last_name')
  }

  return await query.executeTakeFirstOrThrow()
}

const user = await getUser(1, true);

// Property 'last_name' does not exist on type '{ name: string; }'
console.log(user.last_name);

Это проблема будет возникать с такими функциями, как select, returning, innerJoin и всеми другими, которые влияют на кол-во полей в запросе и соответственно на тип возвращаемых данных. Таких проблем не будет с функциями where, groupBy, orderBy и другими, которые не влияют на тип query builder.

Решение проблемы есть, оно заключается в использовании метода $if, который позволяет опционально добавлять sql код и при этом итоговый тип данных будет верный:

async function getUser(id: number, withLastName: boolean) {
  return await db
    .selectFrom('user')
    .select('name')
    .$if(withLastName, (qb) => qb.select('last_name'))
    .where('id', '=', id)
    .executeTakeFirstOrThrow()
  ;
}

const user = await getUser(1, true);
/*{
    name: string;
    last_name?: string | null | undefined;
}*/
console.log(user.last_name);

Join

Для выполнения join есть следующий набор методов: innerJoin, leftJoin, rightJoin, fullJoin.

const result = await db.selectFrom('user as u')
  .innerJoin('order as o', 'u.id', 'o.user_id')
  .select([ 'u.id', 'u.name', 'o.status', 'o.amount'])
  .where('o.status', '=', 'paid')
  .execute();

// [{ id: 1, name: 'Иван', status: 'paid', amount: 546 }]
console.log(result);

Метод select необходимо вызывать после всех join, чтобы скомпилировать код и получить работающий автокомплит для колонок таблиц.

Внутрь метода join можно передать вторым параметром функцию для построения более сложных условий объединения:

const result = await db.selectFrom('user as u')
  .innerJoin('order as o', (join) =>
    join
      .onRef('u.id', '=', 'o.user_id')
      .on('o.status', '=', 'paid')
  )
  .select([ 'u.id', 'u.name', 'o.status', 'o.amount'])
  .where('o.status', '=', 'paid')
  .execute();

// [{ id: 1, name: 'Иван', status: 'paid', amount: 546 }]
console.log(result);

Данный запрос аналогичен запросу, который написан ранее, но отличие в том, что условие, что платежи должны иметь статус paid было перенесено из where внутрь условия join on. onRef здесь является аналогом whereRef, а именно чтобы использовать в условии колонки из других таблиц, а on просто является оператором ON.

Бывают ситуации, когда join таблицы могут быть дважды добавлены в запрос, как правило это происходит в запросах с условиями:

async function getUser(
  id: number,
  withOrderAmount: boolean,
  withOrderStatus: boolean
) {
  return await db
    .selectFrom('user')
    .selectAll('user')
    .$if(withOrderAmount, (qb) =>
      qb
        .innerJoin('order as o', 'user.id', 'o.user_id')
        .select('o.amount as orderAmount')
    )
    .$if(withOrderStatus, (qb) =>
      qb
        .innerJoin('order as o', 'user.id', 'o.user_id')
        .select('o.status as orderStatus')
    )
    .where('user.id', '=', id)
    .executeTakeFirst()
}

// ОШИБКА: inner join таблицы order будет дважды добавлен
const result = await getUser(1, true, true);

И тут на помощь приходит встроенный плагин в kysely DeduplicateJoinsPlugin. Подключить его можно глобально (не рекомендую, ибо если в проекте есть сложные sql запросы c подзапросами, то он может некорректно отработать):

import { Kysely, DeduplicateJoinsPlugin } from 'kysely';

const db = new Kysely<Database>({
  dialect,
  plugins: [ new DeduplicateJoinsPlugin() ]
});

или локально для конкретного запроса:

import { DeduplicateJoinsPlugin } from 'kysely';

async function getUserDeduplicateJoin(
  id: number,
  withOrderAmount: boolean,
  withOrderStatus: boolean
) {
  return await db
    // Подключаем плагин
    .withPlugin(new DeduplicateJoinsPlugin())
    .selectFrom('user')
    .selectAll('user')
    .$if(withOrderAmount, (qb) =>
      qb
        .innerJoin('order as o', 'user.id', 'o.user_id')
        .select('o.amount as orderAmount')
    )
    .$if(withOrderStatus, (qb) =>
      qb
        .innerJoin('order as o', 'user.id', 'o.user_id')
        .select('o.status as orderStatus')
    )
    .where('user.id', '=', id)
    .executeTakeFirst()
}

const result = await getUser(1, true, true);
/*{
    id: 1,
    name: 'Иван',
    gender: 'man',
    last_name: 'Иванов',
    created_at: 2023-09-07T07:38:49.000Z,
    orderAmount: 235325,
    orderStatus: 'pending'
}*/
console.log(result);

SubQuery

В примерах выше мы уже сталкивались с подзапросами, здесь покажу несколько примеров, как писать подзапросы для insert/update/join.

Insert с подзапросом:

const result = await db.insertInto('user')
  .columns(['name', 'gender'])
  .expression((eb) => eb
    .selectFrom('order')
    .select((eb) => [
      eb.val('Саша').as('name'),
      eb.case().when('order.status', '=', 'paid').then('man').else('woman').end().as('gender'),
     ]).limit(1)
  )
  .executeTakeFirst();

console.log(result.insertId); // 4 

С подзапросами типизация становится немножко слабее. Заключается это в следующем, если я верну 3 значения из подзапроса, а не 2 как ожидает insert, то TypeScript будет молчать об ошибке. Поэтому на практике я стараюсь не увлекаться с подзапросами, их и читать тяжелее, и типизация может быть не всеобъемлющая.

Update c подзапросом:

const result = await db.updateTable('user')
  .set((eb) =>
    ({
      gender: eb
        .selectFrom('order')
        .select((e) => [
          e.case()
            .when('order.status', '=', 'paid')
            .then('man')
            .else('woman')
            .end().as('gender') 
            as AliasedExpression<'man' | 'woman' | null, 'gender'>
         ]).limit(1)
    })
  )
  .where('user.id', '=', 1)
  .executeTakeFirst();

  console.log(result.numChangedRows); // 1

Для join подход аналогичный с select:

const result = await db.selectFrom('user as u')
  .innerJoin(
    (eb) => eb
      .selectFrom('order as o')
      .select(['user_id as userId', 'status'])
      .where('status', '=', 'paid')
      .as('paid_orders'),
    (join) => join
      .onRef('paid_orders.userId', '=', 'u.id'),
    )
  .selectAll('paid_orders')
  .execute();
  
// [ { userId: 1, status: 'paid' } ]
  console.log(result);

Raw sql и JSON type

Если не хватает гибкости для построения запросов, всегда есть возможность написать «сырой» запрос:

import { sql } from 'kysely';

// Полностью «сырой» запрос
const id = 1;
const result = await sql<User[]>`select * from user where id = ${id}`
  .execute(db);

// Частично «сырой» запрос
const result = await db
  .selectFrom('user')
  .select(sql<string>`concat(name, ' ', last_name)`.as('full_name'))
  .execute();
// [
//   { full_name: 'Иван Иванов' },
//   { full_name: 'Алена Петрова' },
//   { full_name: null },
//   { full_name: null }
// ]
console.log(result);

Современные реляционные БД поддерживают тип колонки json, что бывает очень удобно для работы с данными в стиле NoSQL. В kysely поддержку json типа можно реализовать c помощью хэлпера sql (функции для генерации «сырого» sql кода).

Вначале добавим в нашу схему поле settings для таблицы user:

// schema.ts

import { ColumnType, Generated } from 'kysely';

// ...

export interface UserTable {
  id: Generated<number>;
  name: string;
  gender: 'man' | 'woman' | null;
  last_name: string | null;
  // json column
  settings: {
    theme?: 'dark' | 'light';
    pushNotification: boolean;
  } | null,

  created_at: ColumnType<Date, string | Date | undefined, never>
}

// ...

Затем создадим функцию для сериализации json для конкретной БД:

import { RawBuilder, sql } from 'kysely';

function jsonSerialize<T>(value: T): RawBuilder<T> {
  // MySQL
  return sql`${JSON.stringify(value)}`;
  // PostgreSQL
  // return sql`CAST(${JSON.stringify(value)} AS JSONB)`;
}

Теперь можно писать и читать json колонку:

const result = await db.insertInto('user').values({
  name: 'Олег',
  settings: jsonSerialize({
    theme: 'light',
    pushNotification: false,
  })
}).executeTakeFirst();

// MySQL
const result = await db
  .selectFrom('user')
  .selectAll()
  .where((eb) =>
    eb(sql`settings->"$.theme"`, '=', 'light')
  )
  .execute();
/*[{
    id: 58,
    name: 'Олег',
    gender: null,
    last_name: null,
    settings: { theme: 'light', pushNotification: false },
    created_at: 2023-09-09T17:25:31.000Z
}]*/
console.log(result);

// PostgreSQL
const result = await db
  .selectFrom('user')
  .selectAll()
  .where(
    'settings',
    '@>',
    jsonSerialize({
      theme: 'light',
      pushNotification: false,
    })
  )
  .execute();

Транзакции

Поддержка транзакций имеется, есть возможность выставлять уровень изоляции для нее:

await db.transaction().setIsolationLevel('repeatable read').execute(async (trx) => {
  await trx.insertInto('user')
    .values({
      name: 'Алена',
      last_name: 'Попова',
    })
    .executeTakeFirst();

  const result = await trx
    // когда надо сделать select без from, например, SELECT LAST_INSERT_ID();
    .selectNoFrom(({ fn }) => [fn.agg<number>('LAST_INSERT_ID').as('userId')])
    .executeTakeFirst();
  const userId = result?.userId!;

  return await trx.insertInto('order')
    .values({
      user_id: userId,
      amount: 102,
      status: 'pending',
    })
    .executeTakeFirst()
  });

Единственное, не хватает метода для создания транзакции вне колбэк функции. Например, как в knex const trx = await knex.transaction();, а для завершения транзакции соответственно вручную вызывались бы await trx.commit();или await trx.rollback();. Но в целом для этого можно самостоятельно написать функцию-обертку:

import { Kysely, Transaction } from 'kysely';
import { Database } from './schema';

async function begin(
  db: Kysely<Database>, 
  isolationLevel?: 'read uncommitted' | 'read committed' | 'repeatable read' | 'serializable'
) {
  return new Promise<{ 
    trx: Transaction<Database>,
    commit(): void;
    rollback(): void
  }>((resolve) => {
    let trxBuilder = db.transaction();
    if (isolationLevel) {
      trxBuilder = trxBuilder.setIsolationLevel(isolationLevel);
    }
    
    // Не вызываем await
    trxBuilder.execute((trx) => {
      const p = new Promise<void>((commit, rollback) => {
        resolve({
          trx,
          commit: () => commit(),
          rollback: () => { rollback(new Error('Rollback')); },
        });
      });
      return p;
    }).catch(err => {
      // Ничего не делаем просто проглатываем ошибку от вызова rollback 
    });
  });
}

Пример такой функции-обертки от автора библиотеки.

Теперь использовать транзакции можно вне колбэк функции:

const { trx, commit, rollback } = await begin(db);
  try {
    await trx.insertInto('user')
      .values({
        name: 'Аглая',
        last_name: 'Ермакова',
      })
      .executeTakeFirst();

    const result = await trx
      .selectNoFrom(({ fn }) => [
        fn.agg<number>('LAST_INSERT_ID').as('userId')
      ]).executeTakeFirst();
    const userId = result?.userId!;

    await trx.insertInto('order')
      .values({
        user_id: userId,
        amount: 589,
        status: 'pending',
      })
      .executeTakeFirst();

    commit();
  } catch (err) {
    console.log(err);
    rollback();
  }

Миграции

Kysely не имеет cli инструмента для работы с миграциями. Это сделано намеренно, они не хотели завязываться на typescript компилятор, так как они позиционируют себя как query builder, и при этом хотели дать возможность пользователю самостоятельно решать в каком виде они будут запускать миграции, как .js файлы или как .ts. Но в документации есть простой пример, как написать скрипт для работы с миграциями.

Для начала напишем cli скрипт для применения/отката миграций:

// migration-cli.ts

// Модуль для работы с аргументами командной строки
import { program, InvalidArgumentError } from 'commander';

import * as path from 'node:path';
import fs from 'node:fs/promises';

import db from './db';
import { Migrator, FileMigrationProvider } from 'kysely';


const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    // Абсолютный путь к папке с миграциями
    migrationFolder: path.join(__dirname, 'migrations'),
  }),
});

// Функция для примения всех миграций
async function migrateToLatest() {
  const { error, results } = await migrator.migrateToLatest();

  results?.forEach((it) => {
    if (it.status === 'Success') {
      console.log(`migration "${it.migrationName}" was executed successfully`);
    } else if (it.status === 'Error') {
      console.error(`failed to execute migration "${it.migrationName}"`);
    }
  });

  if (error) {
    console.error('failed to migrate');
    console.error(error);
    process.exit(1);
  }

  await db.destroy();
}

// Функция для отката одной миграции
async function migrateDown() {
  const { error, results } = await migrator.migrateDown();

  results?.forEach((it) => {
    if (it.status === 'Success') {
      console.log(`migration "${it.migrationName}" was rollbacked successfully`);
    } else if (it.status === 'Error') {
      console.error(`failed to execute migration "${it.migrationName}"`);
    }
  });

  if (error) {
    console.error('failed to migrate');
    console.error(error);
    process.exit(1)
  }
}

const getNumber = (value) => {
  const parsedValue = parseInt(value, 10);
  if (isNaN(parsedValue)) { throw new InvalidArgumentError('Not a number'); }
  return parsedValue;
}

program
  .description('CLI for apply/rollback migration schema of database')
  .option('-d, --down <number>', 'transaction rollback', getNumber)
  .option('-up-all, --up-all', 'all transaction apply')
  ;
program.parse();

const opts = program.opts();

void async function () {
  // применить все 
  if (opts.upAll) {
    await migrateToLatest();
  // откатить n-ое кол-во миграций
  } else if (opts.down) {
    for await (const _ of new Array(opts.down)) {
      await migrateDown();
    }
    await db.destroy();
  }
}();

Затем создаем миграцию для таблицы user:

// migrations/20230909214556_user.ts

import { Kysely, sql } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('user')
    .addColumn('id', 'int unsigned' as any, (col) => 
      col.primaryKey().autoIncrement()
    )
    .addColumn('name', 'varchar(255)', (col) => col.notNull())
    .addColumn('gender', 'enum("man", "woman")' as any)
    .addColumn('last_name', 'varchar(255)')
    .addColumn('settings', 'json')
    .addColumn('created_at', 'datetime', (col) =>
      col.defaultTo(
        sql`CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP`).notNull()
    )
    .execute()

  await db.schema
    .createIndex('i_name')
    .on('user')
    .column('name')
    .execute()
}

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

Единственная проблема, внутри миграций типизация колонок MySQL сделана слабо (не все типы представлены), приходится писать местами any. Но в PostgreSQL и SQLite с этим проблем меньше.

Запускаем наш скрипт:

# Применить все миграции
Mitya:kysely dmitrijd$ npx tsx src/migration-cli.ts --up-all
migration "20230909214556_user" was executed successfully

# Откатить последнюю миграцию
Mitya:kysely dmitrijd$ npx tsx src/migration-cli.ts --down=1
migration "20230909214556_user" was rollbacked successfully

Схема

Бывают случаи, когда в запросах необходимо использовать разные базы данных (MySQL) или схемы (PostgreSQL). Это может быть связано с тем, что у вас несколько приложений, и каждое должно иметь свой namespace в БД, или у вас, к примеру, шардирование, и у каждого клиента свой namespace в БД. К счастью, в kysely об этом уже подумали.

Допустим у нас пользователи лежат в базе данных/схеме admin, а заказы соответственно в shop. Тогда нам необходимо добавить префиксы для названий таблиц в schema.ts и использовать эти префиксы в самих запросах:

export interface Database {
  'admin.user': UserTable;
  'shop.order': OrderTable;
}

const user = await db
  .selectFrom('admin.user')
  .select(['id', 'name', 'last_name'])
  .executeTakeFirst();
// { id: 1, name: 'Григорий', last_name: 'Гладков' }
console.log(user);

const orders = await db
  .selectFrom('shop.order')
  .select(['id', 'status', 'amount'])
  .where('id', '=', user?.id!)
  .execute();
// [{ id: 1, status: 'pending', amount: 235325 }]
console.log(orders);

Другой вариант: наши пользователи и заказы живут в одной базе данных/схеме, а у каждого магазина персональная база/схема. Тогда мы можем просто указывать необходимый префикс в самом запросе с помощью метода withSchema:

const result = await db
  .withSchema('shard1')
  .selectFrom('user')
  .innerJoin('order', 'user.id', 'order.user_id')
  .select([
    'user.id', 'user.name', 'user.last_name', 'order.status', 'order.amount'
  ])
  .where('order.status', '=', 'paid')
  .execute();
/*[
    {
      id: 1,
      name: 'Никита',
      last_name: 'Блинский',
      status: 'paid',
      amount: 1546
    }
]*/
console.log(result);

const result2 = await db
  .withSchema('shard2')
  .selectFrom('user')
  .innerJoin('order', 'user.id', 'order.user_id')
  .select([
    'user.id', 'user.name', 'user.last_name', 'order.status', 'order.amount'
  ])
  .where('order.status', '=', 'paid')
  .execute();
/*[
    {
      id: 1,
      name: 'Маша',
      last_name: 'Егорова',
      status: 'paid',
      amount: 546
    }
]*/
console.log(result2);

Подробнее об указании схемы/базы данных для запросов

Итог

Kysely интересный инструмент для работы с sql запросами в JavaScript.

Наряду с чистыми sql запросами я давно использую sql билдеры для работы с БД. Долгое время пользовался Knex.js (совсем давно использовал Squel.js, но он устарел и больше не поддерживается), но из-за того, что Knex изначально написан на js, поддержка TypeScript у него далеко не самая лучшая.

Лично для себя я открыл Kysely где-то года 2 назад и вот уже год он в продакшене, и каких-то глобальных проблем не создал, а удобства и надежности добавил.