@geekmidas/db

Database utilities for Kysely with flexible transaction management and Row Level Security (RLS) support.

Declare the database, don't wire it

@geekmidas/db is the toolkit — transactions, RLS helpers, migration utilities. The connection comes from a declared construct:

import { KyselyDatabase } from '@geekmidas/constructs/database/kysely';

export const database = new KyselyDatabase<Database, 'Orders'>('Orders');

One statement gives you the container locally, the RDS instance deployed, the owner/runtime role split, the schema, and a typed client in every handler that names it. See Getting Started.

Installation

pnpm add @geekmidas/db

Features

• Transaction helper that works with any DatabaseConnection type
• Handles Kysely, Transaction, and ControlledTransaction seamlessly
• Automatic transaction detection and reuse
• Type-safe database operations
• Row Level Security (RLS) context management for PostgreSQL
• Introspection: schemas, tables and rows as functions and a read-only JSON API

Package Exports

Export	Description
/kysely	Transaction utilities (withTransaction), DatabaseConnection type, RLS helpers (withRlsContext)
/kysely/pagination	Cursor-based pagination for Kysely (paginatedSearch, encodeCursor, decodeCursor)
/objection/pagination	Cursor-based pagination for Objection.js models (paginatedSearch, encodeCursor, decodeCursor)
/pagination	Shared pagination types and utilities (Direction, PaginationResult, encodeCursor, decodeCursor)
/rls	Row Level Security types and utilities
/introspect	Headless database introspection and a read-only JSON API (createIntrospectionHandler, DataBrowser, introspectSchema)

Basic Usage

Transaction Helper

import { withTransaction } from '@geekmidas/db/kysely';
import type { DatabaseConnection } from '@geekmidas/db/kysely';

interface Database {
  users: UsersTable;
  audit_log: AuditLogTable;
}

async function createUser(
  db: DatabaseConnection<Database>,
  data: UserData
) {
  return withTransaction(db, async (trx) => {
    // All operations in this callback are transactional
    const user = await trx
      .insertInto('users')
      .values(data)
      .returningAll()
      .executeTakeFirstOrThrow();

    await trx
      .insertInto('audit_log')
      .values({
        userId: user.id,
        action: 'created',
        timestamp: new Date(),
      })
      .execute();

    return user;
  });
}

DatabaseConnection Type

The DatabaseConnection type accepts any of:

• Kysely<T> - Standard Kysely instance
• Transaction<T> - Kysely transaction
• ControlledTransaction<T> - Controlled transaction

import type { DatabaseConnection } from '@geekmidas/db/kysely';
import { Kysely, Transaction } from 'kysely';

// Works with Kysely instance
const db: Kysely<Database> = createDb();
await createUser(db, userData);

// Also works with existing transaction
await db.transaction().execute(async (trx) => {
  // trx is reused, not nested
  await createUser(trx, userData);
  await createOrder(trx, orderData);
});

Transaction Reuse

withTransaction automatically detects if already in a transaction:

async function createUserWithProfile(db: DatabaseConnection<Database>, data: UserData) {
  return withTransaction(db, async (trx) => {
    const user = await createUser(trx, data);  // Reuses same transaction
    const profile = await createProfile(trx, { userId: user.id });
    return { user, profile };
  });
}

Isolation Levels

import { withTransaction } from '@geekmidas/db/kysely';

await withTransaction(db, async (trx) => {
  // Your transactional operations
}, {
  isolationLevel: 'serializable',
});

Usage with Services

The hand-written form, for reference — the KyselyDatabase construct is one line and gives you the container and the roles as well.

import type { Service } from '@geekmidas/services';
import { Kysely, PostgresDialect } from 'kysely';

const databaseService = {
  serviceName: 'database' as const,
  async register(envParser) {
    const config = envParser.create((get) => ({
      connectionString: get('DATABASE_URL').string(),
    })).parse();

    return new Kysely<Database>({
      dialect: new PostgresDialect({
        pool: new Pool({ connectionString: config.connectionString }),
      }),
    });
  }
} satisfies Service<'database', Kysely<Database>>;

Cursor-Based Pagination

Kysely

The /kysely/pagination export provides cursor-based pagination for Kysely queries:

import { paginatedSearch, Direction } from '@geekmidas/db/kysely/pagination';

const result = await paginatedSearch({
  query: db.selectFrom('users').selectAll(),
  cursor: requestCursor, // from previous page, or undefined for first page
  limit: 20,
  cursorField: 'createdAt',
  cursorDirection: Direction.Desc,
});

