Ruby - Creating local database and checking if rows already exist

I am using "sequel" and "sqlite3" gems.

I created a local database with

DB.create_table :numbers do
  primary_key :id
  String :important_number
end

I have a list of numbers that are inserted into the database everyday, but before the insert, I need to somehow check if the records already exists in the database or not.

Inserting to database with

numbersFromAFile = Some Code
DB[:numbers].insert(:important_number => numbersFromAFile)

No problems with inserting, but now I need an if statement or something else to check if the important_number already exists.

So something like

if !DB[:numbers][:important_number] == numbersFromAFile
  DB[:numbers].insert(:important_number => numbersFromAFile)
else
  # do something else...
end

EDIT: added unique index

DB.create_table? :numbers do
  String :important_number, unique: true
end

and using

  if DB[:numbers].insert_conflict.insert(:important_number => numbersFromFile)
    DO SOMETHING
  else
    DO SOMETHING ELSE
  end

Solution

SQLite3 supports various conflict resolution methods in INSERT which you can leverage from the sequel gem.

For these methods to work, it is necessary that you define proper uniqueness constraints in your database schema in a way that a "duplicate" INSERT would result in a uniqueness constraint violation. From your example, you could define a unique index in your important_number column.

With this index in place, you can instruct SQLite to ignore an insert if it would result in such a violation by using an INSERT OR IGNORE ... statement.

With sequel, you can emit such a statement with:

DB[:numbers].insert_conflict.insert(:important_number => numbersFromAFile)

See the documentation on insert_conflict for additional options on how to handle conflicts (such as e.g. updating the existing record instead).

insert will either return the auto-generated ID if a new row was inserted or nil if nothing was inserted. You can thus implement your alternative action as:

unless DB[:numbers].insert_conflict.insert(:important_number => numbersFromAFile)
  # do something else...
end