Metadata-Version: 2.4
Name: buildaquery
Version: 1.1.3
Summary: A Python SQL query builder with cross-dialect execution primitives and OLTP support.
License: MIT
License-File: LICENSE.txt
Author: Anirudh Bhattacharya
Author-email: anirudhbhattacharya1@gmail.com
Requires-Python: >=3.12,<4.0
Classifier: Development Status :: 5 - Production/Stable
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Provides-Extra: all-databases
Provides-Extra: cockroach
Provides-Extra: duckdb
Provides-Extra: mariadb
Provides-Extra: mssql
Provides-Extra: mysql
Provides-Extra: oracle
Provides-Extra: postgres
Requires-Dist: Pygments (==2.19.2)
Requires-Dist: colorama (==0.4.6)
Requires-Dist: duckdb (>=1.1.3,<2.0.0) ; extra == "duckdb" or extra == "all-databases"
Requires-Dist: mariadb (>=1.1.11,<2.0.0) ; extra == "mariadb" or extra == "all-databases"
Requires-Dist: mysql-connector-python (==9.4.0) ; extra == "mysql" or extra == "all-databases"
Requires-Dist: oracledb (>=3.4.2,<4.0.0) ; extra == "oracle" or extra == "all-databases"
Requires-Dist: packaging (==26.0)
Requires-Dist: pluggy (==1.6.0)
Requires-Dist: psycopg (==3.3.3) ; extra == "postgres" or extra == "cockroach" or extra == "all-databases"
Requires-Dist: pyodbc (>=5.1.0,<6.0.0) ; extra == "mssql" or extra == "all-databases"
Requires-Dist: python-dotenv (>=1.2.1,<2.0.0)
Requires-Dist: typing_extensions (==4.15.0)
Requires-Dist: tzdata (==2025.3)
Project-URL: Homepage, https://github.com/AnirudhB3000/buildaquery
Project-URL: Repository, https://github.com/AnirudhB3000/buildaquery
Description-Content-Type: text/markdown

# Build-a-Query

A Python-based query builder designed to represent, compile, and execute SQL queries using a dialect-agnostic Abstract Syntax Tree (AST). Supports PostgreSQL, SQLite, DuckDB, MySQL, MariaDB, CockroachDB, Oracle, and SQL Server.

## Features

- **Dialect-Agnostic AST**: Build queries using high-level Python objects.
- **Full DML Support**: Create `SELECT`, `INSERT`, `UPDATE`, and `DELETE` statements.
- **Advanced Querying**: Support for CTEs (`WITH`), Subqueries, Set Operations (`UNION`, `INTERSECT`, `EXCEPT`), and Window Functions (`OVER`).
- **Concurrency Controls**: Optional `lock_clause` support on `SELECT` for row locking (`FOR UPDATE`, `FOR SHARE`, `NOWAIT`, `SKIP LOCKED`) with dialect-aware behavior.
- **Dialect-Aware Upsert**: `InsertStatementNode.upsert_clause` supports conflict handling across dialects (`ON CONFLICT`, `ON DUPLICATE KEY UPDATE`, and `MERGE`-based paths).
- **Write-Return Payloads**: `returning_clause` on `INSERT`/`UPDATE`/`DELETE` supports `RETURNING` and SQL Server `OUTPUT` equivalents.
- **Batch Writes**: `InsertStatementNode.rows` supports multi-row insert payloads, and executors expose `execute_many(...)` for driver-level bulk execution.
- **Rich Expression Logic**: Includes `CASE` expressions, `IN`, `BETWEEN`, and type casting.
- **Expanded DDL Support**: Table-level constraints (`PRIMARY KEY`, `UNIQUE`, `FOREIGN KEY`, `CHECK`), index statements (`CREATE INDEX`/`DROP INDEX`), and `ALTER TABLE` action paths.
- **Visitor Pattern Traversal**: Extensible architecture for analysis and compilation.
- **Secure Compilation**: Automatic parameterization to prevent SQL injection.
- **Execution Layer**: Built-in support for executing compiled queries via `psycopg` (PostgreSQL/CockroachDB), `duckdb` (DuckDB), `mysql-connector-python` (MySQL), `mariadb` (MariaDB), `oracledb` (Oracle), `pyodbc` (SQL Server), and the standard library `sqlite3` (SQLite).
- **Transaction APIs**: First-class transaction control with `begin()`, `commit()`, `rollback()`, `savepoint()`, `rollback_to_savepoint()`, and `release_savepoint()` across executors.
- **Normalized Error + Retry APIs**: Execution retry helpers (`execute_with_retry`, `fetch_all_with_retry`, `fetch_one_with_retry`, `execute_many_with_retry`) with normalized error types for deadlocks/serialization/lock timeouts/connection timeouts.
- **Connection Management Controls**: Executor lifecycle management (`close`, context manager), connect timeout configuration (`connect_timeout_seconds`), and pool hooks (`acquire_connection`, `release_connection`).
- **Observability Hooks**: Structured query observations plus lifecycle logging events (query/retry/transaction/connection) via `ObservabilitySettings`.

