PostgreSQL

PostgreSQL 17 is Ridu’s official networked and multi-replica store. The adapter implements documents, globals, relationships, localization, auth, versions, tasks, locks, preferences, reference indexes, migrations, and readiness behind Ridu’s public store contracts.

Start a new PostgreSQL project

PostgreSQL is the scaffold default:

npm create ridu@latest my-app

bun create ridu@latest my-app

pnpm create ridu@latest my-app

yarn create ridu my-app

Choose Starter and PostgreSQL in the wizard, then run the exact install and dev commands it prints. PostgreSQL is preselected, but the summary still makes the choice explicit before any files are written.

npm create ridu@latest -- \
  --template starter \
  --database postgres \
  --module github.com/acme/content \
  --scope @acme \
  --package-manager npm \
  --agent codex \
  content

The generated compose.yaml exposes PostgreSQL 17 on 127.0.0.1:54329, and the dev script starts it, waits for pg_isready, performs safe additive development synchronization, and runs the API/admin. To use a service you already operate, supply its secret only to the command and skip Compose:

DATABASE_URL="$DEVELOPMENT_DATABASE_URL" npm run dev -- --no-docker

DATABASE_URL="$DEVELOPMENT_DATABASE_URL" bun run dev -- --no-docker

DATABASE_URL="$DEVELOPMENT_DATABASE_URL" pnpm run dev --no-docker

DATABASE_URL="$DEVELOPMENT_DATABASE_URL" yarn run dev --no-docker

Add PostgreSQL to an existing project

Use this path for a clean Ridu project that does not yet have committed migrations or data. Ridu does not provide a generic live SQLite/MongoDB-to-PostgreSQL migration facility; moving an existing dataset between adapters is an application-owned export, transform, validation, and cutover.

1. Replace the current adapter in ridu.toml. This example starts from SQLite; if the project currently names MongoDB, replace that line instead:

   ridu.tomlCopy

   version = 1
   database = "sqlite"
   database = "postgres"
   entry = "./cmd/server"
   admin = "./admin"

2. Replace the old adapter import and store factory inside the existing runtimeOptions function. Keep the generated address, admin assets, handler options, and server options around it. Lazy opening keeps config resolution and generation offline:

   cmd/server/main.goCopy

   import (
     "context"
     "os"
   
     "github.com/acme/content/internal/adminassets"
     "github.com/riducms/ridu"
     "github.com/riducms/ridu/adapters/postgres"
     "github.com/riducms/ridu/adapters/sqlite"
     "github.com/riducms/ridu/store"
   )
   
   func runtimeOptions(applicationConfig ridu.Config) []ridu.ExecuteOption {
     return []ridu.ExecuteOption{
       ridu.WithStore(func(ctx context.Context) (store.Store, error) {
         return sqlite.Open(ctx, sqliteDatabasePath())
         return postgres.OpenWithConfig(ctx, postgres.PoolConfig{
           DatabaseURL:              os.Getenv("DATABASE_URL"),
           AllowInsecureTransport:   envBool("RIDU_ALLOW_INSECURE_DATABASE"),
           MaxUploadLockConnections: envInt32("RIDU_POSTGRES_UPLOAD_LOCK_CONNECTIONS"),
         })
       }),
       ridu.WithAddress(env("RIDU_ADDRESS", ":8080")),
       ridu.WithHandlerOptions(ridu.HandlerOptions{
         AdminAssets:    adminassets.FS(),
         AllowedOrigins: envList("RIDU_ALLOWED_ORIGINS"),
       }),
     }
   }

   The duplicate return statements and adapter imports represent the before/after diff—the finished file contains only the PostgreSQL lines. The generated server template also supplies the envBool and envInt32 parsers; copy them from a fresh --database postgres scaffold if the current entry does not already have them.

   Swap the migration driver in main as well, so ridu migrate and the built binary’s migrate up use PostgreSQL. Move any data transforms to the new call:

   cmd/server/main.goCopy

   options := []ridu.ExecuteOption{
     ridu.WithProjectMigrations(sqlite.ProjectMigrations()),
     ridu.WithProjectMigrations(postgres.ProjectMigrations()),
   }

3. Either copy the generated PostgreSQL service from a disposable scaffold into compose.yaml, or point at an existing PostgreSQL 17 service. The generated local service uses database/user/password ridu, host port 54329, and sslmode=disable; never carry that plaintext credential into production.

4. Start the development loop against the selected service:

   npmBunpnpmYarn

   Copy

   go mod tidy
   DATABASE_URL="$POSTGRES_URL" npm run dev -- --no-docker

   Use the generated Compose URL for $POSTGRES_URL, or an already managed development URL. Remove insecure admission when the service uses verified TLS. ridu dev resolves the Go config, regenerates contracts, synchronizes safe additive changes, and starts the API and admin. Before deploying, create and verify immutable migration artifacts using the migration workflow below.

Connect the generated server

Generated applications open the store lazily so schema generation never needs database access. The factory belongs inside runtimeOptions, between the surrounding address and handler settings, as shown in the existing-project diff above. Do not open a database from a package initializer or from content.Config().

Use encrypted transport outside an explicitly local environment:

export DATABASE_URL='postgres://ridu:[email protected]:5432/content?sslmode=verify-full'
export RIDU_ADDRESS=':8080'

postgres.Open uses bounded defaults. Use OpenWithConfig when the application needs deliberate pool or session tuning:

return postgres.OpenWithConfig(ctx, postgres.PoolConfig{
  DatabaseURL:                     os.Getenv("DATABASE_URL"),
  ApplicationName:                 "acme-content",
  MaxConnections:                  30,
  MinConnections:                  2,
  MaxUploadLockConnections:        4,
  ConnectTimeout:                  10 * time.Second,
  StatementTimeout:                45 * time.Second,
  LockTimeout:                     5 * time.Second,
  IdleInTransactionSessionTimeout: 30 * time.Second,
})

