Skip to main content

​
Overview

@effect/sql-mysql2 provides a MySQL driver for Effect’s SQL toolkit. It uses the mysql2 library and supports connection pooling, transactions, and prepared statements.

​
Installation

npm install @effect/sql-mysql2

​
Basic Usage

​
Creating a Client

Use the MysqlClient.layer function to create a MySQL client layer: 
import { MysqlClient } from "@effect/sql-mysql2"
import { Effect, Layer, Redacted } from "effect"

const SqlLive = MysqlClient.layer({
  host: "localhost",
  port: 3306,
  database: "mydb",
  username: "root",
  password: Redacted.make("password")
})

​
Connection with URL

You can also connect using a connection URL: 
import { Redacted } from "effect"

const SqlLive = MysqlClient.layer({
  url: Redacted.make("mysql://root:password@localhost:3306/mydb")
})

​
Executing Queries

import { Effect } from "effect"
import { SqlClient } from "effect/unstable/sql"

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient

  // Simple query
  const users = yield* sql<{ id: number; name: string }>`
    SELECT id, name FROM users WHERE active = ${true}
  `

  // Insert with helper
  yield* sql`
    INSERT INTO users ${sql.insert({ name: "Alice", email: "[email protected]" })}
  `

  // Update with helper
  yield* sql`
    UPDATE users SET ${sql.update({ name: "Bob" })} WHERE id = ${1}
  `
})

const runnable = program.pipe(Effect.provide(SqlLive))

​
Configuration Options

​
Connection Options

OptionTypeDescription
urlRedactedMySQL connection URL (overrides other connection options)
hoststringDatabase host
portnumberDatabase port (default: 3306)
databasestringDatabase name
usernamestringUsername for authentication
passwordRedactedPassword for authentication

​
Connection Pool Options

OptionTypeDescription
maxConnectionsnumberMaximum number of connections in the pool
connectionTTLDuration.InputTime-to-live for idle connections
poolConfigMysql.PoolOptionsAdditional mysql2 pool configuration

​
Transform Options

OptionTypeDescription
transformResultNames(str: string) => stringTransform column names in results
transformQueryNames(str: string) => stringTransform identifiers in queries

​
Advanced Options

OptionTypeDescription
spanAttributesRecord<string, unknown>Custom telemetry attributes

​
Transactions

Automatic transaction management: 
const sql = yield* SqlClient.SqlClient

const result = yield* sql.withTransaction(
  Effect.gen(function* () {
    yield* sql`INSERT INTO accounts (balance) VALUES (${100})`
    yield* sql`UPDATE accounts SET balance = balance - ${50} WHERE id = ${1}`
    return yield* sql<{ balance: number }>`SELECT balance FROM accounts WHERE id = ${1}`
  })
)
Transactions automatically roll back on errors and commit on success.

​
Name Transformations

Automatically convert between naming conventions: 
import { String } from "effect"

const SqlLive = MysqlClient.layer({
  host: "localhost",
  database: "mydb",
  // Transform camelCase to snake_case for queries
  transformQueryNames: String.camelToSnake,
  // Transform snake_case to camelCase for results
  transformResultNames: String.snakeToCamel
})

// Use camelCase in your code
const users = yield* sql<{ userId: number; firstName: string }>`
  SELECT userId, firstName FROM users
`
// Queries become: SELECT user_id, first_name FROM users

​
Error Handling

All SQL errors are wrapped in SqlError: 
import { SqlError } from "effect/unstable/sql"

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient
  return yield* sql`SELECT * FROM users`
}).pipe(
  Effect.catchTag("SqlError", (error) =>
    Effect.log(`Database error: ${error.message}`)
  )
)

​
Streaming Results

Stream large result sets efficiently: 
import { Stream } from "effect"

const program = Effect.gen(function* () {
  const sql = yield* SqlClient.SqlClient

  const users = sql.stream<{ id: number; name: string }>`
    SELECT id, name FROM users
  `

  yield* Stream.runForEach(users, (user) =>
    Effect.log(`Processing user: ${user.name}`)
  )
})

​
Multiple Statements

The driver automatically enables support for multiple statements: 
const sql = yield* SqlClient.SqlClient

yield* sql`
  INSERT INTO logs (message) VALUES (${'Starting'});
  INSERT INTO logs (message) VALUES (${'Processing'});
  INSERT INTO logs (message) VALUES (${'Complete'});
`

​
Type Safety

The client is fully typed with Effect’s type system: 
interface User {
  id: number
  name: string
  email: string
}

const users = yield* sql<User>`SELECT * FROM users`
// users: ReadonlyArray<User>

​
Advanced Pool Configuration

For advanced use cases, you can pass additional mysql2 pool options: 
const SqlLive = MysqlClient.layer({
  host: "localhost",
  database: "mydb",
  poolConfig: {
    waitForConnections: true,
    queueLimit: 0,
    enableKeepAlive: true,
    keepAliveInitialDelay: 0
  }
})

Build docs developers (and LLMs) love

Get started for freeTalk to us