@withstudiocms/kysely

v0.2.1 GitHub npm Changelog

Kysely^ is a type-safe SQL query builder for TypeScript. It provides a powerful and flexible way to interact with databases while ensuring type safety and reducing runtime errors. Kysely supports various SQL databases, including PostgreSQL, MySQL, SQLite, and more. It allows developers to construct SQL queries using a fluent API, making it easier to read and maintain database interactions in TypeScript applications.

Currently StudioCMS only supports libSQL(SQLite), MySQL, and PostgreSQL databases via Kysely. In the future, support for other database dialects can be added as needed or requested.

Want to have another database dialect supported? Check out the Kysely Dialects documentation^ and open an issue on the StudioCMS GitHub repository^.

The Kysely Client package

A type-safe database client and migration system for StudioCMS, built on top of Kysely^. Provides a unified interface for working with libSQL, MySQL, and PostgreSQL databases with runtime schema management and migrations.

Features

Type-Safe Database Operations - Full TypeScript support with Kysely’s type-safe query builder
Multi-Database Support - Works with libSQL (SQLite), MySQL, and PostgreSQL
Runtime Schema Management - Dynamic schema creation and validation
Error Handling - Custom error types for better debugging
TypeScript-Based Migrations - File-based migrations with automatic tracking
Schema Introspection - Inspect and validate database schemas at runtime
Effect-ts Integration - Functional programming patterns with Effect-ts

Code Example

Basic client setup

import { getDBClientLive } from '@withstudiocms/kysely';
import type { StudioCMSDatabaseSchema } from '@withstudiocms/sdk/tables';
import { libsqlDriver } from '@withstudiocms/kysely/drivers/libsql';
import { ConfigProvider, Effect } from 'studiocms/effect';

export const getDbClient = Effect.gen(function* () {
  // Setup the LibSQL driver with a database URL from config
  const dialect = yield* libsqlDriver.pipe(
    Effect.withConfigProvider(
      ConfigProvider.fromJson({
        CMS_LIBSQL_URL: 'file:./test.db',
      })
    )
  );

  // Return the Kysely DB client with Effect helpers
  return yield* getDBClientLive<StudioCMSDatabaseSchema>(dialect);
});

Get users example

import { getDBClientLive } from '@withstudiocms/kysely';
import type { StudioCMSDatabaseSchema } from '@withstudiocms/sdk/tables';
import { libsqlDriver } from '@withstudiocms/kysely/drivers/libsql';
import { ConfigProvider, Effect } from 'studiocms/effect';

export const getDbClient = Effect.gen(function* () {
  // Setup the LibSQL driver with a database URL from config
  const dialect = yield* libsqlDriver.pipe(
    Effect.withConfigProvider(
      ConfigProvider.fromJson({
        CMS_LIBSQL_URL: 'file:./test.db',
      })
    )
  );

  // Return the Kysely DB client with Effect helpers
  return yield* getDBClientLive<StudioCMSDatabaseSchema>(dialect);
});
// ---cut---
import { Schema } from 'studiocms/effect';
import { StudioCMSUsersTable } from '@withstudiocms/sdk/tables';

export const getUsers = Effect.gen(function* () {
    const { withDecoder } = yield* getDbClient;

  const getUsers = withDecoder({
    decoder: Schema.Array(StudioCMSUsersTable.Select),
    callbackFn: (db) =>
      db((client) =>
        client.selectFrom('StudioCMSUsersTable')
        .selectAll()
        .execute()
      ),
  });

  const users = yield* getUsers();
//        ^?

  console.log('Users:', users);
});

Insert new user example (`withEncoder`)

import { getDBClientLive } from '@withstudiocms/kysely';
import type { StudioCMSDatabaseSchema } from '@withstudiocms/sdk/tables';
import { libsqlDriver } from '@withstudiocms/kysely/drivers/libsql';
import { ConfigProvider, Effect } from 'studiocms/effect';

export const getDbClient = Effect.gen(function* () {
  // Setup the LibSQL driver with a database URL from config
  const dialect = yield* libsqlDriver.pipe(
    Effect.withConfigProvider(
      ConfigProvider.fromJson({
        CMS_LIBSQL_URL: 'file:./test.db',
      })
    )
  );

  // Return the Kysely DB client with Effect helpers
  return yield* getDBClientLive<StudioCMSDatabaseSchema>(dialect);
});
// ---cut---
import { Schema } from 'studiocms/effect';
import { StudioCMSUsersTable } from '@withstudiocms/sdk/tables';

