The Database

How do I wrangle the database?

To run the postgresql database locally, use the dev-database shortcut. If you're running it for the first time or there have been recent schema changes, you'll need to run dev-migrate-schema and then dev-migrate-data to get up to date.

Once you have the database running, you can start an interactive SQL session with:

psql $DATABASE_URL

From there, you can run queries on demand, for example this will show you all information about all documents:

select * from document;

To examine what columns, constraints, and indexes the document table has, you can ask postgres to "describe" it:

\d document

You may also want to write some query in a file (let's say my_query.sql), you can run it against the database like so:

psql $DATABASE_URL -f my_query.sql

Where is our SQL?

• types/migrations: Each .sql file includes several schema change operations that constitute one "database migration"
• types/queries: Each .sql file contains one query to pull certain information from the database

Writing SQL queries

Resources for learning how to write SQL:

• w3schools: Great reference guide for standard SQL queries. Some of the first important ones to learn are SELECT, WHERE, and INNER JOIN.
• postgresql tutorial: This site is awesome for explaining Postgres-specific topics and even SQL essentials with a postgres tint.
• The official Postgresql manual is fantastic if you're looking for info on a specific data type, function, or postgres-specific syntax.

Guidelines

• If you query multiple tables, qualify all column references with their table name.
• Use standard SQL when possible, only resort to Postgres-only syntax when you really need it.

Formatting

• SQL is case-insensitive. We use lowercase keywords like select instead of SELECT because we can distinguish between keywords and other things with syntax highlighting and context. Most editors have syntax highlighting for .sql files built-in or as an add-on, please enable that.
• We don't have our own strict guidelines on formatting, just look at other queries for inspiration.
• Automatically format your query file by navigating to the project root, and running

  sqlfluff fix types/queries

Writing New Schema Migrations

Adding a basic migration

To add new tables and fields to the database, we need to create a migration sql file. First, navigate to the types directory:

cd types

then run

sqlx migrate add "[DESCRIPTION]"

Adding a reversible migration

Warning

All migrations must be either reversible or not. Since we use non-reversible migrations now, we cannot add new reversible migrations until we retroactively convert existing migrations to be reversible.

Creating a reversible migration allows us to roll back breaking migrations without having to rebuild the db from scratch.

In the types directory, run

sqlx migrate add -r "[DESCRIPTION]"

The -r flag creates one .up.sql file and one .down.sql file, allowing for reversible migrations. In the .up.sql write setup code for new tables, fields, etc. In the .down.sql file, write teardown code for those same tables, fields, etc.