node-sql-generate

This library is a command line tool/API for automatically generating definitions usable by the node-sql library, or by the Waterline ORM which is also used by sailsjs. It inspects your database and generates JavaScript.

Currently there is support for MySQL and Postgres.

Installation

Install via NPM: npm install -g sql-generate

This will put the binary node-sql-generate in your path.

Usage

Command line

In its simplest form, you do something like this: node-sql-generate --dsn "mysql://user:password@server/database" which will spit out the generated code to stdout.

// autogenerated by node-sql-generate v0.1.2 on Tue May 21 2013 01:04:12 GMT-0700 (PDT)
var sql = require('sql');

/**
 * SQL definition for database.bar
 */
exports.bar = sql.define({
	name: 'bar',
	columns: [
		'id',
		'foo_id'
	]
});

/**
 * SQL definition for database.foo
 */
exports.foo = sql.define({
	name: 'foo',
	columns: [
		'id',
		'field_1',
		'foo_bar_baz'
	]
});

If your DSN isn't as specific (i.e. it just points to a socket file), then you'll need to specify the database and dialect explicitly: node-sql-generate --dsn "/var/run/mysql.sock" --database "test" --dialect "mysql"

Postgres users will need to specify a schema: node-sql-generate --dsn "postgres://user:password@server/database" --schema "test".

Omitting it will assume a schema of "public".

--help

  Usage: node-sql-generate.js [options]

  Options:

    -h, --help                output usage information
    -V, --version             output the version number
    --dsn <dsn>               Connection string
    -d, --dialect <dialect>   Specify the SQL dialect: "mysql" or "pg"
    -o, --output-file <file>  Output to this file; defaults to stdout
    -w, --waterline           generate models for Waterline ORM (the camelize and includeSchema options are then ignored)
    -i, --indent <token>      Indentation token; defaults to a TAB character
    -D, --database <name>     Name of database to extract from
    -s, --schema <name>       Name of schema to extract from (Postgres only)
    --camelize                Convert underscored names to camel case, requires sql >= 0.18.0"
    --eol <token>             Line terminator token; defaults to "\n"
    --mode <mode>             The permission mode of the generated file; defaults to 0644
    --encoding <encoding>     The encoding to use for writing; defaults to "utf8"
    --prepend <text>          Prepend text to the beginning of the file
    --append <text>           Append text to the end of the file
    --modularize              Omit "require('sql')" and wrap generated code in "module.exports = function(sql) {...}"
    --omit-comments           Omit autogenerated comments
    --include-schema          Include schema in definition
    -v, --verbose             Print debugging information

Example DSN:
  PostgreSQL: "postgres://user:password@host:5432/database"
       MySQL: "mysql://user:password@host:3306/database"

API

You can also generate these files programmatically.

var generateSqlDefinition = require('sql-generate'),
	options = {
		dsn: 'mysql://user:password@host/database',
		omitComments: true,
		prepend: '// (c) 2013 the raddest dude alive'
	};

generateSqlDefinition(options, function(err, stats) {
	if (err) {
		console.error(err);
		return;
	}

	console.log(stats.buffer);
});

Development

To run the tests, you'll need to install Vagrant.

git clone git@github.com:tmont/node-sql-generate.git
cd node-sql-generate
npm install
vagrant up
npm test