---
title: Effect
description: Use tsql from Effect programs with typed query execution and safe transactions.
---

# Effect

`@bms/tsql/effect` is an optional Effect adapter. It imports `effect` as a peer dependency, so applications use their installed Effect version.

Install both packages in the application:

```bash
pnpm add @bms/tsql effect
```

## Promise connections

Use `connect` from the Effect subpath with the same `SqlConnection` shape as core `tsql`.

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

const connection: SqlConnection = {
	execute: async (sql, params) => {
		return { rows: [] };
	},
};

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

const program = db.run(
	db.from("users").select(({ users }) => ({
		id: users.id,
		email: users.email,
	})),
);

const users = await Effect.runPromise(program);
```

`db.run(query)` returns `Effect.Effect<TResult, TSqlError>`. It reuses the query object's compile and result-mapping logic, including dialect value decoding.

## Effect-native connections

If your driver is already wrapped in Effect, use `connectFromEffect`.

```ts
import { Effect } from "effect";
import { sqliteDialect } from "@bms/tsql";
import { connectFromEffect, type EffectSqlConnection } from "@bms/tsql/effect";
import { schema } from "./schema";

type DriverError = { readonly _tag: "DriverError"; readonly cause: unknown };

const connection: EffectSqlConnection<DriverError> = {
	execute: (sql, params) =>
		Effect.tryPromise({
			try: () => driver.execute(sql, params),
			catch: (cause): DriverError => ({ _tag: "DriverError", cause }),
		}),
};

const db = yield* connectFromEffect({
	schema,
	dialect: sqliteDialect(),
	connection,
});
```

`db.run(...)` keeps the driver error inside `TSqlError.reason`.

## Transactions

Use `db.transaction` to let Effect handle commit and rollback.

```ts
const createUser = db.transaction((tx) =>
	tx.run(
		tx.insert("users").values({
			email: "alice@example.com",
			name: "Alice",
			createdAt: new Date(),
		}),
	),
);
```

If the callback succeeds, `transaction` commits. If the callback fails, defects, or is interrupted, it rolls back. The transaction callback receives `EffectTransaction`, which omits manual `commit`, `rollback`, `begin`, and `migrate` from its public type.

## Migrations

Use `migrateEffect` in Effect bootstraps.

```ts
await Effect.runPromise(db.migrateEffect());
```

## Standalone helpers

You can also wrap a core `Database` without using the Effect-aware `connect`.

```ts
import { execute, migrate, transaction } from "@bms/tsql/effect";

const rows = yield* execute(db.from("users").select(({ users }) => ({ id: users.id })));
yield* migrate(db);
yield* transaction(db, (tx) => tx.run(tx.delete("users").where(({ users }) => users.id.eq(1, db.dialect))));
```

## Errors

Failures use `TSqlError`.

```ts
class TSqlError<TReason = unknown> extends Error {
	readonly _tag: "TSqlError";
	readonly operation: "execute" | "begin" | "commit" | "rollback" | "migrate";
	readonly query: CompiledQuery | undefined;
	readonly reason: TReason;
}
```

For Effect-native connections, `reason` includes the driver error type plus possible `Error` values from query compilation or row mapping.

<!--
Sitemap

URL: https://tsql-docs.pages.dev/v0.7.0/guides/quickstart.md
Title: Quickstart
Description: Define migrations, run them safely, and write typed SQL in application code.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/connecting.md
Title: Connecting
Description: Create a database instance from schema, dialect, and runtime connection.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/dialect-and-compilation.md
Title: Dialect & SQL Compilation
Description: Configure SQL dialect behavior and compile schema/query SQL.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/effect.md
Title: Effect
Description: Use tsql from Effect programs with typed query execution and safe transactions.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/grouping-and-aggregates.md
Title: Grouping & Aggregates
Description: Use groupBy, having, and aggregate helpers for analytical queries.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/joins-and-aliases.md
Title: Joins & Aliases
Description: Join tables, use left joins, and self-join safely with aliases.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/migration-runtime.md
Title: Migration Runtime
Description: Understand db.migrate behavior, tracking, and failure semantics.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/mutations.md
Title: Mutations
Description: Insert, update, and delete data with compile or execute.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/predicates-and-expressions.md
Title: Predicates & Expressions
Description: Use Expr methods, logical operators, null checks, and expression composition.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/raw-sql.md
Title: Raw SQL
Description: Use raw templates and expression templates when fluent APIs are not enough.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/schema-and-migrations.md
Title: Schema & Migrations
Description: Define tables, columns, and indexes with ordered migration history.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/select-basics.md
Title: Select Basics
Description: Build typed select queries with from, where, order, limit, and select.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/transactions.md
Title: Transactions
Description: Use explicit begin, commit, rollback, and await using for safety.

URL: https://tsql-docs.pages.dev/v0.7.0/reference/type-utilities.md
Title: Type Utilities
Description: Derive row, insert, and update types directly from schema.
-->