Helpful tools for developers. Currently focused on SQL interaction from the command line.
Globally install pddev from npm...
npm install -g pddev
Globally install the following support packages from npm...
npm install -g postgrator, postgrator-cli, mssql
Create a config.json file wherever your pddev got installed globally -
usually C:\users\<username>\AppData\Roaming\npm\node_modules\pddev
Paste in the below initial content, replacing whatever sql local user your dev team uses...
{
"host": "127.0.0.1",
"port": 1433,
"username": "theNameOfYourLocalSqlUser",
"password": "thePasswordForYourLocalSqlUser"
}Commands:
help Display help
migratedb, m Migrate database using scripts in local sql folder
nukedb, n Nuke database (drop, then create empty)
rebuilddb, r Rebuild your database (drop, create, migrate)
restoredb, s Restore database from bak file
test, t Test cool new console stuffs
version Display version
Options:
-d, --databaseName The database you want to act on
-h, --help Output usage information
-v, --version Output the version number
****** The following can be executed anywhere ******
Nuke will kill all existing connections on named database, drop the database, recreate the database (empty), then ensure your local sql user has the appropriate rights on the database.
pddev-nukedb -d databaseName
Equivalent to...
pddev n -d databaseName
****** The following require a sql folder relative to where the commands are invoked ******
Restore will perform a nuke, then it will look for ./sql/restore.bak relative to where the command is invoked. It will restore the restore.bak into the named database. The databaseName provided must match the filenames in the restore.bak file.
pddev-restoredb -d databaseName
Equivalent to...
pddev s -d databaseName
****** The following also require a postgrator.json file where the commands are invoked - our convention is at the root of the repo, in addition to a sql folder ******
Create the following file - postgrator.json. The convention is that each repo has one database it is responsible for. That database is configured for migrations here. We are using postgrator - https://github.com/rickbergfalk/postgrator and postgrator-cli - https://github.com/MattiLehtinen/postgrator-cli for migrations.
{
"migrationDirectory": "sql",
"driver": "mssql",
"host": "127.0.0.1",
"port": 1433,
"database": "databaseNameForMigrations",
"username": "theNameOfYourLocalSqlUser",
"password": "thePasswordForYourLocalSqlUser",
"schemaTable": "DbMigrationVersion"
}Postgrator also requires the sql folder to hold the sequential list of sql scripts needed to migrate the database. See https://github.com/rickbergfalk/postgrator for details.
Migrate will execute whatever sql scripts in the sql folder have not been executed already against the provided database.
No database name is needed because the configuration info in postgrator.json is used to migrate the database.
pddev-migratedb
Equivalent to...
pddev m
Rebuild will execute a full Nuke, then a Migrate
pddev-rebuilddb -d databaseName
Equivalent to...
pddev r -d databaseName