Guest Blog Post: Shipping a populated SQLite DB with PhoneGap

This post is more than 2 years old.

This is my second "Guest Blog Post" for the month. Sorry I've been a bit slow on my own content lately! Today's post comes from Scott Buckel. He and I shared a conversation earlier this month about databases and PhoneGap. PhoneGap makes it pretty darn easy to create and work with a database in your application. What isn't so trivial is actually shipping a prepopulated database with the app itself. I wasn't able to help him as much as I'd like - but he worked at it until he came up with a solution. Here is what he discovered.

These instructions are very raw, and not optimized.

  1. I used this plugin: https://github.com/chbrody/Cordova-SQLitePlugin/
  2. Copy your sqlite db file to /assets folder of PhoneGap.
  3. Then, follow instructions here: http://gauravstomar.blogspot.com/2011/08/prepopulate-sqlite-in-phonegap.html to copy the file to native storage. Change this.copy("Databases.db","/data/data/"+pName+"/app_database/");
    1. Instead of Databases.db, use your database filename.
    2. Instead of app_database, use "databases"
    3. You'll probably want to delete the file from /assets, since it is duplicated and no longer needed. My app was double the size it needed to be.
  4. (Not sure if this step is necessary, I'm getting out of the office for the day). Edit SQLitePlugin.java
    1. Lines 176, I edited 1==1 so that if statement is always executed. Line 179 I changed this.setStorage(appPackage, false); to this.setStorage(appPackage, true);
  5. You can then use the following command to open the DB and use it as any other PhoneGap database
    1. var db = window.sqlitePlugin.openDatabase("[full_database_name_including_extension]", "1.0", "PhoneGap Demo", 200000);

10 hours of work later, I have a working database!

Feel free to use this information on a blog, I feel it would help a LOT of people out. Step #4 is weird, I'm sure there's a "prettier" way to do it. If used, just give Scott Buckel @ Corporate Zen credit please :).

Raymond Camden's Picture

About Raymond Camden

Raymond is a senior developer evangelist for Adobe. He focuses on document services, JavaScript, and enterprise cat demos. If you like this article, please consider visiting my Amazon Wishlist or donating via PayPal to show your support. You can even buy me a coffee!

Lafayette, LA https://www.raymondcamden.com

Archived Comments

Comment 1 by Vitgas posted on 8/2/2012 at 2:38 PM

May you upload some screenshots showing how your proyect is organized and those changes you remark?
Thanks!

Comment 2 by Scott posted on 8/3/2012 at 11:19 PM

Vitgas, feel free to email me directly and I'd be glad to help. I'll have Ray update the blog after I understand your questions.

Comment 3 by Scott Buckel posted on 8/9/2012 at 12:32 AM

Here's an updated version on how I did this. Thanks to Ray for all of his help!

Comment 4 by Raymond Camden posted on 8/9/2012 at 12:34 AM

Did you mean to post a link?

Comment 5 by Raymond Camden posted on 8/9/2012 at 12:36 AM

Here is the link he meant: http://www.corporatezen.com...

Comment 6 by Jeremy posted on 9/30/2012 at 6:53 PM

Thanks so much for this post! It helped point me in the right direction for my own project. For any iOS developers, I found the linked instructions in http://gauravstomar.blogspo... to be a little out of date.

When using the Cordova-SQLitePlugin, it looks in the Documents directory, and Gaurav Tomar's instructions refers to WebKit/Databases directory (which I believe is no longer used because of an iOS 5.1 change).

I found the following works instead:

NSString *databaseName = @"Database.db";

NSArray *documentPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *databasePath = [documentPaths objectAtIndex:0];

NSString *databaseFile = [databasePath stringByAppendingPathComponent:databaseName];
NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:databaseName];

NSFileManager *fileManager = [NSFileManager defaultManager];

if ([fileManager fileExistsAtPath:databaseFile] == NO) {
[fileManager copyItemAtPath:databasePathFromApp toPath:databaseFile error:nil];
[fileManager release];
}

Thanks again for pointing me in the right direction!

Comment 7 by Steve Husting posted on 10/6/2012 at 1:17 AM

You can use the working steps here to create prepopulated databases with PhoneGap:
http://iphonedevlog.wordpre...
or
http://iphonedevlog.wordpre...

Regards,
Steve Husting

Comment 8 by Vaibhav posted on 1/22/2013 at 11:07 PM

What is the best way to use this data to create knockout JS bindings on the underlying HTML. do i need to create separate JSON object first with the data that i get from the queries or is there an easier way to integrate this with knockout.js?

Comment 9 by Raymond Camden posted on 1/22/2013 at 11:39 PM

