eze-simple-sql

A simple tool for generating orm js 'sql' access call functions for mysql dbs.

Reads the db tables, and creates js for accessing each table, jsdoc for the table row vo, with optional support for build simple fast "left join" queries.

Howto Install

npm install eze-simple-sql -g

Howto Run

eze-simple-sql --rds -r eu-west-2 -h xxx.xxx.eu-west-2.rds.amazonaws.com -p 3306 -d test -u admin -c 'db-join-queries.json' -o 'db-simple-queries.js'

Generates a lightweight javascript file that only requires lodash and mysql libraries to perform orm style queries.

These access functions are self-contained and can be copy and pasted individually, or as a complete package to produce minimalistic javascript for size sensitive environments like aws lambda functions or embedded systems.

CLI help

Welcome to the super simple sql js function writer
This is for projects that want super simple and small js functions for accessing mysql tables

Flags
=========================
--help for help information
--version for version information

Main Flags
=========================
--rds for stating is aws rds database and hence requires aws certs
-r/--region/env.DB_REGION for stating aws region for rds databases
-h/--host/env.DB_HOSTNAME for db hostname
-p/--port/env.DB_PORT for db port
-u/--username/env.DB_USER for db username
-d/--database/env.DB_DATABASE for database name
-p/--password/env.DB_PASSWORD for database password (if rds db, and omitted will use IAM login)
-c/--customJoins optional json file containing tables to write joining functions for

Advanced Usage

Custom Joins

For interesting fast table joins

you can add "-c xxx.json" (example in examples/ folder)

this will create "left join" queries on the "id" of the joint table

• tip: it's a good idea to make sure these join columns have foreign keys for consistency, but that's your call

aka

where tables shirts and colours exist

CREATE TABLE IF NOT EXISTS `colours` (
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Primary ID',
    `colour_name` VARCHAR(256) NULL DEFAULT NULL,
    `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE
);

CREATE TABLE IF NOT EXISTS `shirts` (
    `id` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Primary ID',
    `shirt_name` VARCHAR(256) NULL DEFAULT NULL,
    `colour_id` INT(10) NULL DEFAULT NULL,
    `last_updated` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`) USING BTREE,
    INDEX `shirts__colour_id` (`colour_id`) USING BTREE,
    CONSTRAINT `shirts__colour_id` FOREIGN KEY (`colour_id`) REFERENCES `colours` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);

You could create queries to join the tables colours and shirts together, using this json to produce the output below

[
{
"table": "shirts",
"joinColumn": "colour_id",
"joinTable": "colours",
"postFix": "_with_colour"
}
]

/**
 * Database Value Object for table join of 'shirts' to 'colours'
 * @typedef {Object} shirtWithColourVO
 * @property {number} id - Primary ID, 'id' Column on shirts
 * @property {string} shirtName - 'shirt_name' Column on shirts
 * @property {number} colourId - 'colour_id' Column on shirts
 * @property {string} colourName - 'colour_name' Column on colours
 */

// select with no where
function readShirtsWithColour (orderColumns=null, rowCount=100, offset=0)...
// select with where
function readShirtsByColumnWithColour (columnValues, orderColumns=null, rowCount=100, offset=0)...