@forty-boy/sql-cli

@Forty-boy/SQL-CLI

A CLI for the @forty-boy/sql library.

Installing the Project

1. npm install @forty-boy/sql-cli OR yarn add @forty-boy/sql-cli
2. Follow Project Setup

---

Project Setup

If you want to get started using @forty-boy/sql and @forty-boy/sql-cli here are the necessary steps to take to get your project functioning properly.

1. Create a Folder for your TypeScript to live (e.g. ./src)
2. Create a .env file within that folder for your forty configurations
   • This should look like:

     FORTY_HOST=<YOUR-DB-HOST>
     FORTY_DB=<YOUR-DB-NAME>
     FORTY_USER=<YOUR-DB-USERNAME>
     FORTY_PASS=<YOUR-DB-PASSWORD>
     FORTY_COMPILED_DIR=<YOUR-COMPILED-CODE-FOLDER>
     

3. From within your ./src folder run your @forty-boy/sql-cli commands. Such as
   • forty-sql migration:add users
4. You should notice a new folder called database with two sub-folders; migrations and schemas.
5. Alter those to your liking then run forty-sql migration:up
   • Ensure you're still within your ./src directory. This is important for forty-sql to find the right files.

Why is the folder structure so important?

@forty-boy/sql-cli navigates this file structure and it's not magic. The CLI must know where to go and where files live to function properly. The reason the FORTY_COMPILED_DIR environment variable is so important is because the library needs to know where your migration files are to run them.

My Personal Structure:

lib // Javascript
├───database
│   ├───migration
│   └───schema
│
src // TypeScript
└───database
    ├───migration
    └───schema

---

Cloning the Project

1. Clone the repository here
2. Run npm install OR yarn install

---

Changelog

Version 0.0.4

• Upgraded to @forty-boy/sql version 1.1.2 and did all the proper upgrade steps.

Version 0.0.3

• Can now run forty-sql migration:up
  • This will run all migrations in chronological order
• Can now run forty-sql migration:down
  • This will undo all migrations in chronological order
• Can now run forty-sql migration:undo
  • This will undo the most recent migration`
• Can now run forty-sql migration:redo
  • This will redo the most recent migration`
• Now in your .env you must have a FORTY_COMPILED_DIR flag to point to your compilation folder. This will tell forty-sql where your migrations are. See Project setup section for more details.

Version 0.0.2

• Can now run forty-sql migration:add <migration-name> [--add | -a]
  • This will create a migration file (no schema) with an add column query template.
• Can now run forty-sql migration:add <migration-name> [--update | -u]
  • This will create a migration file (no schema) with an update column query template.
• Can now run forty-sql migration:add <migration-name> [--remove | -r]
  • This will create a migration file (no schema) with a remove column query template.
• Can now run forty-sql migration:add <migration-name> <action> [--table | -t] <table-name>
  • This will create a migration with the given action and target the given table

Version 0.0.1

• Initial Commit
• Can run forty-sql migration:add users
  • This will create a migration file and a schema file
• Can run forty-sql schema:add users
  • This will create a schema file.

---

Commands

Migration

• Add forty-sql migration:add <migration-name>
  • This will create the directories ./database/migrations and ./database/schemas if they do not already exists and add the corresponding files w/ boilerplate.
  • E.g. forty-sql migration:add users will create the following:
    • ./database/migrations/<timestamp>_users.ts
    • ./database/schemas/User.schema.ts
• Up forty-sql migration:up
  • This will run all migrations in chronological order
• Down forty-sql migration:down
  • This will undo all migrations in chronological order
• Undo forty-sql migration:undo
  • This will undo the most recent migration`
• Redo forty-sql migration:red
  • This will redo the most recent migration`

Schema

• Add forty-sql schema:add <schema-name>
  • This will create the directory ./database/schemas if it does not already exist and the corresponding file w/ boilerplate.
  • E.g. forty-sql schema:add users will create the following:
    • ./database/schemas/User.schema.ts

---

Flags

Migration

• Add forty-sql migration:add <migration-name> [--add | -a]
  • This will create a boiler plate migration for adding a column to a table (does not add/update schema).
• Update forty-sql migration:add <migration-name> [--update | -u]
  • This will create a boiler plate migration for updating a column to a table (does not add/update schema).
• Remove forty-sql migration:add <migration-name> [--remove | -r]
  • This will create a boiler plate migration for removing a column from a table (does not add/update schema).
• Table forty-sql migration:add <migration-name> <action> [--table | -t] <table-name>
  • This will create a boiler plate migration with the given action and target the given table
    • E.g. forty-sql migration:add AddFullNameColumn --add --table users

---

Future Release Plans

• Create a remove command for Migrations

  • This will run the down method from the last created migration and remove it.

• Create a migration history table to track migrations similar to EF Core to prevent unwanted side-effects