Migration and seed files plus some administration scripts that help to design a PostgreSQL database.
This project was bootstrapped with GraphQL API Starter Kit. Be sure to join our Discord channel for assistance.
.
├── backups # Database backup files
│ └── ... # - for example "20200101T120000_dev.sql"
├── migrations # Database schema migration files
│ ├── 001_initial.ts # - initial schema
│ └── ... # - the reset of the migration files
├── scripts # Automation scripts (Knex.js REPL shell, etc.)
│ └── ... # - ...
├── seeds # Database seed files
│ ├── 00_reset.ts # - removes existing db records
│ ├── 01_users.json # - user accounts dataset
│ ├── 01_users.ts # - creates user accounts
│ ├── 02_identities.json # - user accounts dataset
│ ├── id_identities.ts # - creates user accounts
│ └── ... # - the reset of the seed files
├── ssl # TLS/SSL certificates for database access
├── knexfile.ts # Configuration file for Knex.js CLI
├── package.json # Node.js dependencies
└── README.md # This file
- Node.js v16, 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
❐)
You can access the database either by using a terminal window:
$ yarn db:repl [--env #0] # Launches Knex.js REPL shell
$ yarn db:psql [--env #0] # 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
.
Create a new .ts
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.
$ yarn db:version [--env #0] # Prints the current schema version to the console
$ yarn db:migrate [--env #0] # Migrates database schema to the latest version
Optionally, clean up up the database and seed it with some sample/reference data:
$ yarn db:seed [--env #0] # Seeds database with sample/reference data
While the app is in development, you can use a simplified migration workflow by
creating a backup of your existing database (data only), making changes to the
existing migration file(s) (e.g. migrations/001_initial.ts
), re-applying the
migrations, and restoring data from the backup file. For example:
$ yarn db:backup [--env #0] # Dumps database data (only) to a backup file
$ yarn db:reset [--env #0] [--from #0]
Where --from #0
flags tells the db:reset
script to import data from the
latest backup file of the selected environment (local
, dev
, test
,
or prod
).
Or, reset and seed database by running:
$ yarn db:reset [--env #0] [--seed]
$ yarn db:rollback [--env #0] # Rolls back the latest migration
$ yarn db:migrate [--env #0] # Migrates database schema to the latest version
$ yarn db:backup [--env #0]
$ yarn db:restore [--env #0] [--from #0]
You can find backup files inside of the /backups
folder.
Generate seed files by using Faker.js (see /seeds/*.ts
).
Alternatively, fetch the actual data from the database and save it into JSON
files as seeds by running:
$ yarn db:import-seeds [--env #0]
Copyright © 2016-present Kriasoft. This source code is licensed under the MIT license found in the LICENSE file.