### Install sqliteconn Package

Source: https://github.com/domonda/go-sqldb/blob/master/sqliteconn/README.md

Use 'go get' to add the sqliteconn package to your project.

```bash
go get github.com/domonda/go-sqldb/sqliteconn
```

--------------------------------

### Test Setup with Database Connection and Schema Reset

Source: https://github.com/domonda/go-sqldb/blob/master/mysqlconn/README.md

Example for setting up tests, including establishing a database connection, dropping all tables, and recreating the schema.

```go
func TestMain(m *testing.M) {
    ctx := context.Background()
    config := &sqldb.Config{
        Driver:   mysqlconn.Driver,
        Host:     "localhost",
        Port:     3306,
        User:     "root",
        Password: "secret",
        Database: "myapp_test",
    }
    conn, err := mysqlconn.Connect(ctx, config)
    if err != nil {
        log.Fatal(err)
    }
    db.SetConn(conn)

    // Drop all tables and recreate schema
    err = mysqlconn.DropAllTables(ctx, conn)
    if err != nil {
        log.Fatal(err)
    }
    err = db.Exec(ctx, mySchema)
    if err != nil {
        log.Fatal(err)
    }

    m.Run()
}
```

--------------------------------

### Start Oracle Test Instance

Source: https://github.com/domonda/go-sqldb/blob/master/oraconn/README.md

Commands to initialize the ephemeral Oracle database container for integration testing.

```bash
cd test
docker compose up -d
```

--------------------------------

### Test Setup: Drop All Database Objects

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Use DropAll to reset the database to a clean state by dropping all user tables and types in the correct order. This is crucial for test setup.

```go
func TestMain(m *testing.M) {
    ctx := context.Background()
    config := &sqldb.Config{
        Driver:   mssqlconn.Driver,
        Host:     "localhost",
        Port:     1433,
        User:     "sa",
        Password: "secret",
        Database: "myapp_test",
    }
    conn, err := mssqlconn.Connect(ctx, config)
    if err != nil {
        log.Fatal(err)
    }
    db.SetConn(conn)

    // Drop everything and recreate schema
    err = mssqlconn.DropAll(ctx, conn)
    if err != nil {
        log.Fatal(err)
    }
    err = db.Exec(ctx, mySchema)
    if err != nil {
        log.Fatal(err)
    }

    m.Run()
}
```

--------------------------------

### Start test database and run tests

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Commands to initialize the PostgreSQL test environment and execute the test workspace script.

```bash
docker compose -f pqconn/test/docker-compose.yml up -d
./test-workspace.sh
```

--------------------------------

### Run shared integration test suite

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Example of invoking conntest.RunAll with a driver-specific configuration to execute the shared test suite.

```go
func TestConnectionSuite(t *testing.T) {
    conntest.RunAll(t, conntest.Config{
        NewConn:      connectPQ,              // factory that creates a real connection per test
        QueryBuilder: pqconn.QueryBuilder{},  // driver-specific query builder
        DDL: conntest.DDL{
            CreateSimpleTable:    `CREATE TABLE conntest_simple (id INTEGER PRIMARY KEY, val TEXT)`,
            CreateUpsertTable:    `CREATE TABLE conntest_upsert (id INTEGER PRIMARY KEY, name TEXT NOT NULL, score INTEGER NOT NULL DEFAULT 0)`,
            CreateReturningTable: `CREATE TABLE conntest_returning (id SERIAL PRIMARY KEY, name TEXT NOT NULL, score INTEGER NOT NULL DEFAULT 0)`,
        },
        DefaultIsolationLevel:        sql.LevelReadCommitted,
        DriverName:                   pqconn.Driver,
        DatabaseName:                 dbName,
        SupportsReadOnlyTransaction:  true,
        SupportsCustomIsolationLevel: true,
        ExecAfterClosedTxErrors:      true,
    })
}
```

--------------------------------

### Drop All Tables for Testing

Source: https://github.com/domonda/go-sqldb/blob/master/mysqlconn/README.md

Drops all base tables in the current database, disabling foreign key checks to allow dropping in any order. Useful for test setup.

```go
err = mysqlconn.DropAllTables(ctx, conn)
```

--------------------------------

### Identifier Escaping Example

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Provides examples of how `EscapeIdentifier` quotes identifiers for SQL Server, handling reserved words, spaces, and safe identifiers.

```APIDOC
## Identifier Escaping

`EscapeIdentifier` wraps identifiers in brackets when necessary:

```go
mssqlconn.EscapeIdentifier("order")   // → [order] (reserved word)
mssqlconn.EscapeIdentifier("name")    // → name    (safe, no quoting)
mssqlconn.EscapeIdentifier("my col")  // → [my col] (contains space)
```

Schema-qualified table names (`schema.table`) are supported by `QueryFormatter.FormatTableName`.
```

--------------------------------

### Connect to a database

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Establishes a database connection using a configuration object and sets it as the global connection for the db package.

```go
config := &sqldb.Config{
    Driver:   "postgres",
    Host:     "localhost",
    User:     "postgres",
    Database: "demo",
    Extra:    map[string]string{"sslmode": "disable"},
}

conn, err := pqconn.Connect(ctx, config)
if err != nil {
    panic(err)
}
defer conn.Close()

// Set as the global connection used by the db package
db.SetConn(conn)
```

--------------------------------

### Connect to PostgreSQL using sqldb.Config

Source: https://github.com/domonda/go-sqldb/blob/master/pqconn/README.md

Use `Connect` to establish a connection from an `sqldb.Config`. Ensure the `ctx` is valid for the operation.

```go
config := &sqldb.Config{
    Driver:   pqconn.Driver, // "postgres"
    Host:     "localhost",
    Port:     5432,
    User:     "postgres",
    Password: "postgres",
    Database: "mydb",
    Extra:    map[string]string{"sslmode": "disable"},
}
conn, err := pqconn.Connect(ctx, config)
```