## OLTP Capabilities

The project includes first-class OLTP-oriented support across AST, compiler, execution, and tests:

- **Transactions**: `begin()`, `commit()`, `rollback()`, `savepoint(name)`, `rollback_to_savepoint(name)`, and `release_savepoint(name)`.
  - DuckDB note: savepoint APIs are runtime-version dependent; unsupported runtimes raise a clear executor `RuntimeError`.
- **Row locking controls**: lock clauses on `SELECT` with dialect-aware compilation (including `NOWAIT` and `SKIP LOCKED` where supported).
- **Upsert paths**: conflict-aware writes across dialects (`ON CONFLICT`, `ON DUPLICATE KEY UPDATE`, `MERGE` paths).
- **Write-return payloads**: `returning_clause` support (`RETURNING` and SQL Server `OUTPUT` equivalents).
- **Batch writes**: multi-row insert payloads via `InsertStatementNode.rows` and executor-level `execute_many(...)`.
- **Transient retry APIs**: `execute_with_retry(...)`, `fetch_all_with_retry(...)`, `fetch_one_with_retry(...)`, `execute_many_with_retry(...)` with `RetryPolicy`.
- **Normalized transient errors**: `DeadlockError`, `SerializationError`, `LockTimeoutError`, and `ConnectionTimeoutError`.
- **Connection lifecycle and pool hooks**: `close()`, context-manager control, `connect_timeout_seconds`, `acquire_connection`, and `release_connection`.
- **Observability hooks**: per-query timing (`query_observer`) and lifecycle logging events (`event_observer`) using `ObservabilitySettings`.
  - Built-in JSON logger helper: `make_json_event_logger(logger=...)` for one-line structured event logs.
  - Built-in adapters: `InMemoryMetricsAdapter`, `InMemoryTracingAdapter`, and `compose_event_observers(...)`.
- **OLTP integration coverage**: contention/retry correctness, deadlock normalization, lost-update prevention, isolation visibility semantics, and lock behavior validation.

## Dialect Notes
- MySQL does not support `INTERSECT` / `EXCEPT` or `DROP TABLE ... CASCADE` in this implementation (the compiler raises `ValueError`).
- SQLite does not support `DROP TABLE ... CASCADE` (the compiler raises `ValueError`).
- DuckDB does not support `DROP TABLE ... CASCADE` or trailing row-lock clauses (`FOR UPDATE` / `FOR SHARE`) in this compiler.
- Oracle does not support `IF EXISTS` / `IF NOT EXISTS` in `DROP TABLE`/`CREATE TABLE` (the compiler raises `ValueError`), and `EXCEPT` is compiled as `MINUS`.
- SQL Server does not support `EXCEPT ALL` / `INTERSECT ALL` or `DROP TABLE ... CASCADE` in this implementation (the compiler raises `ValueError`).
- MariaDB supports `INTERSECT` / `EXCEPT` (including `ALL`), and accepts `DROP TABLE ... CASCADE` (treated as a no-op).
- CockroachDB supports `INTERSECT` / `EXCEPT` (including `ALL`) and `DROP TABLE ... CASCADE`.
- Upsert behavior:
  - PostgreSQL, SQLite, CockroachDB: `ON CONFLICT (...) DO NOTHING` / `DO UPDATE`.
  - MySQL, MariaDB: `ON DUPLICATE KEY UPDATE` (no `DO NOTHING` mode in this AST contract).
  - Oracle, SQL Server: `MERGE`-based upsert generation from `InsertStatementNode` + `upsert_clause`.
