simple-sql-query-builder

A simple SQL query builder. It executes built queries if an executing function is set.

1. Usage
2. Version history

Usage

1. SqlBuilder
2. TableBuilder
3. Column
4. UniqueBuilder
5. InsertUpdateBuilder
6. WhereBuilder
7. Condition
8. SelectBuilder
9. FromBuilder

SqlBuilder

This is the "entry point" of the builder. It contains only static methods and fields.

import SqlBuilder from "simple-sql-query-builder";

1. beginTransaction()
2. commit()
3. createTable()
4. delete()
5. executeSql()
6. insert()
7. rollback()
8. select()
9. setDebug()
10. setFormatOnly()
11. setQuotingSymbol()
12. setSqlExecutor()
13. startTransaction()
14. update()
15. static fields

• beginTransaction()

   SqlBuilder.executeSql("BEGIN TRANSACTION");
  

• commit()

   SqlBuilder.executeSql("COMMIT");
  

• createTable()

  Creates a table using TableBuilder.

   const name = "weights";
   
   const callback = tb => {
       tb.integer("rowid").primary();
       tb.integer("millis").notNull();
       tb.integer("gross").notNull();
       tb.integer("net").notNull();
       tb.text("comment").notNull();
   };
   
   const ifNotExists = Boolean; // Adds "IF NOT EXISTS" if true. Default: true.
   
   SqlBuilder.createTable(name, callback, ifNotExists);
  

• delete()

  Deletes rows using WhereBuilder.

   const table = "journeys";
   const callback = wb => wb.column("rowid").e(rowid);
   
   SqlBuilder.delete(table, callback);
  

• executeSql()

  Executes an sql statement by invoking a function set by setSqlExecutor(). It returns the result of that function invocation or simply the passed sql statement if an executor hasn't been set.

  The result of invoking this method is returned from the CRUD methods.

   SqlBuilder.executeSql("some sql code");
  

• insert()

  Inserts a row using InsertUpdateBuilder.

   const table = "weights";
   
   const callback = ib => ib
       .columnValue("millis", new Date().getTime())
       .columnValue("gross", gross)
       .columnValue("net", net)
       .columnValue("comment", comment);
   
   SqlBuilder.insert(table, callback);
  

• rollback()

   SqlBuilder.executeSql("ROLLBACK");
  

• select()

  Selects rows using SelectBuilder.

   SqlBuilder.select(sb => sb
       .column("rowid")
       .column("*")
       .from("weights"));
  

• setDebug()

  Turns on or off the debug mode. In debug mode each executed sql statement is logged to the console.

   SqlBuilder.setDebug(debug);
  

  On the other hand each sql-executing method receives parameter debug which defaults to false. Setting it to true will have exactly the same result as:

   SqlBuilder.setDebug(true);
   SqlBuilder.executeSql(...);
   SqlBuilder.setDebug(false);
  

• setFormatOnly()

  If true is passed, executeSql() behaves as if an executor hasn't been set.

• setQuotingSymbol()

  Sets a quoting symbol to be used in queries. Defaults to ".

• setSqlExecutor()

  Sets a function to be used to execute sql statements.

   import SQLite from "react-native-sqlite-storage";
   
   ...
   
   const db = await SQLite.openDatabase(...);
   
   SqlBuilder.setSqlExecutor(db.executeSql.bind(db));
  

• startTransaction()

   SqlBuilder.executeSql("START TRANSACTION");
  

• update()

  Updates rows using InsertUpdateBuilder.

   const table = "expenseImages";
   
   const callback = ub => ub
       .columnValue("path", path)
       .where(wb => wb.column("rowid").e(image.rowid));
   
   SqlBuilder.update(table, callback);
  

• static fields

  There are several static fields in the class to facilitate creating instances of commonly used auxiliary classes: SelectBuilder, WhereBuilder and Condition. So instead of

   import SelectBuilder from "simple-sql-query-builder/js/SelectBuilder";
   
   const sb = new SelectBuilder();
  

  you can just write

   const sb = new SqlBuilder.SelectBuilder();
  

TableBuilder