--------------------------------

### Establish Basic SQLite Connection

Source: https://github.com/domonda/go-sqldb/blob/master/sqliteconn/README.md

Connect to a SQLite database file using a configuration object. Ensure to close the connection when done.

```go
import (
    "context"
    "github.com/domonda/go-sqldb"
    "github.com/domonda/go-sqldb/sqliteconn"
)

config := &sqldb.Config{
    Driver:   "sqlite",
    Database: "myapp.db",
}

conn, err := sqliteconn.Connect(context.Background(), config)
if err != nil {
    panic(err)
}
defer conn.Close()
```

--------------------------------

### Connect to MySQL Database

Source: https://github.com/domonda/go-sqldb/blob/master/mysqlconn/README.md

Establishes a connection to a MySQL database using sqldb.Config. Ensure the sqldb.Config includes the correct driver, host, port, user, password, and database.

```go
config := &sqldb.Config{
    Driver:   mysqlconn.Driver, // "mysql"
    Host:     "localhost",
    Port:     3306,
    User:     "root",
    Password: "secret",
    Database: "mydb",
}
conn, err := mysqlconn.Connect(ctx, config)
```

--------------------------------

### Testing Database Interactions with MockConn in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Demonstrates how to integrate `MockConn` with the `db` package for testing, setting up a mock connection with predefined query results within a test function.

```go
func TestGetUser(t *testing.T) {
    mockConn := sqldb.NewMockConn(sqldb.NewQueryFormatter("$")).
        WithQueryResult(
            []string{"id", "email", "name"},
            [][]driver.Value{
                {"550e8400-e29b-41d4-a716-446655440000", "alice@example.com", "Alice"},
            },
            `SELECT id, email, name FROM public.user WHERE id = $1`,
            "550e8400-e29b-41d4-a716-446655440000",
        )

    ctx := db.ContextWithConn(t.Context(), mockConn)

    user, err := GetUser(ctx, uu.IDFrom("550e8400-e29b-41d4-a716-446655440000"))
    require.NoError(t, err)
    assert.Equal(t, "Alice", user.Name)
    assert.Equal(t, "alice@example.com", user.Email)
}
```

--------------------------------

### Connecting to Microsoft SQL Server

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Demonstrates how to establish a connection to a Microsoft SQL Server database using the mssqlconn package and sqldb.Config.

```APIDOC
## Connecting to Microsoft SQL Server

Use `Connect` to establish a connection from an `sqldb.Config`:

```go
config := &sqldb.Config{
    Driver:   mssqlconn.Driver, // "sqlserver"
    Host:     "localhost",
    Port:     1433,
    User:     "sa",
    Password: "secret",
    Database: "mydb",
}
conn, err := mssqlconn.Connect(ctx, config)
```

`MustConnect` panics on error.

Extra connection parameters can be passed via `config.Extra` and are appended as URL query parameters to the connection string:

```go
config.Extra = map[string]string{
    "encrypt": "disable",
}
```
```

--------------------------------

### Create MockConn for PostgreSQL Placeholders

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Instantiate a MockConn for unit testing with PostgreSQL-style placeholders. Configure query normalization and logging using builder methods.

```go
mockConn := sqldb.NewMockConn(sqldb.NewQueryFormatter("$"))
```

--------------------------------

### Execute a database command

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Perform a standard SQL execution using the database context.

```go
err = db.Exec(ctx, `DELETE FROM public.user WHERE id = $1`, userID)
```

--------------------------------

### Create and Set Global PostgreSQL Connection

Source: https://context7.com/domonda/go-sqldb/llms.txt

Establishes a connection to a PostgreSQL database using a configuration struct and sets it as the global connection for the `db` package. Ensure all necessary driver packages are imported.

```go
package main

import (
    "context"
    "github.com/domonda/go-sqldb"
    "github.com/domonda/go-sqldb/db"
    "github.com/domonda/go-sqldb/pqconn"
)

func main() {
    ctx := context.Background()

    config := &sqldb.Config{
        Driver:   "postgres",
        Host:     "localhost",
        Port:     5432,
        User:     "postgres",
        Password: "secret",
        Database: "myapp",
        Extra:    map[string]string{"sslmode": "disable"},
    }

    conn, err := pqconn.Connect(ctx, config)
    if err != nil {
        panic(err)
    }
    defer conn.Close()

    // Set as the global connection used by the db package
    db.SetConn(conn)

    // Now all db.* functions will use this connection
    err = db.Exec(ctx, `SELECT 1`)
    if err != nil {
        panic(err)
    }
}
```

--------------------------------

### Set Global Database Connection

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Establishes the global database connection using the pqconn driver. Ensure to close the connection when done.

```go
conn, err := pqconn.Connect(ctx, config)
if err != nil {
    panic(err)
}
deferr conn.Close()

db.SetConn(conn)
```

--------------------------------

### Execute Database Cleanup in TestMain

Source: https://github.com/domonda/go-sqldb/blob/master/pqconn/README.md

Demonstrates using pre-built drop queries within a TestMain function to reset the database state.

```go
func TestMain(m *testing.M) {
    ctx := context.Background()
    config := &sqldb.Config{
        Driver:   pqconn.Driver,
        Host:     "localhost",
        Port:     5432,
        User:     "postgres",
        Password: "postgres",
        Database: "myapp_test",
        Extra:    map[string]string{"sslmode": "disable"},
    }
    conn, err := pqconn.Connect(ctx, config)
    if err != nil {
        log.Fatal(err)
    }
    db.SetConn(conn)

    // Drop everything and recreate schema
    err = db.Exec(ctx, pqconn.DropAllInCurrentSchemaQuery)
    if err != nil {
        log.Fatal(err)
    }
    err = db.Exec(ctx, mySchema)
    if err != nil {
        log.Fatal(err)
    }

    m.Run()
}
```

