Pormg Usage

2. Defining Models

Models live in a module (typically db/models.jl). Use Models.Model(...) and call set_models at the end.

module models
import PormG.Models

# Table name is the first positional argument (lowercase, snake_case)
Driver = Models.Model("drivers",
    driverid    = Models.IDField(),
    driverref   = Models.CharField(max_length=255),
    forename    = Models.CharField(max_length=50),
    surname     = Models.CharField(max_length=50),
    nationality = Models.CharField(max_length=50, null=true),
    dob         = Models.DateField(null=true),
    code        = Models.CharField(max_length=3, null=true),
    number      = Models.IntegerField(null=true),
    url         = Models.URLField(null=true),
)

Constructor = Models.Model("constructors",
    constructorid = Models.IDField(),
    name          = Models.CharField(max_length=255),
    nationality   = Models.CharField(max_length=50, null=true),
)

Race = Models.Model("races",
    raceid      = Models.IDField(),
    year        = Models.IntegerField(),
    name        = Models.CharField(max_length=255),
    date        = Models.DateField(),
    circuitid   = Models.ForeignKey("Circuit", on_delete="CASCADE"),
)

Result = Models.Model("results",
    resultid      = Models.IDField(),
    raceid        = Models.ForeignKey("Race", on_delete="CASCADE"),
    driverid      = Models.ForeignKey("Driver", on_delete="RESTRICT"),
    constructorid = Models.ForeignKey("Constructor", on_delete="RESTRICT"),
    positionorder = Models.IntegerField(),
    points        = Models.FloatField(null=true),
    laps          = Models.IntegerField(null=true),
    grid          = Models.IntegerField(null=true),
)

Models.set_models(@__MODULE__, @__DIR__)  # Always required at end
end

Naming Conventions (Mandatory)

• Models: Capitalized, singular, snake_case for multi-word: Driver, Race, Order_item
• Field names: Lowercase, snake_case: first_name, created_at
• Never use __ in field or table names — reserved for ORM join traversal
• Prefix reserved Julia keywords: _id, _type, _end

3. Field Types Reference

Common parameters (all fields): null=false, blank=false, unique=false, default=nothing, db_index=false, db_column=nothing, editable=true

on_delete options: "CASCADE", "RESTRICT", "PROTECT", "SET_NULL", "SET_DEFAULT", "DO_NOTHING"

4. Migrations

Always follow this ordered flow:

using PormG

# 1. First-time initialization (safe to run on existing DBs)
PormG.Migrations.init_migrations("db")

# 2. Check current state
PormG.Migrations.status("db")

# 3. Generate migration plan from current models
PormG.Migrations.makemigrations("db")

# 4. Dry-run: review SQL before executing (check for destructive ops)
PormG.Migrations.dry_run("db")

# 5. Apply migrations
PormG.Migrations.migrate("db")

# For destructive changes (column drops, renames), opt in explicitly:
PormG.Migrations.migrate("db", destructive=true)

Never skip dry_run() before a destructive migration. If it reports DROP columns, require explicit approval.

5. Reading Data — Query Patterns

Core pattern

query = M.Driver.objects

query.filter("nationality" => "Brazilian")
query.order_by("surname")
query.limit(10)

rows = query.list()         # Vector{Dict{Symbol, Any}}
df   = query |> DataFrame   # DataFrames.DataFrame

Chainable methods

Terminal (execution) methods

6. Joins and Lookups

Relationship traversal

Use __ to traverse ForeignKey relationships:

# Filter by joined field
M.Result.objects.filter("driverid__nationality" => "British")

# Deep traversal
M.Result.objects.filter("raceid__circuitid__country" => "Monaco")

# Select joined fields
M.Result.objects
    .values("driverid__forename", "driverid__surname", "raceid__year", "points")
    .order_by("-points")

Filter operators

Append __@operator to any field path:

Date transforms

7. Complex Filters: Q Objects

Use Q() for AND logic and Qor() for OR logic when .filter() pairs are insufficient:

using PormG: Q, Qor

