How to define a relation-schema-attribute for a Postgresql array?

questions
1

Hey :wave:

I can’t figure out how to define a schema-attribute for a PostgreSql array.

My requirement:
My users-table has a varchar[]-column, which can be NULL. If I query the record through my relation, I want to return an empty ruby-array, if the col-value is NULL.

I have made 2 attempts. The 1st one works, but I don’t like it, the 2nd one doesn’t quite work.

1st attempt

# relation
module MyApp
  module Relations
    class Users < MyApp::DB::Relation
      schema :users, infer: true
    end
  end
end

# user-struct
module MyApp
  module Structs
    class User < Auth::DB::Struct
      def ids_col
        attributes[:ids_col].nil? ? [] : attributes[:ids_col]
      end
    end
  end
end

2nd attempt

module MyApp
  module Relations
    class Users < MyApp::DB::Relation
      schema :users, infer: true do
        attribute :cost_center_ids, Types::Array, read: Types::Array.default([].freeze)
      end
    end
  end
end

seems to work, nil-values are transformed to empty array, but if I create records I get

ROM::SQL::DatabaseError:
       PG::DatatypeMismatch: ERROR:  column "ids_col" is of type character varying[] but expression is of type record
       LINE 1: ...', '2046-04-05 13:08:45.000000+0200', 6, 'chuck', ('1220...
                                                                    ^
       HINT:  You will need to rewrite or cast the expression.

Thanks

2

Looking at my code, I did this:

attribute :cost_center_ids, Types::Array.of(Types::String), read: Types::Coercible::Array

But I’m not certain it’s the exact same use-case. The problem you’re having is that Sequel uses a specific datatype for this called Sequel::Postgres::PGArray. It needs to be in that datatype when it gets sent to the Sequel compiler.

3

Thanks @alassek.

You are right. Your solution works…

attribute :cost_center_ids, Types::Array.of(Types::String), 
              read: Types::Coercible::Array

…but it requires that you fill the array with Sequel.pg_array.

factory[:user, cost_center_ids: Sequel.pg_array(%w[1 2 3])]

I also need the option to set the array to NULL, …

factory[:user, cost_center_ids: nil] # results in empty array in db

…but this is not possible with this approach, it’s an empty array. Here dry-rb - dry-types v1.2 - Maybe could help, but I haven’t figured out how it works yet.

My goal is generally to work with nil and ruby arrays. The only purpose is to get a nil array as an empty ruby array when reading the record.

Another thing I noticed:
I also wanted to use the Types that are supplied with Hanami first.

# frozen_string_literal: true

require "dry/types"

module MyApp
  Types = Dry.Types

  module Types
    # Define your custom types here
  end
end

but when I use this Types at your example, a different behavior happens.

attribute :cost_center_ids, MyApp::Types::Array.of(Types::String),
              read: MyApp::Types::Coercible::Array

I get

     Failure/Error: subject { factory[:user, cost_center_ids: Sequel.pg_array(%w[1 2 3])] }
     
     Dry::Types::SchemaError:
       ["1", "2", "3"] (Sequel::Postgres::PGArray) has invalid type for :cost_center_ids violates constraints (type?(Array, ["1", "2", "3"]) failed)
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/schema.rb:332:in `rescue in block in resolve_unsafe'
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/schema.rb:329:in `block in resolve_unsafe'
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/schema.rb:324:in `each'
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/schema.rb:324:in `resolve_unsafe'
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/schema.rb:60:in `call_unsafe'
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/constructor.rb:81:in `call_unsafe'
     # /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/type.rb:47:in `call'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/create.rb:26:in `block in execute'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/create.rb:66:in `block in with_input_tuples'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/create.rb:66:in `map'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/create.rb:66:in `each'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/create.rb:66:in `with_input_tuples'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/create.rb:25:in `execute'
     # /usr/local/bundle/gems/rom-core-5.3.2/lib/rom/command.rb:278:in `call'
     # /usr/local/bundle/gems/rom-sql-3.6.4/lib/rom/sql/commands/error_wrapper.rb:18:in `call'
     # /usr/local/bundle/gems/rom-core-5.3.2/lib/rom/commands/composite.rb:19:in `call'
     # /usr/local/bundle/gems/rom-factory-0.12.0/lib/rom/factory/builder/persistable.rb:48:in `persist'
     # /usr/local/bundle/gems/rom-factory-0.12.0/lib/rom/factory/builder/persistable.rb:28:in `create'
     # /usr/local/bundle/gems/rom-factory-0.12.0/lib/rom/factory/factories.rb:171:in `[]'
     # ./spec/slices/auth/structs/user_spec.rb:43:in `block (4 levels) in <top (required)>'
     # ./spec/slices/auth/structs/user_spec.rb:46:in `block (4 levels) in <top (required)>'
     # ------------------
     # --- Caused by: ---
     # Dry::Types::ConstraintError:
     #   ["1", "2", "3"] violates constraints (type?(Array, ["1", "2", "3"]) failed)
     #   /usr/local/bundle/gems/dry-types-1.7.2/lib/dry/types/constrained.rb:37:in `call_unsafe'

for this line of code:

factory[:user, cost_center_ids: Sequel.pg_array(%w[1 2 3])