```go
err = db.Exec(ctx, pqconn.DropAllQuery)
```

--------------------------------

### Connect to MSSQL Database

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Use Connect to establish a connection from an sqldb.Config. Ensure the Driver is set to mssqlconn.Driver.

```go
config := &sqldb.Config{
    Driver:   mssqlconn.Driver, // "sqlserver"
    Host:     "localhost",
    Port:     1433,
    User:     "sa",
    Password: "secret",
    Database: "mydb",
}
conn, err := mssqlconn.Connect(ctx, config)
```

--------------------------------

### Connect to an In-Memory SQLite Database

Source: https://github.com/domonda/go-sqldb/blob/master/sqliteconn/README.md

Create a temporary SQLite database in memory by setting the Database field to ":memory:".

```go
config := &sqldb.Config{
    Driver:   "sqlite",
    Database: ":memory:",
}

conn, err := sqliteconn.Connect(context.Background(), config)
```

--------------------------------

### Connect to Oracle Database

Source: https://github.com/domonda/go-sqldb/blob/master/oraconn/README.md

Configures and establishes a connection to an Oracle database. The Database field corresponds to the Oracle service name.

```go
config := &sqldb.Config{
    Driver:   oraconn.Driver, // "oracle"
    Host:     "localhost",
    Port:     1521,
    User:     "myuser",
    Password: "secret",
    Database: "FREEPDB1", // service name
}

conn, err := oraconn.Connect(ctx, config, true)
```

--------------------------------

### Integrate sqliteconn with db Package

Source: https://github.com/domonda/go-sqldb/blob/master/sqliteconn/README.md

Set the global connection for the db package using the connection established by sqliteconn.Connect.

```go
import (
    "github.com/domonda/go-sqldb/db"
    "github.com/domonda/go-sqldb/sqliteconn"
)

config := &sqldb.Config{
    Driver:   "sqlite",
    Database: "myapp.db",
}

conn, err := sqliteconn.Connect(context.Background(), config)
if err != nil {
    panic(err)
}

db.SetConn(conn)

// Now use db package functions
err = db.Exec(ctx, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")
```

--------------------------------

### Unit test database code with MockConn

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Inject a MockConn into the context to simulate database responses during unit testing without requiring a live database connection.

```go
func TestGetUser(t *testing.T) {
    mockConn := sqldb.NewMockConn(sqldb.NewQueryFormatter("$")).
        WithQueryResult(
            []string{"id", "email", "name"}, // columns
            [][]driver.Value{{"some-id", "alice@example.com", "Alice"}}, // rows
            `SELECT * FROM public.user WHERE id = $1`, // query
            "some-id", // args
        )

    ctx := db.ContextWithConn(t.Context(), mockConn)

    user, err := GetUser(ctx, "some-id")
    require.NoError(t, err)
    assert.Equal(t, "Alice", user.Name)
}
```

--------------------------------

### LISTEN/NOTIFY for PostgreSQL

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Implement real-time notifications using PostgreSQL's LISTEN/NOTIFY mechanism. `db.ListenOnChannel` registers callbacks for channel events, while `db.UnlistenChannel` removes them. The `pqconn` implementation handles automatic reconnection and resubscription.

```go
err = db.ListenOnChannel(ctx, "user_changes",
    func(channel, payload string) {
        fmt.Printf("Notification on %s: %s\n", channel, payload)
    },
    func(channel string) {
        fmt.Printf("Unlistened from %s\n", channel)
    },
)

```

```go
// Later...
err = db.UnlistenChannel(ctx, "user_changes")

```

--------------------------------

### Mocking Database Transactions in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Illustrates how `MockConn` supports transactions, allowing tests to verify transaction-wrapped database operations.

```go
func TestWithTransaction(t *testing.T) {
    mockConn := sqldb.NewMockConn(sqldb.NewQueryFormatter("$")).
        WithQueryResult(
            []string{"count"},
            [][]driver.Value{{int64(42)}},
            `SELECT count(*) FROM public.user`,
        )

    ctx := db.ContextWithConn(t.Context(), mockConn)

    err := db.Transaction(ctx, func(ctx context.Context) error {
        count, err := db.QueryRowAs[int64](ctx, `SELECT count(*) FROM public.user`)
        require.NoError(t, err)
        assert.Equal(t, int64(42), count)
        return nil
    })
    require.NoError(t, err)
}
```

--------------------------------

### Unit Test with MockConn

Source: https://context7.com/domonda/go-sqldb/llms.txt

Use MockConn to simulate database interactions and verify queries without a live database connection.

```go
import (
    "database/sql/driver"
    "testing"
    "github.com/domonda/go-sqldb"
    "github.com/domonda/go-sqldb/db"
)

func TestGetUser(t *testing.T) {
    // Create mock with PostgreSQL-style placeholders
    mockConn := sqldb.NewMockConn(sqldb.NewQueryFormatter("$")).
        WithQueryResult(
            []string{"id", "email", "name"},           // column names
            [][]driver.Value{                           // rows
                {"user-123", "alice@example.com", "Alice"},
            },
            `SELECT * FROM public.user WHERE id = $1`, // query to match
            "user-123",                                 // expected args
        )

    // Inject mock connection via context
    ctx := db.ContextWithConn(t.Context(), mockConn)

    // Test your function
    user, err := GetUser(ctx, "user-123")
    if err != nil {
        t.Fatal(err)
    }
    if user.Name != "Alice" {
        t.Errorf("expected Alice, got %s", user.Name)
    }

    // Verify recorded queries
    if len(mockConn.Recordings.Queries) != 1 {
        t.Errorf("expected 1 query, got %d", len(mockConn.Recordings.Queries))
    }
}
```

--------------------------------

### Implement Context Key Convention

Source: https://github.com/domonda/go-sqldb/blob/master/CLAUDE.md

Use unexported empty struct types as context keys to ensure type safety and avoid collisions.

