Hi everyone,
With the upcoming Julia 1.13 release, LibPQ.jl seems to be showing signs of being unmaintained, and there are breaking/compatibility issues emerging.
What is the current status of the package within JuliaDatabases? Are there plans to transfer maintenance, cut a new release, or is the community rallying around an alternative PostgreSQL interface?
I’d appreciate any insights into the roadmap or how to help move it forward
What are the compatibility issues you see? I’ve been using LibPQ recently, including with the 1.13 Julia release, and haven’t encountered a problem so far.
I’ve used AI to fix my bad english in the last post and got a ban. So, in my bad english now…
“Broken on 1.13” was an unhappy phrase. But the package has pinned apps like Decimals, and others.
So, this could be a problem in the near future.
Your post reminded me I’ve been meaning to publicly announce GitHub - JuliaDatabases/Postgres.jl · GitHub, a native-Julia implementation of the Postgres wire protocol. I’ve been using it in a few production apps for well over a year, so a lot of the basics work great, but recently (with help of AI!), I’ve been trying to build out additional support for features. Try it out if you’re interested!
How is interoperability with FunSQL.jl?
Sorry @quinnj, looking at your project, I see you want to build a pure Julia app.
However, TLS is a real blocker for me. Would a pluggable TLS provider (like OpenSSL.jl) be feasible to you? If so, I can draft a proposal.
Should work fine! As far as I understand, FunSQL.jl is based on DBInterface.jl, which Postgres.jl fully supports.
Of course! Always welcome contributions via either issue reports or PRs.
Postgres.jl already fully supports TLS via the Réseau.jl package, a native Julia TCP/TLS library.
Thank you for sharing @quinnj .
Do you plan to support Kerberos / GSSAPI authentication?
I’ve been briefly testing out Postgres.jl and so far so good. One question - will you be adding the ability to set the default schema in the connection string? In LibPQ.Connection, one can pass in the options=-csearch_path=<schema_name> parameter.
Thanks!
I developed a private fork of LibPQ.jl to support PostgreSQL 18 OAuth authentication, if that is of interest to anyone. I didn’t make changes to any other aspect of LibPQ.
I would appreciate it if you could share it or, even better, make your repository public.
Yes, this is now supported as of the 2.1.0 release.
Yes, this is also supported as of the 2.1.0 release.
Indeed it does. Thank you!
Awesome!
Thank you very much.
It requires this version of LibPQ_jll.jl compiled against PostgreSQL v18
This provides helper functions to make the OAuth use simpler:
These have only been tested with ORCID authentication.
Hi @Rafael_Brus Your work on PormG.jl looks awesome! The other Julia ORM packages, AFAIK, are PostgresORM.jl and Jorm.jl. But I haven’t tried either. How does PormG.jl compare with these?
Your documentation is very full indeed, which is great to see. A long time ago I used Java Persistence API, Hybernate and Glassfish (for a physics-related project). I found ORM was a great way to organise code in Java.
It strikes me that you could store physical models from (say) ModelingTooolkit.jl neatly in a relational form. This would nicely de-couple data from function. My LibPQ - PostgreSQL - Julia experience was essentially in constructing a dynamic (not just kinematic) model of a robot arm from from a set of “components” (including gearboxes etc) from a database, auto-magically, into a RigidBodyDynamics.jl model. The idea was that the physical data could be updated via a web app without that person needing to know anything about Julia. The schema was at worst in 3rd (Boyce/Codd) normal form so that the components were recognisable to the technicians and could be connected or disconnected at will. But there was no Julia ORM at that time, just LibPQ.jl
I just wish you would change PormG’s name as it rhymes too closely with something unpleasant! E.g. just “ORM.jl” ? Julia has structs, types, and functors but I am not sure that the “O” in ORM is a perfect match. You seem to be mapping functions to database tables. I have started having a play and, after a couple of weeks, I may report back!
Hi @Roger_Powell,
I’ve been working on this project for a couple of years. I work with Django web apps for public health; Django is amazing, but Python is slow, so initially I built an API server using Genie.jl.
At that time, AI didn’t exist, and I was looking for syntax similar to the Django ORM; then, with my lack of imagination, I named it as PingoLee ORM form Genie.jl
But I’m facing a lot of gaps in time and knowledge, so with AI, I’m starting another project, Nitro.jl (a fork of Oxygen.jl). Initially, I’m tried to contribute to both (Genie and Oxygen), but I didn’t have much luck.
So, Nitro.jl is a framework to build SPA web packages and API packages. Nitro.jl is getting very fast; PormG.jl is still targeting features, so i haven’t started performance optimizations yet. Both are made to be async.
PormG.jl has a very large collection of features (and a lot more to implement), and it was built to work with AI, including importing models directly from Postgres or Django Python models.
However, neither is registered yet; it’s 0.x with breaking changes between releases. To make those breaking changes cheap, every change ships as a machine-readable upgrade entry (more below).
You can do:
using Pkg
Pkg.add(url="https://github.com/PingoLee/PormG.jl"); Pkg.add("LibPQ")
using PormG, LibPQ
PormG.setup() # db/connection.yml + db/models.jl
PormG.Configuration.load("db")
PormG.Migrations.import_models_from_postgres("db") # reverse-engineer your existing schema
PormG.@import_models "db/models.jl" models
import .models as M
Using it with an AI agent (Claude Code, Copilot, Cursor):
PormG.install_ai_skills() # writes .github/skills/pormg-usage/ into your project
Then ask this for your agent:
Before bumping the PormG dependency, run PormG.upgrade_guide(from = v"<current pin>") and apply what it lists.
Usage examples
Using if:
using PormG, LibPQ, DataFrames
using PormG: Q, Qor
function search_results(; year = nothing, nationality = nothing,
podium_only = false, team = nothing, limit = 20)
query = M.Result.objects
query.values("raceid__year", "raceid__name", "driverid__surname",
"constructorid__name", "positionorder", "points")
if year !== nothing
query.filter("raceid__year" => year)
end
if nationality !== nothing
query.filter("driverid__nationality" => nationality)
end
if podium_only
query.filter("positionorder__@lte" => 3)
end
if team !== nothing
# either the team name or its reference
query.filter(Qor("constructorid__name" => team, "constructorid__constructorref" => team))
end
query.order_by("-raceid__year", "positionorder")
query.limit(limit)
return query |> DataFrame
end
With the same function, you can combine the filters in countless ways:
search_results(nationality = "Brazilian", podium_only = true)
search_results(nationality = "Brazilian", podium_only = true, year = 1988)
search_results(nationality = "Brazilian", team = "McLaren")
search_results(year = 2023, podium_only = true, team = "Red Bull")
search_results(nationality = "British", year = 2020, limit = 5)
search_results() # no filters, latest 20 results
At last:
# pormg_demo.jl — self-contained PormG demo on SQLite.
#
# julia pormg_demo.jl (or, in the REPL: include("pormg_demo.jl"))
#
# First time only:
# using Pkg
# Pkg.add(url = "https://github.com/PingoLee/PormG.jl"); Pkg.add(["SQLite", "DataFrames"])
using PormG, SQLite, DataFrames, Dates
using PormG.Functions: Count, Sum, Avg, Min, Case, When
# ── 1. A throwaway project folder in your home directory ─────────────────────
demo_dir = joinpath(homedir(), "pormg_demo")
rm(demo_dir; recursive = true, force = true) # fresh run every time
mkpath(joinpath(demo_dir, "db"))
cd(demo_dir)
write("db/connection.yml", """
default_env: dev
dev:
adapter: SQLite
database: f1_demo.sqlite
config:
change_db: true
change_data: true
time_zone: 'UTC'
""")
write("db/models.jl", """
module models
import PormG.Models
Circuit = Models.Model(
circuitid = Models.IDField(),
name = Models.CharField(max_length = 100),
country = Models.CharField(max_length = 50),
)
Driver = Models.Model(
driverid = Models.IDField(),
forename = Models.CharField(max_length = 50),
surname = Models.CharField(max_length = 50),
nationality = Models.CharField(max_length = 50),
)
Constructor = Models.Model(
constructorid = Models.IDField(),
constructorref = Models.CharField(max_length = 50),
name = Models.CharField(max_length = 50),
)
Race = Models.Model(
raceid = Models.IDField(),
year = Models.IntegerField(),
name = Models.CharField(max_length = 100),
date = Models.DateField(),
circuitid = Models.ForeignKey(Circuit, on_delete = "CASCADE"),
)
Result = Models.Model(
resultid = Models.IDField(),
raceid = Models.ForeignKey(Race, on_delete = "CASCADE"),
driverid = Models.ForeignKey(Driver, on_delete = "RESTRICT"),
constructorid = Models.ForeignKey(Constructor, on_delete = "RESTRICT"),
grid = Models.IntegerField(),
positionorder = Models.IntegerField(),
points = Models.FloatField(),
)
Models.set_models(@__MODULE__, @__DIR__)
end
""")
# ── 2. Configuration, models, schema ─────────────────────────────────────────
# Absolute path: under include(), a relative "db" would resolve next to this script, not in demo_dir
db = joinpath(demo_dir, "db")
PormG.Configuration.load(db)
include(joinpath(db, "models.jl"))
import .models as M
PormG.Migrations.init_migrations(db)
PormG.Migrations.makemigrations(db; interactive = false)
PormG.Migrations.migrate(db; interactive = false)
# ── 3. Seed a small slice of real F1 history ─────────────────────────────────
bulk_insert(M.Circuit.objects, DataFrame(
circuitid = [1, 2, 3],
name = ["Autódromo José Carlos Pace", "Circuit de Monaco", "Suzuka Circuit"],
country = ["Brazil", "Monaco", "Japan"],
))
bulk_insert(M.Driver.objects, DataFrame(
driverid = [1, 2, 3, 4, 5],
forename = ["Ayrton", "Nelson", "Alain", "Nigel", "Gerhard"],
surname = ["Senna", "Piquet", "Prost", "Mansell", "Berger"],
nationality = ["Brazilian", "Brazilian", "French", "British", "Austrian"],
))
bulk_insert(M.Constructor.objects, DataFrame(
constructorid = [1, 2, 3, 4],
constructorref = ["mclaren", "williams", "ferrari", "benetton"],
name = ["McLaren", "Williams", "Ferrari", "Benetton"],
))
bulk_insert(M.Race.objects, DataFrame(
raceid = [1, 2, 3, 4],
year = [1988, 1988, 1990, 1991],
name = ["Monaco Grand Prix", "Japanese Grand Prix", "Japanese Grand Prix", "Brazilian Grand Prix"],
date = [Date(1988, 5, 15), Date(1988, 10, 30), Date(1990, 10, 21), Date(1991, 3, 24)],
circuitid = [2, 3, 3, 1],
))
bulk_insert(M.Result.objects, DataFrame(
resultid = 1:12,
raceid = [1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4],
driverid = [3, 5, 1, 1, 3, 4, 2, 4, 1, 1, 4, 2],
constructorid = [1, 3, 1, 1, 1, 2, 4, 3, 1, 1, 2, 4],
grid = [2, 3, 1, 1, 2, 5, 6, 3, 1, 1, 3, 7],
positionorder = [1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3],
points = [9.0, 6.0, 4.0, 9.0, 6.0, 4.0, 9.0, 6.0, 4.0, 10.0, 6.0, 4.0],
))
# ── 4. One function, many queries ────────────────────────────────────────────
"""
search_results(; columns, group_by, metrics, year, nationality, podium_only, team,
min_wins, order, limit, show_sql)
Plain mode: `columns` picks what each result row shows.
Aggregate mode: pass `group_by` and every result collapses into one row per group, with the
`metrics` you ask for. The `GROUP BY` is built by PormG from the non-aggregate columns.
group_by e.g. ["driverid__surname"]
year 1988 or 1988:1990
nationality "Brazilian" or ["Brazilian", "French"]
team name ("McLaren") or reference ("mclaren")
min_wins needs :wins in metrics; becomes HAVING
"""
function search_results(; columns = ["raceid__year", "raceid__name", "driverid__surname", "constructorid__name", "positionorder", "points"],
group_by = nothing, metrics = [:races, :wins, :points],
year = nothing, nationality = nothing, podium_only = false, team = nothing,
min_wins = nothing, order = nothing, limit = 20, show_sql = true)
query = M.Result.objects
# ── what to show ──
if group_by === nothing
query.values(columns...)
else
aggregates = Dict(
:races => "races" => Count("resultid"),
:wins => "wins" => Sum(Case(When("positionorder" => 1, then = 1), default = 0)),
:podiums => "podiums" => Sum(Case(When("positionorder__@lte" => 3, then = 1), default = 0)),
:points => "points" => Sum("points"),
:avg_points => "avg_points" => Avg("points"),
:best_grid => "best_grid" => Min("grid"),
)
query.values(group_by..., (aggregates[m] for m in metrics)...)
end
# ── which rows ──
if year isa AbstractRange
query.filter("raceid__year__@gte" => first(year), "raceid__year__@lte" => last(year))
elseif year !== nothing
query.filter("raceid__year" => year)
end
if nationality isa AbstractVector
query.filter("driverid__nationality__@in" => nationality)
elseif nationality !== nothing
query.filter("driverid__nationality" => nationality)
end
if podium_only
query.filter("positionorder__@lte" => 3)
end
if team !== nothing
query.filter(Qor("constructorid__name" => team, "constructorid__constructorref" => team))
end
if min_wins !== nothing && group_by !== nothing
query.filter("wins__@gte" => min_wins) # filter on an aggregate → HAVING
end
# ── how to sort ──
if order !== nothing
query.order_by(order...)
elseif group_by === nothing
query.order_by("-raceid__year", "positionorder")
else
query.order_by("-" * first(aggregates[m] for m in metrics).first)
end
query.limit(limit)
if show_sql # inspection never runs the query
info = inspect_query(query)
println(info[:sql_text])
println("-- parameters: ", info[:parameters])
end
return query |> DataFrame
end
# Prints the title, the SQL, then the rows
function demo(title; kwargs...)
println("\n══ ", title)
println(search_results(; kwargs...))
end
demo("Brazilian podiums";
nationality = "Brazilian", podium_only = true)
demo("Only three columns, 1988";
columns = ["driverid__surname", "raceid__name", "points"], year = 1988)
demo("McLaren, by reference, 1988–1990";
team = "mclaren", year = 1988:1990)
demo("Per driver: races, wins, points";
group_by = ["driverid__surname"])
demo("Per driver, only winners, sorted by wins";
group_by = ["driverid__surname"], metrics = [:wins, :podiums, :points],
min_wins = 1, order = ["-wins", "driverid__surname"])
demo("Per team and season";
group_by = ["constructorid__name", "raceid__year"],
metrics = [:points, :avg_points], order = ["raceid__year", "-points"])
demo("Per nationality, Brazil vs France";
group_by = ["driverid__nationality"], nationality = ["Brazilian", "French"],
metrics = [:podiums, :wins])
demo("Per circuit country";
group_by = ["raceid__circuitid__country"], metrics = [:races, :points])
@Rafael_Brus Many thanks for the very detailed reply. I am not used to using agents or AI in coding (too old) but you have shown me how to start migrating some of the work I’ve done. I will continue to explore. I’ve got quite a lot on at the moment so I don’t have many hours to devote to this. But what you’ve done looks amazing. Thank you! 