Store an entity in database and type mismatch when create command is run

Fire-Dragon-DoL · November 17, 2016, 10:31pm

Hi everyone! Exploring ROM and facing my first couple of issues. I’m creating a logger library which for now will just log to a database (in future, will do other things), so my basic first step is to just write something in the table.

Here is my table composition (the migration):

          create_table?(table) do
            column :id,
                   :uuid,
                   primary_key: true,
                   null:        false,
                   default:     Sequel.function(:uuid_generate_v4)

            column :created_at,
                   "timestamp with time zone",
                   null:    false,
                   default: Sequel::SQL::PlaceholderLiteralString.new(
                     "(statement_timestamp() at time zone 'utc')",
                     []
                   )

            column :severity,     :text,      null: false, default: "debug"
            column :message,      :text,      null: false, default: ""
            column :program_name, :text,      null: false, default: ""
            column :environment,  :text,      null: false, default: ""

            column :metadata, "jsonb",
              null:    false,
              default: "{}"
            column :tags, "text[]",
              null:    false,
              default: Sequel::SQL::PlaceholderLiteralString.new(
                "array[]::text[]",
                []
              )
            column :compressed_message, :tsvector,
              null:    false,
              default: Sequel.function(:to_tsvector, "")

            index :created_at
            index :severity
            index :message
            index :program_name
            index :environment
            index :metadata,           type: "gin"
            index :tags,               type: "gin"
            index :compressed_message, type: "gin"
          end

  class Log < Dry::Struct
    types     = ::CarbideLogger::Types
    coercible = types::Coercible
    strict    = types::Strict

    constructor_type :schema
    attribute :id,                 types::UUID.optional
    attribute :severity,           types::Severity.default(:debug)
    attribute :message,            coercible::String.default("")
    attribute :program_name,       coercible::String.default("")
    attribute :environment,        coercible::String.default("")
    attribute :metadata,           coercible::Hash.default({})
    attribute :tags,               types::ArrayOfIndifferentSymbols.default([])
    attribute :compressed_message, coercible::String.default("")
    attribute :created_at,         strict::Time.default { Time.now.utc }
  end

  class Logs < ROM::Relation[:sql]
    register_as :logs
    gateway     :default
    dataset     :logs
    schema      :infer

    def empty?
      dataset.empty?
    end

    def latest_first
      order(Sequel.desc(:created_at))
    end
  end

  class LogRepo < ROM::Repository[:logs]
    commands :create
  end

Now, beside the specific types, I create an entity Log and try to supply it to create command, but I keep getting weird errors related to SQL. Pretty sure I’m doing something wrong, but I’m not sure what.

The hash I supply looks like this:

{:id=>nil,
 :severity=>:debug,
 :message=>"",
 :program_name=>"",
 :environment=>"",
 :metadata=>{},
 :tags=>[],
 :compressed_message=>"",
 :created_at=>2016-11-17 22:33:44 UTC}

But for some reason, unless I remove the metadata key, I get Sequel::Error: The AND operator requires at least 1 argument, if I remove the metadata one, i get:

ROM::SQL::DatabaseError: PG::UndefinedColumn: ERROR:  column "debug" does not exist
LINE 1: ..."compressed_message", "created_at") VALUES (NULL, "debug", '...

Unless I convert severity from symbol to string. If I fix that too, the next problem is:

ROM::SQL::NotNullConstraintError: PG::NotNullViolation: ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, 2016-11-17 23:33:44.722287+01, debug, , , , {}, null, )

So, yeah the id is nil, but I just want it not to be supplied, because the database will generate one for me if I don’t supply it. I’m not sure how to deal with it though, if I put optional, I get nil, the key is still there, while I want it not to exist unless supplied.

If I remove the id, I get ROM::SQL::NotNullConstraintError: PG::NotNullViolation: ERROR: null value in column "tags" violates not-null constraint, which is related to the tag array, so I fill some values like [:foo, :bar] and I get

ROM::SQL::DatabaseError: PG::UndefinedColumn: ERROR:  column "foo" does not exist
LINE 1: ...age", "created_at") VALUES ('debug', '', '', '', ("foo", "ba...

If I use an array of strings instead, I get

