Mix test gives error of column "inserted_at"


Newbie to Elixir/Phoenix. I come from a Rails background but I’m loving Elixir/Phoenix so far.

When I try to run “mix test”, I’m getting the following error:

** (Postgrex.Error) ERROR (undefined_column): column "inserted_at" of relation "schema_migrations" does not exist
(ecto) lib/ecto/adapters/sql.ex:463: Ecto.Adapters.SQL.struct/6
(ecto) lib/ecto/repo/schema.ex:397: Ecto.Repo.Schema.apply/4
(ecto) lib/ecto/repo/schema.ex:193: anonymous fn/11 in Ecto.Repo.Schema.do_insert/4
(ecto) lib/ecto/repo/schema.ex:124: Ecto.Repo.Schema.insert!/4
(ecto) lib/ecto/adapters/sql.ex:508: anonymous fn/3 in Ecto.Adapters.SQL.do_transaction/3
(db_connection) lib/db_connection.ex:1274: DBConnection.transaction_run/4
(db_connection) lib/db_connection.ex:1198: DBConnection.run_begin/3
(db_connection) lib/db_connection.ex:789: DBConnection.transaction/3

My migrations is as follows:
def change do
drop_if_exists table(:users)

    create table(:users, primary_key: false) do
      add :username, :string, primary_key: true, null: false
      add :password, :string, null: false
      add :nickname, :string, unique: true
      add :verification_code, :string
      add :phone_verified, :boolean, default: false, null: false
      add :max_distance, :integer
      add :distance_unit, :string, default: "km", null: false

      timestamps([{:inserted_at,:created_at}, {:updated_at, false}])

    create unique_index(:users, [:username])
    create unique_index(:users, [:nickname])

My schema is as follows:

  @primary_key {:username, :string, []}
  @derive {Phoenix.Param, key: :username}

  schema "users" do
    field :nickname, :string
    field :verification_code, :string
    field :phone_verified, :boolean, default: false
    field :password, :string
    field :max_distance, :integer
    field :distance_unit, :string

     timestamps([{:inserted_at,:created_at}, {:updated_at, false}])

  @doc """
  Builds a changeset based on the `struct` and `params`.
  def changeset(struct, params \\ %{}) do
    |> cast(params, [:nickname, :verification_code, :phone_verified, :password, :max_distance, :distance_unit])
    |> validate_required([:username, :nickname, :phone_verified, :password])

When I look in my database, I see that the “schema_migrations” table does indeed have an “inserted_at” column, which is where I’m assuming the error is coming from but I haven’t created that column (as far as I can tell).

Any insight?

You might not by any chance have changed database user along the way? It could be a database permission issue in that case…

Also, tests are usually configured to run in a separate database from regular development work, but it should normally “just work” of course.

Any chance you could post your mix.exs, and relevant parts of the config files?

Thank you for answering so quickly.

This is my first Elixir/Phoenix project, so yes, I have been playing with ALL kinds of fun things, including changing users along the way. Could it be a permission issue when the migration does work?

My mix.exs looks like this:
defmodule Pingashop.Mixfile do
use Mix.Project

  def project do
    [app: :pingashop,
     version: "0.0.1",
     elixir: "~> 1.2",
     elixirc_paths: elixirc_paths(Mix.env),
     compilers: [:phoenix, :gettext] ++ Mix.compilers,
     build_embedded: Mix.env == :prod,
     start_permanent: Mix.env == :prod,
     aliases: aliases(),
     deps: deps()]

  # Configuration for the OTP application.
  # Type `mix help compile.app` for more information.
  def application do
    [mod: {Pingashop, []},
     applications: [:phoenix, :phoenix_pubsub, :phoenix_html, :cowboy, :logger, :gettext,
                    :phoenix_ecto, :postgrex, :cowboy, :logger]]

  # Specifies which paths to compile per environment.
  defp elixirc_paths(:test), do: ["lib", "web", "test/support"]
  defp elixirc_paths(_),     do: ["lib", "web"]

  # Specifies your project dependencies.
  # Type `mix help deps` for examples and options.
  defp deps do
    [{:phoenix, "~> 1.2.1"},
     {:phoenix_pubsub, "~> 1.0"},
     {:phoenix_ecto, "~> 3.0"},
     {:postgrex, ">= 0.0.0"},
     {:phoenix_html, "~> 2.6"},
     {:phoenix_live_reload, "~> 1.0", only: :dev},
     {:gettext, "~> 0.11"},
     {:cowboy, "~> 1.0"}]

  # Aliases are shortcuts or tasks specific to the current project.
  # For example, to create, migrate and run the seeds file at once:
  #     $ mix ecto.setup
  # See the documentation for `Mix` for more info on aliases.
  defp aliases do
    ["ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"],
     "ecto.reset": ["ecto.drop", "ecto.setup"],
     "test": ["ecto.create --quiet", "ecto.migrate", "test"]]

From config.exs:
config :pingashop,
ecto_repos: [Pingashop.Repo]

From dev.exs:

Configure your database

config :pingashop, Pingashop.Repo,
  adapter: Ecto.Adapters.Postgres,
  username: "postgres",
  password: “dummypassword_notreal”,
  database: "pingashop_dev",
  hostname: "localhost",
  pool_size: 10

I should point out that I had ejabberd set up and have since removed it and its dependencies (including deleting the ejabberd.yml file). Not sure if that’s relevant or not…

Have the migrations been run on the test database?

I’m with @jwarlander in thinking that the test database and schema_migrations table were created by another database user and so therefore the user you’re running with doesn’t have access. Try running mix test but changing the database config in config/test.exs to use the different users you have set up…

As @benwilson512 pointed out, you want to make sure your mix.exs has in the aliases/0 function something like

"test": ["ecto.create --quiet", "ecto.migrate", "test"]

which will make sure you’re creating the database and migrating it before running your tests.

1 Like

…but also try removing the --quiet flag, so you see what happens when it
tries to create the test database :slight_smile:

Den lör 24 dec. 2016 20:50Mgwidmann noreply@elixirforum.com skrev:

1 Like

Thank you all for your help. First time I’ve interacted with the Elixir community and it’s been pleasantly supportive and helpful. With your responses in mind, I started digging deeper into the test database to see whether it could be a permission issue. Turns out, my test database was actually the one created with Ruby on Rails (it had the same name). Once I deleted the RoR created database and reran tests it worked fine. Thanks again for all the help.


Nothing helps your learning better than having to dig in and troubleshoot a bit, in my experience :grin: