PormG.jl

PormG.jl is a Django-inspired ORM for Julia with async-first execution, designed for high-concurrency web frameworks like Genie.jl. It provides an expressive query builder, automatic migrations, and cross-database support for PostgreSQL and SQLite, and MySQL 8.4 or newer.

📚 Documentation

Full documentation, tutorials, and API reference: https://pingolee.github.io/PormG.jl/dev/

Key Features

• Async-first — on PostgreSQL, non-blocking I/O via LibPQ.async_execute; on SQLite, one process-wide worker runs every statement in turn, and the calling task yields while it waits. Synchronous helpers are thin wrappers that never block the event loop
• Django-style filter syntax — __ for join traversal, __@gt / __@in / __@contains for operators
• F-expressions — column arithmetic and field-to-field comparisons: F("points") > F("grid")
• Aggregation & annotation — Count, Sum, Avg, Max, Min with GROUP BY / HAVING handled automatically
• Migrations — state-based schema reconciliation with makemigrations / migrate, destructive-operation guards, and a history table
• Multi-database & multi-tenancy — switch connections at runtime with .db("tenant_id")
• Advisory locks — distributed coordination via with_advisory_lock (PostgreSQL; on SQLite the body runs unlocked, with a warning — see Backend Capabilities)
• Transactions — run_in_transaction with savepoint support and async context propagation

---

Requirements

