andreh

How to set PRAGMA foreign_keys=OFF in Ecto migration?

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?

6 comments

#sqlite3

6 241 6

2025-09-10 14:55:28 UTC

Marked As Solved

andreh

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.

Post #7

Also Liked

dimitarvp

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.

Post #3

a-nassim

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

Post #2