```go
type myCtxKey struct{}

func ContextWithMyValue(ctx context.Context, val string) context.Context {
    return context.WithValue(ctx, myCtxKey{}, val)
}

func MyValueFromContext(ctx context.Context) string {
    val, _ := ctx.Value(myCtxKey{}).(string)
    return val
}
```

--------------------------------

### Handle Generic Database Errors

Source: https://context7.com/domonda/go-sqldb/llms.txt

Demonstrates how to use errors.As to catch specific constraint violation types.

```go
import (
    "errors"
    "github.com/domonda/go-sqldb"
)

err := db.InsertRowStruct(ctx, &user)
if err != nil {
    // Check for specific constraint violations
    var uniqueErr sqldb.ErrUniqueViolation
    if errors.As(err, &uniqueErr) {
        fmt.Printf("Duplicate value for constraint: %s\n", uniqueErr.Constraint)
        return ErrUserAlreadyExists
    }

    var fkErr sqldb.ErrForeignKeyViolation
    if errors.As(err, &fkErr) {
        fmt.Printf("Foreign key violation: %s\n", fkErr.Constraint)
        return ErrInvalidReference
    }

    // Check for any constraint violation
    var constraintErr sqldb.ErrIntegrityConstraintViolation
    if errors.As(err, &constraintErr) {
        fmt.Printf("Constraint violated: %s\n", constraintErr.Constraint)
    }

    return err
}
```

--------------------------------

### Registering Mock Query Results with Arguments in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Register expected results for SQL queries that include placeholders by providing corresponding argument values to `WithQueryResult`.

```go
mockConn = mockConn.WithQueryResult(
    []string{"id", "email", "name"},
    [][]driver.Value{
        {"550e8400-e29b-41d4-a716-446655440000", "alice@example.com", "Alice"},
    },
    `SELECT id, email, name FROM public.user WHERE id = $1`,
    "550e8400-e29b-41d4-a716-446655440000",         // matches $1
)
```

--------------------------------

### Prepare QueryRowAsStmt for Repeated Queries

Source: https://context7.com/domonda/go-sqldb/llms.txt

Prepares a statement for efficient repeated single-row queries.

```go
queryUser, closeStmt, err := db.QueryRowAsStmt[User](ctx, `SELECT * FROM public.user WHERE id = $1`)
if err != nil {
    return err
}
defer closeStmt()

// Reuse the prepared statement
user1, err := queryUser(ctx, "user-1")
user2, err := queryUser(ctx, "user-2")
user3, err := queryUser(ctx, "user-3")
```

--------------------------------

### Database Helpers and Mocks

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

General database utilities and testing helpers for mocking connections and rows.

```APIDOC
## Other

### Functions
- **CurrentTimestamp(ctx) time.Time**: Get the current database timestamp
- **Prepare(ctx, query) (Stmt, error)**: Prepare a statement
- **NewMockConn(ctx) *MockConn**: Create a MockConn using the context's query formatter
- **NewMockStructRows[S](ctx, rows...) *MockStructRows[S]**: Create mock rows from structs
```

--------------------------------

### Inspecting Recorded Queries and Execs in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Access the `Recordings` field of `MockConn` to verify that expected queries and `Exec` calls were made during a test.

```go
// After running code under test...
require.Len(t, mockConn.Recordings.Queries, 1)
assert.Equal(t, `SELECT id FROM public.user WHERE email = $1`, mockConn.Recordings.Queries[0].Query)

require.Len(t, mockConn.Recordings.Execs, 1)
assert.Contains(t, mockConn.Recordings.Execs[0].Query, "UPDATE")
```

--------------------------------

### Inspect PostgreSQL Errors with pqconn Helpers

Source: https://github.com/domonda/go-sqldb/blob/master/pqconn/README.md

Helper functions like `IsUniqueViolation` can check specific PostgreSQL error codes. For generic sqldb errors, use `errors.As`.

```go
err := db.Exec(ctx, "INSERT INTO orders ...")
if pqconn.IsUniqueViolation(err) {
    // pqconn-specific: works even before errors.As-wrapping
}

var fkErr sqldb.ErrForeignKeyViolation
if errors.As(err, &fkErr) {
    fmt.Println("violated foreign key constraint:", fkErr.Constraint)
}
```

--------------------------------

### Low-level SQL Database API

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Access the root `sqldb` package for operations requiring explicit connection, reflector, and builder arguments. This provides full control for building custom abstractions.

```go
user, err := sqldb.QueryRowAs[User](ctx, conn, reflector, conn, `SELECT * FROM public.user WHERE id = $1`, userID)

```

```go
err = sqldb.InsertRowStruct(ctx, conn, reflector, queryBuilder, conn, &user)

```

```go
err = sqldb.Transaction(ctx, conn, &sql.TxOptions{ReadOnly: true}, func(tx sqldb.Connection) error {
    return tx.Exec(ctx, `UPDATE public.user SET name = $1 WHERE id = $2`, "Alice", userID)
})

```

--------------------------------

### Prepare InsertRowStructStmt for Repeated Inserts

Source: https://context7.com/domonda/go-sqldb/llms.txt

Prepares a statement for efficient repeated struct-based inserts.

```go
insertUser, closeStmt, err := db.InsertRowStructStmt[User](ctx, db.IgnoreColumns("created_at"))
if err != nil {
    return err
}
defer closeStmt()

for _, user := range usersToInsert {
    err = insertUser(ctx, user)
    if err != nil {
        return err
    }
}
```

--------------------------------

### Registering Mock Query Results in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Use `WithQueryResult` to define expected results for a specific SQL query without arguments. It returns a cloned `MockConn` for chaining.

```go
mockConn = mockConn.WithQueryResult(
    []string{"id", "email", "name"},               // column names
    [][]driver.Value{                               // rows
        {"550e8400-e29b-41d4-a716-446655440000", "alice@example.com", "Alice"},
        {"6ba7b810-9dad-11d1-80b4-00c04fd430c8", "bob@example.com", "Bob"},
    },
    `SELECT id, email, name FROM public.user`,      // the query to match
    // args... (if the query has placeholders)
)
```

