Fork me on GitHub

SQLinJS Build Status

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

Features

Check out the demo provided with this package.

Installation

This package can be easily installed using Bower.

$ bower install sqlinjs

Manual install:

  1. Download the latest sources to your computer using a web browser.
  2. Extract the contents of the .zip into a folder on your local computer.
  3. 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

General

SQL common

initTerminal

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

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

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
  }
);
Back to Top

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
  }
);
Back to Top

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
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

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]);
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

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);
      }
    }
  }
);
Back to Top

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]);
      }
    }
  }
);
Back to Top

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]);
      }
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

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
    }
  }
);
Back to Top

Database Storage

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

Back to Top

Supported Syntax

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

CREATE DATABASE

CREATE DATABASE db_name
Back to Top

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.

Back to Top

DELETE

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

DESCRIBE

DESCRIBE tbl_name
Back to Top

DROP DATABASE

DROP DATABASE db_name
Back to Top

DROP TABLE

DROP TABLE tbl_name
Back to Top

INSERT

INSERT INTO [(col_name, ...)] VALUES (col_value, ...)
Back to Top

SELECT

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

SHOW DATABASES

SHOW DATABASES
Back to Top

SHOW TABLES

SHOW TABLES
Back to Top

UPDATE

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

USE

USE db_name
Back to Top

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.

SQLinJS ©2012-2015 Marc S. Brooks