Relation#multi_insert fails with blob data

[paddor]

Describe the bug

Inserting records with blob attributes works using my_relation.command(:create).call(records) but it fails with my_relation.multi_insert(records).

To Reproduce

require 'logger'
require 'securerandom'
require 'rom'
require 'rom-sql'
require 'pry'


class UsersRelation < ROM::Relation[:sql]
  schema :users, infer: true

  def self.create_table(gateway)
    gateway.create_table(:users) do
      primary_key :id
      column      :name,     String, null: false
      column      :settings, :blob
    end
  end
end


class Repo < ROM::Repository[:users]
  def insert_fake_records(n)
    records = Array.new(n) do
      {
        name:     SecureRandom.uuid,
        settings: Marshal.dump({})
      }
    end

    users.transaction do
      users.command(:create).call records
    end
  end

  def multi_insert_fake_records(n)
    records = Array.new(n) do
      {
        name:     SecureRandom.uuid,
        settings: Marshal.dump({})
      }
    end

    users.transaction do
      users.multi_insert records
    end
  end
end


logger = Logger.new STDERR
config = ROM::Configuration.new :sql, 'sqlite::memory' do |config|
  config.default.use_logger logger
end


UsersRelation.create_table config.gateways[:default]
config.register_relation UsersRelation # NOTE: If done before table creation, this can lead to a race condition.

container = ::ROM.container config
repo      = Repo.new container

logger.unknown "#insert_fake_records"
repo.insert_fake_records 5

logger.unknown "#multi_insert_fake_records"
repo.multi_insert_fake_records 5

Output:

