# sql

> SQL databases — open SQLite, Postgres, or MySQL and run exec, query, queryOne, queryValue, and transactions.

# sql

The `sql` module opens SQL databases and runs queries over an integer handle. SQLite, Postgres, and MySQL/MariaDB are supported through bundled pure-Go drivers. Import it with `import sql`.

`sql.open` picks the driver from the connection string:

- `":memory:"` → in-memory SQLite
- `"file.db"` / `"/path/to/file.db"` → on-disk SQLite
- `"postgres://user:pass@host:5432/dbname"` (or `postgresql://`) → Postgres
- `"mysql://user:pass@host:3306/dbname"` → MySQL/MariaDB

It returns an `int` handle, or `-1` on error. Placeholder syntax differs by driver: SQLite and MySQL use `?`, while Postgres uses `$1`, `$2`, … The rest of the API works identically across drivers.

## Functions

| Function | Signature | Description |
| --- | --- | --- |
| `open` | `sql.open(connStr: string) → int` | open a database; returns a handle or `-1` |
| `close` | `sql.close(handle: int) → bool` | close the database (rolls back any open tx); true if the handle existed |
| `exec` | `sql.exec(handle: int, query: string, params?: any[]) → object` | run a statement; returns `{rowsAffected, lastInsertId}` |
| `query` | `sql.query(handle: int, query: string, params?: any[]) → object[]` | run a SELECT; returns an array of row objects keyed by column name |
| `queryOne` | `sql.queryOne(handle: int, query: string, params?: any[]) → object` | first row as an object, or void if no rows |
| `queryValue` | `sql.queryValue(handle: int, query: string, params?: any[]) → any` | first column of the first row, or void if no rows |
| `transaction` | `sql.transaction(handle: int, fn) → bool` | run `fn(handle)` inside BEGIN/COMMIT; rolls back if `fn` errors or returns `false` |

Row objects from `query` / `queryOne` have one field per selected column, with values typed as int, float, string, bool, or void (NULL). `transaction` does not support nesting.

## Example

```goost
import sql

let db = sql.open(":memory:")
if db < 0 {
    println("failed to open database")
    return
}

sql.exec(db, "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INT)")

// Insert with placeholders (SQLite/MySQL use ?, Postgres uses $1)
let res = sql.exec(db, "INSERT INTO users (name, age) VALUES (?, ?)", ["Ada", 36])
println("inserted id: " + toString(res.lastInsertId))

// Read rows back
let rows = sql.query(db, "SELECT id, name FROM users WHERE age > ?", [30])
for row in rows {
    println(toString(row.id) + ": " + row.name)
}

let count = sql.queryValue(db, "SELECT COUNT(*) FROM users")
println("total: " + toString(count))

// Transaction: returning false rolls back
sql.transaction(db, fn(h) {
    sql.exec(h, "UPDATE users SET age = age + 1 WHERE name = ?", ["Ada"])
    return true
})

sql.close(db)
```