Connecting

tsql keeps query building and query execution separate.

You provide:

• schema: output of defineDatabase(...)
• dialect: SQL placeholder and identifier behavior
• connection: your adapter with execute(sql, params) and optional transaction primitives

import { connect, sqliteDialect, type SqlConnection } from "@bms/tsql";
import { schema } from "./schema";

const connection: SqlConnection = {
	execute: async (sql, params) => {
		// Call your runtime driver here.
		return { rows: [] };
	},
};

const db = connect({
	schema,
	dialect: sqliteDialect(),
	connection,
});

SqlConnection

type SqlConnection = {
	execute(
		sql: string,
		params: readonly unknown[],
	): Promise<{
		rows: readonly unknown[];
	}>;

	executeTransaction?(
		statements: readonly CompiledQuery[],
	): Promise<readonly SqlExecutionResult[]>;

	beginTransaction?(): Promise<SqlTransactionConnection>;
};

type SqlTransactionConnection = SqlConnection & {
	commit(): Promise<void>;
	rollback(): Promise<void>;
};

execute always receives compiled SQL and bound parameters.

beginTransaction is for drivers with interactive transaction connections. If omitted, db.begin() falls back to SQL begin, commit, and rollback on the base connection.

executeTransaction is for drivers with batch-level transaction APIs, for example Cloudflare D1. db.migrate() uses it when available.

Cloudflare D1

Use @bms/tsql/d1 for Workers binding-style D1 connections:

import { connect, sqliteDialect } from "@bms/tsql";
import { d1Connection } from "@bms/tsql/d1";

export const db = connect({
	schema,
	dialect: sqliteDialect(),
	connection: d1Connection(env.DB),
});

The adapter maps executeTransaction to D1Database.batch(), so migrations use D1's API-level transaction primitive instead of SQL begin / commit.

Effect integration

Use @bms/tsql/effect when running queries inside Effect programs. It is a separate export so applications use their own effect package version.

import { Effect } from "effect";
import { connect } from "@bms/tsql/effect";

const db = connect({ schema, dialect: sqliteDialect(), connection });

const users = await Effect.runPromise(
	db.run(db.from("users").select(({ users }) => ({ id: users.id }))),
);

See Effect for Effect-native connections and transaction helpers.

Database object

connect(...) returns Database<TSchema>. Main methods:

• from(...) for select
• insert(...), update(...), delete(...)
• raw and expr template helpers
• aggregate helpers: count, max, min, avg, sum
• transaction methods: begin, and on transactions commit / rollback
• migration runner: migrate