create returns nil for custom primary keys

[Jammjammjamm]

Describe the bug

When using a randomly generated string as a primary key, create returns nil.

[1] pry(main)> repo.create(text: 'blah')
I, [2020-12-09T10:48:53.147651 #14228]  INFO -- : (0.000085s) INSERT INTO `notes` (`id`, `text`) VALUES ('129778db-7cdb-4509-9484-86a92403d13a', 'blah')
I, [2020-12-09T10:48:53.147987 #14228]  INFO -- : (0.000065s) SELECT `id`, `text` FROM `notes` WHERE (NULL IN (1))
=> nil
[2] pry(main)> repo.create(text: 'blahblahblah')
I, [2020-12-09T10:48:59.883589 #14228]  INFO -- : (0.000061s) INSERT INTO `notes` (`id`, `text`) VALUES ('a89e11cc-c7d2-46c2-8eb2-ead99044b8b4', 'blahblahblah')
I, [2020-12-09T10:48:59.883807 #14228]  INFO -- : (0.000058s) SELECT `id`, `text` FROM `notes` WHERE (NULL IN (2))
=> nil
[3] pry(main)> rom.relations[:notes].count
I, [2020-12-09T10:49:03.836183 #14228]  INFO -- : (0.000092s) SELECT count(*) AS 'count' FROM `notes` LIMIT 1
=> 2

To Reproduce

require ‘sqlite3’
require ‘rom’
require ‘rom-sql’
require ‘rom-repository’
require ‘pry’

rom = ROM.container(:sql, 'sqlite::memory', logger: Logger.new(STDOUT)) do |config|
  config.default.create_table :notes do
    column :id, :string, primary_key: true
    column :text, :string
  end

  config.relation(:notes) do
    schema(:notes) do
      attribute :id, ROM::Types::String.default { SecureRandom.uuid }
      attribute :text, ROM::Types::String

      primary_key :id
    end
  end
end

class NotesRepo < ROM::Repository[:notes]
  commands :create
end

repo = NotesRepo.new(rom)

binding.pry

Expected behavior

create should return the record that was created.

My environment

• Affects my production application: NO
• Ruby version: 2.7.2
• OS: macOS 10.15.7

[solnic]

I'm working on various improvements in the command API in rom 6.0 that will be reflected in rom-sql 4.0 and so I'll address this issue as part of this work.

[elct9620]

Test in rom 5.0, when primary key isn't integer it always return nil

[elct9620]

I use TracePoint to check the values. The return value invalid causes it.

rom-sql/lib/rom/sql/commands/create.rb

Lines 47 to 50 in 4fe989e

	def insert(tuples)
	pks = tuples.map { |tuple| relation.insert(tuple) }
	relation.where(relation.primary_key => pks).to_a
	end

The pks will return [1] when we insert a new record. And the relation.where use the pks to find the data to return. But our primary_key is UUID that cannot match the 1 and we got the empty results.

The tracepoint usage:

trace = TracePoint.new(:return) { |tp| pp [tp, tp.path, tp.method_id]; tp.binding.pry if tp.path.include?('commands/create.rb') }; trace.enable { UserRepository.new.create(name: 'Aotoki') }

Add Sequel::Dataset#returning may help it:
https://www.rubydoc.info/github/jeremyevans/sequel/Sequel%2FDataset:returning

def insert(*args, &block)
  dataset.returning(primary_key).insert(*args, &block).map { |hash| hash[primary_key] }
end

The SQLite version too old will receive RETURNING is not supported on sqlite (Sequel::Error) error

[ginjo]

I too am having this issue trying to work with a number of tables with non-numeric primary keys. Are there any updates on this? Any workarounds?