nsqlize v1.0.2

NSQLize

As it were SQL, create a CRUD directly on node.

NSQLize is a small and basic project that brings functions to work with sql databases (actually mariadb is the only database motor tested) on node. It was intented to work for personal projects, so I made it in a week when I was moving some projects from php to node. The functions work with promises, so them are asynchronous.

Summary: The module contains some basic functions just for a CRUD.

Installation

npm

npm install nsqlize --save

Then just include it wherever you will use it.

let nsqlize = require('nsqlize');

Why I did this and you shoud use it?

I know there are many frameworks that can do almost everything for you, but some of them requires and generates stuff that sometimes is not documented, so this alternative let me do my stuff which I know and can control. Also this module has less than 1000 lines (you can check the code).

Usage (examples)

The usage is simple, is almost like using SQL statemens directly on the database's client.

Inserting data:

There are multiple forms to define an insert statement, since it build the statement secuantially, let's show it step by step:

• First we call the nsqlize.insertInto() function which can be called this way:

nsqlize.insertInto('table')

or

nsqlize.insertInto('table', ['column1', 'column2', 'column3'])

• After that, we have a pseudo statement object, but it can not be executed, so we need to call a the function values() from the pseudo statement object returned, which and can be called like this:

values('value1', 'value2', 'value3')

or

values(['value1', 'value2', 'value3'])

• Now the statement can be execute with the function go(), so, the sentece must be something like:

nsqlize.insertInto().values().go()

After a well formed statement object, the go() becomes available. The statement object never connects to the database unless you call the function go() wich connects to database and executes the statement returning a promise with the results and errors.

Selecting data (queries):

The select statemens is also formed step by step, since it work with pseudo statment objects too.

• First we call the nsqlize.select() function with:

nsqlize.select('column1', 'column2', 'table.column3')

or

nsqlize.select(['column1', 'column2', 'table.column3'])

• Then we call the from() function like:

from('table1', 'database.table2')

or

from(['table1', 'database.table2'])

• After that, we can use the go() function to retrieve information from the database, but aso we can do more complex statements by using some conditions and options like:

Where:

where(['column1', 'operator', 'argument'])

orWhere:

orWhere(['column1', 'operator', 'argument'])

groupBy:

groupBy('column1', 'column2')

or

groupBy(['column1', 'column2'])

orderBy:

orderBy('column', 'order')

Where 'order' can be 'asc' or 'desc'

limit:

limit(x)

Where x is any integer number.

If we go straight and use all the functions, the sentence must end like this one:

nsqlize.select().from().where().orWhere().orderBy().groupBy().limit().go()

Simple, isn't? Now let's go ahead to updates

Updating data:

Updating data shares some similarities with the inserting and selecting statemens, due to the idea of "inserting" data onto "selected" columns and tables, the way ot works is as shown below:

• First we call the nsqlize.update() function to get a pseudo statement object.

nsqlize.update('table')

• Now we can give some sets of colums and values with the set() function like this:

set({column1: 'value1', column2: 'value2', column3: 'value3'})

or

set(['column1', '=', 'value1'], ['column2', '=', 'value2'], ['column3', '=', 'value3'])

I know, the first option is cleaner and more deadable, so I personally recommend to use that one. By the way, after calling the function update() and set(), the pseudo statement is ready to execute with the function go() but... Calm down my friend! I suggest you to use conditions. We don't want to override all the database, so... Use conditions as in the "select" statement:

Where:

where(['column1', 'operator', 'argument'])

orWhere:

orWhere(['column1', 'operator', 'argument'])

The final sentence must be like this:

nsqlize.update().set().where().orWhere().go()

Do you see it? It't almost the same (if it isn't) as the select statement's "where" and "orWhere", now we can finish with this going ahead to the delete.

Delete

The delete, one more time, share some similarities with the update in terms of filtration (conditions).

• First we cal the nsqlize.deleteFrom() function.

nsqlize.deleteFrom('table')

It returns an executable-ready pseudo statement with go(), but again, don't delete your database... Use conditions!

Where:

where(['column1', 'operator', 'argument'])

orWhere:

orWhere(['column1', 'operator', 'argument'])

limit:

limit(x)

Where x is any integer number.

A well formed deleteFrom statemen must end like this:

nsqlize.deleteFrom().where().orWhere().limit();

Yes, we can use the limit() function on some sql motors, if not any.

The final function: go()

As you may know or at least, you may noticed, every statement ends with the function go() which has explained earlier. I tried to go syncrhnous while I was moving some of my project from php to node, but at the end I couldn't keep it synchronous, so every connection to the database is asynchronous.

While the first functions of each statement are synchronous and returns a pseudo-statement object, the final one is asynchronous and returns a Promise, so you can create the statement and then call the promise when everything is ready. Look at the example:

// create statement
let statement = nsqlize.select('column1', 'column2', 'column3').from('table');

// execute statement in the only way this project can do: Promises
statement.go().then((result) => {
    console.log(result);
}).catch((error) => {
    console.log(error);
});

The result is a result object from the connection created at the pool of the dependency (mysql), in other words, it is the same as a res retrieved by mysql alone in its query function. For more information check the mysql package for node.

Recommendations

You can always check the statement object generated by using the console.log() function, just to see the statement string, parameters, conditions, options, etc. Also you can analize the code and undertand what is happening on each function, it might help a little to build better statements. This module is intented to work with ongoing project, for new project I suggest to use more complete frameworks.

By the way, this small project that I made was only for personal uses... But now I want to share it with the people (even when no one cares). The project is under the MIT license and may containg bugs, use at your own responsability.

I do no plan to maintain (build new features, fix all bugs, or improve performance) if I don't see the needings, so you can clone this repo' and build something amazing by yourself.

With love:

Daniel