SQLinJS

Create web applications with offline capabilities using this simple embeddable DBMS written in JavaScript.

Features

Compatible with Firefox 3+, Chrome, Safari 4+, Opera, and Internet Explorer 7+ web browsers.
Compatible with iOS and Android mobile web browsers.
Includes SQL parsing and processing engine (refer to Supported Syntax for language support)
Provides command-line debug terminal for managing object data using the SQL syntax.
Provides API methods for managing object data from within your application.
Supports jQuery .data() and HTML5 sessionStorage for database storage.
Fast and lightweight (jQuery plug-in *only 13.7 kB)

Check out the demo provided with this package.

Installation

This package can be easily installed using Bower.

$ bower install sqlinjs

Manual install:

Download the latest sources to your computer using a web browser.
Extract the contents of the .zip into a folder on your local computer.
Upload the folder with the following files to your web site.

Filename	Role
SQLinJS.min.js	The main script to be included from within your HTML document.
SQLinJS.min.css	This style sheet that defines the "look & feel" of the SQL terminal.

Source Code

Add the following JavaScript between the <head></head> tags of your HTML document.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

<script src="/path/to/SQLinJS.min.js"></script>
<script>
$(document).ready(function() {
  $('body').SQLinJS();
});
</script>

API

The following methods can be passed to the plugin-in main function after successful initialization. An optional database in the supported format can be provided and will be autoloaded on initialization.

var dbh = $(document).SQLinJS([data] [,handler]);    // initialize using database

SQL common

createDatabase
createTable
deleteFrom
describeTable
dropDatabase
dropTable
insertInto
selectFrom
showDatabases
showTables
updateSet
useDatabase

initTerminal

Launch debug terminal to execute SQL statements using command-line interface. Requires SQLinJS.min.css

dbh.SQLinJS('initTerminal' [,handler]);

executeQuery

Execute SQL statement using the plug-in supported syntax.

dbh.SQLinJS('executeQuery', string [,handler]);

Use Example:

dbh.SQLinJS('executeQuery',"SELECT * FROM user WHERE id > 2 AND name != 'John'"
  function(response) {

    // do something
  }
);

importDatabase

Import an existing database in the supported database format. The database will be autoloaded on successful import.

dbh.SQLinJS('importDatabase', data [,handler]);

Use Example:

dbh.SQLinJS('importDatabase', data,
  function(response) {

    // do something
  }
);

createDatabase

Create a new database.

dbh.SQLinJS('createDatabase', name [,handler]);

Use Example:

sql> CREATE DATABASE accounts

