Custom column names in Phoenix Models

When working with 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 be adhered 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 digaram of the table schema

First, let’s create the migration. Below command will create a new migration file.

$> mix ecto.gen.migration sample_migration

The content of the migration file is as below.

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 constrains [references(:TEST_TENANTS, column: :ID)]

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

User schema

Permission schema

And.. last but not least, the Group schema

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 the the ER diagram, a group belongs to a tenant. below line represent that constrain.

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 TENAT_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 name TENAT_id in TEST_GROUPS table.

Next a particular group can have many permissions. Below line represents that constrain.

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. 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 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 with our database schema. Next, let’s see how we can execute a query with all this custom column names. Suppose I want to obtain all the groups of an user with a particular Id (e.g: 6) , then below query would do the trick.

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.