ROM::SQL::DatabaseError: PG::DatatypeMismatch: ERROR:  column "tags" is of type text[] but expression is of type record
LINE 1: ...sage", "created_at") VALUES ('debug', '', '', '', ('foo', 'b...

And this is the last one, if I remove the tags, I can finally create my object. So from my understanding, I have a clear problem with type conversion. Anyone able to provide some guidance? I have the feeling I need some kind of mapper that converts from entity to “what the db wants”

Fire-Dragon-DoL · November 18, 2016, 2:05am

I have updates! Explicitly defining the schema improved a lot the situation:

  class Logs < ROM::Relation[:sql]
    register_as :logs
    gateway     :default

    types     = ROM::Types
    coercible = types::Coercible
    strict    = types::Strict
    pgtypes   = ROM::SQL::Types::PG

    schema :logs do
      attribute :id,                 pgtypes::UUID.optional
      attribute :severity,           coercible::String
      attribute :message,            coercible::String
      attribute :program_name,       coercible::String
      attribute :environment,        coercible::String
      attribute :metadata,           pgtypes::JSONB
      attribute :tags,               pgtypes::Array
      attribute :compressed_message, coercible::String
      attribute :created_at,         strict::Time
    end
  end

The biggest problems are the following:

UUID if nil is still supplied, which means I need to provide a value for it. I’ll survive by supplying the SQL function, but would be lovely if the column, when nil, could just be ignored/skipped
I can’t make the array work in any way. Tried with pgtypes::Array and pgtypes::Array(types::String) and even pgtypes::Array(coercible::String) but none of them worked, I keep getting

ROM::SQL::DatabaseError: PG::InvalidTextRepresentation: ERROR:  malformed array literal: "foo"
LINE 1: ...1c54ec2d2caa', 'debug', '', '', '', '{}'::jsonb, ('foo'), ''...
                                                             ^
DETAIL:  Array value must start with "{" or dimension information.

Need to solve that and I’m good to go!!!

Fire-Dragon-DoL · November 19, 2016, 3:05am

Solved by myself, after countless hours, I hope it will help other people as reference:

If you use do/end syntax, ruby thinks the block is for attribute, not constructor (you must put block between brackets)
You must manually call Sequel.pg_array to make sure the object is converted to the correct format

The solution is setting the tags attribute like this:

      attribute :tags, pgtypes::Array.constructor(
        ->(input) { Sequel.pg_array(input.map(&:to_s), :text) }
      )

And the others

      attribute :id, pgtypes::UUID.default(
        -> { Sequel.function(:uuid_generate_v4) }
      )
      attribute :severity,           coercible::String
      attribute :message,            coercible::String
      attribute :program_name,       coercible::String
      attribute :environment,        coercible::String
      attribute :metadata,           pgtypes::JSONB
      attribute :compressed_message, coercible::String
      attribute :created_at,         strict::Time.default { Time.now.utc }

solnic · December 2, 2016, 11:19am

So sorry for missing this thread. We had issues with email notifications from discourse AGAIN, and I simply didn’t receive any emails with new threads on the forum I just rebuilt discourse and upgraded to latest version and tested out emails, it works fine and I hope it won’t happen again.

Please let me know if you still need some help.

xero88 · April 24, 2018, 9:14am

And what is the way to increment the pk with standard id ?

  attribute :id,  Types::Strict::String.default(
      -> (_) { Sequel.function(:nextval) }
  )

I tried this, but it’s not the solution

Solution :

attribute :id,  Types::Strict::String.default(
      -> (_) { Sequel.function(:nextval, 'companies_id_seq') }
  )

Here ‘companies’ is my table name

This works, but it’s not clean. Is there an other way ?

Topic		Replies	Views
Relation create command returns Array, expect Hash! ROM crash Issues	8	536	April 9, 2022
Methods of storing arrays in MySQL Issues	5	1555	March 7, 2019
Specify timestamps plugin in Root repository Issues	4	452	November 13, 2023
ROM upsert and schema validation Issues	2	392	May 22, 2023
Unexpected keys [:id] in Hash input Issues	1	786	April 6, 2018

Store an entity in database and type mismatch when create command is run

Related topics