Sql-select-generator NPM

sql-select-generator

SQL query string SELECT generator

Fully designed with TDD. Not production ready for the moment.

Setup - Usage - Development init - Stupidity

Setup

npm i -S sql-select-generator

Usage

For the moment, look in tests, this tool is not ready to use. Even in development for pre-alpha or any kind of version name you can imagine.

const
    SqlSelect = require('sql-select-generator'),
    sqlString = new SqlSelect();
sqlString
    .columns(['id', {
        label: 'name',
        alias: 'n'
    }])
    .from({
        label: 'test',
        alias: 't'
    })
    .order({
        label: 'name',
        order: 'DESC'
    });
expect(sqlString.toString()).toBe('SELECT id, name AS n FROM test AS t ORDER BY name DESC;');

Construct

Requiring the module will provide a function you can call to get a fresh new instance of sql generator object.

Get the generated request

The object, got from calling the method provided by module, come with a toString method. So just put it like in the example above.

columns

You can provide just a string, an object with label property (mandatory) and alias property (optionnal). Or an array of these mixed. Each call append to the previous call.

    const
        sqlString = (new SqlSelect())
            .columns('id')
            .columns(['name', 'address'])
            .columns({label: 'telephone', alias: 'phone'})
            .columns([{label: 'ip_address'}, 'city']);

  expect(sqlString.toString()).toBe('SELECT id, name, address, telephone AS phone, ip_address, city;');

order

You can provide just a string, an object with label property (mandatory) and direction property (optionnal). Or an array of these mixed. Values accepted : 'ASC' and 'DESC' (upper or lower case). By default, will be displayed uppercase. You can change it by using #useLowercase.

    const
        sqlString = (new SqlSelect())
            .columns('name')
            .from('test')
            .order('id');

  expect(sqlString.toString()).toBe('SELECT name FROM test ORDER BY id;');

Or with direction :

    const
        sqlString = (new SqlSelect())
            .columns('name')
            .from('test')
            .order({
                label: 'id',
                order: 'DESC'
            });

  expect(sqlString.toString()).toBe('SELECT name FROM test ORDER BY id DESC;');

groupBy

You can provide just a string, or an array of string. Each call append to the previous call.

    const
        sqlString = (new SqlSelect())
            .groupBy('id')
            .groupBy(['name', 'address']);

  expect(sqlString.toString()).toBe('SELECT GROUP BY id, name, address;');

limit

You can provide just one string.

    const
        sqlString = (new SqlSelect())
            .columns('name')
            .from('test')
            .limit('5');

  expect(sqlString.toString()).toBe('SELECT name FROM test LIMIT 5;');

About offset

You have to describe it by yourself in the limit argument string. Take care that not all SQL engines provide a LIMIT clause.

Exemple with PostgreSQL:

    const
            sqlString = (new SqlSelect())
                .columns('name')
                .from('test')
                .limit('5 OFFSET 10');

      expect(sqlString.toString()).toBe('SELECT name FROM test LIMIT 5 OFFSET 10;');

Exemple with MySQL:

    const
            sqlString = (new SqlSelect())
                .columns('name')
                .from('test')
                .limit('5, 10');

      expect(sqlString.toString()).toBe('SELECT name FROM test LIMIT 5, 10;');

setDefaultOrderDirection

Allows you to set the default value of order direction. By default, no direction is provided, so the result will be based on the default value of your database (mostly, maybe all, ASC).

    const
        sqlString = (new SqlSelect())
            .columns('name')
            .from('test')
            .order('id')
            .setDefaultOrderDirection('DESC');

  expect(sqlString.toString()).toBe('SELECT name FROM test ORDER BY id DESC;');

clone

You can ask the generator to provide you a deep-copy of the current instance. For exemple, in a model (in MVC architecture), you sometimes (always ?) have the FROM clause refers to the same table. Often, you have some columns in common, like an id or something similar. With clone, you can share common part of requests. Here an example :

    const
        sqlString = (new SqlSelect())
            .columns('name')
            .from('test'),
        clone = sqlString
            .clone()
            .from('hell');

  expect(sqlString.toString()).toBe('SELECT name FROM test;');
  expect(clone.toString()).toBe('SELECT name FROM hell;');

lastCalledMethod

You can get the name of last method called. Here an example :

    const
        sqlString = (new SqlSelect())
            .columns('name')
            .from('test');

  expect(sqlString.lastCalledMethod()).toBe('from');

Development init

Start could start develope for this project by fork it and clone your fork. after just run this CMD in terminal:

$npm run init

Stupidity

This tool doesn't prevent misusage and stupidity. So if your request contains, for example, a call to join method but no one to from method, your request will probably not work. No warning or error will be provided from this tool when you code something stupid.

Another example, if you ask your request to select a column name and this one is not present in the from/join called tables, there will be no warnings or error.

Last but not least example of misusage, you may have notice this kind of example in previous parts:

    const
        sqlString = (new SqlSelect())
            .groupBy('id')
            .groupBy(['name', 'address']);
  expect(sqlString.toString()).toBe('SELECT GROUP BY id, name, address;');

Yes this will never work. But you can get it with this tool.

Remember this tool is an helper to build your SQL queries string. Not a magic tool.