Pog

Postgres json and jsonb types are treated as simple strings by Pog.

• Reading JSON: Use decode.string to decode the column value into a string, then parse it with your preferred JSON library.
• Writing JSON: Convert your JSON object to a string (e.g., using json.to_string) and pass it as a text parameter.

Instead of requiring you to manually load certificate files into your application code, pog automatically attempts to read the system's CA certificates from the Operating System using the Erlang function public_key:cacerts_get().

To resolve SSL verification errors, you should add the required CA certificates to your OS's trusted certificate store rather than modifying your pog configuration.

Pog has a default query timeout of 5 seconds. You can manage timeouts in two ways:

1. Global Timeout: Use pog.default_timeout on the Config to set a timeout for every query executed by that connection.
2. Per-Query Timeout: Use pog.timeout on a specific query to override the default for that single execution.

Pog uses connection pools that should be added to your OTP supervision tree. Pools are identified by a process.Name(pog.Message). You should create this name outside the supervision tree and pass it in.

Use pog.default_config(pool_name) to start a configuration, then chain configuration functions like host, database, and pool_size. Use .supervised to turn the configuration into a supervision child.

import gleam/otp/static_supervisor as supervisor
import gleam/erlang/process
import pog

pub fn start_application_supervisor(pool_name: process.Name(pog.Message)) {
  let pool_child = 
    pog.default_config(pool_name)
    |> pog.host("localhost")
    |> pog.database("my_database")
    |> pog.pool_size(15)
    |> pog.supervised

  supervisor.new(supervisor.RestForOne)
  |> supervisor.add(pool_child)
  |> supervisor.start
}

Instead of setting host, port, and user manually, you can use a standard Postgres connection URI (e.g., postgresql://[username:password@][host:port][/dbname][?query]).

Use pog.url_config(name, uri_string) to automatically generate a pog.Config from the URI.

import envoy
import gleam/erlang/process.{type Name}
import pog

pub fn read_connection_uri(name: Name(pog.Message)) -> Result(pog.Config, Nil) {
  use database_url <- result.try(envoy.get("DATABASE_URL"))
  pog.url_config(name, database_url)
}

To add Pog to your Gleam project, use the following command:

gleam add pog@4

If you are experiencing connection errors, you can determine if they are caused by missing or untrusted CA (Certificate Authority) certificates by temporarily disabling SSL verification in pog.

Set the ssl option to pog.SslUnverified. If the connection works with this setting, your issue is related to the CA certificate chain. If it still fails, the problem lies elsewhere (e.g., network, credentials, or database configuration).

// Example of disabling SSL verification to debug
const connection = await pog.connect({
  // ... other config
  ssl: pog.SslUnverified
});