// result: {
//   items: User[],
//   pagination: {
//     total: number,
//     hasMore: boolean,
//     cursor?: string  // pass to next request
//   }
// }

mapRow is optional. When omitted, items are returned as raw row objects:

const result = await paginatedSearch({
  query: db.selectFrom('users').selectAll(),
  limit: 10,
  mapRow: (row) => ({ ...row, displayName: `${row.firstName} ${row.lastName}` }),
});

Objection.js

The /objection/pagination export provides the same cursor-based pagination for Objection.js models:

import { paginatedSearch, Direction } from '@geekmidas/db/objection/pagination';

const result = await paginatedSearch({
  query: User.query(trx).where('orgId', orgId).withGraphFetched('roles'),
  cursor: requestCursor,
  limit: 20,
  cursorField: 'createdAt',
  cursorDirection: Direction.Desc,
});

// result.items are User model instances with roles loaded

mapRow is optional. When omitted, items are returned as Objection model instances:

const result = await paginatedSearch({
  query: User.query(trx).where('active', true),
  limit: 10,
  mapRow: (user) => ({ id: user.id, displayName: user.fullName }),
});

Shared Utilities

Both Kysely and Objection pagination re-export encodeCursor and decodeCursor from /pagination for manual cursor handling:

import { encodeCursor, decodeCursor } from '@geekmidas/db/pagination';

const encoded = encodeCursor(new Date('2024-01-15'));  // base64url string
const decoded = decodeCursor(encoded);                  // Date object

Database Introspection

@geekmidas/db/introspect reads what a Postgres database holds — schemas, tables, columns, primary and foreign keys — and pages through its rows, read-only. It has no UI: the same data is available as functions and as a JSON API, for whatever tool you build on it. gkm dev mounts the JSON API at /__gkm/db for the declared database.

Functions

import {
  DataBrowser,
  Direction,
  FilterOperator,
  introspectSchema,
  introspectTable,
  listSchemas,
} from '@geekmidas/db/introspect';

await listSchemas(db); // ['public', 'auth', ...]

const { tables } = await introspectSchema(db, {
  schemas: ['public'],
  excludeTables: ['kysely_migration'],
});

const users = await introspectTable(db, 'users', 'public');
// { name, schema, primaryKey: ['id'], columns: [{ name, type, rawType,
//   nullable, isPrimaryKey, isForeignKey, foreignKeyTable, ... }] }

const browser = new DataBrowser({ db });
const page = await browser.query({
  table: 'users',
  pageSize: 20,
  filters: [{ column: 'email', operator: FilterOperator.Ilike, value: '%@example.com' }],
  sort: [{ column: 'created_at', direction: Direction.Desc }],
});
const next = await browser.query({ table: 'users', cursor: page.nextCursor });

A table pages by its single-column primary key unless you configure a cursor (cursor, or tableCursors per table). Mistakes are named errors: TableNotFound, ColumnNotFound, UnsupportedFilterOperator, InvalidCursor.

JSON API

createIntrospectionHandler returns a plain fetch-style handler — (request: Request) => Promise<Response> — so it mounts in Hono, Bun, Deno or anything else that speaks web Requests, and @geekmidas/db depends on no HTTP framework.

import { createIntrospectionHandler } from '@geekmidas/db/introspect';

const handler = createIntrospectionHandler({ db, basePath: '/__gkm/db' });
app.all('/__gkm/db/*', (c) => handler(c.req.raw));

Route	Returns
GET /schemas	{ schemas, browsable }
GET /tables?schema=&refresh=true	{ tables, updatedAt }, each table with its columns and keys
GET /tables/:name?schema=	One table
GET /tables/:name/rows	{ rows, hasMore, nextCursor, prevCursor }

Rows take pageSize (at most 100), cursor, sort=col:asc,col2:desc and filters as filter[column][operator]=value — operators eq, neq, gt, gte, lt, lte, like, ilike, in, nin (comma-separated), is_null, is_not_null. A caller's mistake is a 4xx whose error is the error class name; only GET is served.

Development only

The handler returns every row of every browsable table to whoever can reach it. gkm dev mounts it; gkm build never does. Mount it yourself only behind something that decides who that is.

Row Level Security (RLS)

PostgreSQL Row Level Security allows you to restrict which rows users can access based on session variables. The withRlsContext helper sets these variables within a transaction.

Basic RLS Usage

import { withRlsContext } from '@geekmidas/db/kysely';

