Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Custom Column Names in Phoenix Models

DZone's Guide to

Custom Column Names in Phoenix Models

Today, we explore a couple of the lesser used web application development framework, Phoenix and Ecto, and how they can work together.

· Web Dev Zone
Free Resource

Get deep insight into Node.js applications with real-time metrics, CPU profiling, and heap snapshots with N|Solid from NodeSource. Learn more.


When working with the Phoenix framework, you need to create your data models and plug in your database schema to your app. If you are not starting your project from the scratch, there is a high probability that you want your data models to adhere to an existing database schema with unconventional column names. So without any further ado, let’s see how we can introduce custom column names in Ecto.

For this example, I am assuming that you have already set up your Phoenix app. If you haven’t yet, go through the awesome documentation of Phoenix Framework and create your app.

In this example, I have four tables, and those are Tenant, Group, User, and Permission. Further, the primary key of these tables are auto-generated, sequentially increasing integers. But the primary key column name will be ID (not the usual id). Figure 1 depicts the ER diagram of the schema. In the ER diagram, what I would like to highlight is the relationships between each table. There are many_to_many, belongs_to, and has_many relationships among the tables.

Figure 1: ER diagram of the table schema

First, let’s create the migration. The below command will create a new migration file:

$> mix ecto.gen.migration sample_migration

The content of the migration file is as below.

defmodule Test.Repo.Migrations.SampleMigration do
  use Ecto.Migration

  def change do

    create table(:TEST_TENANTS, primary_key: false) do
      add :ID, :serial,  primary_key: true
      add :NAME, :string
    end

    create table(:TEST_GROUPS, primary_key: false) do
      add :ID, :serial,  primary_key: true
      add :TENANT_ID, references(:TEST_TENANTS, column: :ID)
      add :NAME, :string
    end

    create table(:TEST_USERS, primary_key: false) do
      add :ID, :serial,  primary_key: true
      add :TENANT_ID, references(:TEST_TENANTS, column: :ID)
      add :NAME, :string
    end

    create table(:TEST_GROUPS_USERS, primary_key: false) do
      add :GROUP_ID, references(:TEST_GROUPS, column: :ID)
      add :USER_ID, references(:TEST_USERS, column: :ID)
    end

    create table(:TEST_PERMISSIONS, primary_key: false) do
      add :ID, :serial,  primary_key: true
      add :GROUP_ID, references(:TEST_GROUPS, column: :ID)
      add :NAME, :string
    end

  end
end

Note that when creating tables, primary_key: false has been specified so that we can specify our own primary key. add :ID, :serial, primary_key: true specifies our custom primary key column. Since we are specifying a custom primary key column name, we need to explicitly specify the column name when specifying foreign key constraints [references(:TEST_TENANTS, column: :ID)]

Now let’s see the table schemas. Below is the Tenant schema:

defmodule Test.Tenant do
  use Test.Web, :model

  @primary_key {:ID, :id, autogenerate: true}
  schema "TEST_TENANTS" do
    field :NAME, :string
  end

end

User schema:


defmodule Test.User do
  use Test.Web, :model

  @primary_key {:ID, :id, autogenerate: true}
  schema "TEST_USERS" do
    field :NAME, :string

    belongs_to :TENANT, Test.Tenant, foreign_key: :TENANT_ID, references: :ID

    many_to_many :GROUPS, Test.Group, join_through: "TEST_GROUPS_USERS", join_keys: [GROUP_ID: :ID, USER_ID: :ID]
  end
end

Permission schema:


defmodule Test.Permission do
  use Test.Web, :model

  @primary_key {:ID, :id, autogenerate: true}
  schema "TEST_PERMISSIONS" do
    field :NAME, :string

    belongs_to :GROUP, Test.Grup, foreign_key: :GROUP_ID, references: :ID
  end
end

And last but not least, the Group schema:

defmodule Test.Group do
  use Test.Web, :model

  @primary_key {:ID, :id, autogenerate: true}
  schema "TEST_GROUPS" do
    field :NAME, :string

    belongs_to :TENANT, Test.Tenant, foreign_key: :TENANT_ID, references: :ID

    has_many :PERMISSIONS, Test.Permission, foreign_key: :GROUP_ID, references: :ID

    many_to_many :USERS, Test.User, join_through: "TEST_GROUPS_USERS", join_keys: [GROUP_ID: :ID, USER_ID: :ID]
  end
end

I will explain how the custom column names are supported using the group schema. First, we have specified @primary_key {:ID, :id, autogenerate: true} which denotes that the primary key column name of this table is ID and the type of the column is id. After that, schema “TEST_GROUPS” specifies that this model represents the table with the name TEST_GROUPS in the database.

According to the ER diagram, a group belongs to a tenant. The below line represents that constraint.

belongs_to :TENANT, Test.Tenant, foreign_key: :TENANT_ID, references: :ID

But in this case, we need to specify that the foreign key column name is TENANT_ID, and the corresponding primary key column name in the tenant table is ID. Otherwise, when we run an Ecto query, the query will try to find a column named TENANT_id in the TEST_GROUPS table.

Next, a particular group can have many permissions. The below line represents that constraint.

has_many :PERMISSIONS, Test.Permission, foreign_key: :GROUP_ID, references: :ID

In here, we need to specify the foreign key column name in the TEST_PERMISSIONS table explicitly. Otherwise, queries would look for a column named GROUP_id. Apart from that, we need to specify that the GROUP_ID column in TEST_PERMISSIONS table refers to the ID column of the TEST_GROUPS table.

Finally, there is a many_to_many relationship between TEST_USERS and TEST_GROUPS table. The below line denotes that relationship.

many_to_many :USERS, Test.User, join_through: “TEST_GROUPS_USERS”, join_keys: [GROUP_ID: :ID, USER_ID: :ID]

Note that the many_many_relationship is maintained via the TEST_GROUPS_USERS table and we need to specify it using join_through. Further, we need to specify the primary key column names under join_keys since our primary key column names are unconventional.

That’s it. Now the Ecto models are mapped using our database schema. Next, let’s see how we can execute a query with all these custom column names. Suppose I want to obtain all the groups of a user with a particular Id (e.g: 6), then the below query would do the trick.

from user in Test.User,
   join: group in assoc(u, :GROUPS),
   where: user.'ID' == 6,
   select: group

Usually, the where clause would be user.ID == 6. But this will not compile since ID is in uppercase. Hence, we need to specify it within single quotes. This goes for all the other column names as well since all of them are in upper case.

Hope this blog post has been useful to you. Happy coding with Elixir+ Phoenix.

Node.js application metrics sent directly to any statsd-compliant system. Get N|Solid

Topics:
elixir ,web dev ,phoenix ,erlang

Published at DZone with permission of Sajith Dilshan. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}