db-linter

db-linter

Do you wish:

• your codebase came with some helpful markdown that provided a canonical, easily-linkable place for textual descriptions of database tables and columns to be stored?
• and required team members to update them as new ones were added?
• and made sure the schema followed certain conventions?

Then this is for you.

Table of Contents
Setup Rules How Why Caveats Markdown Example

Setup

Run this during your test suite:

require('db-linter').run({
	//sql flavor
	lang: 'postgres',//or 'mysql' (if using mariadb, say 'mysql')
	//db creds
	host: '127.0.0.1',
	port: 5432,
	user: 'postgres',//note this user will need access to information_schema
	password: '',
	database:'test',
	//module settings
	path:'./readme.md',//where it should look for a markdown file
	    //with 2 <!--DB-LINTER--> tags between which
	    //to place generated markdown
	rules:'all',//or array of rule name strings from readme
	//rule options
	boolPrefixes:['is','allow'],
	isObviousColumn:(columnName,tableName,db)=>{
		//custom reasons a column does not need describing in your setup
		//maybe columns that are everywhere, like created_at?
		return false
	}
})
.then(pass=> process.exit(pass ? 0 : 1))//or however you want to handle success / failure

Failed rules will be logged out for the dev to fix.

Rules

Below is the full list of built-in rules, but feel free to create your own and assess the json schema directly:

• require_table_description_in_readme - all tables need explanations for why they exist. Sometimes even describing table x_y as 1 x can have many y's will be appreciated going forward.
• require_column_description_in_readme - all non-obvious (customizable) columns need explanations for why they exist.
• require_lower_snake_case_table_name - some instances, collations, & OSes are case insensitive, making this the only reliable naming style for tables and columns
• require_lower_snake_case_column_name - see above.
• disallow_bare_id - columns named id have repeatedly been found to create footgun-level ambiguity downstream, and make sql more verbose & confusing by eliminating utility of the using keyword
• require_primary_key - each row should always be individually fetchable from each table, otherwise the data structure & needs may be at odds
• require_unique_primary_keys - identical primary keys would suggest they should be the same table
• require_singular_table_name - the table name should describe each row, not the table as a whole. A table holds multiple records, otherwise it would be called a pedestal; clarity is never added when a table is pluralized, it only makes remembering which part to pluralize harder when join tables inevitably have singular qualifiers.
• require_all_foreign_keys - every column titled x_id (when x is another table) should have a foreign key to table x. In composite primary key scenarios, this may require denormalizing properties to retain the link.
• require_same_name_columns_share_type - reduces confusion when talking & promotes more unique names
• require_bool_prefix_on_only_bools - is_, allow_ (etc, add your list) should always refer to boolean columns

How

This is done in a few steps:

1. it queries information_schema to provide a json schema representation of your mysql or postgres db (which you can also use in your code)
2. it constructs and updates a git-flavored markdown readme of your db from this json that preserves user-supplied descriptions across rebuilds, with each table and column deep-linkable
3. it checks whether the current state of the db follows the desired rules

Why

• Documentation - Being able to see an overview is desirable. Being able to point at something in conversation is helpful. Things not committed become folklore.

• Total Freedom Is Not Always Desirable - Dev teams, especially those which suffer from high turnaround, allow too much freedom in databases, which leads to local contradictions, which leads to ever-increasing mental overhead. Adding some reasonable rules can minimize the mental overhead necessary, and increase reliability.

  Given the levels of restrictions and rigor placed on executed code, there are curiously few placed on everything else. Such freedom in a space can send the signal that equivalent rigor is not worthwhile here, when of course it still is.

Caveats

• stored procedures, views, and enums are currently not considered, because they are not recommended.

Markdown Example

Automatically rebuilt with updates, retaining descriptions devs provide. Note all links are deep-linkable for referencing in conversation.

A 4 col-max TOC is on top, for dbs with many tables.

history dimension

rick organism_dimension

portal_gun organism

Table	Relations
dimension - a parallel plane of existence accessible with a portal gun. dimension_id pk uuid name text nullable - the name the discovering Rick gave this dimension description text nullable - distinguishing properties of this dimension	#dimension  ⭦history  ⭦organism_dimension  ⭦rick
history - an archived instance of travel via a specific portal gun, by a Rick, to a dimension, at a specific point in time history_id pk uuid portal_gun_id uuid nullable dimension_id uuid nullable rick_id integer nullable created_at timestamp without time zone nullable	⭧dimension  ⭧portal_gun  ⭧rick #history
organism - a living creature Rick has encountered organism_id pk uuid name text nullable - its apt & fitting name that was not made up on the spot image text nullable - visual confirmation of organism	#organism  ⭦organism_dimension  ⭦organism_dimension
organism_dimension - 1 organism can be found in many dimensions organism_id pk uuid dimension_id pk uuid discovered_at timestamp without time zone nullable	⭧dimension  ⭧organism  ⭧organism #organism_dimension
portal_gun - A portal gun made by a Rick, capable of opening portals to other dimensions. portal_gun_id pk uuid rick_id integer nullable	⭧rick #portal_gun  ⭦history
rick which Rick this is rick_id auto pk integer first_name text nullable last_name text nullable email text nullable dimension_id uuid nullable - which dimension this Rick originated	⭧dimension #rick  ⭦history  ⭦portal_gun

Note you can place anything above or below the <!--DB-LINTER--!> markers. But only descriptions inside the table. Everything else is regenerated.
























































This spaced added so scrolling works as expected when deep-linking.