null validation failed when attribute supplied rom-rb

Question

I'm trying to get to grips with the rom-rb persistence library, using sqlite3.

I ran the following migration, which includes a NOT NULL constraint:

ROM::SQL.migration do
  change do
    create_table :users do
      primary_key :id
      column :name, String, null: false
      column :age, Integer
      column :is_admin, TrueClass
    end
  end
end

Here's my simple app.rb:

require 'rom'

rom = ROM.container(:sql, 'sqlite://db/my-db-file.db') do |config|
  class Users < ROM::Relation[:sql]
    schema(infer: true)
  end

  config.relation(:users)
end

users = rom.relations[:users]
puts users.to_a.inspect # => []

create_user = users.command(:create)
create_user.call( name: 'Rob', age: 30, is_admin: true )
puts users.to_a.inspect # never reached

Trying to run this script produced the following output:

Roberts-MacBook-Pro:my-rom-demo Rob$ ruby app.rb 
[]
/Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:108:in `step': SQLite3::ConstraintException: NOT NULL constraint failed: users.name (ROM::SQL::NotNullConstraintError)
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:108:in `block in each'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:107:in `loop'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/statement.rb:107:in `each'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:156:in `to_a'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:156:in `block in execute'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:95:in `prepare'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sqlite3-1.3.13/lib/sqlite3/database.rb:137:in `execute'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/adapters/sqlite.rb:189:in `block (2 levels) in _execute'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/database/logging.rb:38:in `log_connection_yield'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/adapters/sqlite.rb:189:in `block in _execute'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/database/connecting.rb:253:in `block in synchronize'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/connection_pool/threaded.rb:91:in `hold'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/database/connecting.rb:253:in `synchronize'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/adapters/sqlite.rb:180:in `_execute'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/adapters/sqlite.rb:146:in `execute_insert'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/dataset/actions.rb:1099:in `execute_insert'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/sequel-5.11.0/lib/sequel/dataset/actions.rb:399:in `insert'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-sql-2.5.0/lib/rom/sql/relation/writing.rb:39:in `insert'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-sql-2.5.0/lib/rom/sql/commands/create.rb:46:in `block in insert'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-sql-2.5.0/lib/rom/sql/commands/create.rb:46:in `map'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-sql-2.5.0/lib/rom/sql/commands/create.rb:46:in `insert'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-sql-2.5.0/lib/rom/sql/commands/create.rb:31:in `execute'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-core-4.2.1/lib/rom/command.rb:280:in `call'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-sql-2.5.0/lib/rom/sql/commands/error_wrapper.rb:16:in `call'
    from /Rob.rvm/gems/ruby-2.4.0@learn-rails/gems/rom-core-4.2.1/lib/rom/commands/composite.rb:17:in `call'
    from app.rb:15:in `<main>'

Why does it think my name attribute is null when I'm providing it?

Did you try changeset? `users.changeset(:create, name: 'Bob').commit` — Martin, Aug 21 '18 at 16:55
Thanks for replying. No- I’m completely new to ROM, but as far as I can figure out from the docs changesets are an abstraction built on top of commands, and I was deliberately going for the simplest possible thing. I’ll try it and see if it works. — rwold, Aug 21 '18 at 16:59

Zerodestiny · Accepted Answer · 2018-08-22T01:03:36.217

NOTE: I revised my answer after some testing and learning about ops gem versions

The reason you're getting a NULL CONSTRAINT error is because ROM does not have a schema loaded for the the users table.

When you defined the container below

rom = ROM.container(:sql, 'sqlite://db/my-db-file.db') do |config|
  class Users < ROM::Relation[:sql]
    schema(infer: true)
  end

  config.relation(:users)
end

you defined two things, a relation class bound to a constant called Users and an auto generated relation with the same name but is actually registered inside the ROM container. Effectively the Users constant relation is being ignored. The reason this is important is because the auto generated relation isn't automatically inferring the schema from the database so when you go to write data out, the schema forces all of the unknown keys to be removed causing the error. All you're sending to the db is {}.

To fix the error just tell the relation to infer the schema - an example can be seen below.

require 'rom'
require 'rom/sql'
require 'sqlite3'

puts "ROM Version #{ROM::Core::VERSION}" # 4.2.1
puts "ROM Version #{ROM::SQL::VERSION}" # 2.5.0
puts "Sequel Version #{Sequel::VERSION}" # 5.11.0
puts "SQLite3 Gem Version #{SQLite3::VERSION}" # 1.3.13

opts = {
  adapter: :sqlite,
  database: 'c:/mydb.db'
}

rom = ROM.container(:sql, opts) do |c|
  # Just another way to write the same users table
  # c.gateways[:default].create_table(:users) do
  #   column :id,   :integer, primary_key: true
  #   column :name, :string, null: false
  #   column :age,  :integer
  #   column :is_admin, :bool
  # end

  c.gateways[:default].create_table :users do
    primary_key :id
    column :name, String, null: false
    column :age, Integer
    column :is_admin, TrueClass
  end

  c.relation(:users) do
    schema(infer: true)
  end
end

users = rom.relations[:users]
puts users.to_a.inspect # => []

create_user = users.command(:create)
create_user.call(name: 'Rob', age: 30, is_admin: true)
puts users.to_a.inspect # never reached

# Uncomment if you want to see the users schema
# puts users.dataset.db.schema(:users)

If you want to use standalone relation classes instead of the container config dsl then I suggest reading up on the Auto Registration system.

DATABASE CREATION ISSUE

There is a whole host of things that could be going on which could prevent a sqlite database from being created.

It could be a permissions issue
The directory structure might not exist
Sqlite might not be compiled to handle URI's (only matters if you are using file:// in your paths) [see sqlite docs]

My advice here is when working with sqlite and ROM, use the opts hash example from the script above and try and use a relative path from the current working directory. That seems to always work.

Yes, this works fine, thanks. I was trying to get it working with sqlite though. FYI ROM Version 4.2.1 ROM Version 2.5.0 Sequel Version 5.11.0 SQLite3 Gem Version 1.3.13 Strangely, if I take your script and replace the config with `ROM.container(:sql, 'sqlite:://my-db-file.db')` (note: no db/ path) then I get the desired output, but it doesn't seem to create the db file. Also FWIW, I did check the schema created by the migration using the sqlite cli and it seemed fine. — rwold, Aug 21 '18 at 23:17
Incidentally, trying to separate the creation of a persistent db from the script that accesses that db is why I went for a migration rather than this version (which is by far the most common in the documentation). So if you know of a more idiomatic way of achieving that I'd be grateful. — rwold, Aug 21 '18 at 23:25
If you're getting the desired output then the database is being created otherwise ROM should choke on the initial connection. Keep in mind that the path will be relative to the current working directory. Also the container definition should be `ROM.container(:sql, 'sqlite://my-db-file.db')` — Zerodestiny, Aug 22 '18 at 00:16
I updated my answer to more thoroughly answer the question but I just wanted to say that your migration scripts would work just fine and once you start registering your relations everything will work as expected. — Zerodestiny, Aug 22 '18 at 01:08
Ah, good catch on the stupid typo in the comment, thanks. Now that creates the file, and I can only run that amended version of your script once because the users table already exists. I guess my remaining issue though is that if it were either a DB issue or a registration issue, I shouldn't be able to successfully read the empty table in my original script. — rwold, Aug 22 '18 at 06:51
Just seen your update to the post- will have a play. Thanks. — rwold, Aug 22 '18 at 06:54

null validation failed when attribute supplied rom-rb

1 Answers1