Grunt tasks for version control on PostgreSql stored procedures, automatized database restore/dump, query execution.
WARNING: this project is no longer maintained by the developers, we have switched to a way more stable solution: pg_extractor
This plugin requires Grunt ~0.4.1
If you haven't used Grunt before, be sure to check out the Getting Started guide, as it explains how to create a Gruntfile as well as install and use Grunt plugins. Once you're familiar with that process, you may install this plugin with this command:
npm install grunt-pg-utils --save-devOnce the plugin has been installed, it may be enabled inside your Gruntfile with this line of JavaScript:
grunt.loadNpmTasks('grunt-pg-utils');All of the listed tasks are gruntjs multitasks, see the documentation about what this emplies.
For all the possible values in this object you can refer to node-postgres config Object documentation,
dump, restore and run-sql tasks take a small subset of those options, since they are based off
official postgres tools (pg_dump, psql).
Such subset is composed of:
'dump/restore/run-sql': {
targetname: {
connection: {
user: 'username',
password: 'password',
host: '127.0.0.1',
port: 5432,
database: 'dbname' // Only used in `run-sql` queries that affects a specific DB.
}
}
}Key task to backup the stored procedures in your database(s).
It accepts a regex that is used to filter postgreSQL functions.
This task has a mandatory dest destination path, it must be a directory and options.connection as written here.
Type: gruntjs files documentation link REQUIRED
Default: NONE
Type: String REQUIRED
Example: somefunc_[aeiou]*
PostgreSQL functions will be matched against this regex, if they do, they will be written as text file for version control.
Type: String OPTIONAL
Default: {{fname}}-{{fargv}}.sql
Possible variables:
| Variable | Content | Example |
|---|---|---|
| fname | Function Name | fn_test_sp |
| nspace | Function Namespace | public |
| fargs | Function Arguments, number | 2 |
| fargv | Function Arguments, type | (int, boolean) |
| fargdesc | Function Arguments, extented type | (argname int, booleanvalue boolean) |
| fdef | Function Definition | CREATE OR REPLACE FUNCTION public.fn_test_sp(argname int, booleanvalue boolean) .... |
| fdesc | Function Description | 'This is a Comment to the Function...' |
Task to restore a bunch of .sql files containing Stored Procedures definition(s).
This task has a mandatory src source file/array and options.connection as written here.
Type: gruntjs files documentation link REQUIRED
Default: NONE
Task to dump a database using the pg_dump utility present in your system.
This task has a mandatory dest file and options.connection as written here, note this task has a subset of options as described.
It REQUIRE(S) a database to be specified in options.connection.
Type: String REQUIRED
Default: NONE
It's a relative path string, it MUST be a single file per target.
Type: String OPTIONAL
Default: pg_dump
In case pg_dump utility is installed in a particular directory, put the complete path here.
Task to restore a (previously) dumped database, using psql utility present in your system.
This task has a mandatory src file and options.connection as written here.
If an options.connection.database key is specified, it will be ignored, as it does not apply in this task.
Type: String REQUIRED
Default: NONE
It's a relative path string, it MUST be a single file per target.
Type: String
Default: psql
In case psql utility is installed in a particular directory, put the complete path here.
Task that provides ability to run of arbitrary low or high level sql code.
Uses psql utility present in your system.
This task has a mandatory src file/array and options.connection as written here.
NOTE: Pay particular attention to options.connection.database on this task.
If you have some low-level queries (to be run without a database), you don't have to provide one.
Otherwise, you need to provide one.
If you have mixed queryes, for example: some that creates a database, some others that creates tables.
You shall create 2 grunt targets, one with an options.connection.database specified (the former), and the latter without.
Type: gruntjs files documentation link REQUIRED
Default: NONE
Type: String
Default: psql
In case psql utility is installed in a particular directory, put the complete path here.
Refer to Gruntfile.js for extended usage example
module.exports = function (grunt) {
var dbConnection = {
user: 'postgres',
password: 'postgres',
database: 'dbname',
host: 'localhost',
port: 5432
};
grunt.initConfig({
'backup-sp': {
demotarget: {
dest: 'spsql/dbname/',
options: {
connection: dbConnection,
spRegex: '^(sp_|fn_).*',
filenameFormat: '{{nspace}}.{{fname}}-{{fargv}}.sql'
}
}
},
'restore-sp': {
demotarget: {
src: 'spsql/dbname/*.sql',
options: {
connection: dbConnection
}
}
},
clean: ['spsql']
});
// Load tasks
grunt.loadNpmTasks('grunt-pg-utils');
grunt.loadNpmTasks('grunt-contrib-clean');
// Default task(s).
grunt.registerTask('default', ['clean', 'backup-sp']);
grunt.registerTask('restore', ['restore-sp']);
};Than you can than use:
$ grunt backup-sp
$ grunt restore-sp:1We would be happy to accept external contributions, would this be pull requests, issues, or general encouragement.
Requirements:
- Postgres 9.x installation
- Postgres 9.x utilities
- Configure such postgreSQL instllation in
Gruntfile.js
Edit Gruntfile.js with your credentials:
var defUser = 'postgres',
defPassword = 'postgres',
defHost = '127.0.0.1',
defPort = 5432;
- Run tests:
grunt test
- 01/10/2013 v0.1.0 Multitask release
- 06/08/2013 v0.0.2 Progress release - Implement a task to run SQL files - Add multiple servers ability - Include tests
- 30/07/2013 v0.0.1 Initial release