A dead simple query builder for javascript

SELECT ALL

DB.Table('posts').select();

// SELECT * FROM posts

SELECT Some

DB.Table('posts').select('id, name');
// SELECT id, name FROM posts

// the above can also be written in any of these ways: 
DB.Table('posts').select(['id', 'name']);
DB.Table('posts').fields('id, name').select();
DB.Table('posts').fields(['id', 'name']).select(); 

UPDATE

DB.Table('posts').where('id', '=', 1).update({
    title: 'The first post',
    is_published: true
});

// UPDATE posts SET title='The first post', is_published='t'  WHERE id=1

DELETE

DB.Table('posts').where('id', '=', 1).delete();

// DELETE FROM posts WHERE id=1 

INSERT

DB.Table('posts').insert({
    title: 'New post',
    body: 'Lorem... amet',
    is_published: true
});

// INSERT INTO posts (title, body, is_published) VALUES ('New post', 'Lorem... amet', true) 

API

DB.table(tablename)

Creates a Query builder associated with the tablename table. After that, you can chain the quey builder with any of these methods, to construct your query.

query construction methods

use these methods to construct your queries.

.where	whereSpec	creates a where clause
.orWhere	whereSpec	creates a orWhere clause
.whereIn	whereInSpec	creates a whereIn clause
.orWhereIn	whereInSpec	creates a orWhereIn clause
.whereNotIn	whereInSpec	creates a whereNotIn clause
.orWhereNotIn	whereInSpec	creates a orWhereNotIn clause
.whereLike	whereLikeSpec	creates a whereLike clause
.orWhereLike	whereLikeSpec	creates a orWhereLike clause
.whereNotLike	whereLikeSpec	creates a whereNotLike clause
.orWhereNotLike	whereLikeSpec	creates a orWhereNotLike clause
.whereNull	collumn	creates a whereNull clause
.orWhereNull	collumn	creates a orWhereNull clause
.whereNotNull	collumn	creates a whereNotNull clause
.orWhereNotNull	collumn	creates a orWhereNotNull clause

When you are done constructing, you should end the chain with a call to any of the final methods. These are the standard SQL methods

select, insert, update, delete

Of course, using some of the above final methods does not always make sense. For example, it dosn't make sense to issue multiple where methods and then end the chain with an insert.

select

select can take an optional argument, either an array or a comma separated string, to define the rows you want to be retrieved. For example

.select();
// selects all the columns

.select('*');
// selects all the columns

.select('id');
// selects only the id column

.select('id, name');
// selects the id and name columns

.select(['id', 'name']);
// selects the id and name columns

insert

Takes a required object, representing the columns and values. For example,

DB.Table('users').insert({
    'name': 'John',
    'email': 'john@doe.here',
});

// INSERT INTO users (name, email) VALUES('John', 'john@doe.here');

upate

Takes a required object, and updates the columns with the values.

DB.Table('users').where('email', 'john@doe.here').update({
    'is_admin': false
});

// UPDATE users SET is_admin='f' WHERE email='john@doe.here';

The values can use the DB.RAW method to create a built-in command'

DB.Table('users').where('email', 'john@doe.here').update({
    'last_login': DB.RAW('now')
});

// UPDATE users SET last_login=NOW() WHERE email='john@doe.here';

delete

Delete, takes no arguments. It just deletes the matched rows.

DB.table('users').where('last_login', '<', '2020-01-01').delete();

Some examples

DB.Table('posts').where(id).select();
// SELECT * FROM posts WHERE id IS NOT NULL

DB.Table('posts').where(id, 3).select('title');
// SELECT title FROM posts WHERE id=3

DB.Table('posts').fields(['id', 'title']).where(id, DB.RAW('MAX(id)')).select();
// SELECT id, title FROM posts WHERE id=MAX(id)

DB.Table('posts').where(group, '>', 1).select();
// SELECT * FROM posts WHERE group>3

DB.Table('posts').where(group, '=', 1).select('id');
// SELECT id FROM posts WHERE group=3

Parenthesized queries

In many cases, it is required to have queries in parentheses to accomodate with ORs and ANDs. This is easily done by passing a callback function to the whereSpec, as demonstrated below

DB.Table('users')
    .where('email', email)
    .where(function(sub) {
        sub.where('is_admin', true)
           .orWhere('is_super_admin', true);
            //notice, you should not specify select() here! 
    }).select('hashed_password');

SELECT hashed_password FROM users WHERE email=? AND (is_admin='t' OR is_super_admin='t')

subselects

Using subselects in your queries is pretty straight forward. Let's view an example. In this example, we get the column id from the users, in order to use it to delete the 10 oldest (by id) entries in this table. This is a pretty common use case, because the SQL standard does not allow ORDERBY and LIMIT to be used along with the DELETE statement.

DB.Table('users')
    .whereIn(
        id,  DB.table('users').cols('id').orderBy('id', 'DESC').limit(10)
    )
    .delete();

DELETE FROM users WHERE id IN (SELECT id FROM users ORDER BY id DESC OFFSET 10)