DELETE query will not run locally

[barnaclebarnes]

The code below when run against a remote database will run delete queries. When run locally it will not.

# Remote DB
# Replace environment variables with your own values
@db = Libsql::Database.new(url: ENV.fetch("TURSO_DATABASE_URL"), auth_token: ENV.fetch("TURSO_AUTH_TOKEN"))
project_id = 1; key_type = "test"; key = "test"

@db.connect do |conn|
  sql = <<~SQL.squish
    CREATE TABLE IF NOT EXISTS keys(
      project_id INTEGER NOT NULL,
      key_type TEXT NOT NULL,
      key TEXT NOT NULL
    );
  SQL
  conn.query(sql)
end

@db.connect do |conn|
  sql = "INSERT INTO keys(`project_id`, `key_type`, `key`) VALUES(?, ?, ?);"
  conn.query(sql, [project_id, key_type, key])
end

@db.connect { |conn| conn.query("SELECT COUNT(*) FROM keys;").first.first }
# Returns 1

@db.connect do |conn|
  sql = "DELETE FROM `keys` WHERE `project_id` = ? AND `key_type` = ? AND `key` = ?"
  conn.query(sql, [project_id, key_type, key])
end

@db.connect { |conn| conn.query("SELECT COUNT(*) FROM keys;").first.first }
# returns 0

# Local DB
@db = Libsql::Database.new(path: Rails.root.join("turso.db").to_s)
project_id = 1; key_type = "test"; key = "test"

@db.connect do |conn|
  sql = <<~SQL.squish
    CREATE TABLE IF NOT EXISTS keys(
      project_id INTEGER NOT NULL,
      key_type TEXT NOT NULL,
      key TEXT NOT NULL
    );
  SQL
  conn.query(sql)
end

@db.connect do |conn|
  sql = "INSERT INTO keys(`project_id`, `key_type`, `key`) VALUES(?, ?, ?);"
  conn.query(sql, [project_id, key_type, key])
end

@db.connect { |conn| conn.query("SELECT COUNT(*) FROM keys;").first.first }
# Returns 1

@db.connect do |conn|
  sql = "DELETE FROM `keys` WHERE `project_id` = ? AND `key_type` = ? AND `key` = ?"
  conn.query(sql, [project_id, key_type, key])
end

@db.connect { |conn| conn.query("SELECT COUNT(*) FROM keys;").first.first }
# returns 1

[levydsa]

I was able to reproduce this behavior. I suspect a error is happening on the delete query, but something is preventing it from being thrown up to Ruby from libsql-c. In the mean time, I think that enabling WAL mode should fix it: conn.execute_batch('PRAGMA journal_mode=WAL')

[barnaclebarnes]

@levydsa The workaround seems to be working. Cheers.