- Write-return behavior:
  - PostgreSQL, SQLite, CockroachDB: `RETURNING ...` on `INSERT`/`UPDATE`/`DELETE`.
  - MariaDB: `RETURNING ...` on `INSERT`/`DELETE` (not `UPDATE` in this compiler).
  - SQL Server: `OUTPUT INSERTED...` / `OUTPUT DELETED...` compiled from `returning_clause`.
  - MySQL: generic `RETURNING` payloads are not supported in this compiler.
- Oracle: `RETURNING ... INTO` requires out-bind plumbing and is not yet supported.
- Batch insert behavior:
  - All dialect compilers support multi-row insert payloads through `InsertStatementNode.rows`.
  - Oracle compiles multi-row inserts via `INSERT ALL ... SELECT 1 FROM dual`.
  - SQL Server and Oracle `MERGE` upsert paths currently require single-row `values` (not `rows`).
- DDL constraint/index behavior:
  - `CreateStatementNode.constraints` supports table-level `PRIMARY KEY`, `UNIQUE`, `FOREIGN KEY`, and `CHECK`.
  - `CreateIndexStatementNode` / `DropIndexStatementNode` are available with dialect-specific syntax/guards.
  - `AlterTableStatementNode` supports add/drop column and add/drop constraint actions, with dialect-specific limitations (for example, SQLite `ALTER TABLE` is intentionally restricted in this compiler).
- Transaction and lock behavior:
  - Lock clause behavior is dialect-specific; integration tests validate supported semantics, including `NOWAIT` and `SKIP LOCKED` paths where available.
- Normalized retry behavior:
  - Retry helpers target transient classes (deadlock/serialization/lock timeout/connection timeout); non-transient failures are surfaced directly.

## Installation

### For Users

Install Build-a-Query via pip:

```bash
pip install buildaquery
```

Install with optional database driver extras as needed:

```bash
pip install "buildaquery[postgres]"
pip install "buildaquery[cockroach]"
pip install "buildaquery[mysql]"
pip install "buildaquery[mariadb]"
pip install "buildaquery[oracle]"
pip install "buildaquery[mssql]"
pip install "buildaquery[duckdb]"
pip install "buildaquery[all-databases]"
```

**Requirements:**
- Python 3.12+
- **PostgreSQL database**: A running PostgreSQL instance (version 12+ recommended). You can set this up locally, via Docker, or use a cloud service.
  - Example with Docker: `docker run --name postgres -e POSTGRES_PASSWORD=yourpassword -d -p 5432:5432 postgres:15`
- `psycopg` (install via `buildaquery[postgres]` or `buildaquery[cockroach]`) - the PostgreSQL/CockroachDB adapter for Python.
- **MySQL database**: A running MySQL instance (version 8.0+ recommended).
  - Example with Docker: `docker run --name mysql -e MYSQL_ROOT_PASSWORD=yourpassword -e MYSQL_DATABASE=buildaquery -d -p 3306:3306 mysql:8.0`
- `mysql-connector-python` (install via `buildaquery[mysql]`) - the MySQL adapter for Python.
- **MariaDB database**: A running MariaDB instance (MariaDB 10.3+ recommended).
  - Example with Docker (MariaDB): `docker run --name mariadb -e MARIADB_ROOT_PASSWORD=yourpassword -e MARIADB_DATABASE=buildaquery -d -p 3306:3306 mariadb:11.4`