Unfortunately, I have not had a chance yet to play with Knockout. Therefore, I've definitely not used it with PhoneGap either.

Comment 10 by Kristen posted on 2/14/2013 at 12:46 AM

Wow! Thank you so much! I have been trying to get this working forever. Your example worked like a charm and I'm finally up and running.

Comment 11 by M. K. posted on 3/8/2013 at 3:52 PM

Wow from me too!! We got this sorted via this post. Thanks a bunch guys!! :-)

Comment 12 by Iszuddin Ismail posted on 4/8/2013 at 9:26 AM

Correct me if I am wrong (haven't really tested the code), but the example here would overwrite the database with the preshipped version everytime the app loads. Is that right? If we are building an app with persistent storage, should we check to see if the database files already exist before doing this.copy(...) or perhaps modify this.copy()?

Comment 13 by John Gag posted on 4/25/2013 at 2:32 AM

If anyone is looking to use a pre populated db with iOS here is updated plugin SQLitePlugin-iOS. You dont need to do any of the steps above. All you need to do is put your db in the root of your XCode project and it does everything for you. Android you still need to do the steps above.

https://github.com/jarlehan...

Comment 14 by Rahul Kadukar posted on 4/25/2013 at 5:26 PM

This is how my MyPhoneGapActivity.java looks like

package com.rkadukar.database;

import org.apache.cordova.DroidGap;
import android.os.Bundle;

public class MyPhoneGapActivity extends DroidGap {
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
super.loadUrl("file:///android_asset/www/index.html");
}
}

where do I add the code from Gaurav Tomar's site after I make the changes suggested by you

Comment 15 by Raymond Camden posted on 4/25/2013 at 5:31 PM

Thanks for sharing that John.

Comment 16 by shashank dwivedi posted on 5/6/2013 at 1:58 PM

How can I use this with phonegap build? Any idea.....

Comment 17 by Raymond Camden posted on 5/6/2013 at 4:34 PM

You can't. PGB supports a limited set of plugins only.

Comment 18 by Iszuddin posted on 5/6/2013 at 4:55 PM

I've successfully implement this on a simple POS app. Now I have a different requirement -- nothing to do with the previous POS app.

Is there a way for me to download a SQLite file and do SQL operations on that SQLite database file? Here's why I need that. My app will be downloading a huge document, and for searching purpose, I am thinking of implementing the index using the SQLite database file.

Got ideas on how I can do this?

Comment 19 by vikas.s posted on 6/12/2013 at 3:04 PM

i get error as cordova not defined in SQLiteplugin.js

Comment 20 by Naresh Ramoliya posted on 7/15/2013 at 3:35 PM

I have some issue in installation of SQLite Plugin for both Android and iOS. I have followed exactly given steps but somehow i am not able to run correctly. so Please help me in configuration of SQLite plugin

I am using Cordova 2.9.0 for phonegap.

Regards,

Comment 21 by Steve posted on 7/15/2013 at 5:13 PM

@Naresh,

You did not tell us what error you are receiving, What part is not working correctly? At what step does it fail?

Comment 22 by William posted on 7/22/2013 at 3:41 PM

I have the question as Iszuddin Ismail asked.
I didn't get it, cause each time I turn off my app and reopen it.
Then the database got cleaned up.

So what can I do to prevent my database be cleaned up?
Thanks~

Comment 23 by jeff posted on 7/23/2013 at 6:00 AM

Following http://gauravstomar.blogspo... copy sqlite db to assets folder, where is "data/data" in my application? is the 'databases' folder automatically created?

This is what I wrote
String pName = this.getClass().getPackage().getName();
this.copy("aaa.db","/data/data/"+pName+"/databases/"); this.copy("aaa.db","/data/data/"+pName+"/databases/file__0/");

Thanks

Comment 24 by William posted on 7/25/2013 at 10:03 AM

Hi Jeff,
The data/data folder is at the system root.
I found the folder from eclipse's window.

I turn on the DDMS (window > open perspective > DDMS) and there is a
File Explorer tab. When you run your app, you can find the data/data folder
here.

And the folder will be created, cause the copy method check if the directory exist. And create it if not.

Comment 25 by Marco posted on 8/6/2013 at 2:43 PM

