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 terminal for managing object data using the SQL syntax.
Provides API methods for managing object data from within your application.
Supports jQuery .data() for database storage.
Fast and lightweight (jQuery plug-in *only 12 kB)

Check out the demo provided with this package.

Installation

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://www.google.com/jsapi"></script>
<script>
	google.load('jquery','1.7.1');
</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.

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 supported database format.

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) {

		// 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) {

		// 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) {

		// 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) {

		// do something
	}
);

dropDatabase

Delete a database, by name.

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

Use Example:

sql> DROP DATABASE accounts

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

		// 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) {

		// 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')

dbh.SQLinJS('insertInto','user',
	{
		id   : 1,
		name : 'Jerry'
	},
	function(response) {

		// 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    : 1
	},
	function(response) {

		// do something
	}
);

showDatabases

Return an array of database names.

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

Use Example:

sql> SHOW DATABASES

dbh.SQLinJS('showDatabases',
	function(response) {

		// do something
	}
);

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) {

		// do something
	}
);

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) {

		// do something
	}
);

useDatabase

Return an array of table names in the active database.

dbh.SQLinJS('useDatabase', name);

Use Example:

sql> USE DATABASE accounts

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

		// do something
	}
);

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

USE

USE db_name

Releases

I have included with this package a packed version (12 kB) and developers version (unpacked 29 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.