- `mariadb` (install via `buildaquery[mariadb]`) - the MariaDB adapter for Python.
- **CockroachDB database**: A running CockroachDB instance.
  - Example with Docker (CockroachDB): `docker run --name cockroach -p 26257:26257 -p 8080:8080 cockroachdb/cockroach:v24.3.1 start-single-node --insecure`
- **Oracle database**: A running Oracle instance (Oracle XE is suitable for development).
  - Example with Docker (Oracle XE): `docker run --name oracle-xe -e ORACLE_PASSWORD=yourpassword -e APP_USER=buildaquery -e APP_USER_PASSWORD=yourpassword -d -p 1521:1521 gvenzl/oracle-xe:21-slim`
- `oracledb` (install via `buildaquery[oracle]`) - the Oracle adapter for Python.
- **SQL Server database**: A running SQL Server instance (Express is suitable for development).
  - Example with Docker (SQL Server Express): `docker run --name sqlserver -e ACCEPT_EULA=Y -e MSSQL_SA_PASSWORD=yourpassword -e MSSQL_PID=Express -d -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest`
- `pyodbc` (install via `buildaquery[mssql]`) - the SQL Server adapter for Python.
- `python-dotenv` (automatically installed as a dependency) - for loading environment variables from a `.env` file.
- **SQLite**: Uses Python's standard library `sqlite3` module.
  - **SQLite Version**: SQLite 3.x via Python's `sqlite3` module (the exact SQLite version depends on your Python build; check `sqlite3.sqlite_version` at runtime).
- **DuckDB**: Embedded OLAP database via the `duckdb` Python package.
  - Install driver via `buildaquery[duckdb]`.

### Environment Variables

To connect to your PostgreSQL database, set the following environment variables (or use a `.env` file with `python-dotenv`):

- `DB_HOST`: PostgreSQL host (e.g., `localhost`)
- `DB_PORT`: PostgreSQL port (e.g., `5432`)
- `DB_NAME`: Database name (e.g., `mydatabase`)
- `DB_USER`: Database username (e.g., `postgres`)
- `DB_PASSWORD`: Database password (e.g., `yourpassword`)

Example `.env` file:
```
DB_HOST=localhost
DB_PORT=5432
DB_NAME=buildaquery
DB_USER=postgres
DB_PASSWORD=yourpassword
```

For MySQL, you can use a connection URL directly in code (e.g., `mysql://user:password@host:3306/dbname`) or set your own environment variables and construct the URL similarly.

For Oracle, use a connection URL in the format `oracle://user:password@host:port/service_name` (for example: `oracle://buildaquery:password@127.0.0.1:1521/XEPDB1`).

For SQL Server, use a connection URL in the format `mssql://user:password@host:port/dbname?driver=...` (for example: `mssql://sa:password@127.0.0.1:1433/buildaquery?driver=ODBC+Driver+18+for+SQL+Server&encrypt=no&trust_server_certificate=yes`).

For MariaDB, use a connection URL in the format `mariadb://user:password@host:port/dbname` (for example: `mariadb://root:password@127.0.0.1:3306/buildaquery`).

For CockroachDB, use a connection URL in the format `postgresql://user@host:port/dbname?sslmode=disable` (for example: `postgresql://root@127.0.0.1:26257/buildaquery?sslmode=disable`).

For DuckDB, use a local database file path (for example: `static/test-duckdb/db.duckdb`) or `:memory:`.

### For Developers

Clone the repository and set up the development environment:

```bash
git clone https://github.com/yourusername/buildaquery.git
cd buildaquery
```

Install dependencies using Poetry:

```bash
poetry install
```

Activate the virtual environment:

```bash
poetry shell
```

## Quick Start

For the fastest first run, start with SQLite in the section below (`### SQLite Quick Start (Recommended)`), which requires no external database server.

### PostgreSQL Full CRUD Quick Start

Here's a full CRUD example for PostgreSQL using environment variables for connection setup (see Environment Variables section above).

