A package containing CLI scripts to manage a Postgres database including scripts to execute migrations, rollback migrations, seed or truncating a Postgres database.
npm install pg-toolboxI decided to create this package after reading this blog post from the creator of the NPM Package slonik (Gajus Kuizinas). In the blog post he makes what I think is a solid argument to write pure SQL instead of using libraries like ORMs or Knex.JS as a way to solidify your SQL knowledge, and due to the fact that dynamic query builders (like Knex.JS, for instance) are overkill for most applications and use cases.
I had heavily relied on Knex.JS in my career up to the time read this blog post, and it really resonated with me so I started using slonik to query PostgreSQL databases. However, I had grown used to the CLI commands in knex to manage migrations and seeding, and slonik does not have that capability so I built this package to be used in tandem with slonik in order to manage your database.
If you use knex and/or another ORM in all of your projects, then I would implore to give slonik and pg-toolbox a try! Especially if you are a students, or in an early stage of your career, because I guarantee you once you start writing pure SQL you will realize there are gaps in your SQL knowledge. Using these packages will help you solidify your SQL knowledge in a practical and project-based setting by building applications!
- Several CLI commands to migrate, rollback, truncate, or seed your database.
- No bloat or production dependencies. The only packages this package uses (dotenv and slonik) are development dependencies.
- Ability to inject dynamic values into Toolbox files' scripts
- Ability to dynamically build the Toolbox files' scripts
pg-toolbox should be installed as a development dependency so no bloat is added to your app. All of pg-toolboxs's CLI scripts are executed in a development environment.
``
npm install pg-toolbox -D
`
yarn add pg-toolbox -D
You only need to do this in the development environment because the development dependency dotenv is used to read the environment variables when you are running the CLI scripts to manage your database.
Example .env file:
`
PGMIGRATIONS=/path/from/root/folder/of/project/to/folder/containing/toolbox/files
PGURI=postgres://user:pass@host:port_number/database
- Toolbox files are consumed by the scripts which run after executing the CLI commands
- All toolbox files inside the pg-toolbox folder are consumed by the CLI commands. The filepath from the root folder of your project to the folder containing the toolbox files is defined by the PGMIGRATIONS environment variable.
- All scripts executed by the CLI commands are executed in alphabetical order (ascending when migrating and seeding, or descending when truncating and rolling back).
- Toolbox files must export an async function which returns an object literal.
- The object literal should contain the following keys: migrate, rollback, truncate, and seed.seed
- The only optional keys of this object are and truncate- migrate and rollback are mandatory.slonik
- Each value of the object is a query wrapped in backticks, and preceded by a sql template tag which can be imported like so: const {sql} = require('pg-toolbox')
- See the Slonik Documentation to discover many utility functions for advanced query building.
`
const { sql } = require("pg-toolbox");
module.exports = async () => ({
migrate: sqlCREATE TABLE funds (
id SERIAL PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE,
ticker VARCHAR(5) NOT NULL UNIQUE,
url VARCHAR(150) NOT NULL UNIQUE
),DROP TABLE IF EXISTS funds
rollback: sql,TRUNCATE funds RESTART IDENTITY CASCADE
truncate: sql,INSERT INTO funds (name, ticker, url)
seed: sql
SELECT *
FROM ${sql.unnest(
[
[
"ARK Innovation ETF",
"ARKK",
"https://ark-funds.com/wp-content/fundsiteliterature/csv/ARK_INNOVATION_ETF_ARKK_HOLDINGS.csv",
],
[
"ARK Autonomous Technology & Robotics ETF",
"ARKQ",
"https://ark-funds.com/wp-content/fundsiteliterature/csv/ARK_AUTONOMOUS_TECHNOLOGY_&_ROBOTICS_ETF_ARKQ_HOLDINGS.csv",
],
[
"ARK Next Generation Internet ETF",
"ARKW",
"https://ark-funds.com/wp-content/fundsiteliterature/csv/ARK_NEXT_GENERATION_INTERNET_ETF_ARKW_HOLDINGS.csv",
],
[
"ARK Genomic Revolution ETF",
"ARKG",
"https://ark-funds.com/wp-content/fundsiteliterature/csv/ARK_GENOMIC_REVOLUTION_MULTISECTOR_ETF_ARKG_HOLDINGS.csv",
],
[
"ARK Fintech Innovation ETF",
"ARKF",
"https://ark-funds.com/wp-content/fundsiteliterature/csv/ARK_FINTECH_INNOVATION_ETF_ARKF_HOLDINGS.csv",
],
],
["varchar", "varchar", "varchar"]
)},`
});
`
const { sql } = require("pg-toolbox");
module.exports = async () => ({
migrate: sqlCREATE TABLE companies (
id SERIAL PRIMARY KEY,
given_ticker VARCHAR(50) UNIQUE,
readable_ticker VARCHAR(6) UNIQUE,
cusip VARCHAR(9) NOT NULL UNIQUE,
given_name VARCHAR(50) NOT NULL UNIQUE,
readable_name VARCHAR(50) UNIQUE
),DROP TABLE IF EXISTS companies
rollback: sql,TRUNCATE companies RESTART IDENTITY CASCADE
truncate: sql,
});
`
`
const { sql } = require("pg-toolbox");
module.exports = async () => ({
migrate: sqlCREATE TABLE holdings (
id SERIAL PRIMARY KEY,
day DATE NOT NULL,
fund INTEGER REFERENCES funds (id) ON DELETE RESTRICT,
company INTEGER REFERENCES companies (id) ON DELETE RESTRICT,
shares INT NOT NULL,
value DECIMAL(14,2) NOT NULL,
weight DECIMAL(5,2) NOT NULL,
CONSTRAINT day_fund_company UNIQUE(day,fund,company)
),DROP TABLE IF EXISTS holdings
rollback: sql,TRUNCATE holdings RESTART IDENTITY CASCADE
truncate: sql,`
});
The CLI commands use the toolbox files you defined (see above) to manage your database.
- Executes migration scripts in ascending alphabetical order
- Skips any migrations which have already been ran
- A table named pg_toolbox_migrations is automatically created (and updated) to keep track of which migrations have already been executed.
- Executes rollback scripts in descending alphabetical order
- Each toolbox file's rollback script is only ran if the migration script for that file was already executed.
- The table named pg_toolbox_migrations which keeps track of migrations is automatically dropped after all rollback scripts have been processed.
- Executes truncate scripts in descending alphabetical order
- Each toolbox file's truncate script is only ran if the migration script for that file was already executed.
- Executes seed scripts in ascending alphabetical order
- Each toolbox file's seed script is only ran if the migration script for that file was already executed.
- The slonik package is the only export of this package, and can be imported to a toolbox file like so: const slonik = require('pg-toolbox')`
- This export contains the entire slonik library so you can write more advanced SQL scripts using slonik's many utility functions.
- Furthermore, toolbox files export an async function for a reason. You may perform database queries in order to inject dynamic values into your queries, or dynamically build a SQL script.