sqlite-qb v1.1.0

sqlite-qb: a sqlite3 query builder

Install globally to use the builder, and locally to use the database package.

GitHub repository: https://github.com/Arka-Night/SQLiteQueryBuilder

Author: Elon Pereira Neto - Vulgo ArkaNight

• Setup
• Builder reference
• Package reference

Basic usage of package:

with promise format:

const Connection = require('sqlite-qb');
const configFile = require('sqlite-config.json');

const connection = new Connection(configFile);
connection.connectInTable('table').then(tab  => {
	tab.selection('*').select().then(rows  => {
		console.log(rows);
	});
});

with async await:

async function func() {
	const Connection = require('sqlite-qb');
	const configFile = require('sqlite-config.json');

	const connection = new Connection(configFile);
	const table = await connection.connectInTable('table');
	
	console.log(await table.selection('*').select());
}

Setup

You can setup the builder and the lib with npm and yarn

• Install globally: npm install -g sqlite-qb or yarn global add sqlite-qb
• Install package: npm install sqlite-qb or yarn add sqlite-qb

Builder reference:

• init
• createDB
• createMigration
• upTables
• dropTables
• Table construction

The binary to builder is sqb

• sqb command will show the version of builder

Init:

Init the builder and the config json file in the directory that you called the command. command: sqb init

createDB:

Create the database file in the specified directory in config json. command: sqb createDB

createMigration:

Create a migration in specified folder in config json. (the command needs to receive a migration name) in the exit file, the next code will be writen:

export.up = (table) => {
	return table.createTable(tableName, callback);
}

export.down = (table) => {
	return table.dropTable(tableName);
}

command: sqb createMigration [name]

upTables:

Up all migrations in specified folder in config json to database file. command: sqb upTables

dropTables:

Drop all tables in specified folder in config json if a migration file name isn't specified and drop the migration specified if it is specified. (the command can receive a second parameter) command: sqb dropTables [?migrationName]

Table construction:

The table comes in up and down callback function of migrations (see how to create then in createMigration):

export.up = (table) => {
	return table.createTable(tableName, callback);
}

export.down = (table) => {
	return table.dropTable(tableName);
}

• Table

  	- createTable
  	- dropTable

• Column

Table:

table is the parameter of up and down callback function of migrations and it have two methods:

• createTable(tableName, callback(column)):

  this method returns a sql format of the table construction callback and needs to be returned in up callback function of migrations.

• dropTable(tableName):

  this method return a sql format of drop of the table specified in parameter and needs to be returned in down callback function of migrations.

Column:

column is the table.createTable callback parameter, used to create the columns on the table.

the methods of him are:

