PostgreSQL Query Builder

Typed Python PostgreSQL query builder ✨

---

Source Code: https://github.com/yezz123/pgqb

Documentation: TBD

---

pgqb is a Python library for building SQL queries for PostgreSQL databases. It provides a simple and intuitive interface for constructing SQL statements using functions like delete, insert_into, select, and update. This README provides a brief overview of how to use pgqb to build queries and execute them safely with parameter binding.

Installation

You can install pgqb via pip:

pip install pgqb

Project using

from pgqb import Column, Table, and_, join, select


class User(Table):
    id = Column()
    first = Column()
    last = Column()


class Task(Table):
    id = Column()
    user_id = Column()
    value = Column()


sql, params = (
    select(
        User.id,
    )
    .from_(User, join(Task).on(Task.user_id == User.id))
    .where(User.id == 1, and_(Task.id == 1))
    .order_by(
        Task.value.desc(),
    )
).prepare()

expected = " ".join(
    [
        'SELECT "user".id',
        'FROM "user"',
        'JOIN "task" ON "task".user_id = "user".id',
        'WHERE "user".id = ?',
        'AND "task".id = ?',
        'ORDER BY "task".value DESC',
    ]
)

print(sql == expected)

# >>> True

Create Table

from pgqb import Column, Table, TEXT, TIMESTAMP, UUID


class User(Table):
    id = Column(UUID(), primary=True)
    email = Column(TEXT(), unique=True, index=True)
    first = Column(TEXT())
    last = Column(TEXT())
    verified_at = Column(TIMESTAMP(with_time_zone=True))

print(User.create_table())

# CREATE TABLE IF NOT EXISTS "user" (
#   "id" UUID,
#   "email" TEXT NOT NULL UNIQUE,
#   "first" TEXT NOT NULL,
#   "last" TEXT NOT NULL,
#   "verified_at" TIMESTAMP WITH TIME ZONE NOT NULL,
#   PRIMARY KEY (id)
# );
# CREATE INDEX ON "user" (email);

Development

Setup environment

You should create a virtual environment and activate it:

Notes: You need to have python3.9 or higher installed.

I Use uv to manage virtual environments, you can install it with:

# Install uv
pip install uv

# Create a virtual environment
uv venv

# Activate the virtual environment
source .venv/bin/activate

And then install the development dependencies:

# Install dependencies
uv pip install -e .[test,lint]

Run tests 🌝

You can run all the tests with:

bash scripts/tests.sh

Format the code 🍂

Execute the following command to apply pre-commit formatting:

bash scripts/format.sh

Execute the following command to apply mypy type checking:

bash scripts/lint.sh

License 🍻

This project is licensed under the terms of the MIT license.