> ## Documentation Index
> Fetch the complete documentation index at: https://ahvn.top/llms.txt
> Use this file to discover all available pages before exploring further.

# Database integrations

> SQL-family backends and the HeavenBase Database utility.

HeavenBase SQL-family backends now use the native `heavenbase.utils.db.Database` facade for connection lifecycle, schema inspection, table mutation, comments, statistics, sampling, and execution responses. The previous utility stack is reference material only and is no longer a package dependency of the main package.

## Supported providers

The Database utility resolves provider configuration from `src/heavenbase/resources/configs/default.yaml`, with bootstrap overrides used only while the config store initializes. It connects through the shared `Database` engine registry. Current provider presets include SQLite, DuckDB, PostgreSQL, MySQL-compatible engines, SQL Server, Oracle, Trino, and StarRocks-style MySQL wire connections.

`sqlalchemy>=2.0` is a main HeavenBase dependency. Non-SQLite providers still require their driver packages, such as `psycopg2`, `pymysql`, `pymssql`, `oracledb`, or `trino`.

## Execution behavior

* Config bootstrap reads file-based bootstrap settings first, then opens the config store through `Database`.
* Engine pooling is shared by resolved database spec and tracks disposed engines explicitly. Two `Database` instances with the same provider/database/pool args use the same SQLAlchemy engine and pool. Dispose/create races are guarded so stale facades cannot silently recreate an engine after drop.
* Autocreate is best effort on first engine access. If superuser/database-creation credentials are missing or wrong, HeavenBase still attempts a direct connection so existing databases remain accessible.
* Explicit drop uses `db.drop_database(force=True)`. SQLite/DuckDB remove files, PostgreSQL/MySQL/MSSQL/StarRocks drop databases, and Oracle drops the target user/schema while leaving the PDB service intact. Trino catalogs are connector-managed, so catalog create/drop is intentionally not attempted.
* `readonly` execution is explicit. The default is `readonly=False`; pass `readonly=True` only as a conservative guard for statements expected to be read-only. Pass `readonly=None` when you explicitly want conservative auto-detection for commit/rollback behavior.
* Raw SQL placeholders are normalized before execution. Supported styles are `:name`, `?`, `%s`, `%(name)s`, `$name`, and `$1`, with dictionaries, positional tuples/lists, list-of-dicts batches, and list-of-tuples batches where the driver supports batching. Bare `?` tokens are rewritten only for positional parameter payloads, so PostgreSQL JSON operators such as `payload ? 'key'`, `?|`, and `?&` remain valid. PostgreSQL casts such as `?::int` are preserved after bind rewriting.
* `safe=True` turns execution errors into `SQLResponse(ok=False)`. In an active transaction, a safe failure marks the transaction failed and the context rolls back unless the caller explicitly handles it with `rollback()`. In `execute_many(..., safe=True)`, the first failed statement stops the batch and rolls back by default; `autocommit=True` opts into best-effort per-statement commits.
* SQL healing is intentionally future work and tracked separately. The current API returns or raises the original database error instead of calling an LLM repair path.

## Utility surface

`Database` exposes schema listing, table/view/column inspection, table creation and mutation helpers, comments where supported by the dialect, exact percentiles, string-length summaries, deterministic sampling without assuming an `id` column, and `SQLResponse` export helpers for dictionaries, lists, pandas, NumPy, PyArrow, and compact table display.

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
import heavenbase as hb

db = hb.Database(provider="sqlite", database="workspace.db")
db.execute("create table if not exists items (id text primary key, name text)")
db.execute("insert into items (id, name) values (:id, :name)", {"id": "a", "name": "alpha"})
db.execute("insert into items (id, name) values (?, ?)", ("b", "beta"))

db.tables()
db.columns("items")
db.n_rows("items")

rows = db.execute("select id, name from items", readonly=True)
rows.to_dicts(columns=["id"])
rows.table_display()
```

Use `readonly=None` only when you want HeavenBase to infer whether a statement should commit or roll back. Unknown statements are treated as mutating.

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
db.execute("with input(id) as (select 'c') select id from input", readonly=True)
db.execute("select count(*) from items", readonly=None)
```

Database lifecycle helpers are explicit:

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
db = hb.Database(provider="postgres", database="workspace_demo")
db.execute("select 1")  # first engine access may autocreate the database
db.drop_database(force=True)
```

The canonical naming model is the only supported Database API. Use `tables()`, `columns()`, `pks()`, `fks()`, `n_rows()`, `sample()`, `create_table()`, `add_table_col()`, `clear_table()`, and `drop_table()`; legacy compatibility aliases such as `db_tabs`, `tab_cols`, `row_sample`, and `create_tab` are not kept.

When the wrapper is intentionally smaller than SQLAlchemy, use the public engine or ORM execution path directly:

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
from sqlalchemy import select

db.engine
db.orm_execute(select(my_table.c.id), readonly=True)
```

`SQLResponse` validates projected columns by default. Pass `check=False` only when missing columns should be rendered as `None` for permissive export/display code.

```python theme={"theme":{"light":"github-light","dark":"github-dark"}}
rows.to_dicts(columns=["missing"], check=False)
hb.table_display(rows, columns=["name"])
```

<br />