Here’s the entire migration I put together for the id generator for anyone that might be interested:
defmodule YourModule.Repo.Migrations.CreateSnowflake do
use Ecto.Migration
def up do
execute """
CREATE SEQUENCE #{prefix()}.next_id_seq;
"""
execute """
CREATE OR REPLACE FUNCTION #{prefix()}.next_id(OUT result bigint) AS $$
DECLARE
our_epoch bigint := 1000000000000; -- <-Set this to your epoch
seq_id bigint;
now_millis bigint;
shard_id int := #{shard_id()}; -- Set int value here per shard
BEGIN
SELECT nextval('#{prefix()}.next_id_seq') % 1024 INTO seq_id;
SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
result := (now_millis - our_epoch) << 23;
result := result | (shard_id << 10);
result := result | (seq_id);
END;
$$ LANGUAGE PLPGSQL;
"""
end
def down do
execute """
DROP SEQUENCE #{prefix()}.next_id_seq;
"""
execute """
DROP FUNCTION #{prefix()}.next_id;
"""
end
defp shard_id() do
[_, id] = prefix() |> String.split("_")
id
end
end