wrong `pog.Date` format causes crash

[binajmen]

Description

When using pog to insert data into PostgreSQL nullable columns, passing an empty string ("") causes a runtime error. This is a common issue with PostgreSQL drivers since PostgreSQL treats empty strings differently from NULL values. Currently, pog crashes with an Erlang error instead of handling this case gracefully.

Current Behavior

When attempting to insert an empty string into a nullable PostgreSQL column:

The empty string is passed directly to PostgreSQL pog crashes with an Erlang error:

runtime error: Erlang error
No Erlang function clause matched the arguments it was called with.
stacktrace:
  pog_ffi.convert_error
  pog_ffi.query
  pog.execute

Expected Behavior

There are two potential solutions to consider:

• Handle empty strings gracefully: Convert empty strings to NULL values automatically when inserting into nullable columns
• Better error handling: Provide a clear error message when an empty string is provided for a nullable column, explaining that NULL should be used instead

Steps to Reproduce

Create a PostgreSQL table with a nullable column:

create table if not exists transactions (
    id serial primary key,
    account varchar(50) not null,
    counterparty_account varchar(50)  -- nullable
);

Create an insert query:

insert into transactions (
    account,
    counterparty_account
) values (
    $1,  -- account (varchar(50))
    $2   -- counterparty_account (varchar(50), nullable)
)
returning id

Attempt to insert a record with an empty string for the nullable field:

sql.insert_transaction(
  pog,
  tx.account,
  ""  // empty string for counterparty_account
)

For a quick reproduction, you can clone this repo:

https://github.com/binajmen/budget

Additional Context

• This issue was initially discovered when working with squirrel-generated code, but the root cause appears to be in pog's handling of empty strings.
• Related Discord discussion: https://discord.com/channels/768594524158427167/1334687945566847066

Technical Details

pog version: 3.2.0 PostgreSQL version: 17 Gleam version: 1.6.2

[binajmen]

Ok this was due to a wrong format for the date...

[lpil]

We certainly don't want to crash! Could you share a reproduction for the crash please 🙏

[binajmen]

Sure, with the following code snippet:

import gleam/option.{Some}
import pog

pub fn main() {
  let db =
    pog.default_config()
    |> pog.host("localhost")
    |> pog.database("postgres")
    |> pog.user("postgres")
    |> pog.password(Some("postgres"))
    |> pog.pool_size(15)
    |> pog.connect

  let create_table_query =
    "
    CREATE TABLE IF NOT EXISTS test (
      id SERIAL PRIMARY KEY,
      some_date DATE
    )
  "

  let assert Ok(response) =
    pog.query(create_table_query)
    |> pog.execute(db)

  let insert_query =
    "
    INSERT INTO test (some_date) 
    VALUES ($1)
  "

  let assert Ok(response) =
    pog.query(insert_query)
    |> pog.parameter(pog.date(pog.Date(31, 01, 2025)))
    |> pog.execute(db)

  pog.disconnect(db)
}

You should get the following error:

runtime error: Erlang error

No Erlang function clause matched the arguments it was called with.

stacktrace:
  pog_ffi.convert_error /Users/binajmen/Developer/pog_crash/build/dev/erlang/pog/_gleam_artefacts/pog_ffi.erl:122
  pog_ffi.query /Users/binajmen/Developer/pog_crash/build/dev/erlang/pog/_gleam_artefacts/pog_ffi.erl:119
  pog.execute /Users/binajmen/Developer/pog_crash/build/packages/pog/src/pog.gleam:478
  pog_crash.main /Users/binajmen/Developer/pog_crash/src/pog_crash.gleam:43

I was parsing a date in the format dd/mm/yyyy and forgot to switch the year and day, hence the pog.Date(31, 01, 2025).