How to set PRAGMA foreign_keys=OFF in Ecto migration?

Questions / Help
,
1

Hello,

Using SQLite, in order to remove a NOT NULL constraint on a column, I’m trying to follow this procedure: https://www.sqlite.org/lang_altertable.html#making_other_kinds_of_table_schema_changes

The problem is, we must execute PRAGMA foreign_keys=OFF before the transaction, because SQLite’s documentation says:

PRAGMA foreign_keys = boolean ;
This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

I’ve tried this approach:

defmodule Brinjel.Repo.Migrations.RelaxNotNullConstraintForOwnerId do
  use Ecto.Migration

  @disable_ddl_transaction true

  def change do
    execute("PRAGMA foreign_keys=OFF;")

    execute("PRAGMA busy_timeout=5000;")

    execute("BEGIN TRANSACTION")

    create table(:new_farms, primary_key: false, options: "STRICT") do
      add :farm_id, :bigserial, primary_key: true
      add :name, :text, null: false
      add :slug, :text, null: false
      add :default_provider_id, :integer
      add :locked, :boolean, null: false, default: false
      add :trial_expiry_date, :date, null: false

      add :owner_id, references(:users)

      timestamps()
    end

    execute """
      INSERT INTO new_farms(farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at)
      SELECT farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at 
      FROM farms;
    """

    flush()

    drop table("farms")

    rename table("new_farms"), to: table("farms")

    create unique_index(:farms, [:slug])

    execute("COMMIT")

    execute("PRAGMA foreign_keys=ON")
  end
end

and unfortunately got this error:

17:20:26.716 [info] execute "PRAGMA foreign_keys=OFF;"

17:20:26.716 [info] execute "PRAGMA busy_timeout=5000;"

17:20:26.716 [info] execute "BEGIN TRANSACTION"

17:20:26.716 [info] create table new_farms

17:20:26.720 [info] execute "  INSERT INTO new_farms(farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at)\n  SELECT farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at \n  FROM farms;\n"

17:20:26.721 [info] drop table farms
** (Exqlite.Error) Database busy
DROP TABLE "farms"
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.16.1) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (ecto_sql 3.11.3) lib/ecto/adapters/sql.ex:1161: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.11.3) lib/ecto/migration/runner.ex:348: Ecto.Migration.Runner.log_and_execute_ddl/3
    (elixir 1.16.1) lib/enum.ex:1700: Enum."-map/2-lists^map/1-1-"/2
    (stdlib 5.2) timer.erl:270: :timer.tc/2
    (ecto_sql 3.11.3) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.11.3) lib/ecto/migrator.ex:365: Ecto.Migrator.attempt/8

Any idea why dropping the table produces a “Database busy” error?

2

I cannot explain the “Database busy” error

But I had a similar issue which I could work around with a dynamic repo Ecto.Repo — Ecto v3.12.5

I only tried this approach with up and down functions

Adapted to your snippet, this would look like this

defmodule Brinjel.Repo.Migrations.RelaxNotNullConstraintForOwnerId do
  use Ecto.Migration

  import Ecto.Query

  @disable_ddl_transaction true

  def up do
    repo().start_link(name: :migration, foreign_keys: :off)
    repo().put_dynamic_repo(:migration)

    repo().transaction(fn ->
      create table(:new_farms, primary_key: false, options: "STRICT") do
        add(:farm_id, :bigserial, primary_key: true)
        add(:name, :text, null: false)
        add(:slug, :text, null: false)
        add(:default_provider_id, :integer)
        add(:locked, :boolean, null: false, default: false)
        add(:trial_expiry_date, :date, null: false)

        add(:owner_id, references(:users))

        timestamps()
      end

      execute("""
        INSERT INTO new_farms(farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at)
        SELECT farm_id, name, slug, default_provider_id, locked, trial_expiry_date, owner_id, inserted_at, updated_at 
        FROM farms;
      """)

      drop(table("farms"))

      rename(table("new_farms"), to: table("farms"))

      create(unique_index(:farms, [:slug]))

      execute(fn ->
        count = repo().one(from(fragment("pragma_foreign_key_check()"), select: count()))

        if count > 0 do
          raise "Foreign key check failed"
        end
      end)

      flush()
    end)
  end

  def down do
    # reverse
  end
end
1 Like
3

I’ll get you guys sorted soon enough. My SQLite library is nearly done and I’m moving to Ecto integration very soon, migrations included.

3 Likes
4

Instead of using execute have you tried @repo.query/2 you might be able express it more clearly that way with GitHub - elixir-dbvisor/sql: Brings an extensible SQL parser and sigil to Elixir, confidently write SQL with automatic parameterized queries.

IIRC then Ecto migration collects all the execute statements and try to run them in order, so not sure if that the issue you’re running into.

5

Great, curious to see how migrations will be handled

Nice library

It turns out that the exact issue I had (including the “Database busy” error from the OP) is well covered in Unable to set sqlite PRAGMA in migrations · Issue #589 · elixir-ecto/ecto_sql · GitHub

Quoting here the answer from @greg-rychlewski for future reference

It works the following way:

  1. If you are in a transaction then each statement will be run on the same connection
  2. If you are not in a transaction then each statement will get a connection from the connection pool. It might be the same as the last one or might not, there is no guarantee. To guarantee the same one then you need a single connection in the pool

Taking a closer look at your repo, I think the issue might be with how you are setting your pool size. I don’t believe the application configuration is read for the mix tasks. It reads it from the flag --pool-size and if none exists defaults to two. So this might be your issue. Can you try running with --pool-size 1

6

Least of the problems really. There are ways to lock that prevent others from accessing the DB at the time. It’s not crystal-clear but such an app is not meant to run on several nodes – as SQLite is innately local, though people do use it over network-mounted drives, something that SQLite authors themselves say is not very safe – so it’s not difficult either. I am much more worried about extensions and user-defined-functions in the future…

I am quite busy lately and can’t work on Xqlite in full swing but since the Rust FFI code is at like 98% complete for what I want as a first version, I am practically nearly done with it and can work on the Ecto3 integration FairlySoon™.

7

Sorry, I just saw your answers! I’ve switched to PostgreSQL long ago. Too many hours have been wasted on this issue, and it was generally too frustrating to migrate data using SQLite. I also had scaling issues with my one-db-per-tenant architecture. Sure, I now have to deal with Postgres updates, but it’s still really fast because I use a socket connection on the same server.

I still like SQLite for small projects, but Postgres is now my default database for larger ones.

2 Likes