The HTML5 SQLite spec results in a database with a limit of 5MB. Chris Brody’s SQLitePlugin, however, breaks this barrier. Here is how to implement it. These steps assume you’ve already created the Cordova project and are adding this database project to it. I recommend you build for v19.
If you just want a simple prepopulated DB, try this article instead.
My development environment:
Mac OS X 10.9.1 on latest Mac Mini
Cordova CLI 3.4.0
for Android 4.3, 4.4 v19
Testing in device: Android-19, Nexus 7 with 4.3
Not for PG Build; no Jquery or other JS or CSS packages used
Cordova-SQLitePlugin:
https://github.com/brodysoft/Cordova-SQLitePlugin
Benefits:
http://brodyspark.blogspot.in/2012/12/cordovaphonegap-sqlite-plugins-offer.html
Google Groups forum:
https://groups.google.com/forum/#!forum/Cordova-SQLitePlugin
Create a Cordova or PhoneGap project (if you’re new):
https://iphonedevlog.wordpress.com/2014/01/31/adding-cordova-apis-to-android-via-cli-accelerometer-and-camera/
Installation
This article assumes you’ve already created a project and added the Android platform to it (cordova create app, cd myapp, cordova platform add android).
1. Create subfolder structure under platforms/android/src/ as:
platforms/android/src/android/org/pgsqlite/
2. Visit the plugin site on Github at https://github.com/brodysoft/Cordova-SQLitePlugin.
3. On the Github site, navigate step 1’s folder structure (starting with /src) and download the SQLitePlugin.java file contents to the pgsqlite/ folder among the project files. (To do this, right-click on the file, select Save Link As, and select the folder to save in.)
4. On the Github site, enter the www/ folder and download the SQLitePlugin.js file to the project’s www/ folder the same way.
5. On the Github site, download the plugin.xml and SQLitePlugin.coffee.md to the www/folder the same way.
6. In your project’s site, open www/config.xml with a text editor (I use TextWrangler) and add these lines among the other plugins (or right above the </widget> line):
<feature name="SQLitePlugin"> <param name="android-package" value="org.pgsqlite.SQLitePlugin" /> </feature> <plugin name="SQLitePlugin" value="org.pgsqlite.SQLitePlugin"/>
7. Copy and paste the lines in step 6 into platforms/android/res/xml/config.xml
8. Install the plugin:
cordova plugin add https://github.com/brodysoft/Cordova-SQLitePlugin9. Create an html page (open a document in a text editor and save as sqlite.html, or just replace the index.html page if its the default page) and paste the following example into it.
Here are the complete contents of the HTML page:
<!-- ********************* BEGIN *********************** -->
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="format-detection" content="telephone=no" />
<!-- WARNING from Cordova: for iOS 7, remove the width=device-width and height=device-height attributes. See https://issues.apache.org/jira/browse/CB-4323 -->
<meta name="viewport" content="user-scalable=no, initial-scale=1, maximum-scale=1, minimum-scale=1, width=device-width, height=device-height, target-densitydpi=device-dpi" />
<title></title>
<script type="text/javascript" charset="utf-8" src="cordova.js"></script>
<script type="text/javascript" charset="utf-8" src="SQLitePlugin.js"></script>
<style type="text/css">
.buttonClass, .buttonClass2, h2, p {
font-family: "Helvetica"; color: #000; font-size:1em;
}
.buttonClass, .buttonClass2 {
border-radius:8px; background-color:#fff;
border:#878787 solid 1px; padding:0 1em;margin:.5em;
height: 3em; width: 46%;
text-align:center;
-webkit-appearance:none;
}
.buttonClass2 {
background-color: silver;
}
.segment, .segment2 {
display:block; border-radius:8px; background-color:#eee;
border:#878787 solid 1px; padding:1em; margin:.5em;
text-align:left;
-webkit-appearance:none;
}
.segment2 {
}
h2 {
font-size:1.3em; font-weight: bold;
}
table {
width:100%;
}
td {
font-size:.8em;
padding: .5em;
width:25%;
border:1px gray solid;
overflow:auto;
border-radius: 4px;
background-color: #fff;
height:2em;
}
@media screen and (max-width:800px) {
.buttonClass { width: 100%;}
}
</style>
<script type="text/javascript" src="cordova.js"></script>
<script type="text/javascript" src="SQLitePlugin.js"></script>
<script type="text/javascript" charset="utf-8">
document.addEventListener("deviceready", onDeviceReady, false);
function onDeviceReady() {
var db = window.sqlitePlugin.openDatabase({name: "my.db"});
db.transaction(populateDB, errorCB, successCB);
}
// create table
function populateDB(tx) {
tx.executeSql('DROP TABLE IF EXISTS test_table');
tx.executeSql('CREATE TABLE IF NOT EXISTS test_table (id integer primary key, data1 text, data2 integer, svgImage text)');
tx.executeSql('INSERT INTO test_table (data1, data2, svgImage) VALUES (?,?,?)', ['test1', 100, '<svg version="1.1" x="0px" y="0px" width="100px" height="100px" viewBox="0 0 100 100" enable-background="new 0 0 100 100" xml:space="preserve"><rect x="4.815" y="4.815" fill="#039BF9" stroke="" width="100" height="100"></svg>']);
tx.executeSql("INSERT INTO test_table (data1, data2, svgImage) VALUES (?,?,?)", ['test2', 200, '<svg version="1.1" x="0px" y="0px" width="100px" height="100px" viewBox="0 0 100 100" enable-background="new 0 0 100 100" xml:space="preserve"><rect x="4.815" y="4.815" fill="#039BF9" stroke="" width="100" height="100"></svg>']);
queryDB(tx);
}
// form the query
function queryDB(tx) {
tx.executeSql("SELECT id, data1, data2, svgImage from test_table;", [], querySuccess, errorCB);
}
// Display the results
function querySuccess(tx, results) {
var len = results.rows.length;
alert("results.rows.length: " + results.rows.length + " [should be 2]");
for (var i = 0; i < len; i++) { // loop as many times as there are row results
document.getElementById("output").innerHTML +=
"<table><tr><td>ID = " + results.rows.item(i).id +
"</td><td>data1 = " + results.rows.item(i).data1 +
"</td><td>data2 = " + results.rows.item(i).data2 +
"</td><td>svgImage = " + results.rows.item(i).svgImage + "</td></tr></table>";
}
}
// Transaction error callback
function errorCB(err) {
console.log("Error processing SQL: " + err.code);
}
// Success error callback
function successCB() {
}
</script>
</head>
<body>
<input type="button" class="buttonClass" onclick='window.location="index.html"' value="return">
<div class="segment">
<h2>SQLitePlugin Test</h2>
<div id="output"> </div>
<input type="button" class="buttonClass" onclick='window.location="https://iphonedevlog.wordpress.com/2014/04/07/installing-chris-brodys-sqlite-database-with-cordova-cli-android/"' value="View Code On iPhoneDevLog">
</div>
</body>
</html>
<!-- ********************* END *********************** -->
Test the Page
1. In Terminal, build the app, making sure the path starts with your project folder:
cordova build
2. Install in device and test. You should get an alert saying how many rows of records are found in the Select query, and the output will be in table form. You should see a blue square (an SVG image) in each column.
Alert firing:
Final loading:
General Notes
- Android & iOS versions are working with Cordova 3.0 tooling.
- Drop-in replacement for HTML5 SQL API. The only major change is to use window.sqlitePlugin.openDatabase() or sqlitePlugin.openDatabase() instead of window.openDatabase().
- Keeps sqlite database in a user data location that is known, can be reconfigured, and iOS will be backed up by iCloud.
- No 5MB maximum! More information at: http://www.sqlite.org/limits.html
- Problem with Android 4.4 [unspecified].
- Issue buiding with Android SDK < 16.
- The db version, display name, and size parameter values are not supported and will be ignored.
- The sqlite plugin will not work before the callback for the “deviceready” event has been fired.
- The plugin class name starts with “SQL” in capital letters, but in JavaScript the sqlitePlugin object name starts with “sql” in small letters.
iPhone Dev Log 
Is there a reason you’re not installing it with cordova plugin add? It works at least for iOS with the latest Cordova, and I find it a much easier way to install the plugin.
Yes – I simply found very little information on how to get this plugin to work. My article was the first step-by-step method I found that covered everything.
Hi, thank you for great tutorial! I have the sqlite plugin working (it returns the message: result.rows.length: 2 etc.). However, I cannot find where my database is stored. I thought it was supposed to be somewhere on my sdcard. I tested with large data as well and when the ‘data used by application – in settings’ reaches about 60 MB the app crashes with each database query.
I thought this sqlite plugin has no limits (I do have about 11GB free space on the phone I tested on).
Can you please help me at all?
I thought it had no limits too, as their web site says.
I believe the database is created on the fly each time the page loads. I don’t think it is permanent on the device anywhere. That’s the only thing that makes sense to me.
Hi plugin is working fine using phonegap 3.4 and sencha touch 2.3. But I had a strange behavior on iPhone. When I am building application with architecture armv7s its working fine. But if I am building application using armv7 or arm64 it is showing less data rather in the table rows. What can be the issue?
Sorry, I have no idea.
please turorial sqlite database with cordova cli windows phone :D
Unfortunately, I only make apps for which I have a device to test. I don’t have a Windows phone.
where does the database gets store?? can we see through the browser console when running it?
At best, it is “stored” on the html page where you write out the code. Other than that, I don’t have an answer.
i’ve got this error emulating with ripple, can you help me?
Ripple :: Environment Warming Up (Tea. Earl Gray. Hot.) ripple.js:25879
cordova :: Initialization Finished (Make it so.) ripple.js:25879
The key “target-densitydpi” is not supported. (index):8
SQLitePlugin openargs: {“name”:”tper2″} SQLitePlugin.js:8
missing exec:SQLitePlugin.open ripple.js:34331
[Object]
ripple.js:34332
TypeError {stack: (…), message: “Cannot read property ‘open’ of undefined”}
ripple.js:34333
TypeError: Cannot read property ‘open’ of undefined
at Object.module.exports.exec (http://localhost:4400/ripple/assets/ripple.js:34328:30)
at SQLitePlugin.open (http://localhost:4400/SQLitePlugin.js:56:15)
at new SQLitePlugin (http://localhost:4400/SQLitePlugin.js:24:10)
at Object.SQLiteFactory.opendb [as openDatabase] (http://localhost:4400/SQLitePlugin.js:386:14)
at onDeviceReady (http://localhost:4400/:58:31)
at Channel.fire (http://localhost:4400/cordova.js:795:23)
at http://localhost:4400/cordova.js:223:49 ripple.js:34334
SQLitePlugin ripple.js:50497
open ripple.js:50498
missing exec:SQLitePlugin.executeSqlBatch ripple.js:34331
[Object]
ripple.js:34332
TypeError {stack: (…), message: “Cannot read property ‘executeSqlBatch’ of undefined”}
ripple.js:34333
TypeError: Cannot read property ‘executeSqlBatch’ of undefined
at Object.module.exports.exec (http://localhost:4400/ripple/assets/ripple.js:34328:30)
at SQLitePluginTransaction.run (http://localhost:4400/SQLitePlugin.js:286:13)
at SQLitePluginTransaction.start (http://localhost:4400/SQLitePlugin.js:160:12)
at SQLitePlugin.addTransaction (http://localhost:4400/SQLitePlugin.js:38:9)
at SQLitePlugin.transaction (http://localhost:4400/SQLitePlugin.js:43:10)
at onDeviceReady (http://localhost:4400/:59:5)
at Channel.fire (http://localhost:4400/cordova.js:795:23)
at http://localhost:4400/cordova.js:223:49 ripple.js:34334
SQLitePlugin ripple.js:50497
executeSqlBatch ripple.js:50498
No, I am unfamiliar with this.
Hi Steve,
I have facing security issue in integrating this same plugin in my android app.
After adding the SQLite plugin from cordova command line tool, it modified my Manifest file and added one activity node as below..
For security purpose, I need to remove this kind of activity without permission.
Can i remove this node from the manifest and what would be the impact?
Your advice is appreciated.
Thanks
Sorry, for questions on the SQLLite plugin, you’ll need to contact the author. I don’t have an answer.
Fantastic walk through. .. Finally got it all working after weeks of hair pulling with other ‘demos’…is there any way to open a prepopulated database? Where would I need to store it. ..I guess this is a similar question as to where the db created gets stored. .
Thanks!
I don’t understand what you mean by “open a prepopulated database.” When You code all the information in the database on your HTML page, and it runs in the device, then the page displays the contents of the database. The data is “stored” on the page in which you coded it. (It’s possible I am misunderstanding the concept, however. But this is how I presently understand it.)
I completely understand you Tim, and I have the same question.
The database is stored on the HTML page in the Javascript functions. Steve, What we are asking/looking for is to open/query a database that is in a separate file altogether eg. a database.db file in the assets folder etc. With Javascript in the HTML to only query/search/view the data.
My app doesnt write to the database, only looks at it.
so this plugin is only for temporary database creation and usage?
do you have any recommendations for getting around the 5mb limit that is a more permanent local storage solution for tabular data?
I don’t know what you mean by temporary. It is there as long as your code is on the page. Chris says that his plugin breaks the 5MB barrier. If you have reservations about this, you’ll need to address it with him.
Hi sir is this working for phonegap 3.5?
Sorry, I haven’t tried it at that version.
Hi! I’m working for my degree thesis. I’ve a prepopulated database. At the beginning I had some problem where to place this db, then I read the Xcode console the following line:
“2014-10-15 11:55:54.591 FollowMyBus[41862:357738] SQLitePlugin openargs: {“name”:”tper2″,”bgType”:1}
2014-10-15 11:55:54.592 FollowMyBus[41862:357738] Detected docs path: /Users/federico/Library/Developer/CoreSimulator/Devices/76426BBC-EEBC-4DA2-AE6A-93CA67379039/data/Containers/Data/Application/5E51D61F-F7CA-4557-A013-7DCA8E22D1D2/Documents”
so i navigate in this path and i find the empty db and i replace with the prepopulated, i launch my app on the ios simulator and its work great.
But now i don’t know if when install the app into the device take the prepopulated db.
Do you have any ideas?
Thanks a lot.
I’m not positive, but I think the data in that drive location will only work in the simulator, not the device. I base this on the URL portion: …/Library/Developer/CoreSimulator/…
I’m not positive me too. But could you help me?
thanks a lot!
I have no idea on what to do, sorry. I suggest you ask in the Phonegap Google Group or SQLite Google Group.
thank you so much! :)
https://groups.google.com/forum/#!forum/cordova-sqliteplugin
https://groups.google.com/forum/#!forum/phonegap
Hi, is there a way to read the data from existing sqlite database using this plugin; I tried doing that with the above code however did not work well.
This is my code:
I have eliminated the create db function since I already have the database. The database name is test.sqlite
document.addEventListener(“deviceready”, onDeviceReady, false);
function onDeviceReady() {
var db = window.sqlitePlugin.openDatabase({name: “test.sqlite”});
db.transaction(queryDB, errorCB, successCB);
}
// form the query
function queryDB(tx) {
alert(“we are into this function”);
tx.executeSql(“SELECT name, pop_male, pop_female, pop_total from pop_count;”, [], querySuccess, errorCB);
}
// Display the results
function querySuccess(tx, results) {
var len = results.rows.length;
alert(“results.rows.length: ” + results.rows.length + ” [should be 3]”);
for (var i = 0; i < len; i++) { // loop as many times as there are row results
document.getElementById("output").innerHTML +=
"name = ” + results.rows.item(i).name +
“male = ” + results.rows.item(i).pop_male +
“female = ” + results.rows.item(i).pop_female +
“total = ” + results.rows.item(i).pop_total + “”;
}
}
// Transaction error callback
function errorCB(err) {
console.log(“Erroressing SQL: ” + err.code);
document.getElementById(“output1″).innerHTML +=”Error 1”;
}
// Success error callback
function successCB() {
}
Sorry, I simply don’t know. You’ll want to address this on the appropriate Google Groups.
issue: no such file exist in my project – it do exist in “myapp” root folder.
6. In your project’s site, open www/config.xml and add these lines among the other plugins:
Looks like WordPress doesn’t allow code tags. Sorry yours did not go through.
Great! Very usefull guide, I was stuck reading a pre populated sqlite database with ngcordova but with this I got it run.
Thanks
Hi
Just wanted to say Hi and Thanks a lot!
Now that I try it, the cordova build didn’t have the 100 java errors you mentioned on your article, just wanted to let you know that. It works! :)
That’s good to know!
hi steve
i did all the step as you have mentioned in your tutorial, but am facing a strange issue that my app is able to get the db object but db.transaction() funcition isnever called..
can you plz help me in solving it…….
is this also work for IOS plateform ?
I have not tried it on iOS.
Hi,
I am getting window.sqlitePlugin as undefined while trying to create database when run on iOS simulator. In website it is working fine but on iOS simulator, it was giving undefined using window.sqlitePlugin. Do you have any solution for this?
I haven’t worked on this for years. I have no idea of how to help you.