For me does not work :(.
I wrote in my Activity:
try {
String pName = this.getClass().getPackage().getName();
this.copy("dbname.db", "/data/data/" + pName + "/databases/");
} catch (IOException e) {
e.printStackTrace();
}
and the db is copy correctly!!!
But when I open DB,(this is the javascript code):
var db = window.sqlitePlugin.openDatabase("dbname.db", "1.0", "PhoneGap Demo", 200000);
(I tried also with no db extension "dbname")
For me DB not found?
Where is the problem? :(

Comment 26 by Jeff posted on 8/27/2013 at 3:11 PM

I copied source from here https://github.com/k4ml/hal..., its working... my concern now is whenever I use the application from my smartphone, any database transaction I made is gone when I open it again. I know it can write to the sqlite db, I think it somehow overwrite the sqlite file everytime I launch the application. Anyone have solution for this?

Thanks

Comment 27 by velissariouc posted on 9/8/2013 at 7:13 PM

@ Jeff
" I think it somehow overwrite the sqlite file everytime I launch the application"

You are right. The "halal" example always copies the original database from assets to data.
The code should be like this (replace "mydb" with your database name):
try
{
File dbFile = getDatabasePath("mydb.db");
if(!dbFile.exists()){
this.copy("mydb.db",dbFile.getAbsolutePath());
}
}
catch (Exception e)
{
e.printStackTrace();
};

Comment 28 by Shafi posted on 11/4/2013 at 10:40 AM

Hi Raymond
I tried to use prepopulated db in iOS using phonegap 2.7,3 and 3.1 but all showed same database undefined error. As per instructions (from your source and from the others) i tried to put some native code in to mainviewcontroller.m but the database is still cannot be opened i think.. Please help me

Comment 29 by Raymond Camden posted on 11/5/2013 at 5:13 PM

As I said - this is not something I did myself. I was just passing on the tip. This post is now over a year old so it may not apply anymore.

Comment 30 by Vessie posted on 11/29/2013 at 9:51 PM

Thanks for all your posts. They are very helpful.

I'm trying to use phonegap/cordova3 with SQLite plugin from https://github.com/lite4cor... but I have hard time making this work. The plugin seems to be written for cordova 3 but when I run it with the emulator it says "something is missing". It's crashing on crodova.exec.

Anybody successfully used SQLitePlugin with cordova 3? I also read some posts that the plugin is part of cordova 3 but not sure....

Comment 31 by Scott Buckel posted on 11/29/2013 at 10:07 PM

Vessie,

I think that this post is about a year and a half old now, so a lot of the methods might not apply. This was written for PhoneGap 1.8 or 2.0 (I forget), so it might not be relevant anymore.

You'll probably have to figure out on your own how to do this in the latest PhoneGap 3.0+. Could you please post a comment here after you figure it out?

Ray--you should probably put a comment on this blog that it's outdated.

Thanks!
-Scott Buckel

Comment 32 by Vessie posted on 11/30/2013 at 9:31 PM

I realized this is an old post but just wondered if somebody got this work with the latest phonegap/cordova 3. I will post a comment if I figure out (so far no luck).
Thanks!

Comment 33 by Raymond Camden posted on 12/1/2013 at 6:57 PM

@Scott: I tend to believe that people are smart enough to pay attention to the date and understand that the older entries may not be relevant/applicable. Maybe I assume too much. ;)

Comment 34 by Leo posted on 1/6/2014 at 7:28 AM

I invented a sqlite js library, would you like to take a look?
https://github.com/leotsai/...

Comment 35 by Navya Kooram posted on 2/14/2014 at 1:00 AM

Raymond,

I used Gaurav's post to pre-populate my SQLite database and it worked well in iOS and Android platforms using Phonegap 2.8 to 3.3 versions. But it isn't working with Android Kitkat SDK. The app doesn't work for Android 4.4 and above. It looks like the data folder is empty when running on those devices and emulators. Did you face such problems? Does Kitkat handle folder/file permissions differently??

Thanks and Regards,
Navya

Comment 36 by Raymond Camden posted on 2/22/2014 at 7:24 PM

You may be running into FileSystem changes. See: http://www.raymondcamden.co...

Comment 37 by David Guerra posted on 3/11/2014 at 5:01 PM

Hi Raymond I have the same problem of Navya.
For 2 years this solution works like a charm but after Android 4.4 the APP give me this error:
"Error was could not prepare statement (1 no such table reservation) (code 5) "
I suppose Android create an empty DB instead of copy it... so did not found the table.
Any idea about it?

Comment 38 by Raymond Camden posted on 3/11/2014 at 5:04 PM

Unfortunately no - as I mentioned - this was a guest post - not something I did myself.

Comment 39 by Ram Gurung posted on 4/29/2014 at 3:33 PM

I have following issue. Please help me to get out of it.
http://stackoverflow.com/qu...

Comment 40 by Rahul posted on 9/11/2014 at 9:59 AM

Hi,

I have created this plugin for copying the db, hope it will help somebody -

https://github.com/an-rahul...

Comment 41 by Valerio posted on 11/7/2014 at 12:53 AM

Thank you so much!!!
I've spent almost 3 days in get this things working!!!