```python
from dotenv import load_dotenv
import os
from buildaquery.execution.postgres import PostgresExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

# Load environment variables
load_dotenv()

# Build connection string from environment variables
db_host = os.getenv('DB_HOST')
db_port = os.getenv('DB_PORT')
db_name = os.getenv('DB_NAME')
db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')

connection_string = f"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"

# Set up executor with your PostgreSQL connection
executor = PostgresExecutor(connection_info=connection_string)

# Define table
users_table = TableNode(name="users")

# Create table
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="SERIAL", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="TEXT", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INTEGER")
    ]
)
executor.execute(create_stmt)

# Insert data
insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

# Query data
select_stmt = SelectStatementNode(
    select_list=[StarNode()],  # SELECT *
    from_table=users_table
)
results = executor.execute(select_stmt)
print(results)  # [(1, 'Alice', 30)]

# Drop table
drop_stmt = DropStatementNode(table=users_table, if_exists=True)
executor.execute(drop_stmt)
```

### SQLite Quick Start (Recommended)

```python
from buildaquery.execution.sqlite import SqliteExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = SqliteExecutor(connection_info="static/test-sqlite/db.sqlite")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="INTEGER", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="TEXT", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INTEGER")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, if_exists=True)
executor.execute(drop_stmt)
```

### DuckDB Quick Start

```python
from buildaquery.execution.duckdb import DuckDbExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = DuckDbExecutor(connection_info="static/test-duckdb/sample.duckdb")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="INTEGER", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="VARCHAR", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INTEGER")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, if_exists=True)
executor.execute(drop_stmt)
```

### MySQL Quick Start

```python
from buildaquery.execution.mysql import MySqlExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = MySqlExecutor(connection_info="mysql://root:password@127.0.0.1:3306/buildaquery")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="INT AUTO_INCREMENT", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="VARCHAR(255)", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INT")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, if_exists=True)
executor.execute(drop_stmt)
```

### Oracle Quick Start

```python
from buildaquery.execution.oracle import OracleExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = OracleExecutor(connection_info="oracle://buildaquery:password@127.0.0.1:1521/XEPDB1")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="NUMBER", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="VARCHAR2(255)", not_null=True),
        ColumnDefinitionNode(name="age", data_type="NUMBER")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="id"), ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value=1), LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, cascade=True)
executor.execute(drop_stmt)
```

### SQL Server Quick Start

```python
from buildaquery.execution.mssql import MsSqlExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = MsSqlExecutor(connection_info="mssql://sa:password@127.0.0.1:1433/buildaquery?driver=ODBC+Driver+18+for+SQL+Server&encrypt=no&trust_server_certificate=yes")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="INT", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="NVARCHAR(255)", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INT")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="id"), ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value=1), LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, if_exists=True)
executor.execute(drop_stmt)
```

### MariaDB Quick Start

```python
from buildaquery.execution.mariadb import MariaDbExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = MariaDbExecutor(connection_info="mariadb://root:password@127.0.0.1:3306/buildaquery")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="INT AUTO_INCREMENT", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="VARCHAR(255)", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INT")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, if_exists=True, cascade=True)
executor.execute(drop_stmt)
```

### CockroachDB Quick Start

```python
from buildaquery.execution.cockroachdb import CockroachExecutor
from buildaquery.abstract_syntax_tree.models import (
    CreateStatementNode, TableNode, ColumnDefinitionNode,
    InsertStatementNode, ColumnNode, LiteralNode,
    SelectStatementNode, StarNode, DropStatementNode
)

executor = CockroachExecutor(connection_info="postgresql://root@127.0.0.1:26257/buildaquery?sslmode=disable")

users_table = TableNode(name="users")
create_stmt = CreateStatementNode(
    table=users_table,
    columns=[
        ColumnDefinitionNode(name="id", data_type="INT", primary_key=True),
        ColumnDefinitionNode(name="name", data_type="STRING", not_null=True),
        ColumnDefinitionNode(name="age", data_type="INT")
    ]
)
executor.execute(create_stmt)

insert_stmt = InsertStatementNode(
    table=users_table,
    columns=[ColumnNode(name="id"), ColumnNode(name="name"), ColumnNode(name="age")],
    values=[LiteralNode(value=1), LiteralNode(value="Alice"), LiteralNode(value=30)]
)
executor.execute(insert_stmt)

select_stmt = SelectStatementNode(
    select_list=[StarNode()],
    from_table=users_table
)
print(executor.execute(select_stmt))

drop_stmt = DropStatementNode(table=users_table, if_exists=True, cascade=True)
executor.execute(drop_stmt)
```