# OR: British OR Brazilian drivers
M.Driver.objects.filter(Qor("nationality" => "British", "nationality" => "Brazilian"))

# Combined AND + OR
M.Result.objects.filter(
    Q("points__@gt" => 10),
    Qor("driverid__nationality" => "British", "driverid__nationality" => "Brazilian")
)

8. F-Expressions

F("fieldname") creates a database-side field reference. Use for atomic updates and computed columns.

using PormG: F

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

# Field-to-field copy
M.Result.objects.filter("resultid" => 5).update("grid" => F("positionorder"))

# Computed column in SELECT
M.Result.objects.values(
    "driverid__surname",
    "bonus" => F("points") * 0.1
)

# Field-to-field comparison in filter
M.Result.objects.filter(F("grid") == F("positionorder"))

# Cross-join arithmetic
M.Result.objects.filter(
    F("driverid__dob__@month") == F("raceid__date__@month")
)

9. Writing Data

Create a single record

driver = M.Driver.objects.create(
    "forename"    => "Ayrton",
    "surname"     => "Senna",
    "nationality" => "Brazilian",
    "driverref"   => "senna"
)
# Returns Dict with all fields including the new PK

Update matching records

M.Driver.objects
    .filter("driverid" => 1)
    .update("nationality" => "Brazil")

# With F-expression (atomic)
M.Result.objects
    .filter("resultid__@in" => [1, 2, 3])
    .update("points" => F("points") * 1.1)

Delete records

# Delete with filter
M.Result.objects.filter("raceid__year__@lt" => 1960).delete()

# Inspect before deleting (never executes)
sql = M.Result.objects.filter("raceid" => 999).delete(show_query=:sql)

# Delete all (requires explicit opt-in to prevent accidents)
M.Just_a_test_deletion.objects.delete(allow_delete_all=true)

10. Bulk Operations

For large datasets, always use bulk operations instead of loops:

using CSV, DataFrames

# Standard batch insert (all dialects)
df = CSV.File("drivers.csv") |> DataFrame
bulk_insert(M.Driver.objects, df)
bulk_insert(M.Driver.objects, df, chunk_size=500)  # custom chunk size

# Ultra-fast COPY (PostgreSQL only — 10-100x faster than bulk_insert)
bulk_copy(M.Driver.objects, df)
bulk_copy(M.Driver.objects, df, chunk_size=10_000)

# Map DataFrame columns to model fields
bulk_copy(M.Driver.objects, df, columns=[
    "first_name" => "forename",
    "last_name"  => "surname"
])

# Batch update from DataFrame
bulk_update(M.Result.objects, df,
    columns = ["points"],     # fields to SET
    filters = ["resultid"]    # fields to match on (WHERE)
)

Pre-process CSV nulls before bulk operations:

for col in [:position, :milliseconds, :rank]
    df[!, col] = map(x -> ismissing(x) || x == "\\N" ? missing : x, df[!, col])
end

11. Transactions

using PormG

# Wrap multiple operations in a single atomic transaction
PormG.run_in_transaction("db") do
    M.Race.objects.create("year" => 2025, "name" => "New Race", "date" => today())
    bulk_insert(M.Result.objects, results_df)
end
# All operations commit together, or all roll back on error

12. Query Inspection & Debugging

Every terminal method accepts show_query:

query = M.Driver.objects.filter("nationality" => "British")

# Inspect SQL
sql = query.list(show_query=:sql)

# Full metadata (useful for mutations — update, delete, bulk ops)
meta = query.update("nationality" => "GB", show_query=:dict)
println(meta[:sql])
println(meta[:parameters])

# Inspect SELECT queries without executing
using PormG: inspect_query
inspection = query |> inspect_query()
println(inspection[:sql])
println(inspection[:dialect])

13. Multi-Database & Multi-Tenancy

# Route a single query to a different connection pool
M.Driver.objects.filter("nationality" => "British").db("reporting").list()

# Load multiple connections
PormG.Configuration.load_many(["db/conn_primary.yml", "db/conn_replica.yml"])

14. Anti-Patterns