How to set PRAGMA foreign_keys=OFF in Ecto migration?

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.14.0

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

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.

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: SQL provides state-of-the-art, high-performance SQL integration for Elixir, built to handle extreme concurrency with unmatched expressiveness and ergonomic query composition. Write safe, composable, parameterized queries directly, without translating to Ecto or any ORM. · GitHub

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.

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

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™.

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.