--------------------------------

### Add Extra Connection Parameters

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Extra connection parameters can be passed via config.Extra and are appended as URL query parameters to the connection string.

```go
config.Extra = map[string]string{
    "encrypt": "disable",
}
```

--------------------------------

### Run Integration Tests

Source: https://github.com/domonda/go-sqldb/blob/master/oraconn/README.md

Executes the test suite for the oraconn package.

```bash
go test -v -count=1 -timeout 120s ./test/...
```

--------------------------------

### Prepared Statements in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Utilize prepared statements for efficient and safe query execution using `db.QueryRowAsStmt`. This method returns a function to execute the prepared query and a cleanup function. Ensure the statement is closed after use.

```go
// Prepared query statement
queryUser, closeStmt, err := db.QueryRowAsStmt[User](ctx, `SELECT * FROM public.user WHERE id = $1`)
if err != nil {
    return err
}
deferr closeStmt()

user, err := queryUser(ctx, userID)

```

--------------------------------

### Query rows with context-based row capping

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Demonstrates limiting the number of rows returned by a query using ContextWithMaxNumRows and handling the ErrMaxNumRowsExceeded error.

```go
// Cap a request handler to at most 1000 rows across all db.QueryRows* calls.
ctx = db.ContextWithMaxNumRows(ctx, 1000)

users, err := db.QueryRowsAsSlice[User](ctx, `SELECT * FROM public.user`)
if err != nil {
    var capped db.ErrMaxNumRowsExceeded
    if errors.As(err, &capped) {
        // users contains the first 1000 rows, decide how to proceed
    }
    return err
}
```

--------------------------------

### Logging SQL Statements for Debugging in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Configure `MockConn` to log all executed SQL statements to an `io.Writer` for debugging and inspection during tests.

```go
var buf strings.Builder
mockConn := sqldb.NewMockConn(sqldb.NewQueryFormatter("$")).
    WithQueryLog(&buf)

// ... run code under test ...

t.Log("Executed SQL:\n" + buf.String())
```

--------------------------------

### Customize Mock Exec Behavior

Source: https://context7.com/domonda/go-sqldb/llms.txt

Define custom logic for execution calls within a mock connection to simulate errors or specific database responses.

```go
mockConn.MockExec = func(ctx context.Context, query string, args ...any) error {
    if strings.Contains(query, "DELETE") {
        return errors.New("delete not allowed in test")
    }
    return nil
}
```

--------------------------------

### Define Struct for Database Operations

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Defines a User struct with db tags for mapping to a database table. Use `db:"column_name,primarykey"` for primary keys and `db:"column_name,default"` for columns with database defaults.

```go
type User struct {
    db.TableName `db:"public.user"`

    ID        uu.ID     `db:"id,primarykey"`
    Email     string    `db:"email"`
    Name      string    `db:"name"`
    CreatedAt time.Time `db:"created_at,default"`
}
```

--------------------------------

### Query rows as map slice with scan converters

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Shows how to use ScanConverters to transform raw driver values during row scanning in QueryRowsAsMapSlice.

```go
rows, err := db.QueryRowsAsMapSlice(ctx,
    db.ScanConverters{
        db.BytesToStringScanConverter(`\x`),
        db.TimeToStringScanConverter(time.DateTime),
    },
    `SELECT id, name, data, created_at FROM public.event`,
)
```

--------------------------------

### Query Callback with Simple Function

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Process rows one by one using a callback function that receives scanned arguments.

```go
// Callback arguments are scanned from columns via reflection
err = db.QueryCallback(ctx,
    func(name, email string) {
        fmt.Printf("%q <%s>\n", name, email)
    },
    `SELECT name, email FROM public.user`,
)
```

--------------------------------

### Batch Insert Structs

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Efficiently insert a slice of structs into the database using a transaction and prepared statements.

```go
// Batch insert a slice of structs (uses a transaction + prepared statement)
err = db.InsertRowStructs(ctx, users)
```

--------------------------------

### Mocking Exec Calls in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Customize the behavior of `Exec` calls by assigning a function to `mockConn.MockExec` to handle specific queries or return custom errors.

```go
mockConn.MockExec = func(ctx context.Context, query string, args ...any) error {
    if strings.Contains(query, "DELETE") {
        return errs.New("delete not allowed in test")
    }
    return nil
}
```

--------------------------------

### Apply Query Column Options

Source: https://context7.com/domonda/go-sqldb/llms.txt

Filter columns during struct-based database operations using various predefined column modifiers.

```go
// Ignore specific columns during insert
err = db.InsertRowStruct(ctx, &user, db.IgnoreColumns("id", "created_at", "updated_at"))

// Include only specific columns during update
err = db.UpdateRowStruct(ctx, &user, db.OnlyColumns("name", "email"))

// Ignore columns with database defaults
err = db.InsertRowStruct(ctx, &user, db.IgnoreHasDefault)

// Ignore primary key columns
err = db.InsertRowStruct(ctx, &user, db.IgnorePrimaryKey)

// Ignore read-only columns (applied automatically)
err = db.InsertRowStruct(ctx, &user, db.IgnoreReadOnly)

// Ignore by struct field name
err = db.InsertRowStruct(ctx, &user, db.IgnoreStructFields("InternalField"))
```

--------------------------------

### Listen/Notify

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Functions for subscribing and unsubscribing to PostgreSQL NOTIFY events.

```APIDOC
## Listen/Notify

### Functions
- **ListenOnChannel(ctx, channel, onNotify, onUnlisten) error**: Subscribe to PostgreSQL NOTIFY
- **UnlistenChannel(ctx, channel) error**: Unsubscribe from a channel
- **IsListeningOnChannel(ctx, channel) bool**: Check if listening on a channel
```