Size connections against the database service and the total replica count, not one process in isolation.

Pool defaults and limits

Zero values select the following defaults:

A negative duration explicitly disables that individual timeout; connection counts cannot be negative, minimum cannot exceed maximum, and server timeout values must use whole milliseconds. An unbounded setting should be a measured exception, not a routine fix for blocked work.

The upload-lock pool is additional to MaxConnections. Startup and readiness ping both pools. Loss or exhaustion of either makes the instance unready before the next upload discovers it.

TLS is required by default

OpenWithConfig rejects plaintext and TLS-fallback URLs to a remote host unless AllowInsecureTransport is true. Use sslmode=require, verify-ca, or preferably verify-full in production. Modes such as prefer, allow, and disable permit fallback and are rejected.

A host on this machine is the exception: localhost, a loopback address, or a Unix-socket directory needs no TLS, because nothing off the machine can observe it. Every host in a multi-host URL must qualify. That lets ridu migrate reach the same local database as ridu dev without an override.

AllowInsecureTransport exists for development databases on another host that are protected outside PostgreSQL. Do not expose it as a silent production fallback. Protect credentials independently and use a least-privilege application role.

Readiness checks

The store exposes connectivity, manifest, physical-schema, and exact-history checks:

ridu.Execute runs aggregate readiness before binding its listener and includes the same store check in /readyz. Production uses the exact-history form, so it also rejects a missing, altered, renamed, reordered, or additional artifact even when the final manifest happens to be unchanged. An older binary becomes unready after a migration, and a new binary is unready before it.

Readiness verifies the expected Ridu-managed physical schema, including required tables and indexes, index build health, and trigger enablement. It shares this non-mutating verification with migration status and completed-migration preflight. Run ridu migrate status for the detailed ledger, phase/step, and physical-schema drift report. See Production for probe and drain behaviour.

Every probe checks connectivity, the ledger, incomplete migration work, and a digest of the current schema’s catalogue rows. The complete physical inspection and the manifest and history digests run again only when the ledger, that catalogue digest, or the executable manifest changed since the last passing probe. Steady-state probes therefore stay cheap, and drift still fails the next probe.

Transactions and access remain atomic

Ridu’s operation engine owns the lifecycle; PostgreSQL supplies its transactional implementation. Caller filters and filtered access predicates are compiled into the same SQL query for reads, updates, and deletes. A local Go call, REST request, task, admin action, or plugin transport cannot bypass that predicate by choosing another adapter entry point.

Ordinary writes use a transaction. Read snapshots use repeatable-read, read-only transactions. Relationship admission and deletion coordinate row locks so a dangling target cannot commit; expected revisions produce stable conflicts without revealing access-filtered documents; hook or validation failure rolls persistence back; and joined mutations retain the outer transaction boundary.

Retryable PostgreSQL serialization and deadlock outcomes become stable operation conflicts rather than raw driver errors. Application callers should re-read and deliberately retry the operation instead of replaying a stale mutation blindly.

Schema ownership

Ridu owns the configured PostgreSQL schema, including its content tables, framework state, migration ledgers, indexes, and declared ridu_plugin_<key>_... tables. Unrelated tables in that schema are reported as drift.

When another system shares the database, give Ridu a dedicated schema through the connection search_path:

postgres://ridu:[email protected]/content?sslmode=verify-full&search_path=ridu_content

Do not grant a plugin ownership of a core or unrelated table merely to hide drift. Plugin table prefixes are validated as part of descriptor resolution.

Development and production migrations

ridu dev can apply additive, non-destructive development synchronization. When a change looks like a rename, it asks in an interactive terminal and can create and apply the rename migration for you; otherwise it rejects every reload until you restore the old name or run the commands yourself. See Renames in ridu dev. Production uses committed immutable artifacts:

ridu migrate create --name rename-post-title
ridu migrate verify
ridu migrate plan
ridu migrate up
ridu migrate status

Creation is offline. The database-backed commands use their own bounded pools, TLS policy, advisory-lock admission, and schema assertions. Read Migrations for the commands, Safety checks for destructive approval, and Deploy migrations for maintenance work, resumable phases, and recovery.

PostgreSQL accepts only planner atlas 1.3.0 artifacts. Separate working and live content is part of this layout from its initial migration. Earlier framework layouts and planner histories are unsupported; recreate the database and migration history rather than rewriting old artifacts. Startup and readiness never convert an old layout.

Every collection and global has a typed working table. A versioned resource also has a typed live table generated from the same definition, with the same columns, unique constraints, declared indexes, and relationship foreign keys. Publishing copies the saved working row into the live table; deleting a document deletes both rows. Version history stays in its own snapshot table.

Authoring reads use the latest working revision for conflict checks, while public reads filter, sort, count, and paginate against the live table with the same indexes and statistics as working reads. Declare Index() on fields that public queries filter or sort by; the index exists on both tables. Saving a newer draft does not change the public content or its revision/timestamp. Version-history retention may prune old snapshots without removing live content. Migrations rename, rewrite, and retire both tables together. Content rewrites and index rebuilds still need the migration maintenance admission and a verified backup, because working and live content must remain coherent.

Test against PostgreSQL

Use a disposable PostgreSQL 17 database for application tests that cover access rules, localization, transactions, relationship locks, optimistic conflicts, tasks, uploads, migrations, or recovery. Give each test suite its own database or schema and clean it afterward. Never give a test role access to production.

Run ridu migrate verify against a restored backup before deployment, then exercise the Local API, REST or SDK paths your application depends on. Review Testing, Releases and compatibility, and Security for the wider checks.