Skip to content

Querying the Database

Jump to bottom
Rob Parham edited this page Sep 25, 2017 · 9 revisions

All queries are executed by a jSQLQuery object. This object allows you to refine a query, provide values to a prepared statement, get the results of a query, etc.

There are two ways to create any given query:

  1. Using the query constructor for the required query type (jSQL.createTable, jSQL.dropTable, jSQL.select, jSQL.update, jSQL.deleteFrom, or jSQL.insertInto)
  2. Using the SQL Parser (jSQL.query) Which will parse an SQL statement and return the jSQLQuery object of the correct type.

jSQL.query(sqlQuery)

Parse a raw query or prepared statement from an SQL string. This method understands a subset of standard SQL and returns a jSQLQuery object.

Parameters
  • sqlQuery: A string. An SQL query to be parsed and executed on the database. jSQL understands a subset of standard SQL which is covered under the jSQL Syntax header.
Returns
  • A jSQLQuery object of the appropriate type: "CREATE", "UPDATE", "SELECT", "INSERT", "DROP", or "DELETE".
Example
jSQL.query('UPDATE `Users` SET Age = 0 WHERE Age <= 18').execute();

jSQL.createTable(params, keys)

Create a query that can create and populate a new table in the database.

Parameters
  • params: An object containing one property, it's name is the name of the table being created, it's value is an array of column definition objects. Each definition object contains a property for the name of the column, the column type and the arguments for the column type. See the Fun with DataTypes section for more info about types.
  • keys: (optional) An array of objects representing primary keys to be applied to columns. Each object in the array must have a column and a type property. The column property is the column name (a string) [or column names (an array of strings) in the event of a compound key]. The type property is either "primary" for a primary key or "unique" for a unique key. A table may have only one primary key (compound or not), but many unique keys.
Returns
Example 1
jSQL.createTable({Users:[
    {name: "id", type:"INT", args:[] },
    {name: "Full_Name", type: "VARCHAR", args: [30]},
    {name: "Age", type: "INT", args: []},
]}, [
    {column: ["id", "Full_Name"], type: "primary"}
]).ifNotExists().execute();
Example 2
jSQL.createTable({myTable: [
    { name: "ID", type: "INT", args: [], key: "primary", auto_increment: true }, 
    { name: "Name", type: "VARCHAR", args: [30] }
]})
Example 3
jSQL.createTable({myTable: [
    { name: "ID", type: "INT", args: [] }, 
    { name: "Name", type: "VARCHAR", args: [30] }
]})

jSQL.createTable(params) (v<1.4 - Deprecated)

Create a query that can create and populate a table new table in the database.

Deprecated

Due to a bug noted in Issue #2 this signature has been removed as of version 1.4. Use jSQL.createTable(params) instead.

Parameters
  • params: An object containing one property, it's name is the name of the table being created, it's value is an object that represent the column definitions. The column definition object contains a property for each column, it's name is the column type and it's value is the definition for that column, including a "type" parameter and an "args" parameter. See the Fun with DataTypes section for more info about types.
Returns
Example
jSQL.createTable({Users:{
    Name: {type: "VARCHAR", args: [30]},
    Age: {type: "INT", args: []},
}}).ifNotExists().execute();

jSQL.createTable(name, columnsOrData, columnTypes)

Create a query that can create and populate a table new table in the database.

Parameters
  • name: The name of the new table (string).
  • columnsOrData: Can be either 1) An array of column names for the new table. or 2) An array of objects whos properties represent table column names and their values are values to be inserted.
  • columnTypes: (optional) An array of objects, one for each column, each containing a "type" property, for the column type, and an "args" property as an array of column type arguments.
Returns
Example
var users = [];
users.push({Name: 'Bob', Age: 34, PhoneNumber: "888-999-0000"});
users.push({Name: 'Susan', Age: 37, PhoneNumber: "888-111-0000"});
jSQL.createTable('Users', ['Name', 'Age', 'PhoneNumber']).execute(users);

jSQL.select(columns)

Create a query that will gather a result set from a table to be fetched.

Parameters
  • columns: A column name (string) or an array of column names, or the special string * which will return all columns in the table.
Returns
Example
var query = jSQL.select("*").from('Users').execute();
var users = query.fetchAll();

jSQL.insertInto(tableName)

Create a query that will insert rows into a table.

Parameters
  • tableName: The name of the table to insert rows to.
Returns
Example
var query = jSQL.insertInto('Users').values({Name: 'Jill', Age: 25}).execute();

jSQL.dropTable(tableName)

Create a query that will delete a table.

Parameters
  • tableName: The name of the table to delete.
Returns
Example
var query = jSQL.dropTable('Users').execute();

jSQL.update(tableName)

Create a query that will alter records in a table.

Parameters
  • tableName: The name of the table to update.
Returns
Example
jSQL.update('Users')
    .set({Name:'Old Person'})
    .where('Age')
    .greaterThan(37)
    .execute();

jSQL.deleteFrom(tableName)

Create a query that will delete records in a table.

Parameters
  • tableName: The name of the table to delete records from.
Returns
Example
// Without a WHERE clause, this will truncate the table.
jSQL.deleteFrom(`Users`).execute();

jSQL Docs Home | jSQL on GitHub

jSQL Docs Home

Persistence Management

Storage APIs

Querying the Database

jSQLTable class

jSQLQuery interface

jSQLWhereClause class

jSQL Helper Methods

jSQL Syntax

Fun with DataTypes

Error Handling

Examples

License

Clone this wiki locally