--------------------------------

### Query Formatting for SQL Server

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Explains the QueryFormatter's implementation for SQL Server-specific formatting, including identifier escaping and placeholder syntax.

```APIDOC
## Query Formatting

`QueryFormatter` implements `sqldb.QueryFormatter` with SQL Server-specific formatting:

- Table and column names are escaped using bracket quoting (`[identifier]`)
- Placeholders use `@p1`, `@p2`, ... syntax
- `EscapeIdentifier` quotes identifiers that contain special characters or are T-SQL reserved words
- Maximum number of arguments per query: 2100
```

--------------------------------

### Configure Extra Connection Parameters

Source: https://github.com/domonda/go-sqldb/blob/master/mysqlconn/README.md

Passes extra DSN parameters to the MySQL driver via config.Extra. This is useful for setting character sets or time parsing.

```go
config.Extra = map[string]string{
    "charset":   "utf8mb4",
    "parseTime": "true",
}
```

--------------------------------

### Execute SQL Statements

Source: https://context7.com/domonda/go-sqldb/llms.txt

Execute commands that do not return rows, optionally retrieving the number of affected rows.

```go
err = db.Exec(ctx, `DELETE FROM public.user WHERE id = $1`, userID)
if err != nil {
    return err
}
```

```go
n, err := db.ExecRowsAffected(ctx,
    `UPDATE public.user SET active = $1 WHERE last_login < $2`,
    false, time.Now().AddDate(0, -6, 0),
)
if err != nil {
    return err
}
fmt.Printf("%d users marked inactive\n", n)
```

--------------------------------

### Handle SQLite Constraint Violations

Source: https://github.com/domonda/go-sqldb/blob/master/sqliteconn/README.md

Use helper functions like IsUniqueViolation, IsForeignKeyViolation, and IsDatabaseLocked to check for specific SQLite errors returned by Exec.

```go
err := conn.Exec(ctx, "INSERT INTO users (id, name) VALUES (?, ?)", 1, "John")
if sqliteconn.IsUniqueViolation(err) {
    // Handle unique constraint violation
}
if sqliteconn.IsForeignKeyViolation(err) {
    // Handle foreign key violation
}
if sqliteconn.IsDatabaseLocked(err) {
    // Handle database locked error
}
```

--------------------------------

### Dropping Schema for Testing

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Presents three utility functions for resetting the database schema during testing: DropAllTables, DropAllTypes, and DropAll.

```APIDOC
## Drop Schema for Testing

Three functions are provided for resetting the database to a clean state in tests.

| Function          | Drops                                    |
| ----------------- | ---------------------------------------- |
| `DropAllTables`   | All user tables (removes FK constraints first) |
| `DropAllTypes`    | All user-defined types                   |
| `DropAll`         | Tables then types (correct order)        |

Always use `DropAll` (or call `DropAllTables` before `DropAllTypes`) because types referenced by tables cannot be dropped while the tables exist.

### Example: Test Setup

```go
func TestMain(m *testing.M) {
    ctx := context.Background()
    config := &sqldb.Config{
        Driver:   mssqlconn.Driver,
        Host:     "localhost",
        Port:     1433,
        User:     "sa",
        Password: "secret",
        Database: "myapp_test",
    }
    conn, err := mssqlconn.Connect(ctx, config)
    if err != nil {
        log.Fatal(err)
    }
    db.SetConn(conn)

    // Drop everything and recreate schema
    err = mssqlconn.DropAll(ctx, conn)
    if err != nil {
        log.Fatal(err)
    }
    err = db.Exec(ctx, mySchema)
    if err != nil {
        log.Fatal(err)
    }

    m.Run()
}
```
```

--------------------------------

### Manual Row Scanning

Source: https://context7.com/domonda/go-sqldb/llms.txt

Perform low-level queries and manually scan results into variables.

```go
var name string
var email string
var createdAt time.Time

err = db.QueryRow(ctx,
    `SELECT name, email, created_at FROM public.user WHERE id = $1`, userID,
).Scan(&name, &email, &createdAt)
if err != nil {
    return err
}
```

--------------------------------

### Insert with RETURNING Clause

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Insert a row and retrieve a value (e.g., ID) from the inserted row using a RETURNING clause.

```go
// Insert with RETURNING clause
var id uu.ID
err = db.InsertReturning(ctx, "public.user", db.Values{
    "name":  "Erik Unger",
    "email": "erik@example.com",
}, "id").Scan(&id)
```

--------------------------------

### Custom Query Handling with MockQuery in Go

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Set the `MockQuery` function for dynamic query responses, overriding `WithQueryResult` behavior for more complex mocking scenarios.

```go
mockConn.MockQuery = func(ctx context.Context, query string, args ...any) sqldb.Rows {
    if strings.Contains(query, "public.user") {
        return sqldb.NewMockRows("id", "name").
            WithRow("some-id", "Alice")
    }
    return sqldb.NewErrRows(sql.ErrNoRows)
}
```

--------------------------------

### Reset database data directories

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Scripts to clear persistent data for specific database drivers after configuration changes.

```bash
./pqconn/test/reset-postgres-data.sh
./mysqlconn/test/reset-mariadb-data.sh
./mssqlconn/test/reset-mssql-data.sh
# oraconn has no persistent data — use: docker compose -f oraconn/test/docker-compose.yml down
```

--------------------------------

### Connect to SQLite in Read-Only Mode

Source: https://github.com/domonda/go-sqldb/blob/master/sqliteconn/README.md

Configure the connection to be read-only by setting the ReadOnly field to true in the sqldb.Config.

```go
config := &sqldb.Config{
    Driver:   "sqlite",
    Database: "myapp.db",
    ReadOnly: true,
}

conn, err := sqliteconn.Connect(context.Background(), config)
```

--------------------------------

### Subscribe to PostgreSQL Channel Notifications

