Abejide Ayodele bio photo

Abejide Ayodele

Software Engineer

Email Twitter LinkedIn Github Stackoverflow

When null nullifies default

Given a table foos created via the below query:

CREATE TABLE foos (
  id serial PRIMARY KEY,
  name text,
  ref_id text DEFAULT uuid_generate_v4()::text
);

We write an insert query to confirm the test default uuid generation behaves correctly:

insert into foos(name) values('bar');

Running a select query to confirms the insert works as expected:

soro=# select * from foos;
 id | name |                ref_id
----+------+--------------------------------------
  1 | bar  | 10cbf8ac-fbd6-45d8-918e-e913b9dbe2ca

Excited this works we start writing the client code via our ORM of choice(ecto for example) and find the below:

foo = Foo.Repo.insert(%Foo{name: "foo"})
foo = Repo.one from f in Foo, where f.id == ^foo.id
foo.id #=> 2
foo.name #=> "foo"
foo.ref_id #=> nil

:o Woohoo why is foo.ref_id nil, well the ORM sends null for properties not supplied.
 
In this post we would examine 2 possible approaches to solving this problem:

  • Generate a uuid by hand and pass in the ref_id e.g:
foo = Foo.Repo.insert(%Foo{name: "doe", ref_id: Ecto.UUID.generate})
foo.id #=> 3
foo.name #=> "doe"
foo.ref_id #=> "8C98BF51-2B4F-4AC6-A254-378A6666BB9C"
  • Write a database trigger that overrides ref_id if it is null and sets it to a uuid as expected, e.g:
CREATE OR REPLACE FUNCTION update_ref_id() RETURNS trigger AS $$
DECLARE
BEGIN
  IF NEW.ref_id IS NULL THEN
    NEW.ref_id = uuid_generate_v4();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_refs
  BEFORE INSERT ON foos
  FOR EACH ROW EXECUTE PROCEDURE update_ref_id();

BEFORE INSERT guarantees that this trigger runs before the record is committed. So running the client code again:

foo = Foo.Repo.insert(%Foo{name: "Bob"})
foo = Repo.one from f in Foo, where f.id == ^foo.id
foo.id #=> 4
foo.name #=> "Bob"
foo.ref_id #=> "10cbf8ac-fbd6-45d8-918e-e913b9dbe2ca"

Boom! now our client code behaves just as expected.
 
The solutions would work with ref_id being NOT NULL
 
Thanks for reading!  
 
PS: The queries above are all postgres specific but the idea should work across databases that comply with the sql standard.