Description
The main goal of the project is to provide an easy way to check the consistency of the database constraints with the application validations.
DatabaseConsistency alternatives and similar gems
Based on the "Database Tools" category.
Alternatively, view DatabaseConsistency alternatives based on common mentions on social networks and blogs.
-
Database Cleaner
Strategies for cleaning databases in Ruby. Can be used to ensure a clean state for testing. -
Large Hadron Migrator
Online MySQL schema migrations -
Seed dump
Rails 4/5 task to dump your data to db/seeds.rb -
Active Record Doctor
Identify database issues before they hit production. -
Lol DBA
lol_dba is a small package of rake tasks that scan your application models and displays a list of columns that probably should be indexed. Also, it can generate .sql migration scripts. -
Squasher
Squasher - squash your old migrations in a single command -
Foreigner
Adds foreign key helpers to migrations and correctly dumps foreign keys to schema.rb -
Seed Fu
Advanced seed data handling for Rails, combining the best practices of several methods together. -
Seedbank
Seedbank gives your seed data a little structure. Create seeds for each environment, share seeds between environments and specify dependencies to load your seeds in order. All nicely integrated with simple rake tasks. -
BatchLoader
:zap: Powerful tool for avoiding N+1 DB or HTTP queries -
SchemaPlus
SchemaPlus provides a collection of enhancements and extensions to ActiveRecord -
Polo
Polo travels through your database and creates sample snapshots so you can work with real world data in development. -
DatabaseValidations
Database validations for ActiveRecord -
Upsert
Upsert on MySQL, PostgreSQL, and SQLite3. Transparently creates functions (UDF) for MySQL and PostgreSQL; on SQLite3, uses INSERT OR IGNORE. -
OnlineMigrations
Catch unsafe PostgreSQL migrations in development and run them easier in production (code helpers for table/column renaming, changing column type, adding columns with default, background migrations, etc). -
SecondBase
Seamless second database integration for Rails. -
FastPage
Blazing fast pagination for ActiveRecord with deferred joins ⚡️ -
QueryTrack
Find time-consuming database queries for ActiveRecord-based Rails Apps -
Shiba
Catch bad SQL queries before they cause problems in production -
Ruby Whatsapp SDK
A lightweight, efficient Ruby gem for interacting with Whatsapp Cloud API. -
Redis Dashboard
Sinatra app to monitor Redis servers. -
Ruby PG Extras
Ruby PostgreSQL database performance insights. Locks, index usage, buffer cache hit ratios, vacuum stats and more. -
ocran
Turn ruby files into .exe files on windows (supported safe fork of ocran) -
PgDriveBackup
Simple solution to make encrypted with ccrypt PostgreSQL backups and storing on Google Drive API -
Perfect Shape
Perfect Shape is a collection of geometric algorithms that are mostly useful for GUI manipulation like checking containment of a point in popular geometric shapes such as rectangle, square, arc, circle, polygon, and paths containing lines, quadratic bézier curves, and cubic bezier curves. Also, some general math algorithms like IEEE-754 Remainder. -
Slack Smart Bot
Create a Slack bot that is smart and so easy to expand, create new bots on demand, run ruby code on chat, create shortcuts... The main scope of this gem is to be used internally in the company so teams can create team channels with their own bot to help them on their daily work, almost everything is suitable to be automated!! slack-smart-bot can create bots on demand, create shortcuts, run ruby code... just on a chat channel. You can access it just from your mobile phone if you want and run those tests you forgot to run, get the results, restart a server... no limits. -
ActiveRecord Setops
Union, Intersect, and Difference set operations for ActiveRecord (also, SQL's UnionAll). -
PgDice
Postgres partitioning built on top of https://github.com/ankane/pgslice -
ActiveRecord::DataIntegrity
Check data integrity for your ActiveRecord models -
Rapidity
Simple but fast Redis-backed distributed rate limiter. Allows you to specify time interval and count within to limit distributed operations. -
bundler-download
Bundler plugin for auto-downloading specified extra files after gem install -
SlackHook
A Simple Interface to Slack Incoming Webhooks Integrations -
PRY-BYETYPO 👋
A Pry plugin that captures exceptions that may arise from typos and deduces the correct command.
PopRuby: Clothing and Accessories for Ruby Developers
* Code Quality Rankings and insights are calculated and provided by Lumnify.
They vary from L1 to L5 with "L5" being the highest.
Do you think we are missing an alternative of DatabaseConsistency or a related project?
README
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.
If the project helps you or your organization, I would be very grateful if you contribute or donate.
Your support is an incredible motivation and the biggest reward for my hard work.
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
andHasMany
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 support autocorrection with -fix
/--autofix
options! Currently, only several issues can be auto-corrected.
We provide flexible configuration (even multiple) ([example](rails-example/.database_consistency.yml)) and integrations.
The TODO configuration can be generated automatically for you with -g
, --generate-todo
options.
We support the following databases: SQLite3
, PostgreSQL
and MySQL
.
We support any framework or pure ruby which uses ActiveRecord.
Check out our [FAQ](FAQ.md) 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](rails-example/.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](rails-example/.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
TODO generation
You can generate TODO file with the following command: bundle exec database_consistency -g
.
It works fine with passed/existing configurations and will generate the configuration only for failing.
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
orupdated_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.
We fail if the following conditions are satisfied:
- belongs_to association is not polymorphic
- there is no foreign key constraint
MissingUniqueIndexChecker
Imagine your model has a validates :email, uniqueness: true
validation but has no unique index in the database. As general
problem your validation can be skipped or there is possible duplicates insert because of race condition. To keep your data
consistent you should cover your validation with proper unique index in the database (if possible). It will ensure you don't
have duplicates.
We fail if the following conditions are satisfied:
- there is no unique index for the uniqueness validation
MissingIndexChecker
Imagine your model has a has_one :user
association but has no index in the database. In this case querying the database
to get the associated instance can be very inefficient. Mostly, you'll need an index to process such queries fast.
Additionally, has_one
associations should be unique on the database level to avoid unexpected behaviour.
We fail if the following conditions are satisfied:
- there is no index for the
HasOne
orHasMany
association - it has a
through
option
PrimaryKeyTypeChecker
ActiveRecord has changed its default types for primary keys (PR). Given no one is immune to problems short types may create, we added a checker to identify those IDs.
We fail if the following conditions are satisfied:
- primary key type is not in the list: bigint, bigserial, uuid.
ForeignKeyTypeChecker
It's dangerous to have foreign key type to be smaller than paired primary key type. Given no one is immune to possible problems, we added a checker to identify those mismatches.
We fail if the following conditions are satisfied:
- foreign key type is less than a paired primary key.
RedundantIndexChecker
This checker helps to identify redundant non-unique indexes. Assuming you have an index in the database that covers column A and another index that covers columns A and B (order is important). In this case, the first index may be removed as it is covered by second one.
We fail if the following conditions are satisfied:
- there is an index that has prefix that consists the current one.
RedundantUniqueIndexChecker
This checker helps to identify redundant uniqueness on some indexes. Assuming you have an unique index in the database that covers columns A and B (order is not important) and another unique index that covers column A only. In this case, the first unique constraint is redundant as it is covered by the second one.
We fail if the following conditions are satisfied:
- there is an unique index that consists only from columns for the current one.
Example
$ bundle exec database_consistency
NullConstraintChecker fail User code column is required in the database but do not have presence validator
NullConstraintChecker fail User company_id column is required in the database but do not have presence validator for association (company)
LengthConstraintChecker fail Company note column has limit in the database but do not have length validator
MissingUniqueIndexChecker fail User name+email model should have proper unique index in the database
ForeignKeyChecker fail User company should have foreign key in the database
ForeignKeyTypeChecker fail User company associated model key (id) with type (integer) mismatches key (company_id) with type (integer(8))
MissingIndexChecker fail Company user associated model should have proper index in the database
ForeignKeyTypeChecker fail Company user associated model key (company_id) with type (integer(8)) mismatches key (id) with type (integer)
MissingIndexChecker fail Country users associated model should have proper index in the database
ColumnPresenceChecker fail User phone column should be required in the database
ColumnPresenceChecker fail User name column is required but there is possible null value insert
UniqueIndexChecker fail User index_users_on_name_and_slug index is unique in the database but do not have uniqueness validator
RedundantUniqueIndexChecker fail User index_users_on_name_and_slug index uniqueness is redundant as (index_users_on_slug) covers it
RedundantIndexChecker fail User index_users_on_phone index is redundant as (index_users_on_phone_and_slug) covers it
ColumnPresenceChecker fail User tmp column (tmp) is missing in table (users) but used for presence validation
ForeignKeyTypeChecker fail User something association (something) of class (User) relies on field (something_id) of table (users) but it is missing
See [rails-example](rails-example) project for more details.
Integrations
Configuration example for overcommit gem.
PreCommit:
DatabaseConsistency:
enabled: true
quiet: false
command: ['bundle', 'exec', 'database_consistency']
Development
After checking out the repo, run bundle install
to install dependencies.
You need to have installed and running postgresql
and mysql
.
And for each adapter manually create a database called database_consistency_test
accessible by your local user.
MySQL
# user is your local user from $whoami command
mysql -uroot --password
mysql> CREATE DATABASE database_consistency_test;
mysql> CREATE USER user@localhost IDENTIFIED BY '';
mysql> GRANT ALL PRIVILEGES ON database_consistency_test.* TO user@localhost;
PostgreSQL
psql postgres
postgres=# CREATE DATABASE database_consistency_test;
Then, run DATABASE=<adapter name> bundle exec rspec
to run the tests for the specified database.
Available options are:
mysql
postgresql
sqlite
Default is sqlite
.
To install this gem onto your local machine, run bundle exec rake install
. To release a new version,
update the version number in version.rb
, and then run bundle exec rake release
, which will create a git
tag for the version, push git commits and tags, and push the .gem
file to rubygems.org.
Funding
Open Collective Backers
You're an individual who wants to support the project with a monthly donation. Your logo will be available on the Github page. [Become a backer]
Open Collective Sponsors
You're an organization that wants to support the project with a monthly donation. Your logo will be available on the Github page. [Become a sponsor]
Contributing
Bug reports and pull requests are welcome on GitHub. This project is intended to be a safe, welcoming space for collaboration, and contributors are expected to adhere to the Contributor Covenant code of conduct.
Code of Conduct
Everyone interacting in the DatabaseConsistency project’s codebases, issue trackers, chat rooms and mailing lists is expected to follow the [code of conduct](CODE_OF_CONDUCT.md).
Changelog
DatabaseConsistency's changelog is available [here](CHANGELOG.md).
Copyright
Copyright (c) Evgeniy Demin. See [LICENSE.txt](LICENSE.txt) for further details.
*Note that all licence references and agreements mentioned in the DatabaseConsistency README section above
are relevant to that project's source code only.