Get started
Browse all documentation

PostgreSQL

Configure, operate, and test Ridu’s official transactional store.

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

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.

Preselect every scaffold choice

For a non-interactive npm setup, pass every choice as an argument:

terminal
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

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.toml
    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.go
    import (
    "context"
    "os"

    "github.com/acme/content/internal/adminassets"
    "github.com/riducms/ridu"
    "github.com/riducms/ridu/adapters/sqlite"
    "github.com/riducms/ridu/adapters/postgres"
    "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.

  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:

    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:

terminal
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:

cmd/server/main.go
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:

Setting Default Purpose
Document connections 20 maximum, 0 minimum Bounds ordinary content and framework-state work per process.
Upload-lock connections 4 maximum Separate advisory-lock pool so staged uploads cannot consume the document connection needed to commit metadata.
Connection lifetime 1 hour ± up to 5 minutes jitter Rotates connections without synchronizing every replica.
Idle connection lifetime 30 minutes Releases unused capacity.
Pool health check 1 minute Checks idle connections.
Connect timeout 10 seconds Bounds startup and new connections.
Statement timeout 60 seconds Bounds server-side statements.
Lock timeout 10 seconds Bounds PostgreSQL lock waits and upload-lock admission.
Idle transaction timeout 60 seconds Prevents abandoned sessions from holding transaction state indefinitely.

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 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.

AllowInsecureTransport exists for local Unix sockets and development databases 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, and exact-history checks:

Check Required state
Ping(ctx) Both document and upload-lock pools can reach PostgreSQL.
Ready(ctx, manifest) Ping succeeds, the immutable migration ledger exists, its latest complete digest exactly matches the executable manifest, and no phased migration work is incomplete.
ReadyWithMigrationHistory(ctx, manifest, hash) Ordinary readiness passes and the complete ordered ledger matches the filename/digest fingerprint embedded by ridu build.

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 does not inspect every physical table and index. Run ridu migrate status for complete ledger, phase/step, and physical-schema drift inspection. See Production for probe and drain behaviour.

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:

text
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. It pauses when a possible rename needs explicit intent. Production uses committed immutable artifacts:

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

Creation is offline. The database-backed commands use their own bounded pools, TLS policy, advisory-lock admission, and schema assertions. Read Migrations for exact command semantics, destructive and maintenance admission, resumable phases, and recovery.

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.