What is Codd?

Codd is a tool to help teams of developers version-control their PostgreSQL databases locally and for deployment. It provides a few main features:

Plain SQL migrations	$ cat create-animals-table.sql CREATE TABLE animals (id SERIAL PRIMARY KEY, popular_name TEXT NOT NULL); INSERT INTO animals (popular_name) VALUES ('Dog'), ('Cat'); $ codd add create-animals-table.sql Migration applied and added to sql-migrations/2022-02-27-23-14-50-create-animals-table.sql $ psql -c "SELECT popular_name FROM animals" popular_name -------------- Dog Cat (2 rows)
Extensive schema equality checks	$ psql -c "ALTER TABLE animals ALTER COLUMN popular_name TYPE VARCHAR(30)" ALTER TABLE $ codd verify-schema [Error] DB and expected schemas do not match. Differing objects and their current DB schemas are: {"schemas/public/tables/animals/cols/popular_name":["different-schemas",{"collation":"default","collation_nsp":"pg_catalog","default":null,"generated":"","hasdefault":false,"identity":"","inhcount":0,"local":true,"notnull":true,"order":2,"privileges":null,"type":"varchar"}]}
Applies pending migrations in a single transaction, optionally rolls back on schema mismatch before committing¹	$ codd up [Info] Checking if database 'codd-experiments' is accessible with the configured connection string... (waiting up to 5sec) [Info] Checking which SQL migrations have already been applied... [Info] Parse-checking headers of all pending SQL Migrations... [Info] BEGINning transaction [Info] Applying 2022-02-27-23-14-50-create-animals-table.sql [Info] Applying 2022-02-27-23-30-41-create-people-table.sql [Info] Database and expected schemas match. [Info] COMMITed transaction [Info] All migrations applied to codd-experiments successfully
Meaningful merge conflicts²	$ git merge branch-with-conflicting-db-migration Auto-merging expected-schema/schemas/public/tables/animals/cols/popular_name CONFLICT (content): Merge conflict in expected-schema/schemas/public/tables/animals/cols/popular_name Automatic merge failed; fix conflicts and then commit the result.

¹ Some SQL must run without explicit transactions; single-transaction application only works when none of that is present.
² There can be false positives and false negatives in some cases.

• What is Codd?
  • Installing Codd
    • 1. Self-contained executable
    • 2. Nix
    • 3. Docker
  • Get codd up and running in 15 minutes
  • Start using codd with an existing database
  • Safety considerations
  • Frequently Asked Questions

Installing Codd

1. Self-contained executable

If you are on x86_64-linux, the easiest thing is to download our self-contained statically linked executable from Github Releases. If you can't use that, there are two other installation methods, described below.

2. Nix

This method will install an executable named codd and make it available in your PATH just like installing from a package manager would. It is a bit more cumbersome to install than with docker but easier to use once installed.

1. Install Nix if you don't have it yet by using your package manager or following instructions from https://nixos.org/download.html.
2. Run sh <(curl -L https://raw.githubusercontent.com/mzabani/codd/master/nix/install-codd.sh) to install codd. If things are compiling and taking too long, you may want to check if you're a privileged Nix user (otherwise it means our Nix cache is not being used). After installed, just run codd --help to invoke it for the first time. To uninstall it, run nix-env --uninstall codd.

3. Docker

You can find up-to-date images of codd in DockerHub. To run codd through docker just run docker run --rm mzabani/codd --help. Invoking codd this way will often require mounting volumes, specifying UIDs and thus is more bureaucratic than other installation methods.

Get codd up and running in 15 minutes

Here's a super quick way to get a taste of codd if you have postgres running. Let's first define three required environment variables:

$ # codd understands URI or keyword value pairs, e.g. dbname=codd_experiments user=postgres host=localhost
$ export CODD_CONNECTION=postgres://postgres@localhost/codd_experiments
$ export CODD_MIGRATION_DIRS=sql-migrations
$ export CODD_EXPECTED_SCHEMA_DIR=expected-schema

Make sure you create the sql-migrations folder. If you're using docker, it helps to have these environment variables in a .env file.

But the database codd_experiments doesn't exist yet, so this connection string will not work. That is not a problem, and we can make codd create this database for us with a migration that overrides the connection string just for itself.

Create this file and save it as bootstrap-db.sql:

-- codd: no-txn
-- codd-connection: postgres://postgres@localhost/postgres

CREATE DATABASE codd_experiments;

That's a lot to take in. Codd handles pure SQL migrations but also has some special header comments defined that can make it do special things.

• The -- codd: no-txn header comment specifies that this migration can't run inside a transaction. Postgres doesn't allow us to create databases (plus a few other statements) inside transactions, after all.
• The -- codd-connection header comment specifies that this specific migration will run with its own connection string, not with the default one.

You can find more about the special migration directives that codd understands here.

Now add this migration by running one of the two commands below:

$ # If you installed codd with Nix
$ codd add bootstrap-db.sql

$ # If you're using the docker image with a .env file:
$ docker run --rm -it --env-file .env --network=host --user `id -u`:`id -g` -v "$(pwd):/working-dir" mzabani/codd add bootstrap-db.sql

The file should now have been timestamped and moved to the sql-migrations folder. The migration ran and so the codd_experiments database was created, and schema representation files were written to the expected-schema folder.

Optionally, explore the expected-schema folder. You won't find much yet, but all the files in there reflect existing database objects. That's how codd knows if schemas in different environments match and also how multiple developers can add migrations and get warned by merge conflicts if any two people modify the same database object.

Just for completeness, let's now create a table. Write the following to a create-employees-table.sql:

CREATE TABLE employee (
    employee_id SERIAL PRIMARY KEY
    , employee_name TEXT NOT NULL
);
INSERT INTO employee (employee_name) VALUES ('John Doe');

Add this migration with codd add just like you did to the previous one and it will be added and applied.

Before we finish this tutorial, some things you might want to do:

• psql into your database and manually create a table there, without a migration. Then run codd verify-schema.
• Run dropdb codd_experiments and then codd up to get a fresh database from the start.
• Read all the knobs you can configure codd with in CONFIGURATION.md.
• Read safety considerations.

Start using codd with an existing database

If you already have a database and want to start using codd without losing it, read START-USING.md. If you're running codd in multiple environments where connection strings can differ between them, environment variable templating might be of assistance.

Safety considerations

We recommend following these instructions closely to catch as many possible issues with your database setup/management as possible.

• Never merge code that has been tested without master merged into it.
  • There are non-conflicting changes which can break your App. One example is one developer removes a column and another developer writes a new query using that column. Only a test could catch this.
• Always run codd up --strict-check on CI because it's a good place to be strict.
• After running codd up --strict-check on CI, make sure codd verify-schema doesn't error. It might seem redundant because codd up --strict-check verifies schemas, but there are edge cases.
• Read about what codd cannot do in DATABASE-EQUALITY.md. This will also give you another idea about how far codd is willing to go to ensure your schema is the same across environments.

Frequently Asked Questions

1. Why does taking and restoring a database dump affect my expected codd schema?

   pg_dump does not dump all of the schema state that codd checks. A few examples include (at least with PG 13) role related state, the database's default transaction isolation level and deferredness, among possibly others. So check that it isn't the case that you get different schemas when that happens. We recommend using pg_dumpall to preserve more when possible instead. If you've checked with psql and everything looks to be the same please report a bug in codd.