• column()

  Creates a Column and returns it for method chaining.

   tb
       .column(
           name: "rate",
           type: "REAL")
       .notNull();
  

  There are shorthands for the BLOB INTEGER, REAL and TEXT types:

   tb.integer("rowid").primary();
   tb.text("comment").notNull();
   tb.blob("image");
  

• unique()

  Makes a column unique using UniqueBuilder.

   tb.unique(ub => {
       ub
           .column("name")
           .collate("NOCASE")
           .order("ASC");
       
       ub
           .column("code")
           .collate("NOCASE")
           .order("ASC");
   });
  

Column

The following methods return this to allow method chaining.

• default()

  Adds DEFAULT value to this column definition. The value is quoted if it's a string.

• foreign()

  Adds REFERENCES tableName(columnName) to this column definition.

   tb.integer("type").foreign("tableName", "columnName");
  

• notNull()

  Adds NOT NULL to this column definition.

• onDelete()

  Adds ON DELETE action to this column definition.

   tb.integer("journeyRowid")
       .foreign("tableName", "column name")
       .onDelete("action");
  

• primary()

  Adds PRIMARY KEY to this column definition.

UniqueBuilder

• column()

  Specifies the unique column name and optionally collation and order.

   ub
       .column("code")
       .collate("NOCASE")
       .order("ASC");
  

InsertUpdateBuilder

The following methods return this to allow method chaining.

• columnValue()

  Specifies a column value.

   insertUpdateBuilder.columnValue(
       column,
       value,
       add, // Boolean. If true this column will be added to the generated SQL code. Default: true.
       quoteIfString // Boolean. If true and value is a string, quotes are added to the generated SQL code for this value. Default: true.
   );
  

  Examples:

  • "INSERT INTO tableName (columnName1) VALUES (10);"

     SqlBuilder.insert(
         "tableName",
         ib => ib.columnValue("columnName1", 10));
    

    or

     SqlBuilder.insert(
         "tableName",
         ib => ib
             .columnValue("columnName1", 10)
             .columnValue("columnValue2", 20, false));
    

  • "INSERT INTO tableName (columnName1, columnName2) VALUES (10, "String value");"

     SqlBuilder.insert(
         "tableName",
         ib => ib
             .columnValue("columnName1", 10)
             .columnValue("columnName2", "String value"));
    

• where()

  Specifies a WHERE clause. It's a no-op if this instance is used for insertion.

   insertUpdateBuilder.where(
       callbackOrConditionString, // See below.
       add // Boolean. If true the WHERE-clause will be added to the generated SQL code. Default: true.
   );
  

  callbackOrConditionString can be one of:

  • A callback function receiving a WhereBuilder instance;
  • a string without WHERE itself;
  • A Condition instance;

  Examples:

  • UPDATE tableName SET columnName1 = 10, columnName2 = "String value" WHERE columnName3 = 314;

     SqlBuilder.update(
         "tableName",
         ib => ib
             .columnValue("columnName1", 10)
             .columnValue("columnName2", "String value")
             .where(wb => wb.column("columnName3").e(314)));
    

    or

     SqlBuilder.update(
         "tableName", ib => ib
             .columnValue("columnName1", 10)
             .columnValue("columnName2", "String value")
             .where("columnName3 = 314"));
    

    or

     const condition = new SqlBuilder.Condition("columnName3");
     condition.e(314);
     
     SqlBuilder.update(
         "tableName",
         ib => ib
             .columnValue("columnName1", 10)
             .columnValue("columnName2", "String value")
             .where(condition));
    

WhereBuilder

• condition()

  Sets a condition. condition is an object that evaluates to a string without WHERE.

   const condition = "columnName3 = 314";
   
   // or
   
   const condition = new SqlBuilder.Condition("columnName3");
   condition.e(314);
   
   wb.condition(condition);
  

• column()

  Adds a Condition to this WHERE and returns it for method chaining.

   wb.column("columnName").e(1);
  

• grouping()

  Groups conditions. Returns this for method chaining. There are shorthands for the AND, OR, ( and ) groupings:

   WHERE (c1 = 10 AND c2 = 20) OR (c3 >= 30 AND c4 <= 40)
    
   wb
       .push()
           .column("c1").e(10)
           .and()
           .column("c2").e(20)
       .pop()
       .or()
       .push()
           .column("c3").ge(30)
           .and()
           .column("c4").le(40)
       .pop();
  

