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

**URL:** <https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106>\
**Category:** Issues\
**Created:** [November 17, 2016, 10:31pm UTC](https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106 "2016-11-17T22:31:20Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Fire-Dragon-DoL](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/fire-dragon-dol/32/89_2.png) [@Fire-Dragon-DoL](https://discourse.rom-rb.org/u/Fire-Dragon-DoL)\
**Post date:** [November 17, 2016, 10:31pm UTC](https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106/1 "2016-11-17T22:31:20Z")

</div>

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):

```auto
          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:

```auto
{: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:

```auto
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:

```auto
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

```auto
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

```auto
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”

---

<div class="post-metadata">

**Author:** ![Fire-Dragon-DoL](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/fire-dragon-dol/32/89_2.png) [@Fire-Dragon-DoL](https://discourse.rom-rb.org/u/Fire-Dragon-DoL)\
**Post date:** [November 18, 2016, 2:05am UTC](https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106/2 "2016-11-18T02:05:26Z")

</div>

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

```auto
  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:

1. 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
2. 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

```auto
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!!!

---

<div class="post-metadata">

**Author:** ![Fire-Dragon-DoL](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/fire-dragon-dol/32/89_2.png) [@Fire-Dragon-DoL](https://discourse.rom-rb.org/u/Fire-Dragon-DoL)\
**Post date:** [November 19, 2016, 3:05am UTC](https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106/3 "2016-11-19T03:05:50Z")

</div>

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

1. If you use do/end syntax, ruby thinks the block is for attribute, not constructor (you must put block between brackets)
2. 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:

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

```

And the others

```auto
      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 }

```

---

<div class="post-metadata">

**Author:** ![solnic](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/solnic/32/279_2.png) [@solnic](https://discourse.rom-rb.org/u/solnic)\
**Post date:** [December 2, 2016, 11:19am UTC](https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106/4 "2016-12-02T11:19:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![xero88](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.rom-rb.org/xero88/32/148_2.png) [@xero88](https://discourse.rom-rb.org/u/xero88)\
**Post date:** [April 24, 2018, 9:14am UTC](https://discourse.rom-rb.org/t/store-an-entity-in-database-and-type-mismatch-when-create-command-is-run/106/5 "2018-04-24T09:14:16Z")

</div>

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 ?**