/cas-postgres-style-tests

A collection of automated tests to ensure good sql coding practices and consistent formatting of a postgres database.

Primary LanguagePLpgSQLApache License 2.0Apache-2.0

cas-postgres-style-tests

A collection of automated tests to ensure good sql coding practices and consistent formatting within a Postgres database.

Pre-Requisites

These tests use pgTap to run. The installation instructions & troubleshooting can be found on the pgTap github page.

You may find it helpful to create a make target to handle the installation of pgTap for re-use in CI environments.
(Note: a PostgreSQL server instance must be running in order to install pgTap).

Example:

install_pgtap:
  ## install pgTAP extension into postgres
	@git clone https://github.com/theory/pgtap.git && \
		cd pgtap && \
		git checkout v1.1.0;
	@$(MAKE) -C pgtap
	@$(MAKE) -C pgtap install
	@$(MAKE) -C pgtap installcheck
	@rm -rf pgtap

Usage

Add these tests to a place that makes sense within your project's structure.

To run all the tests within your style test directory run this command in your terminal:

pg_prove -d <database name> <path/to/style/tests/>*_test.sql --set schemas_to_test=<comma separated list of schemas to test>

To run a single test file replace the * with the test file name:

pg_prove -d <database name> <path/to/style/tests/>table_test.sql --set schemas_to_test=<comma separated list of schemas to test>

pg_prove has some other helpful command line options like -v (verbose). Those options can be listed by running:

pg_prove --help

Like install_pgtap above, you may find it helpful to create a make target to run all your style tests.

Example:

prove_style:
	# Run style test suite on all objects in schemas_to_test using pg_prove
	pg_prove --failures -d $(TEST_DB) test/style/*_test.sql --set schemas_to_test=schema1,schema2

What the tests check for

The automated tests below were derived from guidelines provided for database design within the Natural Resources Ministries.

Schema tests

  • listed schemas exist
  • schemas have a description
  • schemas do not use reserved keywords as identifiers

Table tests (includes Views)

  • tables have a description
  • table names are lower-case and separated by underscores
  • tables do not use reserved keywords as identifiers

Materialized View tests

  • materialized views have a description
  • materialized views are lower-case and separated by underscores
  • materialized views do not use reserved keywords as identifiers
  • materialized views must have a primary key

Column tests

  • columns have a description
  • char columns have a defined maximum
  • columns have a defined type and are not of type [text, blob, clob, xml_type]
  • column names are lower-case and separated by underscores
  • columns do not use reserved keywords as identifiers