Overview
This is a library to execute MySQL SQL files in directories.
It is not meant to be executed directly on the command line, although it can be used as the basis to create command line utilities to deploy SQL.
installation
npm install exec-sql
Procedures
connect
Connects to a given database. Currently always use 'localhost' as it was enough for our internal needs so far, but it will be specifiable as an argument in a future version.
Signature
connect(connectionObj)
Arguments
- connectionObj: This is a connection object that is passed to
mysql.createConnection. At the very least, the properties 'database', 'user' and 'password' should be set. The 'host' property will be set to 'localhost' if absent and the 'multipleStatements' will always be set true.
Return value
Returns the database connection.
disconnect
Disconnects from the database
Signature
disconnect()
Return value
None.
executeFile
Execute a given SQL file.
Signature
executeFile(file, callback)
Arguments
- file: SQL file to execute. Needs to be a valid path to the file.
- callback: Callback to be executed once the execution of the SQL files has completed. An error will be passed as its sole argument if any.
Return value
A promise will be returned if a callback is not passed as the second argument, else nothing will be returned.
executeDirectory
Execute all SQL files in a given directory. SQL files are identified by their '.sql' ending in the file name. Currently, sub-directories are ignored.
In future versions, sub-directories will be traversed recursively.
Signature
executeDirectory(directory, callback)
Arguments
- directory: Directory to execute the SQL files from. Needs to be a valid path to the directory.
- callback: Callback to be executed once the execution of the SQL files has completed. An error will be passed as its sole argument if any.
Return value
A promise will be returned if a callback is not passed as the second argument, else nothing will be returned.
Example
//Let's say that we want to execute all SQL files in the relative 'views' directory in database 'mysql' with user 'root' that has the equivalent password.
var execSQL = require('exec-sql');
var path = require('path');
execSQL.connect({
'database': 'mysql',
'user': 'root',
'password': 'root'
});
execSQL.executeDirectory(path.join(__dirname,'views'), function(err) {
execSQL.disconnect();
console.log('Done!');
});
tests
You can run the tests either directly on your host or in a dockerized work environment (I personally recommend the later).
Host
Run the following:
yarn install
yarn run test
Docker
Note that you'll need to have docker and docker-composed installed.
Run the following:
docker-compose up -d database
Note that you might have to wait a bit for the database server to properly start. You can look at the mysql server logs to infer status by typing:
docker-compose logs database
Then, run any of the 3 commands below (by default, the workspace is boron aka node 6):
docker-compose run workspace
docker-compose run workspace-argon
docker-compose run workspace-carbon
Then, from inside the workspace, type:
yarn install
yarn run test
To exit the worspace (when inside), type:
exit
To cleanup, from outside the worspace, type:
docker-compose down --volumes
Note
The tests will output some error logs on the screen. That is normal (as long as you get an indication at the end that all the tests passed) as error paths in the code are tested as well.
Release Notes
2.0.2
- Updated npm repo pointer back to clone after removal of original repo
- Improvement in repo packaging declaration
- Improved test structure and automated them
2.0.1
- Corrected default host value:
locahost -> localhost - Updated npm repo pointer to original repo after clearing permission for update
- Slight documentation improvement
2.0.0
- Changed connect method argument signature to improve connection flexibility
- Added optional promise support
- Added easy to run visual tests for node argon and boron
- Documentation update and formating adjustments
- Updated version for mysql dependency
1.1.0
Added 'file' as a propertie to logged errors when calling executeDirectory.
1.0.2
Added installation instruction and corrected error in example.
1.0.1
- Added doc and corrected errata in documentation.
- Fixed regex error.
1.0.0
Original Release