The connection.yml Configuration File

PormG uses a centralized YAML configuration file—typically located at db/connection.yml, or in your specific folder path like nitro_server/db/connection.yml—to govern how the application connects to databases and what operations it's authorized to execute.

Creating connection.yml

During standard initialization, PormG.Configuration.load(path) reads a connection.yml from path (default DB_PATH). If the folder or file is missing it throws a MissingConfigurationError that points you at PormG.setup(path) (interactive) or load(path; scaffold=true) (writes an editable skeleton) — it no longer silently scaffolds a file and returns.

To scaffold it programmatically, you can run:

using PormG
# Provide your path, typically DB_PATH, for instance "nitro_server/db"
PormG.Generator.create_db_folder_and_yml(path="nitro_server/db", adapter="PostgreSQL", database="my_database")

Or you can manually create the connection.yml file and populate it using the structures below:

Adapters & Environments

Each top-level block (dev, prod, test, …) describes one environment, and PormG loads whichever one is active. The active environment is chosen by the env= argument to load(...), the PORMG_ENV variable, or — in an interactive session only — an optional top-level default_env: key; see Multi-environment Routing below. The supported adapters are PostgreSQL, SQLite and MySQL (see Using MySQL); any other is refused when the file is loaded.

Using PostgreSQL

To use a PostgreSQL database, assign adapter: PostgreSQL and configure your credentials.

default_env: dev

dev:
  adapter: PostgreSQL
  database: my_database
  host: 127.0.0.1
  username: my_user
  password: my_password
  port: 5432
  extensions:
    - unaccent
  config:
    change_db: true
    change_data: true
    time_zone: 'UTC'

prod:
  adapter: PostgreSQL
  database: my_prod_database
  host: prod.database.server.com
  username: prod_user
  password: secure_password
  port: 5432
  config:
    change_db: false
    change_data: true
    time_zone: 'UTC'

Using SQLite

When using SQLite, the parameters are simplified. You specify adapter: SQLite and pass the path (or file name) of the SQLite database to the database property. Missing elements like host, username, or port safely get ignored.

default_env: dev

dev:
  adapter: SQLite
  database: dev_database.sqlite  # Will be generated inside your DB folder 
  config:
    change_db: true
    change_data: true
    time_zone: 'UTC'

In-memory databases

Two database: values are SQLite keywords rather than file paths, and PormG hands them to the driver untouched instead of resolving them inside your DB folder:

test:
  adapter: SQLite
  database: ":memory:"   # in-memory, private to each pool connection

test:
  adapter: SQLite
  database: "file:pormg_test?mode=memory&cache=shared"   # in-memory, shared across the pool

The distinction matters as soon as pool_size is above 1 (it defaults to 3). A bare :memory: database belongs to the connection that opened it — a table created through one pool slot is invisible from the next, which surfaces as a puzzling no such table rather than as a connection error. The file:…?mode=memory&cache=shared URI form gives every connection in the pool one shared database and still writes nothing to disk, so prefer it unless you have deliberately set pool_size: 1.

PormG does not leave you to discover that on your own:

The last one is refused rather than warned about because it cannot work at all: split mode sends writes to one fixed connection and reads to the others, so with a per-connection database every read would look in an empty one. Use the shared-cache URI, or pool_size: 1.

Using MySQL

To use a MySQL database, assign adapter: MySQL. It needs MySQL 8.4 or newer, and MySQL.jl 2.2.2 or newer loaded with using MySQL. What differs on MySQL, from its collation to its non-transactional DDL, is in the MySQL Guide.

default_env: dev

dev:
  adapter: MySQL
  host: 127.0.0.1
  port: 3306
  username: f1_app
  password: "your-password"
  database: f1
  sslmode: required          # disabled, preferred (the default), required, verify_ca, verify_identity
  config:
    change_db: true
    change_data: true
    time_zone: 'UTC'

