buildaquery 1.1.0

A Python-based query builder for PostgreSQL, SQLite, MySQL, and Oracle.

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 per-query observations (timing, operation, success/failure, tracing-safe metadata) 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 and structured execution signals using ObservabilitySettings.
• 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

Here's a simple example of creating a table, inserting data, querying it, and dropping the table. This example uses environment variables for database connection (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

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_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, and examples/sample_observability.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 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

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.

License

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