// Execute queries with RLS context
const orders = await withRlsContext(
  db,
  { user_id: session.userId, tenant_id: session.tenantId },
  async (trx) => {
    // PostgreSQL policies can now use:
    // current_setting('app.user_id')
    // current_setting('app.tenant_id')
    return trx.selectFrom('orders').selectAll().execute();
  }
);

How It Works

1. Transaction Scope: Variables are set using SET LOCAL which scopes them to the current transaction
2. Automatic Cleanup: Variables are automatically cleared when the transaction ends (commit or rollback)
3. Custom Prefix: Default prefix is app, configurable via options

PostgreSQL Policy Example

-- Create RLS policy that uses session variables
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id', true));

CREATE POLICY user_access ON orders
  USING (user_id = current_setting('app.user_id', true));

-- Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Custom Prefix

await withRlsContext(
  db,
  { user_id: 'user-123' },
  async (trx) => {
    // Uses 'rls.user_id' instead of 'app.user_id'
    return trx.selectFrom('orders').selectAll().execute();
  },
  { prefix: 'rls' }
);

Isolation Levels

await withRlsContext(
  db,
  { user_id: session.userId },
  async (trx) => {
    // Your queries here
  },
  { settings: { isolationLevel: 'serializable' } }
);

Nested RLS Contexts

When nesting withRlsContext calls, the same transaction is reused and all variables accumulate:

await withRlsContext(
  db,
  { tenant_id: 'tenant-1' },
  async (outerTrx) => {
    // Both tenant_id and user_id are available here
    return withRlsContext(
      outerTrx,
      { user_id: 'user-123' },
      async (innerTrx) => {
        return innerTrx.selectFrom('orders').selectAll().execute();
      }
    );
  }
);

Value Types

The RLS context accepts various value types:

const context: RlsContext = {
  user_id: 'user-123',      // string
  count: 42,                 // number (converted to string)
  is_admin: true,            // boolean (converted to 'true'/'false')
  optional: null,            // null/undefined values are skipped
};

RLS Bypass

For admin operations that need to bypass RLS:

import { RLS_BYPASS } from '@geekmidas/db/kysely';

// Use RLS_BYPASS symbol to skip RLS context
const allOrders = await db.selectFrom('orders').selectAll().execute();

RLS with Endpoints

When using @geekmidas/constructs, RLS integrates seamlessly with endpoints. The key is to use the db parameter from the handler context, which is a transaction with RLS variables set—not services.database which is the raw connection.

import { api } from '../constructs/api';

const router = api
  .database(database)  // the declared KyselyDatabase — `db` in every handler
  .authorizer('jwt')
  .rls({
    extractor: ({ session }) => ({
      user_id: session.sub,
      tenant_id: session.tenantId,
    }),
    prefix: 'app',
  });

// All endpoints inherit RLS configuration
const listOrders = router
  .get('/orders')
  .handle(async ({ db }) => {
    // db is a transaction with RLS context applied
    // PostgreSQL policies using current_setting('app.user_id') work here
    return db
      .selectFrom('orders')
      .selectAll()
      .execute();
  });

// Bypass RLS for specific endpoints
const adminListOrders = router
  .get('/admin/orders')
  .rls(false)  // Disable RLS for this endpoint
  .handle(async ({ db }) => {
    // db is now the raw connection without RLS context
    return db
      .selectFrom('orders')
      .selectAll()
      .execute();
  });

Important

Always use db from the handler context when RLS is configured. Using services.database directly bypasses the RLS transaction and PostgreSQL session variables won't be set.

// ❌ Wrong - bypasses RLS
.handle(async ({ services }) => {
  return services.database.selectFrom('orders').execute();
});

// ✅ Correct - uses RLS transaction
.handle(async ({ db }) => {
  return db.selectFrom('orders').execute();
});

Per-Endpoint RLS

import { api } from '../constructs/api';

const endpoint = api
  .get('/orders')
  .database(database)
  .rls({
    extractor: ({ session, header }) => ({
      user_id: session.userId,
      ip_address: header('x-forwarded-for'),
    }),
  })
  .handle(async ({ db }) => {
    // db has RLS context with user_id and ip_address set
    return db
      .selectFrom('orders')
      .selectAll()
      .execute();
  });

Combining RLS with Services

When you need both the RLS-protected db and other services:

const endpoint = api
  .get('/orders')
  .handle(async ({ db, services, logger }) => {
    // Use db for RLS-protected queries
    const orders = await db
      .selectFrom('orders')
      .selectAll()
      .execute();

    // Use other services as needed
    logger.info({ count: orders.length }, 'Fetched orders');

    return orders;
  });