Generating `schema_migrations` table automatically in Livebook with sqlite3

Questions / Help
, ,
1

Hi everyone,

I’m trying to run a LiveBook that uses ecto and sqlite.

When I trun to run a migration, I get the following error: ** (Exqlite.Error) no such table: schema_migrations.

I suppose I could create the table manually, but I was wondering if there’s a way it can be created automatically. Since I’m using livebook, I can’t really run the mix tasks.

This is the content of my livebook:

Get Sqlite to work

Mix.install(
  [
    {:ecto, "~> 3.11"},
    {:ecto_sqlite3, "~> 0.13"},
    {:ecto_sql, "~> 3.11"},
    {:kino, "~> 0.12.3"}
  ],
  config: [
    my_app: [
      {:ecto_repos, [MyApp.Repo]},
      {:"Elixir.MyApp.Repo", [database: :memory]}
    ]
  ]
)

Setup Repo

defmodule MyApp.Repo do
  use Ecto.Repo,
    otp_app: :my_app,
    adapter: Ecto.Adapters.SQLite3
end

alias MyApp.Repo
{:ok, repo_pid} = Kino.start_child(Repo)

# Ecto.Adapters.SQL.query!(MyApp.Repo, "create table users (id int)")

defmodule Migrations.AddPositionsTable do
  use Ecto.Migration

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

      timestamps()
    end
  end

  def down do
    drop(table("positions"))
  end
end

Ecto.Migrator.run(Repo, [{0, Migrations.AddPositionsTable}], :up, all: true)

Whith this being the entirety of the error message when I try to run the migration:

** (Exqlite.Error) no such table: schema_migrations
SELECT s0."version" FROM "schema_migrations" AS s0
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:952: Ecto.Adapters.SQL.execute/6
    (ecto 3.11.2) lib/ecto/repo/queryable.ex:232: Ecto.Repo.Queryable.execute/4
    (ecto 3.11.2) lib/ecto/repo/queryable.ex:19: Ecto.Repo.Queryable.all/3
    (ecto_sql 3.11.1) lib/ecto/migrator.ex:586: anonymous fn/5 in Ecto.Migrator.lock_for_migrations/4
    (ecto_sql 3.11.1) lib/ecto/migrator.ex:585: Ecto.Migrator.lock_for_migrations/4
    (ecto_sql 3.11.1) lib/ecto/migrator.ex:432: Ecto.Migrator.run/4

Any help on how to create the schema_migrations table would be greatly appreciated!

2

Sadly, by the nature of how ecto migrations work, you will not be able to use the in-memory option.

Just change the configuration to point to a local file and everything should work as expected.

3

Gotcha. Thank you very much! It’s a bit of a bummer, since in-memory dbs seem like a perfect match for prototyping something in LiveBook.

Appreciate the response!

Any chance you could explain what the issue is, purely for educational purposes?

4

When you start sqlite with in-memory option, the database exists only as long as the process is alive. In this case, ecto currently doesn’t guarantee that migrations will be done in the same process, so when a new process is started there are no migrations left as the database is in memory.

This is a limitation of ecto design as it was more oriented in interacting with persistent databases, maybe it will be fixed in the future, however from what I understand is not that trivial.

2 Likes