db

Database Schema and Tooling

Migration and seed files plus some administration scripts that help to design a PostgreSQL database.

This project was bootstrapped with Node.js API Starter Kit. Be sure to join our Discord channel for assistance.

Directory Layout

.
├── backups                     # Database backup files
│   └── ...                     #   - for example "20200101T120000_dev.sql"
├── migrations                  # Database schema migration files
│   ├── 001_initial.js          #   - initial schema
│   └── ...                     #   - the reset of the migration files
├── scripts                     # Automation scripts (Knex.js REPL shell, etc.)
│   └── ...                     #   - ...
├── seeds                       # Database seed files
│   ├── 00_reset.js             #   - removes existing db records
│   ├── 01_users.js             #   - creates user accounts
│   ├── 01_users.json           #   - user accounts dataset
│   └── ...                     #   - the reset of the seed files
├── ssl                         # TLS/SSL certificates for database access
├── knexfile.js                 # Configuration file for Knex.js CLI
├── package.json                # Node.js dependencies
└── README.md                   # This file

Requirements

• Node.js v12 or higher, Yarn package manager
• Local or remote instance of PostgreSQL (see Postgres.app, Google Cloud SQL)
• Optionally, psql, pg_dump, pg_restore client utilities (brew install libpq ❐)

How to open the database

You can access the database either by using a terminal window:

$ yarn db:repl                  # Launches Knex.js REPL shell
$ yarn db:psql                  # Launches PostgreSQL REPL shell

Or, by using a GUI such as Postico. Find connection settings inside of the env package.

Optionally pass the --env=#0 argument with one of the pre-configured environments — dev (default), local, test, or prod.

How to create a new migration

Create a new .js file inside of the migrations folder, give it a descriptive name prefixed with the migration version number, for example 002_products.ts. Open it in the editor, start typing migration and hit TAB which should insert a VS Code snippet.

How to migrate database schema and data

$ yarn db:version               # Prints the current schema version to the console
$ yarn db:migrate               # Migrates database schema to the latest version
$ yarn db:seed                  # Seeds database with some reference data

While the app is in development, you can use a simplified migration workflow by creating a backup of your existing database, making changes to the existing migration file (migrations/001_initial.ts), re-apply the migration and restore all the data (as opposed to re-seeding).

How to re-apply the latest migration

$ yarn db:rollback              # Rolls back the latest migration
$ yarn db:migrate               # Migrates database schema to the latest version
$ yarn db:seed                  # Seeds database with some reference data

Alternatively, you can drop and re-create the database, then apply all the outstanding migrations and seed files over again by running:

$ yarn db:reset                 # Re-creates the database; applies migrations and seeds

How to backup and restore data

$ yarn db:backup                # Exports database data to a backup file
$ yarn db:restore               # Restores data from a backup file

You can find backup files inside of the /backups folder.

References

• Knex.js Migration API
• Knex.js Seed API

License

Copyright © 2016-present Kriasoft. This source code is licensed under the MIT license found in the LICENSE file.