• A supported backend: PostgreSQL (production), SQLite (development and tests), or MySQL 8.4 or newer (an older server is refused when a connection opens; see the MySQL Guide). What each backend supports, and the guarantees that rest on the engine, are in Backend Capabilities
• SQLite 3.24 or newer for ON CONFLICT (bulk_insert(on_conflict = …), update_or_create, get_or_create; not checked up front, so an older library raises SQLite's own error) and 3.25 for window functions (checked against the library PormG loads)
• Julia 1.13 or newer — the only version CI tests. The hot-reload model-loading machinery (@import_models / set_models) relies on the world-age semantics Julia 1.12 introduced, so the 1.10 LTS is not targeted (#211)
• PostgreSQL 9.6 or newer to read the schema — inspectdb, import_models_from_postgres and check (below it, status skips its drift check); a catalog column newer than 9.6 is read only where it exists, and its feature reads as absent on an older server (#1162). PostgreSQL 11 or newer for a migration plan — makemigrations, migrate and check(kinds = [:schema_drift]). An older server is refused up front with BackendCapabilityError. A plan also asks for what its statements need: adding a generated column (generated_from) needs PostgreSQL 12, and removing generated_from (ALTER COLUMN … DROP EXPRESSION) needs 13. makemigrations and migrate refuse such a plan on an older server, naming the statement, before any statement runs (#1032, #1146)
• PostgreSQL: no stated floor for queries and writes — filter, list, create, update, bulk_* and delete use ordinary SQL, not the catalog columns schema management reads, so a server older than 11 whose schema is managed elsewhere can still be read and written. That is best effort: they are tested only against the PostgreSQL version the integration suite runs. A feature that needs a newer server fails with PostgreSQL's own error when the query runs; GROUPS window frames and search_type = "websearch" need PostgreSQL 11 (#1097; see Server versions)
• A SQL driver for your backend — LibPQ (PostgreSQL), SQLite, or MySQL 2.0 (MySQL). The pure-Julia Postgres driver is a supported alternative to LibPQ, which stays the default: select it with postgres_driver: Postgres (see Choosing the PostgreSQL driver)
• Decimals is declared as "0.4, 0.5" rather than the newest major alone, and that is deliberate: every LibPQ release through 1.18.0 requires Decimals 0.4, so declaring "0.5" alone leaves the default PostgreSQL driver with no installable version and any app depending on both fails to resolve (#558). PormG builds a Decimals.Decimal only through the three-argument Decimal(sign, coefficient, exponent) constructor, the one spelling both majors share — parse(Decimal, "1.23456789e6") throws on 0.4.1, and 0.5 prints a positive exponent as 1E+1 — so an environment carrying LibPQ resolves 0.4.x, a driver-free or SQLite-only one resolves 0.5.x, and PormG works the same either way (#648)
• OrderedCollections is declared as "1, 2" rather than the newest major alone, and that is deliberate too: an app whose environment pins a package that caps OrderedCollections at 1 — XLSX accepts 2 only from 0.12, Genie only from 6 — cannot take OC 2, so declaring "2" alone would make such an app unresolvable the moment it bumps PormG, a break no PormG environment can observe (#560). PormG constructs OrderedDict / OrderedSet explicitly and with the element type spelled out everywhere, and uses no API that differs between the majors (#549), so an app whose graph caps it resolves 1.x, an unconstrained one resolves 2.x, and PormG works the same either way. Spelling the element type is not decoration: OrderedDict(k => v for …) only infers a concrete type from OC 1.3 — below that it yields OrderedDict{Any, Any} — so the version that bites is a minor, not the 1/2 major boundary everyone watches (#574)

Installation

PormG is not yet registered in the Julia General Registry. Install the development version:

using Pkg
Pkg.add(url="https://github.com/PingoLee/PormG.jl")

Once registered, this becomes:

using Pkg
Pkg.add("PormG")

PormG does not pull in a SQL driver automatically — LibPQ (PostgreSQL) and SQLite are weak dependencies. Install and load the one your app uses as a direct dependency:

Pkg.add("LibPQ")     # PostgreSQL
Pkg.add("SQLite")    # SQLite
# Pkg.add("Postgres") # PostgreSQL through Postgres.jl — opt in with `postgres_driver: Postgres`

A bare using PormG loads the ORM but no backend; Configuration.load then raises a clear error telling you to using LibPQ (or using SQLite).

PormG depends on neither DataFrames nor CSV (#1344). A query is a Tables.jl table and the bulk writers take any Tables.jl table, so query |> DataFrame and bulk_insert(…, df) work once your app adds the package it uses (Pkg.add("DataFrames")) and loads it, as the examples below do with using DataFrames.

---

Quick Start

1. Scaffold a project (optional)

using PormG
PormG.setup()   # interactive: writes db/connection.yml and a db/models.jl skeleton

Prefer to wire it up by hand? The next steps show the files setup() would create.

2. Configure the connection

db/connection.yml:

default_env: dev

dev:
  adapter: PostgreSQL       # or SQLite
  database: your_database
  host: 'localhost'
  username: your_username
  password: your_password
  port: 5432
  config:
    change_db: true         # allow schema migrations
    change_data: true       # allow data mutations
    time_zone: 'America/Sao_Paulo'

Note: If config: is omitted, PormG applies safety-first defaults — change_db and change_data are false (migrations and writes disabled). Set them to true explicitly to enable schema changes and data mutations.

3. Define models

# db/models.jl
module models
import PormG.Models

Driver::Models.ModelType = Models.Model("drivers",
    driverid    = Models.IDField(),
    forename    = Models.CharField(max_length=50),
    surname     = Models.CharField(max_length=50),
    nationality = Models.CharField(max_length=50),
    dob         = Models.DateField(null=true),
)

Constructor::Models.ModelType = Models.Model("constructors",
    constructorid = Models.IDField(),
    name          = Models.CharField(max_length=50),
    nationality   = Models.CharField(max_length=50),
)

Race::Models.ModelType = Models.Model("races",
    raceid = Models.IDField(),
    name   = Models.CharField(max_length=255),
    year   = Models.IntegerField(),
    date   = Models.DateField(),
)

Result::Models.ModelType = Models.Model("results",
    resultid      = Models.IDField(),
    raceid        = Models.ForeignKey(Race, pk_field="raceid", on_delete="CASCADE"),
    driverid      = Models.ForeignKey(Driver, pk_field="driverid", on_delete="RESTRICT"),
    constructorid = Models.ForeignKey(Constructor, pk_field="constructorid", on_delete="RESTRICT"),
    positionorder = Models.IntegerField(),
    points        = Models.FloatField(),
)
end

Note: PormG preserves the case you declare field names with, and field lookups are case-sensitive. The recommended house style is lowercase snake_case; reserve mixed-case declarations for mapping existing columns you don't control.

4. Load configuration and models

using PormG, LibPQ, DataFrames          # LibPQ → PostgreSQL; swap in SQLite for the SQLite backend
using PormG.Functions: Count            # SQL functions (Count, Sum, Max, …) — namespaced

PormG.Configuration.load("db"; env = "dev")   # the dev block of db/connection.yml (must precede @import_models)
PormG.@import_models "db/models.jl" models
import .models as M

5. Run migrations

PormG.Migrations.makemigrations("db")   # analyze models, generate a migration
PormG.Migrations.migrate("db")          # apply it to the database

6. Query the database

# All Brazilian race winners — INNER JOINs resolved automatically from the __ paths
df = M.Result.objects.filter(
        "driverid__nationality" => "Brazilian",
        "positionorder"         => 1,
    ).values(
        "driverid__forename",
        "driverid__surname",
        "raceid__year",
        "raceid__name",
    ).order_by("-raceid__year") |> DataFrame

# Wins per constructor, using aggregation (GROUP BY handled automatically)
df = M.Result.objects.filter(
        "positionorder" => 1
    ).values(
        "constructorid__name",
        "wins" => Count("resultid"),
    ).order_by("-wins") |> DataFrame

Julia method-chain gotcha: multi-line chains must use trailing-dot syntax (the . at the end of the line) or stay inline. A leading dot on the next line is a Julia ParseError.

# ✓ trailing dot
df = M.Driver.objects.
    filter("nationality" => "Brazilian").
    list()

# ✗ leading dot → ParseError
df = M.Driver.objects
    .filter("nationality" => "Brazilian")
    .list()

7. Create and update records

# Single insert
M.Driver.objects.create(
    "forename"    => "Ayrton",
    "surname"     => "Senna",
    "nationality" => "Brazilian",
)

# Bulk insert from a DataFrame — call on the .objects handler to respect the ORM boundary
df = DataFrame([
    Dict("forename" => "Alain",  "surname" => "Prost",  "nationality" => "French"),
    Dict("forename" => "Nelson", "surname" => "Piquet", "nationality" => "Brazilian"),
])
bulk_insert(M.Driver.objects, df)

# Update matching rows in place
M.Driver.objects.filter("nationality" => "Brazilian").update("nationality" => "Brazil")

# Atomic F-expression update (no read-modify-write race)
M.Result.objects.filter("resultid" => 1).update("points" => F("points") + 10)

---

Django Compatibility

PormG is wire-format compatible with tables managed by Django, with identical column serialization and mutation semantics across PostgreSQL and SQLite — TIMESTAMPTZ handling, DateField truncation, DecimalField precision (NUMERIC-backed; at most 15 digits on SQLite, which has no exact decimal type), and auto_now / auto_now_add temporal fields. See Import from Django for details.

---

Contributing

Contributions are welcome — please open an issue or pull request on GitHub. CONTRIBUTING.md has the architecture map and the test commands; the Contributing & Debugging page has the development workflow, debugging guide, and testing conventions.

License

MIT License — see LICENSE.