Type parsed twice with SQLite

[pointlessone]

Describe the bug

A column type is parsed twice (successively) when the db is SQLite.

To Reproduce

#!/usr/bin/env ruby
# frozen_string_literal: true

require 'bundler/inline'

gemfile do
  source 'https://rubygems.org'
  gem 'sqlite3'
  gem 'rom-sql'
  gem 'dry-core'
  gem 'dry-auto_inject'
end

require 'rom'
require 'rom/sql'

rom_config = ROM::Configuration.new(:sql, 'sqlite::memory')
gateway = rom_config.default_gateway

migration = gateway.migration do
  change do
    create_table :items do
      primary_key :id
      column :tags, 'text', null: false
    end
  end
end

migration.apply(gateway.connection, :up)

module Structs
  class Item < ROM::Struct
  end
end

class Items < ROM::Relation[:sql]
  Tags = Types.define(Types::String) do
    input { |tags| ::JSON.dump(Array(tags)) }
    output { |json| Array(::JSON.parse(json)) }
  end

  schema :items, infer: true do
    attribute :tags, Tags
  end
end

rom_config.register_relation(Items)

container = Dry::Core::Container.new
container.register("persistence.rom", ROM.container(rom_config))

Deps = Dry::AutoInject(container)

class Repository < ROM::Repository::Root
  include Deps[container: "persistence.rom"]
  def find(id) = root.by_pk(id).one
end

class ItemRepo < Repository[:items]
  struct_namespace Structs
  commands :create #, update: :by_pk, delete: :by_pk
end

repo = ItemRepo.new

# binding.irb

repo.create(tags: ['foo'])

Expected behavior

I'd expect the type transformation to be applied once.

I was told that this works as expected with a pg db but fails with SQLite.

My environment

• Affects my production application: NO
• Ruby version: 3.4.1
• OS: macOS
• DB: SQLite

[pointlessone]

I dug a little bit into the code and found a few things.

It doesn’t happen in pg because pg redefines Commands::Create#insert.

Default (and sqlite):

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

PG:

def insert(tuples)
  dataset = tuples.flat_map do |tuple|
    returning_dataset.insert(tuple)
  end

  wrap_dataset(dataset)
end

In sqlite first type mapping happens in:

block{} at […]/rom-core-5.4.0/lib/rom/relation.rb:224 (in Relation#each)

And the second time in finalize (called as the after hook).

As far as I can tell, in sqlite/default version of insert returns already deserialized columns. to_a does that (actually, Relation#each).

In pg this is not the case. insert returns values from the db and finalize does the actual deserialization.

I also confirmed that this double type cast happens in mysql as it uses the default insert.

There's obviously an issue with insert returning different things but I'm not sure which is the correct one. There are a few options here:

1. pg is wrong. insert should type case. There was Materialize relation on single insert, fixes #57 #58 that explicitly introduced relation materialisation. It's a fix to Create#insert doesn't materialize returned relation #57, it mentions weird things happening and even crashes. At the time there was no pg-specific Create#insert. It was introduced later and it didn't exactly mirror the default implementation. I don't know if pg's insert is susceptible to the issue described in Create#insert doesn't materialize returned relation #57. TBH, I'm not even sure if relation materialisation and type casting should go together or are independent.
2. The default is wrong. insert should not type case. At least that's the impression I get from Add missing PG types with auto-inference #131 where finalize was introduced. Maybe, relation materialisation should happen in the finalize as well.
3. Types should be idempotent. This seem less plausible. For one, it's not always possible to detect if the value has already been cast. For example, let's say I have a Base64 type. Is there a reliable way to know if "beef" an encoded streeng that need decoding, or if already was decoded? Likewise, is "123" an already decoded JSON that turned out to be a string, or is it a JSON string that should be decoded to a number?