A MySQL connection is configured by these keys, not by a URL. connect_timeout, sslrootcert, sslcert and sslkey are read as on PostgreSQL, and sslmode takes MySQL's modes listed above. The PostgreSQL-only keys url, hostaddr, passfile, client_encoding, postgres_driver and extensions raise InvalidConfigurationError instead of being dropped. For the same reason, register_connection does not take adapter = "MySQL".

The server's default account plugin, caching_sha2_password, sends the password in full only over TLS, so with sslmode: disabled MySQL.jl refuses to authenticate unless the server already has the account's credentials cached. Keep TLS on.

Environment-block Keys

These are the keys PormG reads directly under an environment block — the peers of config:. Anything else in that block is not read by anything, and warns on load (see Unrecognised keys below).

The PostgreSQL-only keys are inert under adapter: SQLite, so a block may carry both sets without harm. Under adapter: MySQL they are refused, as Using MySQL lists. hostaddr, port, password, passfile, connect_timeout, client_encoding and the four ssl* keys reach libpq under exactly the names written here; username and database are translated to libpq's user= and dbname= for you.

Every forwarded value is single-quoted and escaped for libpq, so write it exactly as it is: a password such as corr3ct horse 'battery', or a certificate path such as C:\certs\root ca.crt, needs no quoting of its own beyond what YAML requires. YAML's own typing still applies first, so quote any value YAML would read as a number or a boolean — password: 007 reaches libpq as 7, and password: '007' as 007. A value containing a NUL character cannot be sent to PostgreSQL at all and is rejected when the connection is built. (Only url: is passed through untouched, so a value inside it follows libpq's own quoting rules.)

Choosing the PostgreSQL driver

PostgreSQL pools use LibPQ.jl unless told otherwise. Postgres.jl, a pure-Julia driver with no libpq underneath, is a supported alternative:

dev:
  adapter: PostgreSQL
  database: f1
  host: 127.0.0.1
  username: my_user
  password: my_password
  postgres_driver: Postgres

Load the driver package as you would LibPQ — using PormG, Postgres — so its extension loads. register_connection(...; postgres_driver = "Postgres") does the same for a dynamic connection, and PORMG_POSTGRES_DRIVER=Postgres switches every PostgreSQL pool that does not name a driver, which is how the test suite runs through it.

PormG needs Postgres.jl 2.6 or newer. An older one still loads, because Julia does not check an extension's [compat], so a manifest resolved before PormG raised the floor keeps it; PormG then warns once at using Postgres, naming the floor and the Ctrl+C cancellation that does not work below it (#1719). Pkg.update() fixes it.

PormG.setup() asks for the driver when the adapter is PostgreSQL (PostgreSQL driver (LibPQ or Postgres) [Default: LibPQ]), accepting the same spellings as this key. The driver you type is written into each environment block (postgres_driver: Postgres, or postgres_driver: LibPQ). An empty answer writes no key, so PORMG_POSTGRES_DRIVER still decides there, then LibPQ; when the variable is set, the prompt says so.

PormG opens Postgres.jl sessions the way LibPQ.jl opens its own — DateStyle=ISO,YMD and TimeZone=UTC — and decodes results to the same types (Decimals.Decimal, ZonedDateTime in UTC, DateTime). Server notices go to @debug rather than the log.

Both drivers are supported and held to the same integration suite. PormG's performance work is measured on Postgres.jl, which ran faster than LibPQ in the read and write benchmarks recorded on #1072; LibPQ stays the default, so an application that loads only LibPQ keeps working unchanged. Known gaps of Postgres.jl:

