Ecto Migration: Edit Start ID

Hi there!

When inserting entries into a new Postgres table, I don’t like the fact that IDs start at 1.
I’ve found this Postgres query online, I just don’t know how to add it to a migration:

execute("ALTER SEQUENCE accounts_id_seq START with 1000 RESTART;")

Does someone know how to add this to an Ecto migration? I’ve searched the docs but couldn’t find something helpful.

I’ve found something that works. Can this cause any problems or is there a better way to do it?

defmodule Rumbl.Repo.Migrations.CreateCategory do
  use Ecto.Migration

  def up do
    create table(:categories) do
      add :name, :string, null: false

      timestamps
    end

    create unique_index(:categories, [:name])
    execute "ALTER SEQUENCE categories_id_seq START with 1000 RESTART"
  end

  def down do
    drop table(:categories)
  end
end

Can you elaborate on why? I’m asking out of genuine curiosity, because I very much like the UUID approach, but am curious about why 1,000 as it seems to be arbitrary.

1 Like

It is arbitrary, and I will randomize this number. I just don’t like that fact that it’s so obvious how many entries acutally exist when using them as url parameters. I’ve used MongoDB before which generates ObjectIDs like 507f191e810c19729de860ea, which I really like. Also those IDs are hard to guess.

Does it exist for Postgres? If so, I’d love to use it, can you tell me how?

1 Like

Already built in to Ecto actually, change your primary_key type from :integer to :uuid. Phoenix can even do that for you globally for all things when you mix phoenix.new --binary-id.

4 Likes

if you are able to install
https://www.postgresql.org/docs/9.5/static/pgcrypto.html

then in create table you can use
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

can’t comment on possible issues with Ecto as I have not used Ecto for anything yet

update: @OvermindDL1 solution is obviously better :slight_smile: unless you might have some other tools that will do inserts then having DB handle the id’s might be better

1 Like

@OvermindDL1 Could you have a quick look at this post to see, if it’s still up to date? I think I need to use Ecto.UUID instead of :uuid, as I just discovered this pull request, which seems to deprecate :uuid.

If you want to do it on a per-schema bases then yes, Ecto.UUID. If however you want to use UUID’s globally for your primary keys then you can set it in the config and repo files like phoenix.new --binary-id does and then you never have to think about it again.

4 Likes

Thank you! As I will start a new app anyway, I will do that. Here’s a helpful SO post and a Gist about this topic as well, if someone needs more information.

1 Like