export const insertUser = Effect.gen(function* () {
    const { withEncoder } = yield* getDbClient;

  const insertUser = withEncoder({
    encoder: StudioCMSUsersTable.Insert,
    callbackFn: (db, newUser) =>
      db((client) =>
        client.insertInto('StudioCMSUsersTable')
        .values(newUser)
        .executeTakeFirst()
      ),
  });

  const data = {
    username: 'new_user',
    email: '[email protected]',
    password: null,
    avatar: null,
    emailVerified: false,
    name: 'user',
    notifications: '',
    url: null,
    id: crypto.randomUUID(),
    updatedAt: new Date().toISOString(),
    createdAt: new Date().toISOString(),
  };

  const newUser = yield* insertUser(data);
//        ^?
  console.log('Inserted new user:', newUser);
});

Insert new user example (`withCodec`)

import { getDBClientLive } from '@withstudiocms/kysely';
import type { StudioCMSDatabaseSchema } from '@withstudiocms/sdk/tables';
import { libsqlDriver } from '@withstudiocms/kysely/drivers/libsql';
import { ConfigProvider, Effect } from 'studiocms/effect';

export const getDbClient = Effect.gen(function* () {
  // Setup the LibSQL driver with a database URL from config
  const dialect = yield* libsqlDriver.pipe(
    Effect.withConfigProvider(
      ConfigProvider.fromJson({
        CMS_LIBSQL_URL: 'file:./test.db',
      })
    )
  );

  // Return the Kysely DB client with Effect helpers
  return yield* getDBClientLive<StudioCMSDatabaseSchema>(dialect);
});
// ---cut---
import { Schema } from 'studiocms/effect';
import { StudioCMSUsersTable } from '@withstudiocms/sdk/tables';

export const insertUser = Effect.gen(function* () {
    const { withCodec } = yield* getDbClient;

  const insertNewUser = withCodec({
    encoder: StudioCMSUsersTable.Insert,
    decoder: StudioCMSUsersTable.Select,
    callbackFn: (db, newUser) =>
      db((client) =>
        client
          .insertInto('StudioCMSUsersTable')
          .values(newUser)
          .returningAll()
          .executeTakeFirstOrThrow()
      ),
  });

  const data = {
    username: 'codec_user',
    email: '[email protected]',
    password: null,
    avatar: null,
    emailVerified: false,
    name: 'user',
    notifications: '',
    url: null,
    id: crypto.randomUUID(),
    updatedAt: new Date().toISOString(),
    createdAt: new Date().toISOString(),
  };

  const insertedUser = yield* insertNewUser(data);
//        ^?
  console.log('Inserted user with codec:', insertedUser);
});

Get user by ID example

import { getDBClientLive } from '@withstudiocms/kysely';
import type { StudioCMSDatabaseSchema } from '@withstudiocms/sdk/tables';
import { libsqlDriver } from '@withstudiocms/kysely/drivers/libsql';
import { ConfigProvider, Effect } from 'studiocms/effect';

export const getDbClient = Effect.gen(function* () {
  // Setup the LibSQL driver with a database URL from config
  const dialect = yield* libsqlDriver.pipe(
    Effect.withConfigProvider(
      ConfigProvider.fromJson({
        CMS_LIBSQL_URL: 'file:./test.db',
      })
    )
  );

  // Return the Kysely DB client with Effect helpers
  return yield* getDBClientLive<StudioCMSDatabaseSchema>(dialect);
});
// ---cut---
import { Schema } from 'studiocms/effect';
import { StudioCMSUsersTable } from '@withstudiocms/sdk/tables';

export const insertUser = Effect.gen(function* () {
    const { withCodec } = yield* getDbClient;

  const getUserById = withCodec({
    encoder: Schema.String,
    decoder: Schema.UndefinedOr(StudioCMSUsersTable.Select),
    callbackFn: (db, id) =>
      db((client) =>
        client.selectFrom('StudioCMSUsersTable')
        .selectAll()
        .where('id', '=', id)
        .executeTakeFirst()
      ),
  });

  const user = yield* getUserById('some-user-id');
//        ^?
  console.log('User by ID:', user);
});