Subramanya's Digital Garden

Recent Notes

Create a Database

3 min read

links: Phoenix MOC

Create a table in Phoenix app from command line

Phoenix provides generators for a quick bootstrap of schema, views, controllers and even context. The command is a mix task and starts with phx.gen followed by necessary arguments

Let’s say I want to generate a basic CRUD for users with email, password

mix phx.gen.html Accounts User users user_id:uuid email:string:unique password_hash:string time_joined: string

This command will create necessary files for CRUD operations in controllers, views, a context named Account, a migration file

Now, let’s say I dont’ want to name id as default primary key, instead I would like to have user_id. We need to tell our phoenix about that

  • open the migration file created by phoenix (it’s under priv folder)
  • Inside the create_table arguments add primary_key: false. This tells phoenix not to create primary key with id
  • Now add primary_key: true to user_id (add :user_id, :uuid, primary_key: true, null: false)

The output will look like this:

def change do
 
create table(:users, primary_key: false) do
	add :user_id, :uuid, primary_key: true, null: false
	add :email, :string, null: false
	add :password_hash, :string
	add :time_joined, :utc_datetime
 
	timestamps()
 
end

Now run the migration with the following command

mix ecto.migrate which will apply this to database

We still need to modify few things in other files, since phoenix assumes id as the default primary key. we need to instruct phoenix to consider user_id as primary key

Open user.ex file created in accounts context folder.

  • Inside the users schema, comment out user_id field.
  • On top of users schema, add the following code
@primary_key {:user_id, Ecto.UUID, autogenerate: true}
@derive {Phoenix.Param, key: :user_id}

We are instructing to use user_id as primary key, which is a type of UUID and also to take care of auto generating it.

Now for the @derive directive part. This tells to use different key name for the routes, the reason to add derive is it manages data structure to an ID in the routes.

The final result would be something like this

@primary_key {:user_id, Ecto.UUID, autogenerate: true}
@derive {Phoenix.Param, key: :user_id}
schema "users" do
	field :email, :string
	field :password_hash, :string, redact: true
	field :time_joined, :utc_datetime
	# field :user_id, Ecto.UUID
 
	timestamps()
 
end

In modern versions of phoenix, you can also achieve same thing as follows. This can be used for composite keys

@primary_key false
@derive {Phoenix.Param, key: :user_id}
schema "users" do
	field :email, :string
	field :password_hash, :string, redact: true
	field :time_joined, :utc_datetime
	field :user_id, Ecto.UUID, primary_key: true
 
	timestamps()
 
end

tags: database, migrations source:

Graph View