avery246813579

gkelley

jssql

brennen.ryder

  "description": "A simple javascript MySQL library",

  "author": "Avery Durrant <avery@frostbyte.co>",

    "url": "https://github.com/avery246813579/JSSQL/issues",

    "url": "https://github.com/avery246813579/JSSQL.git"

This is a node.js driver that makes SQL easy! It doesnt matter if you're a SQL noob or expert, everyone can enjoy JSSQL.

The first thing we need to do is set up a database. The code below initializes a database instance and if the database does not exist, then it will create one. 

We can also add in the `connectionLimit` message to add the number of 

Now that we have a database initilized we can go right into creating out first scheme and table. Let's start by creating our scheme. 

The most important part about creating a scheme is the syntax we use. Inside the project we include constant variables that can be used that describe types and indexs. You can use the constants we define or just write in the keywords yourself. The column syntax is shown below:

- type: The datatype of the column (required)

- length: The datatype length of the column

- ai: If the column is auto incrementing 

Once the Scheme is setup, we then have to create our table. A table definition only needs to contain the table name and the scheme. The most important part about creating a table is remembering to pair it with a database. Without a database pair, the table will fail to work. 

var testTable = new Table('TABLE_NAME', testScheme);

Once we have our table sorted out, we can place some information in it. Below is a quick example of how to add a row.

Recieving a row is just as easy! Just use the find function and we can get all the rows with the information we want!

If we just want to find one row, we can use the findOne function. Instead of having a callback that returns rows, the findOne callback returns one row.  

There is a small window of time that the save function takes to actually save the new row. Because of this time, having a save and find right after each other will not show the new row. This should not be a problem unless you perform the find directly after the save. If doing this is completely necessary, set a timeout before you call the find. 

If you want to update a row, use the update function. The example below updates all the rows where NAME is equal to 'BOB' and sets NAME equal to 'NOT BOB'

A common use case of the WHERE clause in SQL is to find records that match one of a set of values. To do this, simply pass an array as the value for one of your where clauses. Below finds Products that have the ID of either 1 or 2 and a LOCATION_ID of 1.

If we want to do more advanced select statements, we are going to use the `findAdvanced` function. We use the first

argument to find using basic equal conditions (like above), the second argument is to do a list of "advanced" topics.

This is by far the best way to do complicated queries. 

Below is an example of joining two tables, returning select columns, searching using like comparison, and paging using 

    COLUMNS: ["Orders.*", "Users.USERNAME", "Users.EMAIL AS USER_EMAIL"],

    LEFT_JOIN: {TABLE: "Users", LEFT: "Orders.ACCOUNT_ID", RIGHT: "Users.ID"},

    LIKE: [{KEY: "Orders.UNIQUE_ID", VALUE: search + "%"}, {KEY: "Users.USERNAME", VALUE: search + "%"}, {KEY: "Users.EMAIL", VALUE: search + "%"}],

We can only use promises with advanced find. 

If you would like to select what columns will be returned back, you can use `COLUMNS` in the advanced parameters.

    COLUMNS: ["Patrons.*", "Accounts.FULL_NAME", "Accounts.ID"],

You can use the like comparison using the following. The LIKE block will default to be using or's but you can change it

    LIKE: [{KEY: "USERNAME", VALUE: "av%", CONJUNCTION: "AND"}],

If you want to utilize the power join syntax in sql, we can use one of the following: LEFT_JOIN, RIGHT_JOIN, INNER_JOIN, 

    LEFT_JOIN: {TABLE: "Accounts", LEFT: "Patrons.ACCOUNT_ID", RIGHT: "Accounts.ID"},

If you want to add more joins of the same type just put them in an array. 

        {TABLE: "Accounts", LEFT: "Patrons.ACCOUNT_ID", RIGHT: "Accounts.ID"},

        {TABLE: "Orders", LEFT: "Patrons.ORDER_ID", RIGHT: "Orders.ID"}

If you want to check if a column is not something, use the not parameter.

If you want to get rows before or after an index, use the `BEFORE` and/or `AFTER` advanced parameter.

We can limit the amount of rows we get by just adding the `LIMIT` key to our query.

testTable.findAdvanced({ACCOUNT_ID: 3}, {

QueryHelper.checkData = function (keys, properties) {

    properties = this.getKeys(properties);

    for (var i = 0; i < properties.length; i++) {

        if (keys.indexOf(properties[i]) == -1) {

QueryHelper.toValues = function (properties, callback) {

        if (properties.hasOwnProperty(key)) {

            valuesArray.push(properties[key]);

    callback(keys.substr(2), values.substr(2), valuesArray);

QueryHelper.getKeys = function (properties) {

    if (Object.prototype.toString.call(properties) === '[object Array]') {

        for (var i = 0; i < properties.length; i++) {

            var newItems = this.getKeys(properties[i]);

            for (var j = 0; j < newItems.length; j++) {

                toReturn.push(newItems[j]);

            if (properties.hasOwnProperty(key)) {

QueryHelper.toKeyValueComma = function (properties, callback) {

                if (properties[key] == null) {

                    keys += " , " + key + " = NULL";

                    keys += " , " + key + " = ?";

                    values.push(properties[key]);

                callback("Could not parse value", keys, values);

        callback(null, keys.substr(2), values);

QueryHelper.toKeyValue = function (properties, callback) {

        properties.forEach(function (key) {

            instance.toKeyValue(key, function (err, keyz, valuez) {

                keys += " OR (" + keyz + ")";

                for (var i = 0; i < valuez.length; i++) {

                    values.push(valuez[i]);

                        keys += " AND " + key + " IS NULL";

                    } else if (Array.isArray(properties[key]) ) {

                        keys += " AND " + key + " IN (" + properties[key].join() + ")";

                        keys += " AND " + key + " = ?";

                        values.push(properties[key]);

                    callback("Could not parse value", keys, values);

            callback(null, keys.substr(5), values);

QueryHelper.toLikeKeyValue = function (properties, callback) {

            instance.toLikeKeyValue(key, function (err, keyz, valuez) {

        callback(null, keys.substr(4), values);

            for (var key in properties) {

                if (properties.hasOwnProperty(key)) {

                    if (properties[key] == null) {

                        keys += " AND " + key + " LIKE ?";

		{
		"name": "jssql",
		"description": "A simple javascript MySQL library",
		"version": "1.5.2",
		"version": "1.5.3",
		"author": "Avery Durrant <avery@frostbyte.co>",
		@@ -6,0 +6,0 @@ "license": "MIT",

		@@ -126,2 +126,14 @@ ## Install

		### WHERE IN (...)
		A common use case of the WHERE clause in SQL is to find records that match one of a set of values. To do this, simply pass an array as the value for one of your where clauses. Below finds Products that have the ID of either 1 or 2 and a LOCATION_ID of 1.

		```js
		Products.find(
		{
		ID: [1, 2],
		LOCATION_ID: 1
		}
		);
		```

		## "Advanced Find"
		@@ -128,0 +140,0 @@ If we want to do more advanced select statements, we are going to use the `findAdvanced` function. We use the first

		@@ -108,3 +108,5 @@ function QueryHelper() {
		keys += " AND " + key + " IS NULL";
		} else {
		} else if (Array.isArray(properties[key]) ) {
		keys += " AND " + key + " IN (" + properties[key].join() + ")";
		}else {
		keys += " AND " + key + " = ?";
		@@ -111,0 +113,0 @@ values.push(properties[key]);

Improved metrics