I have JSONB columns in my entities. Before, reading them by Hanami::Model, resulted in a hash. After switching to ROM, the column read from DB returns JSON.
# Before (Hanami::Model)
message.payload
# => { author: "Jsmith", content: "My message" }
# After (ROM 5.0)
# frozen_string_literal: true
module Entities
class Message < ROM::Struct
end
end
# frozen_string_literal: true
module Persistence
module Relations
class Messages < ROM::Relation[:sql]
schema(:messages, infer: true)
auto_struct true
end
end
end
class MessageRepository < Rom::Repository[:messages]
end
MessageRepository.new.messages.first.payload
# => "{\"author\":\"Jsmith\",\"content\":\"My message\"}"
Question: How I should configure a Mapper for all JSONB columns in my system at once?
#!/usr/bin/env ruby
require 'bundler/inline'
gemfile(true) do
source 'https://rubygems.org'
gem 'pg'
gem 'sequel_pg', require: 'sequel'
gem 'rom', '~> 5'
gem 'rom-sql'
end
require 'open3'
o, e, s = Open3.capture3("psql -c 'CREATE DATABASE rom_example'")
s.success? ? puts(o) : abort(e)
o, e, s = Open3.capture3('psql rom_example', stdin_data: <<~SQL)
CREATE schema public;
CREATE TABLE public.messages (
id bigint NOT NULL,
payload jsonb NOT NULL default '{}'
);
SQL
s.success? ? puts(o) : abort(e)
require 'sequel'
require 'rom'
require 'rom/sql'
ROM::SQL.load_extensions :postgres
config = ROM::Configuration.new(:sql, "postgres://localhost:5432/rom_example", extensions: %i[pg_json])
config.relation(:messages) do
schema(infer: true)
auto_struct true
end
rom = ROM.container(config)
relation = rom.relations[:messages]
attrs = { id: 1, payload: { author: "John Smith", content: "My message" } }
relation.changeset(:create, attrs).commit
message = relation.where(id: 1).one
puts "MESSAGE PAYLOAD: #{message.payload.inspect}"
rom.gateways[:default].disconnect
system "psql -c 'drop database rom_example'"
$ ./example.rb
Fetching gem metadata from https://rubygems.org/.......
Resolving dependencies...
Using bundler 2.3.3
Using dry-initializer 3.1.1
Using method_source 1.0.0
Using pg 1.4.3
Using transproc 1.1.1
Using sequel 5.59.0
Using coderay 1.1.3
Using dry-inflector 0.3.0
Using ice_nine 0.11.2
Using concurrent-ruby 1.1.10
Using dry-equalizer 0.3.0
Using pry 0.14.1
Using sequel_pg 1.15.0
Using dry-core 0.8.1
Using dry-container 0.10.1
Using dry-logic 1.2.0
Using dry-types 1.5.1
Using dry-struct 1.4.0
Using rom-core 5.2.6
Using rom-changeset 5.2.3
Using rom-repository 5.2.2
Using rom 5.2.6
Using rom-sql 3.5.0
Expanded display is used automatically.
Null display is "¤".
CREATE DATABASE
Expanded display is used automatically.
Null display is "¤".
CREATE TABLE
MESSAGE PAYLOAD: {"author"=>"John Smith", "content"=>"My message"}
Expanded display is used automatically.
Null display is "¤".
DROP DATABASE
@alassek Thank you! It actually worked! It appears, I loaded extensions and plugins wrongly.
I experienced more problems, as the Sequel::Postgres::JSONHash returns stringified keys, while the rest of attributes symbolized and it can’t be stringified via: Hanami::Utils::Hash.deep_stymbolize(attrs)
I’ve handled that. However, I got stucked with other advanced query magic with Sequel and ROM mix :(.
I have a particular interest in SQL ASTs so this was an amusing diversion, although the software engineer in me wonders if this might be simpler to just implement as a database view.
This is complex so I’m going to take this a piece at a time.
SQL literals
There’s nothing wrong with using literals this way but there are Ruby interfaces for what you’re doing.
@swilgoszRelation#pluck is supposed to be used to return values from specific columns, it doesn’t support projected columns. You can probably skip pluck and just do .to_hash(:user_id, :featured_at).