For more examples, see the `examples/` directory (including `examples/sample_syntax_quickstart.py`, `examples/sample_duckdb.py`, `examples/sample_mysql.py`, `examples/sample_oracle.py`, `examples/sample_mssql.py`, `examples/sample_mariadb.py`, `examples/sample_cockroachdb.py`, `examples/sample_transactions.py`, `examples/sample_connection_management.py`, `examples/sample_observability.py`, and `examples/sample_observability_integration.py`).
For transaction control, see `examples/sample_transactions.py`.
For normalized retry/error handling, use `RetryPolicy` with `*_with_retry(...)` executor APIs.
For connection management patterns, see `examples/sample_connection_management.py`.
For observability hooks, see `examples/sample_observability.py`.
For app-level observability wiring (no library source edits), see `examples/sample_observability_integration.py`.
For upsert patterns, see `examples/sample_upsert.py`.
For write-return payloads, see `examples/sample_returning.py`.
For batch writes, see `examples/sample_batch_write.py`.
For DDL constraints/indexes and `ALTER TABLE`, see `examples/sample_ddl_constraints.py`.

## Development Setup

### Prerequisites

- Python 3.12+
- Poetry (for dependency management)
- Docker (for running integration tests)

### Setting Up the Environment

1. Clone the repository:
   ```bash
   git clone https://github.com/yourusername/buildaquery.git
   cd buildaquery
   ```

2. Install dependencies:
   ```bash
   poetry install
   ```

3. Activate the virtual environment:
   ```bash
   poetry shell
   ```

### Running Tests

#### Unit Tests

Run unit tests for all modules:

```bash
poetry run pytest buildaquery/tests
```

#### Integration Tests

Integration tests require PostgreSQL, MySQL, MariaDB, CockroachDB, Oracle, and SQL Server databases (and the respective drivers). DuckDB and SQLite integration tests use local database files.
Start the server-based test databases using Docker:

```bash
docker-compose up -d
```

Then run integration tests:

```bash
poetry run pytest tests
```

SQLite integration tests use the file-based database at `static/test-sqlite/db.sqlite`.
CockroachDB integration tests use the SQL port `26258` by default (see `tests/README.md` for the full URL and override env var).
OLTP-focused integration coverage includes contention/retry correctness, deadlock normalization, lost-update prevention patterns, isolation visibility semantics, and row-lock behavior (`NOWAIT`/`SKIP LOCKED`).
See `tests/test_oltp_integration.py` for the dedicated OLTP integration scenarios.

#### All Tests

Run all tests (unit and integration):

```bash
poetry run all-tests
```

#### Package Checks (PyPI Readiness)

Build source/wheel artifacts and validate distribution metadata:

```bash
poetry run package-check
```

### Running Examples

Execute a sample script:

```bash
poetry run python examples/sample_syntax_quickstart.py
```

## Project Structure

- `buildaquery/abstract_syntax_tree/`: Defines query nodes and AST models.
- `buildaquery/traversal/`: Base classes for AST traversal (Visitor/Transformer pattern).
- `buildaquery/compiler/`: Dialect-specific SQL generation (PostgreSQL, SQLite, DuckDB, MySQL, MariaDB, CockroachDB, Oracle, SQL Server).
- `buildaquery/execution/`: Database connection and execution logic.
- `tests/`: Exhaustive unit and integration tests.
- `examples/`: Practical demonstrations of the library.
- `scripts/`: Utility scripts for testing and maintenance.

## Contributing

Contributions are welcome! Please see the contributing guidelines for more information.

For package release steps, see [RELEASES.md](RELEASES.md).

## License

This project is licensed under the MIT License - see the [LICENSE.txt](LICENSE.txt) file for details.