Source: https://context7.com/domonda/go-sqldb/llms.txt

Listens for NOTIFY events on a specific channel and provides an unlisten mechanism.

```go
err = db.ListenOnChannel(ctx, "user_changes",
    func(channel, payload string) {
        fmt.Printf("Notification on %s: %s\n", channel, payload)
        // Handle the notification (e.g., invalidate cache)
    },
    func(channel string) {
        fmt.Printf("Unlistened from %s\n", channel)
    },
)
if err != nil {
    return err
}

// Later, unsubscribe
err = db.UnlistenChannel(ctx, "user_changes")
```

--------------------------------

### Simple Transaction Management in Go

Source: https://github.com/domonda/go-sqldb/blob/master/CLAUDE.md

Use `db.Transaction` for basic atomic operations. All database calls within the provided function will be part of the transaction.

```go
// Simple transaction
err := db.Transaction(ctx, func(ctx context.Context) error {
    // All db.Conn(ctx) calls use the transaction
    return db.Conn(ctx).Exec(/*sql*/ `INSERT ...`)
})
```

--------------------------------

### Configure Custom Struct Reflector

Source: https://context7.com/domonda/go-sqldb/llms.txt

Customizes how Go structs are mapped to database columns by configuring a `TaggedStructReflector`. This allows specifying different tags for column names, primary keys, read-only fields, and handling untagged fields.

```go
import "github.com/domonda/go-sqldb"

reflector := &sqldb.TaggedStructReflector{
    NameTag:          "col",           // Use "col" tag instead of "db"
    Ignore:           "_ignore_",      // Ignore fields with this value
    PrimaryKey:       "pk",
    ReadOnly:         "readonly",
    Default:          "default",
    UntaggedNameFunc: sqldb.ToSnakeCase, // Convert untagged fields to snake_case
}

// Set globally
db.SetStructReflector(reflector)

// Or override per context
ctx = db.ContextWithStructReflector(ctx, reflector)
```

--------------------------------

### Define User Struct with Database Tags

Source: https://context7.com/domonda/go-sqldb/llms.txt

Defines a Go struct `User` to be mapped to a database table. Embed `db.TableName` to specify the table name and use `db` struct tags for column mapping and options like `primarykey`, `readonly`, and `default`.

```go
package main

import (
    "time"
    "github.com/domonda/go-sqldb/db"
)

type User struct {
    db.TableName `db:"public.user"`

    ID        string    `db:"id,primarykey,default"`
    Email     string    `db:"email"`
    Name      string    `db:"name"`
    Active    bool      `db:"active"`
    CreatedAt time.Time `db:"created_at,readonly,default"`
    UpdatedAt time.Time `db:"updated_at,default"`
}

// Tag options:
// - primarykey: marks the column as part of the primary key
// - readonly: excluded from INSERT and UPDATE operations
// - default: column has a database default value
// - "-": ignore the field entirely
```

--------------------------------

### Connection Management Functions

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Functions for managing global and context-specific database connections, query builders, and struct reflectors.

```APIDOC
## Connection Management

### Description
Functions to configure and retrieve database connections, query builders, and struct reflectors globally or via context.

### Functions
- **SetConn(conn)** - Set the global connection
- **Conn(ctx) Connection** - Get the connection from context or global
- **ContextWithConn(ctx, conn) context.Context** - Override connection in context
- **ContextWithGlobalConn(ctx) context.Context** - Explicitly use the global connection in context
- **Close() error** - Close the global connection
- **SetQueryBuilder(qb)** - Set the global query builder
- **QueryBuilder(ctx) QueryBuilder** - Get the query builder from context, connection, or global
- **ContextWithQueryBuilder(ctx, qb) context.Context** - Override query builder in context
- **SetStructReflector(sr)** - Set the global struct reflector
- **StructReflector(ctx) StructReflector** - Get the struct reflector from context or global
- **ContextWithStructReflector(ctx, sr) context.Context** - Override struct reflector in context
- **ContextWithMaxNumRows(ctx, n) context.Context** - Cap the rows scanned by any QueryRows* call on this context
- **MaxNumRowsFromContext(ctx) int** - Read the current row cap from the context
```

--------------------------------

### Implement a custom type wrapper

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Define a struct that implements WrapAsScanner and WrapAsValuer to handle custom database type serialization.

```go
type moneyTypeWrapper struct{}

func (moneyTypeWrapper) WrapAsScanner(val reflect.Value) sql.Scanner {
    if val.Type() != reflect.TypeFor[Money]() {
        return nil // not handled
    }
    return &moneyScanner{ptr: val.Addr()}
}

func (moneyTypeWrapper) WrapAsValuer(val reflect.Value) driver.Valuer {
    if val.Type() != reflect.TypeFor[Money]() {
        return nil // not handled
    }
    return moneyValuer{val: val.Interface().(Money)}
}
```

--------------------------------

### Insert with RETURNING Clause

Source: https://context7.com/domonda/go-sqldb/llms.txt

Inserts a row and retrieves generated values. Supported only in PostgreSQL and SQLite.

```go
var generatedID string
err = db.InsertReturning(ctx, "public.user", db.Values{
    "name":  "Alice",
    "email": "alice@example.com",
}, "id").Scan(&generatedID)
if err != nil {
    return err
}
fmt.Printf("Created user with ID: %s\n", generatedID)
```

--------------------------------

### Parse Database Connection URI

Source: https://context7.com/domonda/go-sqldb/llms.txt

Parses a database connection string in URI format into a `sqldb.Config` struct. This is useful for externalizing connection details.

```go
config, err := sqldb.ParseConfig("postgres://user:password@localhost:5432/database?sslmode=disable")
if err != nil {
    panic(err)
}
// config.Driver = "postgres"
// config.Host = "localhost"
// config.Port = 5432
// config.Database = "database"
```

--------------------------------

