DatabaseConsistency

The main goal of the project is to provide an easy way to check the consistency of the database constraints with the application validations.

Currently, we can:

find missing null constraints (ColumnPresenceChecker)
find missing length validations (LengthConstraintChecker)
find missing presence validations (NullConstraintChecker)
find missing uniqueness validations (UniqueIndexChecker)
find missing foreign keys for BelongsTo associations (ForeignKeyChecker)
find missing unique indexes for uniqueness validation (MissingUniqueIndexChecker)
find missing index for HasOne and HasMany associations (MissingIndexChecker)
find primary keys with integer/serial type (PrimaryKeyTypeChecker)
find mismatching primary key types with their foreign keys (ForeignKeyTypeChecker)
find redundant non-unique indexes (RedundantIndexChecker)
find redundant uniqueness constraint (RedundantUniqueIndexChecker)

We also provide flexible configuration (example) and integrations.

We support the following databases: SQLite3, PostgreSQL and MySQL. We support any framework or pure ruby which uses ActiveRecord.

Check out our FAQ section.

Check out the database_validations to have faster and reliable uniqueness validations and BelongsTo associations using ActiveRecord.

Installation

Add this line to your application's Gemfile:

gem 'database_consistency', group: :development, require: false

And then execute:

bundle install

If you are using ActiveStorage and/or ActionText, run the installer to prevent false positives caused by these libraries.

bundle exec database_consistency install

Usage

In the root directory of your Rails project just run bundle exec database_consistency.

Non Rails projects

For any other framework or pure ruby, you can copy the following code and create a file database_consistency_runner.rb.

# First of all, you need to load all models
# The following example is for Rails, but it can be anything
require_relative 'config/environment'
Rails.application.eager_load!

# Now start the check
require 'database_consistency'
result = DatabaseConsistency.run
exit result

Now, just start the script: bundle exec ruby database_consistency_runner.

Configuration

You can configure the gem to skip some of its checks using .database_consistency.yml file. By default, every checker is enabled.

There is also a way to pass settings through environment variables (they will have priority over settings from .database_consistency.yml file). You can pass LOG_LEVEL=DEBUG and/or COLOR=1.

Multiple configuration files

It's common scenario to have few configuration files - e.g. one is convenient setup and other is list of temporarily disabled checks that are subject to fix (TODOs). You can run the check with multiple configuration files by passing them in -c path_to_config or --config=path_to_config using built in database_consistency script:

bundle exec database_consistency -c db_consistency_todo.yml -c db_consistency_another_todo.yml

Default .database_consistency.yml config is always used and included first in this case.

Or in your custom script:

require 'database_consistency'
# Notice that here you need to explicitely pass '.database_consistency.yml' if you want it to be used along with others
result = DatabaseConsistency.run(['.database_consistency.yml', 'db_consistency_todo.yml', 'db_consistency_another_todo.yml'])
exit result

# If you need only '.database_consistency.yml' you don't have to explicitely pass it
DatabaseConsistency.run # same as `DatabaseConsistency.run('.database_consistency.yml')

The order of files is important - latest given has the highest priority. That means if we have these config files:

User:
  email:
    ColumnPresenceChecker:
      enabled: false

User:
  code:
    NullConstraintChecker:
      enabled: false
    MissingIndexChecker:
      enabled: false

User:
  code:
    NullConstraintChecker:
      enabled: true

Applying them in given order is the same as having this one config file:

User:
  email:
    ColumnPresenceChecker:
      enabled: false
  code:
    NullConstraintChecker:
      enabled: true
    MissingIndexChecker:
      enabled: false

How it works?

ColumnPresenceChecker

Imagine your model has a validates :email, presence: true validation on some field or a required belongs_to :user association but doesn't have a not-null constraint in the database. In that case, your model's definition assumes (in most cases) you won't have null values in the database but it's possible to skip validations or directly write improper data in the table. Keep in mind that belongs_to is required by default starting from Rails 5 given config.load_defaults is in place and unless config.active_record.belongs_to_required_by_default is explicitly set to false.

To avoid the inconsistency and be always sure your value won't be null you should add not-null constraint.

allow_nil/allow_blank/if/unless	database	status
at least one provided	required	fail
at least one provided	optional	ok
all missing	required	ok
all missing	optional	fail

LengthConstraintChecker

Imagine your model has limit constraint on some field in the database but doesn't have validates :email, length: { maximum: <VALUE> } validation. In that case, you're sure that you won't have values with exceeded length in the database. But each attempt to save a value with exceeded length on that field will be rolled back with error raised and without errors on your object. Mostly, you'd like to catch it properly and for that length validator exists.

We fail if any of following conditions are satisfied:

there is no length validation for the column
there is length validation for the column but with greater limit than in database, so some values will still throw an error

NullConstraintChecker

Imagine your model has not-null constraint on some field in the database but doesn't have validates :email, presence: true validation. In that case, you're sure that you won't have null values in the database. But each attempt to save the nil value on that field will be rolled back with error raised and without errors on your object. Mostly, you'd like to catch it properly and for that presence validator exists.

We fail if the column satisfies the following conditions:

column is required in the database
column is not a primary key (we don't need need presence validators for primary keys)
model records timestamps and column's name is not created_at or updated_at
column is not used for any Presence or Inclusion validators
column is not used for any Exclusion validators with nil
column is not used for any Numericality validators with allow_nil disabled
column is not used for required BelongsTo association
column has not a default value
column has not a default function

UniqueIndexChecker

Imagine your model has a unique index in the database but doesn't have validates :email, uniqueness: true validation. In that case, you're sure that you won't have duplicated values in the database. But each attempt to save a duplicated value on that field will be rolled back with error raised and without errors on your object. Mostly, you'd like to catch it properly and for that uniqueness validator exists. This checker also support unique index on multiple columns (which should have a validates :email, uniqueness: { scope: :last_name } validation).

We fail if any of following conditions are satisfied:

there is no uniqueness validation for the column(s)

ForeignKeyChecker

Imagine your model has belongs_to :user. It can happen that the user, it's being belonging to, may not be existing anymore in the database. This could bring bugs and in order to ensure the data consistency, you need to have foreign key constraint in the database.