`phoenix.gen.model` question, probably ecto question

The help for phoenix.gen.model states:

The generator also supports `belongs_to` associations
via references:

    mix phoenix.gen.model Post posts title user_id:references:users

This will result in a migration with an `:integer` column
of `:user_id` and create an index. It will also generate
the appropriate `belongs_to` entry in the schema.

However this is not strictly true, it looks like it actually uses the serial datatype, which creates an auto-incrementing serializer in the database itself, and considering this is a foreign column then this seems to be entirely wasteful. Why does it not default to integer, or better yet default to the type of the primary key of the associated table if detectable otherwise integer instead of the useless serial? Is there a case for having a foreign key be serial over integer that I have never yet ran across in years?

I think MySQL adapter is not as widely used as PostgreSQL one, and it’s a bug that you found. I’d report it back to Ecto team :slight_smile:

Ah right, I should mention, using postgrex. A design bug perhaps but not necessarily a coding bug?

This is the migration that gets created when I run the generator:

defmodule Wr.Repo.Migrations.CreatePost do
  use Ecto.Migration

  def change do
    create table(:posts) do
      add :title, :string
      add :user_id, references(:users, on_delete: :nothing)

      timestamps
    end
    create index(:posts, [:user_id])

  end
end

…and database table:

wr_dev=# \d posts
                                      Table "public.posts"
   Column    |            Type             |                     Modifiers                      
-------------+-----------------------------+----------------------------------------------------
 id          | integer                     | not null default nextval('posts_id_seq'::regclass)
 title       | character varying(255)      | 
 user_id     | integer                     | 
 inserted_at | timestamp without time zone | not null
 updated_at  | timestamp without time zone | not null
Indexes:
    "posts_pkey" PRIMARY KEY, btree (id)
    "posts_user_id_index" btree (user_id)
Foreign-key constraints:
    "posts_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

Do you have different results? I assumed you are using MySQL since there is no serial column type in PostgreSQL. Your database structure looks different?

There is a serial datatype in PostgreSQL, see the bottom of: https://www.postgresql.org/docs/9.5/static/datatype-numeric.html

8.1.4. Serial Types

The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
colname SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval(‘tablename_colname_seq’)
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as “owned by” the column, so that it will be dropped if the column or table is dropped.

And as per: https://hexdocs.pm/ecto/Ecto.Migration.html#references/2

:type - The foreign key type, default is :serial

So if you check the PostgreSQL database the column data type will be integer, however when you specify serial on creation then it sets up sequences as well.

It also looks like the PostgreSQL adapter overrides :serial to :integer on references explicitly, but other databases do not necessarily do the same. As :serial would generically never be useful on a reference it is still odd that that is the default instead of :integer, a far more reasonable default, so reasonable in fact that the PostgreSQL adapter overrides :serial (which would really suck if that was really wanted for some reason)…

So you don’t really have a problem with wrong column types in your resulting database, you have problem with the semantics used?

I already override references with type: :integer everywhere anyway (well, a few cases of :bigint because of external database interaction). It is definitely a semantic issue in the very least, and a coding issue if used with an adapter that does not override it as the PostgreSQL one does.