Mix test - ** (Postgrex.Error) ERROR 42703 (undefined_column) column "created_at" of relation "shipments" does not exist

Phoenix Forum Questions / Help
, ,
1

I am working on converting one Ruby project to Elixir. So I have to use the database which already used in Ruby.

In one of my cases, the “shipments” table is not migrated from Elixir’s side. When I run the mix test I am getting the below error.

** (Postgrex.Error) ERROR 42703 (undefined_column) column "created_at" of relation "shipments" does not exist

mix.exs

  defp aliases do
    [
      setup: ["deps.get", "ecto.setup", "cmd npm install --prefix assets"],
      "ecto.setup": ["ecto.create", "ecto.migrate", "run priv/repo/seeds.exs"],
      "ecto.reset": ["ecto.drop", "ecto.setup"],
      test: ["ecto.create --quiet", "ecto.load --skip-if-loaded", "test"]
    ]
  end

My schema file

defmodule Shipments.Shipment do
  use Ecto.Schema
  import Ecto.Changeset
  
  @primary_key {:id, :string, autogenerate: false}
  @foreign_key_type :string
  schema "shipments" do

    ....
    ....
    ....

    timestamps([{:inserted_at,:created_at}])
  end
end

Any insight on how to fix this will be much appreciated.

2

You are using the timestamps macro to re-map the conventional “inserted_at” column to “created_at”, so you need “created_at” available in the database, or set that option to a column that is indeed available and should be used as the “inserted_at”. Alternatively you can set it to false to disable inserted_at functionnality.

3

In this ruby project already there are 15 tables are there. And those tables already contain thousands of data.

Do I need to create a separate migration file for all those tables under priv/migration folder? For running test or production

For running test I found in the below link

mix.exs

      test: ["ecto.create --quiet", "ecto.load --quiet --skip-if-loaded", "test"]

So do I need to create a separate migration file for all those tables under priv/migration folder or is there any better way for that?

4

You do not need migrations at all. Migrations are to create/alter database structure across the lifetime of your project.

Schemas do not require migrations to exist, nor would they read any data from them.

The error is not about any migration, it is about a missing column, you specified in your scheme as beeing available.

PS: I’d still create a “from legacy” migration that were able to recreate pre-existing tables for local development without requiring to run migrations of the legacy rails application first. That will make things easier once the legacy rails application has been dropped.

5

Okay. When I run mix test without the shipments table migration file in the priv/migration folder. It says

**** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "shipments" does not exist**

So for running mix test do I need to put the shipment table migration file on the priv/migration folder or is there a better way of doing it?

6

Obviously you do not have a test database pre setup, so of course you need something that creates the tables as they already exist in production for your testenvironment.

This can be through migrations or through a massive test seeder. It’s up to you.

Though as you say you already have a migration, which you haven’t shown us, I’d guess it’s not recreating the legacy table scheme, but something else.

1 Like
7

Have you also renamed the inserted_at timestamp field to created_at in your Ecto migration using the timestamp/1 function?

An example is shown below copied and modified from the Ecto.Migration docs.

defmodule MyRepo.Migrations.AddWeatherTable do
  use Ecto.Migration

  def up do
    create table("weather") do
      add :city,    :string, size: 40
      add :temp_lo, :integer
      add :temp_hi, :integer
      add :prcp,    :float

      timestamps(inserted_at: :created_at)
    end
  end

  def down do
    drop table("weather")
  end
end
1 Like
8

Hi! One option that I have done recently when transitioning a project from ruby to elixir is to keep a structure.sql file updated from the ruby migrations and then you can load that with ecto.

So process is, run your rails migrations, then do mix ecto.dump to create a structure.sql file. If you want migrations to be handled by elixir going forward what I did was dump my db via above command, then I called that file base.sql. Then this is what my aliases looks like

defp aliases do
  [
    "db.setup": ["ecto.create", "ecto.load", "run priv/repo/seeds.exs"],
    "db.reset": ["ecto.drop", "db.setup"],
    "db.migrate": ["ecto.migrate", "ecto.dump"],
    "db.pristine": ["ecto.drop", "ecto.create", "ecto.load --dump-path priv/repo/base.sql", "ecto.migrate", "run priv/repo/seeds.exs"],
    test: ["ecto.create", "ecto.load --skip-if-loaded", "test"]
  ]
end

So any new migrations are created by ecto and the state of my database at the time of the migration from rails is stored in base.sql. structure.sql is then kept up to date like schema.rb from rails, as long as you use the mix db.migrate command. Hope this helps!

2 Likes