I, [2022-05-19T14:38:28.887565 #606153]  INFO -- : (0.000219s) CREATE TABLE `users` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `name` varchar(255) NOT NULL, `settings` blob)
I, [2022-05-19T14:38:28.890809 #606153]  INFO -- : (0.000202s) SELECT sqlite_version()
I, [2022-05-19T14:38:28.891337 #606153]  INFO -- : (0.000240s) PRAGMA table_xinfo('users')
I, [2022-05-19T14:38:28.892458 #606153]  INFO -- : (0.000092s) PRAGMA index_list('users')
I, [2022-05-19T14:38:28.892759 #606153]  INFO -- : (0.000089s) PRAGMA foreign_key_list('users')
I, [2022-05-19T14:38:28.895820 #606153]  INFO -- : (0.000201s) SELECT NULL AS 'nil' FROM `users` LIMIT 1
A, [2022-05-19T14:38:28.898728 #606153]   ANY -- : #insert_fake_records
I, [2022-05-19T14:38:28.899546 #606153]  INFO -- : (0.000109s) BEGIN
I, [2022-05-19T14:38:28.906446 #606153]  INFO -- : (0.000089s) INSERT INTO `users` (`name`, `settings`) VALUES ('e40f56f9-910a-4f5b-a67e-9f27767b34e8', X'04087b00')
I, [2022-05-19T14:38:28.906770 #606153]  INFO -- : (0.000046s) INSERT INTO `users` (`name`, `settings`) VALUES ('3c31dbdd-06bf-4ff3-99bb-addcae15514f', X'04087b00')
I, [2022-05-19T14:38:28.907119 #606153]  INFO -- : (0.000051s) INSERT INTO `users` (`name`, `settings`) VALUES ('a0b7738f-69bd-4341-88ea-9ea0a91ff5f2', X'04087b00')
I, [2022-05-19T14:38:28.907448 #606153]  INFO -- : (0.000054s) INSERT INTO `users` (`name`, `settings`) VALUES ('22064e5f-31d5-406c-9e89-0c38cf50305c', X'04087b00')
I, [2022-05-19T14:38:28.907754 #606153]  INFO -- : (0.000049s) INSERT INTO `users` (`name`, `settings`) VALUES ('5c291e83-c016-448e-9146-af11291b5e42', X'04087b00')
I, [2022-05-19T14:38:28.910022 #606153]  INFO -- : (0.000915s) SELECT `users`.`id`, `users`.`name`, `users`.`settings` FROM `users` WHERE (`id` IN (1, 2, 3, 4, 5)) ORDER BY `users`.`id`
I, [2022-05-19T14:38:28.910336 #606153]  INFO -- : (0.000036s) COMMIT
A, [2022-05-19T14:38:28.910438 #606153]   ANY -- : #multi_insert_fake_records
I, [2022-05-19T14:38:28.910809 #606153]  INFO -- : (0.000031s) BEGIN
E, [2022-05-19T14:38:28.911337 #606153] ERROR -- : SQLite3::SQLException: unrecognized token: "{": INSERT INTO `users` (`name`, `settings`) VALUES ('8b1b213d-980d-4d93-8038-631555ee7e69', {'), ('a03af835-7fe0-4b47-acce-7820b0474db9', {'), ('e05abbe6-114f-442d-9cf1-72fa89a732e4', {'), ('dce80468-7e2f-4e7e-989b-77f30289694a', {'), ('38b75c60-fea3-447d-8d79-158f68154fbf', {')
I, [2022-05-19T14:38:28.911700 #606153]  INFO -- : (0.000033s) ROLLBACK
/home/user/.gem/ruby/3.0.3/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:147:in `initialize': SQLite3::SQLException: unrecognized token: "'\x04\b{" (Sequel::DatabaseError)
        from /home/user/.gem/ruby/3.0.3/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:147:in `new'
        from /home/user/.gem/ruby/3.0.3/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:147:in `prepare'
        from /home/user/.gem/ruby/3.0.3/gems/sqlite3-1.4.2/lib/sqlite3/database.rb:269:in `execute_batch'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/adapters/sqlite.rb:221:in `block (2 levels) in _execute'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/logging.rb:43:in `log_connection_yield'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/adapters/sqlite.rb:221:in `block in _execute'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/connection_pool/threaded.rb:88:in `hold'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/connecting.rb:269:in `synchronize'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/adapters/sqlite.rb:209:in `_execute'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/adapters/sqlite.rb:161:in `execute_dui'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:1104:in `execute_dui'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:985:in `block (2 levels) in _import'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:985:in `each'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:985:in `block in _import'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/transactions.rb:229:in `block in transaction'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/connection_pool/threaded.rb:88:in `hold'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/connecting.rb:269:in `synchronize'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/transactions.rb:195:in `transaction'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:985:in `_import'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:351:in `import'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/dataset/actions.rb:487:in `multi_insert'
        from /home/user/.gem/ruby/3.0.3/gems/rom-sql-3.5.0/lib/rom/sql/relation/writing.rb:55:in `multi_insert'
        from multi_insert_blob.rb:44:in `block in multi_insert_fake_records'
        from /home/user/.gem/ruby/3.0.3/gems/rom-sql-3.5.0/lib/rom/sql/transaction.rb:15:in `block in run'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/transactions.rb:258:in `_transaction'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/transactions.rb:233:in `block in transaction'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/connection_pool/threaded.rb:92:in `hold'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/connecting.rb:269:in `synchronize'
        from /home/user/.gem/ruby/3.0.3/gems/sequel-5.56.0/lib/sequel/database/transactions.rb:195:in `transaction'
        from /home/user/.gem/ruby/3.0.3/gems/rom-sql-3.5.0/lib/rom/sql/transaction.rb:15:in `run'
        from /home/user/.gem/ruby/3.0.3/gems/rom-sql-3.5.0/lib/rom/sql/relation.rb:143:in `transaction'
        from /home/user/.gem/ruby/3.0.3/gems/rom-core-5.2.6/lib/rom/auto_curry.rb:51:in `block (2 levels) in auto_curry'
        from multi_insert_blob.rb:43:in `multi_insert_fake_records'
        from multi_insert_blob.rb:66:in `<main>'

My environment

• Ruby version: 3.0.3
• OS: Ubuntu 20.04

[flash-gordon]

multi_insert is a low-level API that delegates to Sequel directly, you should check its documentation

[flash-gordon]

I reckon Sequel may require some sort of escaping for blob values, though I'm not sure.

[paddor]

Yeah using Sequel.blob it works:

    records = Array.new(n) do
      {
        name:     SecureRandom.uuid,
        settings: Sequel.blob(Marshal.dump({}))
      }
    end

ROM::SQL::Relation::Writing#multi_insert is defined as @api public, so I'd still treat this as a bug.

[solnic]

@paddor it's not a bug, it's intentional that certain lower level methods don't use coercion. We should properly document it though.