• string

  	- [maxLenght](#maxlenght)
  	- [notNullable](#notnullable)
  	- [foreignKey](#foreignkey)
  	- [primary](#primary)

• integer

  	- [autoIncrement](#autoincrement)
  	- [notNullable](#notnullable)
  	- [foreignKey](#foreignkey)
  	- [primary](#primary)

• real

  	- [notNullable](#notnullable)
  	- [foreignKey](#foreignkey)
  	- [primary](#primary)

• boolean

  	- [notNullable](#notnullable)
  	- [foreignKey](#foreignkey)
  	- [primary](#primary)

Column Methods:

string:

uses: column.string(columnName) this method create a string type column with the specified name in parameter.

integer:

uses: column.integer(columnName) this method create a integer type column with the specified name in parameter.

real:

uses: column.real(columnName) this method create a real number type column with the specified name in parameter.

boolean:

uses column.boolean(columnName) this method create a boolean type column with the specified name in parameter. the receive is 1 and 0. the return is 1 and 0. (integer format)

Methods of Column Methods:

maxLenght:

Only in string type uses: column.string(columnName).maxLenght(lenghtNumber) this method put a max lenght in string column.

autoIncrement:

Only in integer type uses: column.interger(columnName).autoIncrement(no args) this method create a auto increment sqlite column.

notNullable:

Available in every column type uses: column.'type'(columnName).notNullable(no args) this method create a column that not be can nullable.

primary:

Available in every column type uses: column.'type'(columnName).primary(no args) this method create a primary sqlite column. (Can be used in every column in table if you want to create a multiple primary column).

foreignKey:

Available in every column type uses:

column.'type'(columnName).foreignKey(
column(column of reference that you are referencing), 
reference(table that you are referencing), config: {
	ondelete: 'cascade for default',
	onupdate: 'cascade for default'
})

Package reference:

• Basic usage
• Connection class
• Table class

Basic usage:

with promise format:

const Connection = require('sqlite-qb');
const configFile = require('sqlite-config.json');

const connection = new Connection(configFile);
connection.connectInTable('table').then(tab  => {
	tab.selection('*').select().then(rows  => {
		console.log(rows);
	});
});

with async await:

async function func() {
	const Connection = require('sqlite-qb');
	const configFile = require('sqlite-config.json');

	const connection = new Connection(configFile);
	const table = await connection.connectInTable('table');
	
	console.log(await table.selection('*').select());
}

in package, if you dont need a return, you can just use the function without promise format or async await.

Connection class:

const Connection = require('sqlite-qb');
const connection = new Connection(json-config);

(every method in that class are static(except closeDb()), but can be used with constructor)

Methods:

• connectInTable
• getAllTables
• closeDb

connectInTable:

This method returns table class with a single connection in one table in the database connected. uses:

/Constructor version/
const table = await connection.connectInTable(tableName); //returns table class
/or/
connection.connectInTable(tableName).then(table); //returns table class

/Static version/
const table = await Connection.connectInTable(tableName, json-config); //returns table class
/or/
connection.connectInTable(tableName, json-config).then(table); //returns table class

getAllTables:

This method returns a array with all tables in database connected. uses:

/Constructor version/
const tables = await connection.getAllTables(); // returns array with all tables
/or/
connection.getAllTables().then(tables); // returns array with all tables

/Static version/
const tables = await Connection.getAllTables(json-config); // returns array with all tables
/or/
Connection.getAllTables(json-config).then(tables); // returns array with all tables

closeDb:

This method close the database connected(constructor version only). uses:

connection.closeDb();

Table class:

const tab = await connection.connectInTable(tableName);
/or/
connection.connectInTable(tableName).then(tab);

(static version in connection class reference on connectInTable)

Methods:

• selection - where - distinct - order - limit - select
• insert
• deleteColumn - where - del
• updateColumn - where - update

SubMethods:

• where
• andWhere
• orWhere
• distinct
• order
• limit - offset
• select
• del
• update

Methods:

Selection:

this method initiate a column selection on the table that you called and need to be finalized with select submethod. Uses: table.selection(...data) SubMethods: where, distinct, order, limit, select

Insert:

this method insert a value in columns of the table that you called. Uses: table.insert(insert_data = { column: value }) SubMethods: none.

deleteColumn:

this method initiate a column delete on the table that you called and need to be finalized with del submethod. Uses: table.deleteColumn() SubMethods: where, del

updateColumn:

this method initiate a column update on the table that you called and need to be finalized with update submethod. Uses: table.updateColumn(config: { column: value }) SubMethods: where, update

SubMethods:

where:

this submethod is used to specify a column that you want to acess. can be used with any method and return 2 submethods: andWhere, orWhere (return the anothers submethod of the method too) Uses: table.method().where(column, value, [operation = '='])

andWhere:

this submethod is used to specify a column that you want to acess and put an and logical operator in the where operation. can be used with any method and return 2 submethods: andWhere, orWhere (return the anothers submethod of the method too) similar with where.

orWhere:

this submethod is used to specify a column that you want to acess and put an or logical operator in the where operation. can be used with any method and return 2 submethods: andWhere, orWhere (return the anothers submethod of the method too) use is similar with where.

distinct:

this submethod is used to take a distinct column, and the distinct column is the first column in the selection method parameter. can be used just with selection method and return the anothers submethod of selection method. Uses: table.method().distinct()

order:

this submethod is used to put a specified column in a order(descend or ascend). can be used just with selection method and return the anothers submethod of selection method and another order submethod. Uses: table.selection().order(column, 'asc' or 'desc')

limit:

this submethod is used to put a limit in the select method return. can be used just with selection method and return the anothers submethod of selection method and offset submethod. Uses: table.selection().limit(limit) Offset submethod: this submethod is used to put a offset in limit of the select method return. can be used just with selection method after limit submethod and return the anothers submethod of selection method. Uses: table.selection().limit(limit).offset(offset)

select:

this submethod is used to finalize the selection and return the columns. can be used just with selection method and return the columns. Uses: table.selection().select()

del:

this submethod is used to finalize the delete and return 1 if with sucess and error if without sucess. can be used just with deleteColumn method. Uses: table.deleteColumn().del()

update:

this submethod is used to finalize the update and return 1 if with sucess and error if without sucess. can be used just with updateColumn method. Uses: table.updateColumn().update()