• A hand-written string holding several statements fails when you send it yourself, for example two ALTER TABLEs in one fetch call: Postgres.jl refuses it, where LibPQ runs it when it has no parameters. Send one statement per call. fetch runs every statement over the extended protocol, which takes one statement at a time. It does not switch to Postgres.execute_script (JuliaDatabases/Postgres.jl#23) by design: that call discards result rows, so a string ending in a SELECT could not return what LibPQ returns, and telling a script from a single statement would mean parsing the SQL (#1480). Migrations are not affected: migrate sends a plan one statement at a time under either driver.
• passfile, hostaddr, service, multiple hosts and Unix-socket hosts are not supported, nor is a client_encoding other than UTF-8 or a target_session_attrs other than any; reconnect=true in a connection string is refused (the pool renews connections itself). sslmode takes only disable, prefer, require or verify-full, and gssencmode only disable, prefer or require, whether from the connection string, connection.yml or PGSSLMODE/PGGSSENCMODE. libpq's allow and verify-ca, and an empty value, are refused before PormG connects, by name, without echoing the value. Under gssencmode=require a Postgres.jl before 2.7 never reads sslmode, so PormG does not check it there; from 2.7 the driver checks it anyway and refuses a value outside that list, and PormG names it the same way. Postgres.jl decides which connection strings it accepts. When it refuses one, PormG reads the string itself to name the parameter at fault. A refusal never shows a value, and it names a parameter only when that is a real parameter keyword. Any other word is referred to by its position: in password=abc def=ghi the word def, which is part of the password, is parameter 2.
• fetch_copy reports the number of CSV records it sent rather than the server's COPY n count. That is exact for PormG's own bulk_copy, but can differ for a hand-written COPY with HEADER true, FORMAT text or binary, or a custom QUOTE.
• interval values cast to text render in PostgreSQL's default style rather than ISO 8601.

Prepared statements under Postgres.jl

Under Postgres.jl, each connection keeps the statements it runs prepared on the server, in the driver's least-recently-used cache of statement_cache_maxsize statements (100 unless the connection string says otherwise). A query PormG has already run on that connection skips parsing and planning: a two-join read on the F1 data took about a quarter less time. A statement the connection has not seen costs two extra round trips the first time, and three once the cache is full, since the least recently used statement is closed to make room. A connection that cycles through more distinct statements than the cache holds pays that on every call; raise statement_cache_maxsize if yours does. LibPQ prepares nothing and sends every statement as a new one.

• Turning it off. Put statement_cache_maxsize=0 in the connection string (url:) and every statement is sent unprepared, as LibPQ sends it. A PgBouncer in transaction-pooling mode needs this unless its max_prepared_statements setting is above zero (PgBouncer 1.21 and later), because it hands each transaction a server connection that may not hold the statement.
• Generic plans. After a prepared statement has run five times, PostgreSQL may switch it to a generic plan that does not look at the parameter values, which can be slower for a filter on a skewed column, a LIKE prefix pattern or a partitioned table. A statement sent unprepared is always planned for its values. If a query slows down after its fifth call, set PostgreSQL's plan_cache_mode for the session: options='-c plan_cache_mode=force_custom_plan' in a key=value connection string, ?options=-c%20plan_cache_mode%3Dforce_custom_plan in a URL. Or turn the cache off.
• A schema change under a cached statement. When a connection or process — this one included — changes the columns a cached statement returns, PostgreSQL refuses that statement with cached plan must not change result type (SQLSTATE 0A000). An added column no longer reaches writes: create(), get_or_create() and update_or_create() return the model's columns by name, never *, so it does not change what they return. A type change to a column the model declares still does, for writes and reads alike: a CharField's max_length from 50 to 100 (varchar(50) to varchar(100)), or integer to bigint. And a read that selects no fields meets an added column too: get(), first() and list() without values() send SELECT *, so a deploy's migrate that adds a column to the table they read changes the result of every connection's cached copy. A DEALLOCATE ALL or DISCARD ALL sent on the connection makes PostgreSQL refuse every cached statement with SQLSTATE 26000. In either case PormG clears the connection's cache. Outside a transaction it then runs the statement again, so the call succeeds. Inside a transaction, PostgreSQL has already aborted the transaction, so the error propagates as a StatementError; run the transaction again. The cache that was cleared is that connection's only, so each pooled connection that cached the statement fails at most one transaction this way. LibPQ does not raise here.

Where the two drivers return different values

The rows list(), list(:dict), list(:json), first and get return hold the same Julia types under both drivers: every field of Race, Driver, Result, Pit_stops and Constructor_results was read under each. Where the drivers decode a value differently, the read path converts it to one type. A DataFrame(query) column and a raw fetch result are where the differences remain:

The two drivers decode every other column type PormG maps to the same Julia type: integers, floats, numeric (Decimals.Decimal), boolean, text, date, time, timestamp, timestamptz (ZonedDateTime in UTC), json/jsonb, uuid, inet (String) and bytea (Vector{UInt8}).

Tables PormG leaves alone

Some tables in a database belong to another system, like a timing feed another service loads or a reporting job's staging tables. PormG must not migrate them, and you do not want to model them. List them under ignore_tables: and PormG never reads them on that connection:

dev:
  adapter: PostgreSQL
  database: f1
  host: 127.0.0.1
  username: my_user
  password: my_password
  ignore_tables:
    - legacy_timing_
    - etl_staging

• Entries are prefixes. legacy_timing_ skips legacy_timing_laps and legacy_timing_pits. A whole table name is its own prefix, so etl_staging skips that table, but it also skips etl_staging_2. This is the same rule as every other ignore list.
• Skipped tables are invisible to makemigrations (never dropped, never altered), to check, and to import_models_from_postgres / import_models_from_sqlite / import_models_from_mysql.
• The list adds to the others and replaces none of them. Those others are the backend's built-in list (see below), the process-wide register_ignore_tables!, and the ignore_table= keyword that check and the importers take. The difference is scope: ignore_tables: applies to one connection. An app that drives two databases can skip a table on the replica and still migrate it on the primary.
• Case is kept. Entries are table names, not keywords.

ignore_tables: left empty, or set to '', means the key is not set. Two values are refused when the file loads, with InvalidConfigurationError. The first is a value that is not a string or a list of strings. The second is a blank string inside a list: as a prefix, "" matches every table.

Declaring a model for an ignored table is a contradiction, and it is refused. The model asks PormG to migrate the table, and the list asks PormG never to read it. Because PormG would not see the existing table, it would plan to create it again on every run. So makemigrations and check(kinds = [:schema_drift]) raise InvalidConfigurationError, naming the model, the entry it matched, and the list the entry came from. (makemigrations plans nothing at all under change_db: false, so there only check reports it.) The same rule covers all three lists: this key, register_ignore_tables!, and the backend's built-in list. So a managed model on an auth_ or django_ table is refused on PostgreSQL and MySQL. The one list it does not cover is check's own ignore_table= keyword, which only filters what check reads. The check covers every declared model, so check's include_table= does not narrow it.

The fix depends on what you want:

• Query the table without migrating it: declare the model with managed = false. An unmanaged model on an ignored table is fine.
• Migrate it after all: take the table off every list that hides it, on that connection. Remove the entry from ignore_tables:, and list a built-in entry under unignore_defaults:. register_ignore_tables! cannot be switched off for one connection, and neither can the built-in pormg_migrations entry. The error names every list the table matches, and offers only the fixes that would work.
• Let PormG own a new table instead: give the model a table name (or db_table) outside the prefix. That plans a new, empty table; the existing table and its rows stay where they are.

A ManyToManyField on an unmanaged model is not always fine. Its automatic join table, <table>_<field>, is managed whenever the other end is, and it usually shares the prefix. Give that field a db_table outside the prefix, or declare an explicit through model.

The built-in list

PormG skips these prefixes on every connection, before ignore_tables: adds any:

The PostgreSQL and MySQL lists hide the tables of Django, django-allauth, python-social-auth and Celery, which often share a database with the tables PormG migrates. The SQLite list holds no framework prefix, so the engines differ: a model on an auth_ table is refused on PostgreSQL and MySQL and migrated on SQLite.

Switching a built-in entry off

Django names a table <app_label>_<model>, so your own Django app labelled account keeps its tables under account_, a prefix the PostgreSQL and MySQL lists hide. List the entry under unignore_defaults: and that connection reads and migrates those tables like any other:

dev:
  adapter: PostgreSQL
  database: f1
  host: 127.0.0.1
  username: my_user
  password: my_password
  unignore_defaults:
    - account_

• Each entry must equal a built-in entry. account_ is one; account or account_sponsor is not. A value that is not an entry is refused when the file loads, with InvalidConfigurationError naming the entries you can remove, so a typo cannot silently do nothing. Use ignore_tables: to hide more tables, not this key.
• The whole prefix comes back. Every table under account_ becomes visible to makemigrations, check and the importers on that connection, including any that belong to django-allauth. A visible table that no model declares is planned for removal, like any other undeclared table, so declare a model for each one you keep (with managed = false if PormG should only read it).
• Entries are independent, and some overlap. Removing django_ leaves django_celery_ in place, so django_celery_beat_* stays hidden; list both to read those tables. When a managed model is refused, the error names every entry that hides its table.
• pormg_migrations and the SQLite engine tables cannot be removed. They belong to PormG and to the engine. On SQLite that leaves nothing to remove, so any entry is refused. The entries are prefixes, so pormg_migrations also covers pormg_migrations_data, the table run_once records its steps in.
• It is per connection, like ignore_tables:. Another connection in the same process keeps the full list. register_ignore_tables! is not affected.

Configuration Settings (config:)

At the core of connection.yml is the config sub-dictionary. This section governs runtime permissions, timezones, and naming conventions for the loaded environment:

Unrecognised keys

change_data

• true: DML operations (Data Manipulation Language) are permitted. You can save(), update(), and delete() records through PormG models.
• false: Makes the database connection read-only internally. Queries fetch data securely, but any invocation of model-mutating functions will fail safely at the ORM layer before generating SQL.

change_db

• true: DDL operations (Data Definition Language) are permitted. PormG's migration subsystem is authorized to create tables, alter columns, and perform schema patches directly against the database.
• false: Blocks schema changes. All Migrations.migrate() commands will be defensively rejected, protecting your production database from unintended, automated alteration.

django_prefix

Optional (default: unset). Names the Django app label whose tables this connection reads, when the schema is owned by a Django project:

dev:
  adapter: PostgreSQL
  database: f1_paddock
  config:
    change_data: true
    django_prefix: myapp     # Django tables are myapp_<model>

It only ever shapes names. The Django importer emits it as each generated model's db_table; relationship accessor names strip it; and it is the fallback used to spell the physical table of a reverse-join target that declares no db_table. It does not switch any behaviour on: not sequence synchronisation, not Django-style short-form join paths. See django_prefix interop.

django_prefix: '' means the same as omitting the key — an empty app label is the absence of one, not a prefix that happens to be empty. Earlier versions composed "$(prefix)_" regardless and derived table names beginning with _.

Leave it unset for a multi-app Django project. One connection-level value cannot name three app labels, and import_models_from_django takes "<app_label>" => "<models.py>" pairs for that case — it refuses to run when this key is set, because accessor derivation strips one prefix from every logical name regardless of which app the model came from. See Importing a multi-app project.

PostgreSQL Extensions

PostgreSQL extensions can be declared in the active environment block with a simple extensions list.

dev:
  adapter: PostgreSQL
  database: my_database
  extensions:
    - unaccent

Currently supported extensions:

• unaccent: installs the unaccent extension and an IMMUTABLE helper function public.immutable_unaccent(text), then enables accent-insensitive lookups:
  • field__@iunaccent_contains — accent- and case-insensitive substring match (ILIKE on immutable_unaccent).
  • field__@iunaccent_exact — accent- and case-insensitive equality (LOWER(immutable_unaccent(field)) = LOWER(immutable_unaccent($1))).

When are extensions installed? Installing an extension is DDL, so PormG applies it through the migration runner — the same place schema changes happen — gated by config.change_db. Running PormG.migrate(db) provisions the configured extensions (and the helper function) before applying the schema plan, even when there is no schema diff; CREATE ... IF NOT EXISTS keeps it idempotent. PormG.Configuration.load(...) performs no DDL: it only probes pg_extension and warns if a configured extension is still missing, so misconfiguration surfaces before the first query fails. That probe is the one query load runs, so it is skipped while a package precompiles: a package that loads its configuration in its module body builds without its database, and the load in its __init__ runs the probe at run time. A sysimage or juliac executable keeps no configuration from its build, so its run-time load probes as well.

The database user must be allowed to create the extension and function. If PostgreSQL rejects the commands (or change_db is false, as in production), run them once as the database owner/admin:

CREATE EXTENSION IF NOT EXISTS unaccent WITH SCHEMA public;

-- unaccent(text) is only STABLE, so it cannot back an index. This IMMUTABLE
-- wrapper (explicit dictionary) is what iunaccent_contains emits, so it can.
CREATE OR REPLACE FUNCTION public.immutable_unaccent(text)
  RETURNS text LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE
  AS $$ SELECT public.unaccent('public.unaccent', $1) $$;

Making iunaccent_contains index-assisted

field__@iunaccent_contains emits public.immutable_unaccent(column) ILIKE public.immutable_unaccent($1). Without a matching index this is a sequential scan. For large tables, add a pg_trgm GIN index on the same expression so the ILIKE '%…%' pattern can use it:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_drivers_forename_unaccent
  ON drivers USING gin (public.immutable_unaccent(forename) gin_trgm_ops);

For iunaccent_exact (equality), a plain btree on the lowered expression is enough:

CREATE INDEX idx_drivers_forename_unaccent_exact
  ON drivers (lower(public.immutable_unaccent(forename)));

SQLite configurations ignore extensions with a warning and MySQL ones refuse it, and iunaccent_contains raises BackendCapabilityError on both.

Multi-environment Routing

PormG selects the active environment block by this precedence — first match wins:

1. the env= argument to load(...) — e.g. PormG.Configuration.load("db"; env="prod");
2. the PORMG_ENV environment variable — e.g. PORMG_ENV=prod (a blank value counts as unset);
3. in an interactive session only, the optional top-level default_env: key in this file — e.g. default_env: prod;
4. in an interactive session only, otherwise dev.

Steps 3 and 4 are guesses, so they are a REPL's (#1228): there load logs which one decided. A service, a test or a script that reaches step 3 gets InvalidConfigurationError instead, naming the load(…; env = …) call to add.

The recommended pattern for a server is to let the host resolve its own environment and pass it explicitly: a framework (or your bootstrap) reads its own env var and calls load(...; env=…), so the same connection.yml works unchanged in every environment. default_env: is a convenience for a REPL session that would rather pin a default in the file than pass env every time — omit it and the REPL falls back to dev. A script or a single-environment app states its environment with env= or PORMG_ENV.

Any other top-level value that is not an environment block is warned about on load, with a default_env suggestion for near-misses like defaultenv: — a common way to lose the setting silently. An environment block written with an empty body (prod: with nothing under it) is still a block and is never flagged.

Pre-connect hooks

Some databases are only reachable after external setup such as a VPN, SSH tunnel, or credential refresh. PormG keeps that logic out of the ORM: register an app-level hook once at boot and decide inside the callback which connections need setup.

Register the hook before the first query or ping:

using PormG

PormG.Configuration.set_before_connect_hook() do key, settings
    folder = basename(settings.db_def_folder)
    folder == "db_legacy" && return ensure_vpn_connection()
    return true
end

PormG.Configuration.load_many(["db", "db_legacy"]; env="dev")

The callback receives (key::String, settings::Settings) and must return true to allow the physical connection or false to abort.

Semantics:

• It runs only when a new physical connection must be opened, not on connection reuse — so a warm pool does not pay the hook cost on every query.
• It runs outside the pool lock, so a slow hook (VPN bring-up, sleep) does not block other tasks acquiring connections.
• Returning false aborts the acquire with a clear error naming the connection key.
• When no hook is registered, connections proceed normally.