dbh.SQLinJS('createDatabase', 'accounts',
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

createTable

Create a new table in an existing database.

dbh.SQLinJS('createTable', name, defs [,handler]);

Use Example:

sql> CREATE TABLE user (id int(10), name varchar(10))

dbh.SQLinJS('createTable', 'user', { "id": "int(10)", "name": "varchar(10)" },
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

deleteFrom

Delete record(s) from the selected table.

dbh.SQLinJS('deleteFrom', table [,clause] [,handler]);

Use Example:

sql> DELETE FROM user WHERE id > 2 AND id != 4

dbh.SQLinJS('deleteFrom', 'user',
  {
    conds: ['id > 2',"id != 4"]
  },
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

describeTable

Returns basic information about the columns of the table.

dbh.SQLinJS('describeTable', name [,handler]);

Use Example:

sql> DESCRIBE user

dbh.SQLinJS('describeTable', 'user',
  function(response) {
    for (var key in response) {
      alert('column=' + key + '; type=' + response[key]);
    }
  }
);

dropDatabase

Delete a database, by name.

dbh.SQLinJS('dropDatabase', name [,handler]);

Use Example:

sql> DROP DATABASE accounts

dbh.SQLinJS('dropDatabase', 'accounts',
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

dropTable

Delete a table, by name, from an existing database.

dbh.SQLinJS('dropTable', name [,handler]);

Use Example:

sql> DROP TABLE user

dbh.SQLinJS('dropTable', 'user',
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

insertInto

Insert a new record into the selected table.

dbh.SQLinJS('insertInto', table, vals [,handler]);

Use Example:

sql> INSERT INTO user (id, name) VALUES ('1', 'Jerry'), ('2', 'Alice'), ('3', 'Mable')

dbh.SQLinJS('insertInto', 'user',
  [
    { id: '1', name: 'Jerry' },
    { id: '2', name: 'Alice' },
    { id: '3', name: 'Mable' }
  ],
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

selectFrom

Select column(s) data from the selected table.

dbh.SQLinJS('selectFrom', table, cols [,clause] [,handler]);

Use Example:

sql> SELECT id, name FROM user WHERE id > 2 AND name != 'John' ORDER BY id DESC LIMIT 3

dbh.SQLinJS('selectFrom', 'user', ['id', 'name'],
  {
    conds:    ['id > 2',"name != 'John'"],
    order_by: 'id',
    sort:     'desc',
    limit:    3
  },
  function(response) {
    for (var i = 0; i < response.length; i++) {
      for (var key in response[i]) {
        alert('id=' + key.id + '; name=' + key.name);
      }
    }
  }
);

showDatabases

Return an array of database names.

dbh.SQLinJS('showDatabases' [,handler]);

Use Example:

sql> SHOW DATABASES

dbh.SQLinJS('showDatabases',
  function(response) {
    for (var i = 0; i <= response.length; i++) {
      for (var key in response[i]) {
        alert(response[key]);
      }
    }
  }
);

showTables

Return an array of table names in the active database.

dbh.SQLinJS('showTables' [,handler]);

Use Example:

sql> SHOW TABLES

dbh.SQLinJS('showTables',
  function(response) {
    for (var i = 0; i <= response.length; i++) {
      for (var key in response[i]) {
        alert(response[key]);
      }
    }
  }
);

updateSet

Select column data from the selected table based on conditional arguments.

dbh.SQLinJS('updateSet', table, cols [,clause] [,callback]);

Use Example:

sql> UPDATE user SET name = 'Fred' WHERE id > 2 AND name != 'John'

dbh.SQLinJS('updateSet', 'user', ["name = 'Fred'"],
  {
    conds: ['id > 2',"name != 'John'"]
  },
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

useDatabase

Load the selected database to access.

dbh.SQLinJS('useDatabase', name);

Use Example:

sql> USE DATABASE accounts

dbh.SQLinJS('useDatabase', 'accounts',
  function(response) {
    if (response === true) {

      // do something
    }
  }
);

Database Storage

HTML5 sessionStorage is used, by default. In cases where this is not supported, jQuery jQuery.data is used.

Supported Syntax

In order to avoid creating a complex SQL parser I have provide limited support the following functions.

CREATE DATABASE
CREATE TABLE
DELETE
DESCRIBE
DROP DATABASE
DROP TABLE
INSERT
SELECT
SHOW DATABASES
SHOW TABLES
UPDATE
USE

CREATE DATABASE

CREATE DATABASE db_name

CREATE TABLE

CREATE TABLE tbl_name [create_specification]

Currently supported data types are INT, CHAR, VARCHAR. Furthermore, the INT type works similar to a zerofill (character count/length) when an argument is present. This is due to limitations in the ability to determine the required storage and range for each integer type across web browsers and the various system architectures.

DELETE

DELETE FROM tbl_name
   [WHERE where_condition]
   [ORDER BY col_name [ASC | DESC]]

DESCRIBE

DESCRIBE tbl_name

DROP DATABASE

DROP DATABASE db_name

DROP TABLE

DROP TABLE tbl_name

INSERT

INSERT INTO [(col_name, ...)] VALUES (col_value, ...)

SELECT

SELECT col_name, ...
   [FROM  tbl_name
   [WHERE where_condition]
   [ORDER BY col_name [ASC | DESC]]]

SHOW DATABASES

SHOW DATABASES

SHOW TABLES

SHOW TABLES

UPDATE

UPDATE tbl_name
   SET col_name1=expr1 [,col_name2=expr2] ...
   [WHERE where_condition]
   [ORDER BY col_name [ASC | DESC]]

USE

USE db_name

jQuery Support

In order to support older web browsers, specifically Internet Explorer 7, this package requires jQuery 1.8.3. Furthermore, jQuery has recently dropped support for IE8 in version 2, so if there is no compelling reason to support these two web browsers it is recommended that you upgrade to the latest version of jQuery. If you still need to support IE8 please upgrade to jQuery 1.9+.

Functional Testing

This package has been preconfigured to support QUnit headless testing using Travis-CI. If you plan on using another integration tool you will have to create a custom project that combines the use of QUnit and PhantomJS plugins. You can manually run these tests in your web browser by clicking here.

Releases

I have included with this package a packed version (13 kB) and developers version (unpacked 46 kB)

You can always find the latest updates within this projects repository.

Code Repository

This projects repository is currently hosted on Github

https://github.com/nuxy/SQLinJS

Maintainer

For feedback, bug reports, or other comments, feel free to contact me at: devel at mbrooks dot info

License and Warranty

This package is distributed in the hope that it will be useful, but without any warranty; without even the implied warranty of merchantability or fitness for a particular purpose.

SQLinJS is provided under the terms of the MIT license.