{"_id":"5777c96e5b2b430e00b982c2","user":"5435b410495d5d0800f3a603","version":{"_id":"5777c9635b2b430e00b982a5","__v":1,"project":"54348ec95b10711400c6c445","createdAt":"2016-07-02T14:02:11.084Z","releaseDate":"2016-07-02T14:02:11.084Z","categories":["5777c9635b2b430e00b982a6","5777c9635b2b430e00b982a7","5777c9635b2b430e00b982a8","5777c9635b2b430e00b982a9","5777c9635b2b430e00b982aa"],"is_deprecated":false,"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"","version_clean":"1.2.0","version":"1.2.0"},"category":{"_id":"5777c9635b2b430e00b982aa","version":"5777c9635b2b430e00b982a5","project":"54348ec95b10711400c6c445","__v":0,"sync":{"url":"","isSync":false},"reference":false,"createdAt":"2014-12-03T21:36:49.014Z","from_sync":false,"order":4,"slug":"bonus-guides","title":"Bonus Guides"},"project":"54348ec95b10711400c6c445","__v":0,"parentDoc":null,"updates":[],"next":{"pages":[],"description":""},"createdAt":"2015-09-09T16:51:26.487Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":4,"body":"Sometimes we inherit a legacy database on top of which we need to build a new application. We can't control how these databases were created, and changing them to meet our current needs can be both difficult and expensive.\n\nEcto expects each table to have an auto-incremented integer for a primary key. What if our legacy database requires a string as the primary key instead? No problem. We can create our models with a custom primary key, and Ecto will work just the same as if we had an integer.\n\n> Note: While Ecto allows us to do this, it's not the natural path Ecto wants to take. Allowing Ecto to use an auto-incremented integer is definitely the right way to go for new applications.\n\n> Also, we chose this example for simplicity. `name` might not be the best choice for a primary key.\n\nLet's say that we need a JSON resource that stores rows of team athletes. Each athlete has a name, a position they play on the field, and the number of their jersey. The database that will back this resource requires that each table have a string for a primary key.\n\nWe can generate that resource like this.\n\n```console\n$ mix phoenix.gen.json Player players name:string position:string number:integer\n* creating priv/repo/migrations/20150908003815_create_player.exs\n* creating web/models/player.ex\n* creating test/models/player_test.exs\n* creating web/controllers/player_controller.ex\n* creating web/views/player_view.ex\n* creating test/controllers/player_controller_test.exs\n* creating web/views/changeset_view.ex\n\nAdd the resource to your api scope in web/router.ex:\n\n    resources \"/players\", PlayerController\n\nand then update your repository by running migrations:\n\n    $ mix ecto.migrate\n```\n\nThe first thing we need to do is add the resources route to the `api` scope in the router.\n\n```elixir\n. . .\nscope \"/api\", HelloPhoenix do\n  pipe_through :api\n\n  resources \"/players\", PlayerController\nend\n. . .\n```\n\nNow we'll need to make a few quick changes to the generated files.\n\nLet's take a look at the migration first, `priv/repo/migrations/20150908003815_create_player.exs`. We'll need to do two things. The first is to pass in a second argument - `primary_key: false` to the `table/2` function so that it won't create a primary_key. Then we'll need to pass `primary_key: true` to the `add/3` function for the name field to signal that it will be the primary_key instead.\n\n```elixir\ndefmodule HelloPhoenix.Repo.Migrations.CreatePlayer do\n  use Ecto.Migration\n\n  def change do\n    create table(:players, primary_key: false) do\n      add :name, :string, primary_key: true\n      add :position, :string\n      add :number, :integer\n\n      timestamps\n    end\n  end\nend\n```\n\nLet's move on to `web/models/player.ex` next. We'll need to add a module attribute `:::at:::primary_key {:name, :string, []}` describing our primary key as a string. Then we'll need to tell Phoenix how to convert our data structure to an ID that is used in the routes: `@derive {Phoenix.Param, key: :name}`. We'll also need to remove the `field :name, :string` line because this is our new primary key. If this seems unusual, recall that the schema doesn't list the `id` field in models where `id` is the primary key.\n\n```elixir\ndefmodule HelloPhoenix.Player do\n  use HelloPhoenix.Web, :model\n\n  @primary_key {:name, :string, []}\n  @derive {Phoenix.Param, key: :name}\n  schema \"players\" do\n    field :position, :string\n    field :number, :integer\n\n    timestamps\n  end\n  . . .\n```\n\nThere's just one more thing we'll need to do, and that's remove the reference to `id: player.id,` in the `def render(\"player.json\", %{player: player})` function body.\n\n```elixir\ndefmodule HelloPhoenix.PlayerView do\n  use HelloPhoenix.Web, :view\n\n  . . .\n\n  def render(\"player.json\", %{player: player}) do\n    %{name: player.name,\n      position: player.position,\n      number: player.number}\n  end\nend\n```\n\nWith all of that taken care of, let's run our migration.\n\n```console\n$mix ecto.migrate\n```\n\nThe resulting `players` table will look like this:\n\n```sql\nhello_phoenix_dev=# \\d players\n                Table \"public.players\"\n   Column    |            Type             | Modifiers\n-------------+-----------------------------+-----------\n name        | character varying(255)      | not null\n position    | character varying(255)      |\n number      | integer                     |\n inserted_at | timestamp without time zone | not null\n updated_at  | timestamp without time zone | not null\nIndexes:\n    \"players_pkey\" PRIMARY KEY, btree (name)\n```\n\nNow we have a model with the primary key `name` that we can query for with `Repo.get!/2`. We can also use it in our routes instead of an integer id - `localhost:4000/players/iguberman`.\n\n\n### Composite primary keys\n\nIn some cases, you will want two or more fields to make up the primary key. In\nthis case, the syntax becomes:\n\n```elixir\ndefmodule HelloPhoenix.Repo.Migrations.CreatePlayer do\n  use Ecto.Migration\n\n  def change do\n    create table(:players, primary_key: false) do\n      add :first_name, :string, primary_key: true\n      add :last_name, :string, primary_key: true\n      add :position, :string\n      add :number, :integer\n  . . .\n```\n\nand\n\n```elixir\ndefmodule HelloPhoenix.Player do\n  use HelloPhoenix.Web, :model\n\n  @primary_key false\n  schema \"players\" do\n    field :first_name, :string, primary_key: true\n    field :last_name, :string, primary_key: true\n    field :position, :string\n    field :number, :integer\n  . . .\n```\n\nWith composite primary keys, you can no longer use get/3 or get!/3,\nbut you have to use the more generic get_by/3 or get_by!/3, like so:\n\n```elixir\nlocalhost:4000/players?first_name=John&last_name=Doe\n\ndef index(conn, %{\"first_name\" => first_name, \"last_name\" => last_name}) do\n  player = Repo.get_by!(Player, first_name: first_name, last_name: last_name)\n  . . .\n```","excerpt":"","slug":"ecto-custom-primary-keys","type":"basic","title":"Ecto Custom Primary Keys"}

Ecto Custom Primary Keys


Sometimes we inherit a legacy database on top of which we need to build a new application. We can't control how these databases were created, and changing them to meet our current needs can be both difficult and expensive. Ecto expects each table to have an auto-incremented integer for a primary key. What if our legacy database requires a string as the primary key instead? No problem. We can create our models with a custom primary key, and Ecto will work just the same as if we had an integer. > Note: While Ecto allows us to do this, it's not the natural path Ecto wants to take. Allowing Ecto to use an auto-incremented integer is definitely the right way to go for new applications. > Also, we chose this example for simplicity. `name` might not be the best choice for a primary key. Let's say that we need a JSON resource that stores rows of team athletes. Each athlete has a name, a position they play on the field, and the number of their jersey. The database that will back this resource requires that each table have a string for a primary key. We can generate that resource like this. ```console $ mix phoenix.gen.json Player players name:string position:string number:integer * creating priv/repo/migrations/20150908003815_create_player.exs * creating web/models/player.ex * creating test/models/player_test.exs * creating web/controllers/player_controller.ex * creating web/views/player_view.ex * creating test/controllers/player_controller_test.exs * creating web/views/changeset_view.ex Add the resource to your api scope in web/router.ex: resources "/players", PlayerController and then update your repository by running migrations: $ mix ecto.migrate ``` The first thing we need to do is add the resources route to the `api` scope in the router. ```elixir . . . scope "/api", HelloPhoenix do pipe_through :api resources "/players", PlayerController end . . . ``` Now we'll need to make a few quick changes to the generated files. Let's take a look at the migration first, `priv/repo/migrations/20150908003815_create_player.exs`. We'll need to do two things. The first is to pass in a second argument - `primary_key: false` to the `table/2` function so that it won't create a primary_key. Then we'll need to pass `primary_key: true` to the `add/3` function for the name field to signal that it will be the primary_key instead. ```elixir defmodule HelloPhoenix.Repo.Migrations.CreatePlayer do use Ecto.Migration def change do create table(:players, primary_key: false) do add :name, :string, primary_key: true add :position, :string add :number, :integer timestamps end end end ``` Let's move on to `web/models/player.ex` next. We'll need to add a module attribute `@primary_key {:name, :string, []}` describing our primary key as a string. Then we'll need to tell Phoenix how to convert our data structure to an ID that is used in the routes: `@derive {Phoenix.Param, key: :name}`. We'll also need to remove the `field :name, :string` line because this is our new primary key. If this seems unusual, recall that the schema doesn't list the `id` field in models where `id` is the primary key. ```elixir defmodule HelloPhoenix.Player do use HelloPhoenix.Web, :model @primary_key {:name, :string, []} @derive {Phoenix.Param, key: :name} schema "players" do field :position, :string field :number, :integer timestamps end . . . ``` There's just one more thing we'll need to do, and that's remove the reference to `id: player.id,` in the `def render("player.json", %{player: player})` function body. ```elixir defmodule HelloPhoenix.PlayerView do use HelloPhoenix.Web, :view . . . def render("player.json", %{player: player}) do %{name: player.name, position: player.position, number: player.number} end end ``` With all of that taken care of, let's run our migration. ```console $mix ecto.migrate ``` The resulting `players` table will look like this: ```sql hello_phoenix_dev=# \d players Table "public.players" Column | Type | Modifiers -------------+-----------------------------+----------- name | character varying(255) | not null position | character varying(255) | number | integer | inserted_at | timestamp without time zone | not null updated_at | timestamp without time zone | not null Indexes: "players_pkey" PRIMARY KEY, btree (name) ``` Now we have a model with the primary key `name` that we can query for with `Repo.get!/2`. We can also use it in our routes instead of an integer id - `localhost:4000/players/iguberman`. ### Composite primary keys In some cases, you will want two or more fields to make up the primary key. In this case, the syntax becomes: ```elixir defmodule HelloPhoenix.Repo.Migrations.CreatePlayer do use Ecto.Migration def change do create table(:players, primary_key: false) do add :first_name, :string, primary_key: true add :last_name, :string, primary_key: true add :position, :string add :number, :integer . . . ``` and ```elixir defmodule HelloPhoenix.Player do use HelloPhoenix.Web, :model @primary_key false schema "players" do field :first_name, :string, primary_key: true field :last_name, :string, primary_key: true field :position, :string field :number, :integer . . . ``` With composite primary keys, you can no longer use get/3 or get!/3, but you have to use the more generic get_by/3 or get_by!/3, like so: ```elixir localhost:4000/players?first_name=John&last_name=Doe def index(conn, %{"first_name" => first_name, "last_name" => last_name}) do player = Repo.get_by!(Player, first_name: first_name, last_name: last_name) . . . ```