Running into column not found error when running migrations

Questions / Help
,
1

Hello,

I created a fresh project and can create and migrate my DB tables but when I run the following I get a “column does not exist” error. I tried looking for answers and tweaking my code but to no avail. Any suggestions or insights would be SO amazing!

Insert ran

iex(1)> KzyBeApi.Users.create_user(%{username: "testuser", first_name: "test", last_name: "user", email: "testuser@fakecompany.com"})

Error

[debug] QUERY ERROR db=2.0ms queue=8.3ms idle=822.9ms
INSERT INTO "users" ("email","first_name","last_name","username","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6) RETURNING "user" ["testuser@fakecompany.com", "test", "user", "testuser", ~N[2023-02-19 19:46:04], ~N[2023-02-19 19:46:04]]
↳ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309
** (Postgrex.Error) ERROR 42703 (undefined_column) column "user" does not exist

    query: INSERT INTO "users" ("email","first_name","last_name","username","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6) RETURNING "user"
    (ecto_sql 3.9.2) lib/ecto/adapters/sql.ex:913: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.9.4) lib/ecto/repo/schema.ex:756: Ecto.Repo.Schema.apply/4
    (ecto 3.9.4) lib/ecto/repo/schema.ex:369: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    iex:1: (file)

I’m new to Elixir but have been loving it so far. I got my base Users and Orgs created running the following,

➜  ~ mix phx.gen.json Users User users first_name:string last_name:string email:string:unique phone_num:integer
➜  ~ mix phx.gen.json Orgs Org orgs company_email:string:unique company_phone:integer company_name:string:unique

This created the following which I updated using @primarykey and @primary_foreign_key and added username to the Users schema

Here is the uses schema

defmodule KzyBeApi.Users.User do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:user, :id, autogenerate: true}
  @foreign_key_type :integer
  schema "users" do
    field :email, :string
    field :username, :string
    field :first_name, :string
    field :last_name, :string
    field :phone_num, :integer
    belongs_to :org, KzyBeApi.Orgs.Org

    timestamps()
  end

  @doc false
  def changeset(user, attrs) do
    user
    |> cast(attrs, [:username, :first_name, :last_name, :email, :phone_num])
    |> validate_required([:username, :email])
    |> validate_format(:email, ~r/^[^\s]+@[^\s]+$/, message: "Email format must include an @ symbol and have no spaces")
    |> validate_length(:email, max: 160)
    |> unique_constraint(:email)
  end
end

Here is the Users migration

defmodule KzyBeApi.Repo.Migrations.CreateUsers do
  use Ecto.Migration

  def change do
    create table(:users, primary_key: false) do
      add :id, :integer, primary_key: true
      add :username, :string
      add :first_name, :string
      add :last_name, :string
      add :email, :string
      add :phone_num, :integer
      add :org_id, references(:orgs, on_delete: :delete_all, type: :integer)

      timestamps()
    end

    create_if_not_exists unique_index(:users, [:org_id, :first_name, :last_name])
  end
end

Here is the Orgs schema

defmodule KzyBeApi.Orgs.Org do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key {:org, :id, autogenerate: true}
  @foreign_key_type :integer
  schema "orgs" do
    field :company_email, :string
    field :company_name, :string
    field :company_phone, :integer
    has_many :users, KzyBeApi.Users.User

    timestamps()
  end

  @doc false
  def changeset(org, attrs) do
    org
    |> cast(attrs, [:company_email, :company_phone, :company_name])
    |> validate_required([:company_email])
    |> validate_format(:company_email, ~r/^[^\s]+@[^\s]+$/, message: "Email format must include an @ symbol and have no spaces")
    |> validate_length(:company_email, max: 160)
    |> unique_constraint(:company_name)
    |> unique_constraint(:company_email)
  end
end

Here is the Orgs migration

defmodule KzyBeApi.Repo.Migrations.CreateOrgs do
  use Ecto.Migration

  def change do
    create table(:orgs, primary_key: false) do
      add :id, :integer, primary_key: true
      add :company_email, :string
      add :company_phone, :integer
      add :company_name, :string

      timestamps()
    end

    create unique_index(:orgs, [:company_name])
    create unique_index(:orgs, [:company_email])
  end
end
2

Why did you add that setting? It’s the reason you’re getting an error:

@primary_key {:user, :id, autogenerate: true}

This tells Ecto that the primary key for the User schema is a column named user of type :id - which leads directly to the RETURNING "user" clause.

3

This bit seems a bit odd, why not just fall back to the normal primary_key option and, similarly why set @primary_key in the schema at all?

4

I see what you’re saying. My noob it showing. Let me fix that. Thanks for the feedback and your time!

5

Hi, does the id not need to be called out at all or is it specifically the primary_key: true part that is odd? I’m still blazing through the documentation and come from a javascript background. So still getting used to the syntax and what not. Appreciate your time and sorry if this is something basic I should already know :-/

6

No problem at all!

Normally you do not need to specify either the primary_key: true or the :id itself. If you do a regular:

create table(:users) do
  # other columns here
end

You get an :id column that is primary_key: true by default. Similarly in your Schema module, you don’t need to mention :id at all, it’s just there and works correctly.

7

Thank you everyone for the help, this was a huge. So glad to be part of such a helpful community!. Shout out to @benwilson512 and @al2o3cr , I’m forever in your debt :slight_smile:

iex(1)> KzyBeApi.Users.create_user(%{username: "testuser", first_name: "test", last_name: "user", email: "testuser@fakecompany.com"})
[debug] QUERY OK db=7.8ms decode=1.5ms queue=9.4ms idle=1323.3ms
INSERT INTO "users" ("email","first_name","last_name","username","inserted_at","updated_at") VALUES ($1,$2,$3,$4,$5,$6) RETURNING "id" ["yangoni@keasy.com", "yang", "hawk", "yangOni", ~N[2023-02-20 03:37:00], ~N[2023-02-20 03:37:00]]
↳ anonymous fn/4 in :elixir.eval_external_handler/1, at: src/elixir.erl:309
{:ok,
 %KzyBeApi.Users.User{
   __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
   id: 1,
   email: "testuser@fakecompany.com",
   username: "testuser",
   first_name: "test",
   last_name: "user",
   phone_num: nil,
   org_id: nil,
   org: #Ecto.Association.NotLoaded<association :org is not loaded>,
   inserted_at: ~N[2023-02-20 03:37:00],
   updated_at: ~N[2023-02-20 03:37:00]
 }}
iex(2)>
2 Likes