Condition

• constructor()

  Constructs a condition.

   new SqlBuilder.Condition(
       "columnName",
       whereBuilder // An instance of WhereBuilder. It's returned from operator(). Can be undefined.
   );
  

• operator()

  Specifies a relation between a column value and the passed value. Returns the WhereBuilder instance passed to the constructor.

   const condition = new SqlBuilder.Condition("columnName");
   
   condition.operator(
       operator, // String. One of comparison operators.
       value, // Object.
       quoteIfString // Boolean. If true and value is a string, quotes are added to the generated SQL code for this value. Default: true.
   );
  

  There are several shorthands defined:

  Method	SQL operator
  e()	=
  ne()	!=
  g()	>
  ge()	>=
  l()	<
  le()	<=
  in()	IN
  like()	LIKE

like() has 2 additional parameters, defaulting to false: startsWith and endsWith. So

like(pattern) -> LIKE `%${pattern}%`
like(pattern, true) -> LIKE `${pattern}%`
like(pattern, false, true) -> LIKE `%${pattern}`

SelectBuilder

The following methods return this to allow method chaining.

• column()

  Specifies a column name to select data from.

   sb.column(
       column, // String. Column name.
       alias // String. Alias to use. Can be undefined.
   ));
  

• from()

  Specifies a data source.

   sb.from(
       table, // String. Table name.
       callback // A callback function used for JOINs. Can be undefined.
   );
  

  If you specify callback it will be invoked and passed a FromBuilder instance.

• orderBy()

  Adds an ORDER BY statement.

   sb.orderBy(
       column, // String. Column name.
       direction // String. Order direction. Default: "ASC"
   );
  

• limit()

  Adds a LIMIT statement.

   sb.limit(
       limit, // Number. The necessary limit.
       add // Boolean. If true this statement will be added to the generated SQL code. Default: true.
   );
  

FromBuilder

• addJoin()

  Adds a JOIN of the specified type and returns this for method chaining.

   fb.addJoin(
       joinType, // String. JOIN type.
       table, // String. The second table name.
       field1, // String. A column name in the first table.
       field2 // String. A column name in the second table.
   );
  

  There are several shorthands defined:

  Method	JOIN type
  innerJoin()	INNER JOIN
  leftOuterJoin()	LEFT OUTER JOIN
  rightOuterJoin()	RIGHT OUTER JOIN

   SELECT c1, c2 FROM table1 LEFT OUTER JOIN table2 ON table1.rowid = table2.rowid;
   
   SqlBuilder.select(sb => sb
       .column("c1")
       .column("c2")
       .from("table1", fb => fb
           .leftOuterJoin("table2", "table1.rowid", "table2.rowid")));
  

Version history

Version number	Changes
v1.13.0	SQLite.upsert(): legacy implementation for SQLite versions that don't support INSERT ... ON CONFLICT DO ....
v1.12.0	1. SQLite tables can be created without rowids. 2. SqlBuilder.delete(): callbackOrWhere defaults to "".
v1.11.0	1. The SQLite flavor is added. 2. Column.default() is added.
v1.10.0	1. SqlBuilder.beginTransaction() added. 2. Several SqlBuilder methods documented.
v1.9.2	Condition.like() parameter default values were invalid.
v1.9.1	Condition.like(): parameters startsWith / endsWith added.
v1.9.0	Parameter debug is added to each sql-executing method.
v1.8.0	Condition.like() added.
v1.7.0	1. TableBuilder.real() added. 2. SqlBuilderOptions added to remove require cycles.
v1.1.0	SqlBuilder.setQuotingSymbol() added.
v1.0.4	Imports fixed.
v1.0.3	client-side-common-utils deprecated; switched to simple-common-utils.
v1.0.2	1. Readme updated. 2.  SELECT and DELETE queries weren't terminated with ;. Fixed.
v1.0.1	1. Readme updated. 2. UPDATE queries weren't terminated with ;. Fixed.
v1.0.0	Initial release.

Written with StackEdit.