buildaquery 1.1.2

A Python SQL query builder with cross-dialect execution primitives and OLTP support.

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, 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), 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).
• 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).
• 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:

pip install buildaquery

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 (automatically installed as a dependency) - the PostgreSQL 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 (automatically installed as a dependency) - 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 (automatically installed as a dependency) - 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 (automatically installed as a dependency) - 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 (automatically installed as a dependency) - 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).

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 Developers

Clone the repository and set up the development environment:

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

Install dependencies using Poetry:

poetry install

Activate the virtual environment:

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).

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)

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)

MySQL Quick Start

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

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

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

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

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_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:

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

2. Install dependencies:

   poetry install
   

3. Activate the virtual environment:

   poetry shell
   

Running Tests

Unit Tests

Run unit tests for all modules:

poetry run pytest buildaquery/tests

Integration Tests

Integration tests require PostgreSQL, MySQL, MariaDB, CockroachDB, Oracle, and SQL Server databases (and the respective drivers). Start the test databases using Docker:

docker-compose up -d

Then run integration tests:

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):

poetry run all-tests

Package Checks (PyPI Readiness)

Build source/wheel artifacts and validate distribution metadata:

poetry run package-check

Running Examples

Execute the sample script:

poetry run python examples/sample_query.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, 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.

License

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