### Query Callback with Context and Error Return

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Process rows using a callback function that accepts context and can return an error.

```go
// With context and error return
err = db.QueryCallback(ctx,
    func(ctx context.Context, user *User) error {
        return processUser(ctx, user)
    },
    `SELECT * FROM public.user`,
)
```

--------------------------------

### Query Single Row by Primary Key with Default

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Query a single row by primary key, returning a default value if no row is found.

```go
// Return a default value instead of sql.ErrNoRows
user, err = db.QueryRowStructOr(ctx, defaultUser, userID)
```

--------------------------------

### Query Struct by Primary Key

Source: https://context7.com/domonda/go-sqldb/llms.txt

Retrieve a struct directly using its primary key, or provide a default value if no rows are found.

```go
// Query by primary key (table and PK columns from struct tags)
user, err := db.QueryRowStruct[User](ctx, userID)
if err != nil {
    return err
}

// Return default value instead of error on no rows
user, err = db.QueryRowStructOr(ctx, User{Name: "Unknown"}, userID)
```

--------------------------------

### Update Records in SQL Database

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Use `db.Update` for general updates with a values map and WHERE clause, or `db.UpdateRowStruct` to update based on a struct, automatically building the WHERE clause from primary key fields. Specific columns can be targeted using `db.OnlyColumns`.

```go
// Update with a values map and WHERE clause
err = db.Update(ctx, "public.user", db.Values{"name": "New Name"},
    `WHERE id = $1`, userID,
)

```

```go
// Update using a struct (WHERE clause built from primarykey fields)
err = db.UpdateRowStruct(ctx, &user)

```

```go
// Update only specific columns
err = db.UpdateRowStruct(ctx, &user, db.OnlyColumns("name", "email"))

```

--------------------------------

### Insert and Query Struct Row

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Inserts a new user row using InsertRowStruct and queries it back by its primary key. Use db.IgnoreHasDefault to prevent inserting zero values for columns with database defaults.

```go
userID := uu.IDv4()

err := db.InsertRowStruct(ctx, 
    &User{
        ID:    userID,
        Email: "alice@example.com",
        Name:  "Alice",
    },
    db.IgnoreHasDefault, // prevents the zero CreatedAt field to be inserted
)
if err != nil {
    return err
}

// user.CreatedAt will contain the default value created for the new row
user, err := db.QueryRowStruct[User](ctx, userID)
if err != nil {
    return err
}
```

--------------------------------

### Execute Database Transaction

Source: https://github.com/domonda/go-sqldb/blob/master/db/README.md

Wraps a series of database operations within a transaction. All db.* calls within the callback use the transaction's context.

```go
err := db.Transaction(ctx, func(ctx context.Context) error {
    user, err := db.QueryRowAs[User](ctx, `SELECT * FROM public.user WHERE id = $1`, id)
    if err != nil {
        return err
    }
    return db.Exec(ctx, `UPDATE public.user SET last_login = now() WHERE id = $1`, id)
})
```

--------------------------------

### Serialized Transactions in Go

Source: https://github.com/domonda/go-sqldb/blob/master/CLAUDE.md

Employ `db.SerializedTransaction` for scenarios requiring high concurrency to prevent race conditions.

```go
// Serialized transaction (for high concurrency scenarios)
err := db.SerializedTransaction(ctx, func(ctx context.Context) error { ... })
```

--------------------------------

### Inspect SQL Server Constraint Errors

Source: https://github.com/domonda/go-sqldb/blob/master/mssqlconn/README.md

Check for specific SQL Server constraint violations using helper functions like IsUniqueViolation and IsForeignKeyViolation.

```go
err := db.Exec(ctx, "INSERT INTO orders ...")
if mssqlconn.IsUniqueViolation(err) {
    // handle duplicate key
}
if mssqlconn.IsForeignKeyViolation(err) {
    // handle FK violation
}
```

--------------------------------

### Execute Database Transactions

Source: https://context7.com/domonda/go-sqldb/llms.txt

Manages transactions with automatic commit/rollback, return values, custom options, or read-only modes.

```go
err = db.Transaction(ctx, func(ctx context.Context) error {
    // All db.* calls within this function use the transaction
    user, err := db.QueryRowAs[User](ctx, `SELECT * FROM public.user WHERE id = $1`, userID)
    if err != nil {
        return err // triggers rollback
    }

    err = db.Exec(ctx, `UPDATE public.user SET last_login = now() WHERE id = $1`, userID)
    if err != nil {
        return err // triggers rollback
    }

    return nil // triggers commit
})
if err != nil {
    return err
}
```

```go
user, err := db.TransactionResult[User](ctx, func(ctx context.Context) (User, error) {
    var user User
    err := db.QueryRow(ctx, `SELECT * FROM public.user WHERE id = $1 FOR UPDATE`, userID).Scan(&user)
    if err != nil {
        return User{}, err
    }

    user.Name = "Updated"
    err = db.UpdateRowStruct(ctx, &user)
    if err != nil {
        return User{}, err
    }

    return user, nil
})
if err != nil {
    return err
}
fmt.Printf("Updated user: %s\n", user.Name)
```

```go
import "database/sql"

err = db.TransactionOpts(ctx, &sql.TxOptions{
    Isolation: sql.LevelSerializable,
    ReadOnly:  false,
}, func(ctx context.Context) error {
    // Operations with serializable isolation
    return nil
})
```

```go
users, err := db.TransactionReadOnlyResult[[]User](ctx, func(ctx context.Context) ([]User, error) {
    return db.QueryRowsAsSlice[User](ctx, `SELECT * FROM public.user`)
})
```

--------------------------------

### Query Multiple Rows into Slice of Structs

Source: https://github.com/domonda/go-sqldb/blob/master/README.md

Query multiple rows and scan them into a slice of structs.

```go
// Query into a slice of structs
users, err := db.QueryRowsAsSlice[User](ctx, `SELECT * FROM public.user`)
```