tehshrike / joi-sql

Create Joi validation code for MySQL databases

joi-sql

Point it at your local database, and it spits out a Joi object validator.

Install

npm install joi-sql

(npm install -g joi-sql if you want to use it from the command-line)

The only "breaking" change from 1.x to 2.x is that support for versions of node older than 8 was dropped.

Usage

CLI

joi-sql --host=localhost --user=root --password=abc123 --schema=awesomedb --table=customer --camel

host, user, password, and camel are all optional. schema and table are not.

If camel is set, then column names are converted to camel case identifiers.

Spits out something like:

Joi.object({
	projectId: Joi.required().invalid(null).number().integer().max(4294967295).min(0),
	contactId: Joi.required().invalid(null).number().integer().max(4294967295).min(0),
	dateCreated: Joi.required().invalid(null).date(),
	engineerId: Joi.number().integer().max(4294967295).min(0),
	name: Joi.required().invalid(null).string().allow('').max(200),
	engineeringProject: Joi.required().invalid(null).boolean(),
	printingProject: Joi.required().invalid(null).boolean(),
	activeProjectStateId: Joi.required().invalid(null).number().integer().max(4294967295).min(0),
	startDate: Joi.date(),
	done: Joi.required().invalid(null).boolean(),
	doneDate: Joi.date(),
	deadReason: Joi.string().allow('').max(65535),
	quotedEngineeringHours: Joi.number().precision(1).less(10000),
	actualEngineeringHours: Joi.number().precision(1).less(10000),
	engineeringDueDate: Joi.date(),
	printParts: Joi.string().allow('').max(65535),
	printQuantity: Joi.number().integer().max(8388607).min(-8388608),
	printTimeHours: Joi.number().precision(1).less(10000),
	printDueDate: Joi.date(),
	paymentReceived: Joi.required().invalid(null).boolean(),
	contactDate: Joi.date(),
	replyDate: Joi.date(),
	quoteDate: Joi.date(),
	followUpDate: Joi.date(),
	notes: Joi.string().allow('').max(65535),
	version: Joi.required().invalid(null).number().integer().max(4294967295).min(0)
})

Programmatic

Returns a promise that resolves to a string containing the code snippet above.

const joiSql = require('joi-sql')

joiSql({
    host: 'localhost',
    user: 'root',
    password: 'abc123',
    schema: 'awesomedb',
    table: 'customer',
    camel: true
}).then(result => {
	typeof result // => 'string'
})

You may also pass in an optional connection property which must be a mysql connection instance.

Pull requests welcome.

License

WTFPL

joi-sql's Issues

Error: ER_BAD_FIELD_ERROR: Unknown column 'datetime_precision' in 'field list'

When executing I get the following error

$ joi-sql --host=127.0.0.1 --user=root --password=root --schema=test --table=test
/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/Parser.js:82
        throw err;
              ^
Error: ER_BAD_FIELD_ERROR: Unknown column 'datetime_precision' in 'field list'
    at Query.Sequence._packetToError (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/sequences/Sequence.js:48:14)
    at Query.ErrorPacket (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/sequences/Query.js:83:18)
    at Protocol._parsePacket (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/Protocol.js:271:23)
    at Parser.write (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/Connection.js:92:28)
    at Socket.emit (events.js:107:17)
    at readableAddChunk (_stream_readable.js:163:16)
    at Socket.Readable.push (_stream_readable.js:126:10)
    at TCP.onread (net.js:538:20)
    --------------------
    at Protocol._enqueue (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/protocol/Protocol.js:135:48)
    at Connection.query (/usr/local/lib/node_modules/joi-sql/node_modules/mysql/lib/Connection.js:197:25)
    at module.exports (/usr/local/lib/node_modules/joi-sql/fetch.js:15:5)
    at Object.<anonymous> (/usr/local/lib/node_modules/joi-sql/index.js:18:2)
    at Module._compile (module.js:460:26)
    at Object.Module._extensions..js (module.js:478:10)
    at Module.load (module.js:355:32)
    at Function.Module._load (module.js:310:12)
    at Function.Module.runMain (module.js:501:10)
    at startup (node.js:129:16)

Using MySQL Version 5.1.73

Empty strings

So Joi doesn't consider an empty string as valid. This is from their docs:

Note that the empty string is not allowed by default and must be enabled with allow(''). Don't over think, just remember that the empty string is not a valid string by default. Also, don't ask to change it or argue why it doesn't make sense. This topic is closed.

Docs Link

If I made a PR that added .allow('') to strings, would you accept that?

enums should be a string type?

Currently joi-sql outputs enums as any() type.
https://github.com/TehShrike/joi-sql/blob/master/build.js#L68.

As far as I can tell, MySQL enum values can only be a string.
https://dev.mysql.com/doc/refman/5.7/en/enum.html#enum-literals)

I've switched yup-sql to output enums as a string type.
https://github.com/daytonlowell/yup-sql/blob/master/build.js#L75

Thoughts?

Doesn't work on recent versions of MySQL due to uppercase columns in response

Win 10
node v10.15.1
database: cloud mysql

I can get table information via this snippet

const table_name = "users"
const sql = `desc ${table_name}`;
con.query(sql, function (err, resuts) {
  if (err) throw err;
  console.log(resuts);
  con.end();
});

But joi- sql returns:

Joi.object({
        undefined: Joi,
        undefined: Joi,
        undefined: Joi,
        undefined: Joi,
        undefined: Joi
})

I used joi-sql via this snippet with/without connection: con parameter

const con = mysql.createConnection({
  host: '***********',
  user: '*******',
  password: '************',
  database: '**********'
});


joiSql({
  host: '**********************',
  user: '*******',
  password: '********',
  schema: '*********',
  table: '****',
  camel: false,
  connection: con
}).then(result => {
  // typeof result // => 'string'
  console.log(result);
})