Kysely Adapter

The Kysely adapter provides type-safe SQL query building with support for multiple databases including PostgreSQL, MySQL, and SQLite.

Installation

First, install the Kysely package and the appropriate database driver:

# For PostgreSQL
npm install kysely pg @types/pg
# For MySQL
npm install kysely mysql2 @types/mysql2
# For SQLite
npm install kysely better-sqlite3 @types/better-sqlite3

Configuration

Configure the Kysely adapter with your database connection:

import { c15tInstance } from '@c15t/backend';
import { kyselyAdapter } from '@c15t/backend/db/adapters/kysely';
 
// PostgreSQL configuration
const instance = c15tInstance({
  baseURL: 'http://localhost:3000',
  database: kyselyAdapter({
    dialect: 'postgres',
    connection: {
      host: 'localhost',
      port: 5432,
      database: 'c15t',
      user: 'postgres',
      password: 'password',
    },
    // Optional: Enable query logging
    debug: process.env.NODE_ENV !== 'production',
    // Optional: Connection pooling settings
    pool: {
      min: 2,
      max: 10
    }
  }),
});

MySQL Configuration

const instance = c15tInstance({
  database: kyselyAdapter({
    dialect: 'mysql',
    connection: {
      host: 'localhost',
      port: 3306,
      database: 'c15t',
      user: 'root',
      password: 'password',
    }
  }),
});

SQLite Configuration

const instance = c15tInstance({
  database: kyselyAdapter({
    dialect: 'sqlite',
    connection: {
      filename: ':memory:' // or path to file like './database.db'
    }
  }),
});

Usage Examples

Basic CRUD Operations

// Create a new record
const user = await instance.database.create('users', {
  name: 'John Doe',
  email: 'john@example.com'
});
 
// Find records with complex conditions
const users = await instance.database.find('users', {
  where: { 
    email: { $like: '%@example.com' },
    createdAt: { $gt: new Date('2023-01-01') }
  },
  orderBy: { createdAt: 'desc' },
  limit: 10
});
 
// Update records
const updatedUser = await instance.database.update(
  'users',
  { where: { id: user.id } },
  { name: 'John Smith' }
);
 
// Delete records
await instance.database.delete('users', { where: { id: user.id } });

Transactions

await instance.database.transaction(async (trx) => {
  // All operations inside this function use the same transaction
  const user = await trx.create('users', { name: 'Alice' });
  await trx.create('profiles', { userId: user.id });
  
  // Transaction automatically commits unless an error is thrown
  // If an error is thrown, the transaction is rolled back
});

Schema Management

import { sql } from 'kysely';
 
// Create tables programmatically
await instance.database.raw(sql`
  CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
  )
`);

Best Practices

Use prepared statements - Kysely automatically uses prepared statements to prevent SQL injection
Configure connection pooling - Set appropriate pool sizes based on your application needs
Implement retry logic - Add retries for transient database errors
Monitor query performance - Use the debug option to log slow queries during development

Limitations

Schema must be created separately or with raw SQL
Complex joins require custom SQL or advanced Kysely usage

Kysely Adapter

On this page