2009-02-04 20:11:52 8 Comments
If your application requires a database and it comes with built in data, what is the best way to ship that application? Should I:
Precreate the SQLite database and include it in the
.apk
?Include the SQL commands with the application and have it create the database and insert the data on first use?
The drawbacks I see are:
Possible SQLite version mismatches might cause problems and I currently don't know where the database should go and how to access it.
It may take a really long time to create and populate the database on the device.
Any suggestions? Pointers to the documentation regarding any issues would be greatly appreciated.
Related Questions
Sponsored Content
14 Answered Questions
[SOLVED] Dilemma: when to use Fragments vs Activities:
- 2013-11-30 21:53:00
- Emil Adz
- 170650 View
- 718 Score
- 14 Answer
- Tags: android android-fragments android-activity architecture
38 Answered Questions
[SOLVED] Is quitting an application frowned upon?
- 2010-01-09 15:59:46
- Ted
- 262700 View
- 1076 Score
- 38 Answer
- Tags: android
7 Answered Questions
1 Answered Questions
[SOLVED] Which approach is better creating database programmatically or copying it from assets folder?
- 2017-01-31 14:01:56
- pankaj khedekar
- 65 View
- 1 Score
- 1 Answer
- Tags: android android-sqlite
1 Answered Questions
How to use Pre populated Database with the real device
- 2016-03-16 14:09:42
- Allay Khalil
- 226 View
- 0 Score
- 1 Answer
- Tags: android android-sqlite android-assets android-database
0 Answered Questions
Light weight mechanism for encrypting sqlite database on android without shipping customized sqlite distibution
- 2014-06-06 07:12:40
- Parikshit Sarnaik
- 252 View
- 0 Score
- 0 Answer
- Tags: android database sqlite encryption android-sqlite
1 Answered Questions
1 Answered Questions
[SOLVED] Using multiple SQL databases
- 2014-04-24 00:02:54
- user3566673
- 57 View
- 1 Score
- 1 Answer
- Tags: android android-sqlite
1 Answered Questions
[SOLVED] How to encrypt shipped database in android?
- 2013-01-17 09:44:03
- daigoor
- 256 View
- 0 Score
- 1 Answer
- Tags: android database sqlite encryption
4 Answered Questions
[SOLVED] Android Pre-installing NDK Application
- 2011-07-01 10:24:05
- artsylar
- 5116 View
- 9 Score
- 4 Answer
- Tags: android android-ndk
16 comments
@Heikki Toivonen 2009-03-06 19:23:44
I just found a way to do this in
ReignDesign blog
in an article titled Using your own SQLite database in Android applications. Basically you precreate your database, put it in your assets directory in your apk, and on first use copy to/data/data/YOUR_PACKAGE/databases/
directory.@Will 2009-03-06 23:17:10
Nice link. This looks faster than INSERTing every row into a database, but you still have the "duplicate databse" issue. There will be two copies of the database, one in the .apk and one in the filesystem.
@Will 2009-03-07 03:51:20
I tested the code. It works perfectly and brought an 18+ second operation down to about 2 seconds. I'd vote up again if I could.
@strange 2011-03-30 21:37:14
Does this still work with 2.3 as I read somewhere in the comments that people can't copy their DB anymore on devices running 2.3+. Would it make any difference if we compiled our apps against 2.2?
@Robert Massaioli 2011-06-07 01:11:22
@Will: If you want you could demand Internet Access and download the database from a server if you did not want to include it in the APK.
@gonzobrains 2011-08-30 15:34:28
This causes your app to have two databases consuming space, right?
@Jack BeNimble 2011-09-09 12:57:53
I had all sorts of trouble with this solution, it is a real issue with 2.3. I suggest follow Robert's suggestion and downloading the DB from the web. Once I did this, I've had no problems.
@scottyab 2011-10-20 13:43:41
It works ok on nexus s, but issues with the Samsung Galaxy S on 2.3.3
@gonzobrains 2011-12-09 23:30:19
I have tried this approach but the database fails to open on some models, such as Galaxy S, Motorola Bionic, and Evo 4G. Any ideas?
@sargas 2012-07-29 01:35:10
Don't forget to use Context.getDatabasePath instead of hard-coding the path.
@powerj1984 2012-08-07 01:54:52
Would it be possible to attach it as an APK Expansion file? (developer.android.com/guide/google/play/expansion-files.html)
@iDroid Explorer 2013-01-22 10:12:36
@sargas: where to use Context.getDatabasePath ??
@sargas 2013-02-18 01:08:01
@iDroidExplorer, instead of using the DB_PATH+DB_NAME constant in the linked blog, you can use myContext.getDatabasePath(DB_NAME) in the methods of the database helper. This allows flexibility if paths change in future versions of Android.
@tricknology 2013-05-15 15:36:37
sargas and @iDroidExplorer, this assumes that you already have a database in the path the you'd like to locate. If there is no database in /data/data/.../databases/ you'll get NPE.
@CommonsWare 2013-07-10 23:11:04
This code from that blog post is old, outdated, dreadful (concatenation to create file paths?), and problematic (I'm tired of seeing developer questions about trying to get it to work). Please, just use
SQLiteAssetHelper
: github.com/jgilfelt/android-sqlite-asset-helper@Christian García 2013-11-07 17:53:31
@CommonsWare's proposed solution in his comment is by far the best solution. Just extending your
SQLiteOpenHelper
fromSQliteAssetHelper
in that library, and provide the database files and the upgrade scripts in the assets folder. It couldn't be easier!@Vijay 2014-02-07 04:48:13
we can always delete duplicate database.
@PSIXO 2014-03-10 12:22:24
Well you can not delete files from assets folder... Although packing that file (with Zip for example) would reduce this problem.
@Ray Kiddy 2015-09-11 00:12:58
I cannot make this work. See github.com/rkiddy/android-database-first-loading and the exception I get is also described there.
@Script Kitty 2016-07-19 19:14:47
@CommonsWare When using SQLiteAssetHelper, even when saving changes, I've noticed I have to uninstall the app and reinstall it for changes to take place. Has anyone else experienced this? I may open up an issue if it is a problem with the library, or maybe I'm just making a stupid mistake
@CommonsWare 2016-07-19 19:16:49
@ScriptKitty: I have not encountered this problem. There is documentation for how to upgrade the database in the
SQLiteAssetHelper
docs. If you are having difficulty following those, or you are following them but still encountering this problem, consider opening a fresh Stack Overflow question with a Minimal, Complete, and Verifiable example.@bharal 2018-05-30 02:22:07
i bought that book commonsware is selling - in case anyone else wants to find the solution to this problem it is not in that book!
@DavidEG 2012-08-03 21:08:03
The
SQLiteAssetHelper
library makes this task really simple.It's easy to add as a gradle dependency (but a Jar is also available for Ant/Eclipse), and together with the documentation it can be found at:
https://github.com/jgilfelt/android-sqlite-asset-helper
Note: This project is no longer maintained as stated on above Github link.
As explained in documentation:
Add the dependency to your module's gradle build file:
Copy the database into the assets directory, in a subdirectory called
assets/databases
. For instance:assets/databases/my_database.db
(Optionally, you may compress the database in a zip file such as
assets/databases/my_database.zip
. This isn't needed, since the APK is compressed as a whole already.)Create a class, for example:
@Pr38y 2014-12-03 13:21:30
android-sqlite-asset-helper.jar downloading require which credential ?
@Suragch 2015-04-09 03:30:09
If you are using gradle then you just add the dependency.
@Machado 2015-09-15 20:17:31
How do you get data from the DB?
@bendaf 2015-10-06 14:31:05
It is even more easier with Android Studio and gradle. Check the link!
@Roon13 2016-01-22 15:36:19
zip is only necessary if you are targeting gingerbread (api 10) or lower.
@PatriceG 2016-06-28 10:49:17
How is it possible to remove the database to reaload it from the asset directory?
@Mateusz Konieczny 2018-02-13 17:35:02
Note that this library is abandoned, with last update 4 years ago.
@mstoic 2018-10-14 21:00:19
The library crashes the app on devices running Android Pie.
@Harrix 2017-04-12 17:14:01
I modified the class and the answers to the question and wrote a class that allows updating the database via DB_VERSION.
Using a class.
In the activity class, declare variables.
In the onCreate method, write the following code.
If you add a database file to the folder res/raw then use the following modification of the class.
http://blog.harrix.org/article/6784
@LordRaydenMK 2017-11-28 17:21:34
In November 2017 Google released the Room Persistence Library
From the documentation:
The Room database has a callback when the database is first created or opened. You can use the create callback to populate your database.
Code from this blog post.
@Jerry Sha 2018-05-16 17:49:23
Thanks, This worked for me. Java example here
@Carlos Alberto Martínez Gadea 2018-08-17 11:01:02
If you would like to ship an APK with an already existing SQLite, you can add it to the assets folder and use this package github.com/humazed/RoomAsset to perform a migration that will load the SQLite file data into the new one. This way, you can save the populating of data with an existing DB.
@Fangming 2017-11-07 20:23:00
Shipping the app with a database file, in Android Studio 3.0
Shipping the app with a database file is a good idea for me. The advantage is that you don't need to do a complex initialization, which sometimes costs lots of time, if your data set is huge.
Step 1: Prepare database file
Have your database file ready. It can be either a .db file or a .sqlite file. If you use a .sqlite file, all you need to do is to change file extension names. The steps are the same.
In this example, I prepared a file called testDB.db. It has one table and some sample data in it like this
Step 2: Import the file into your project
Create the assets folder if you haven't had one. Then copy and paste the database file into this folder
Step 3: Copy the file to the app's data folder
You need to copy the database file to the app's data folder in order to do further interaction with it. This is a one time action (initialization) to copy the database file. If you call this code multiple times, the database file in data folder will be overwritten by the one in assets folder. This overwrite process is useful when you want to update the database in future during the app update.
Note that during app update, this database file will not be changed in the app's data folder. Only uninstall will delete it.
The database file needs to be copied to
/databases
folder. Open Device File Explorer. Enterdata/data/<YourAppName>/
location. This is the app's default data folder mentioned above. And by default, the database file will be place in another folder called databases under this directoryNow, the copy file process is pretty much like the what Java is doing. Use the following code to do the copy paste. This is the initiation code. It can also be used to update(by overwriting) the database file in future.
Then refresh the folder to verify the copy process
Step 4: Create database open helper
Create a subclass for
SQLiteOpenHelper
, with connect, close, path, etc. I named itDatabaseOpenHelper
Step 5: Create top level class to interact with the database
This will be the class that read & write your database file. Also there is a sample query to print out the value in the database.
Step 6: Test running
Test the code by running the following lines of codes.
Hit the run button and cheer!
@Daniele B 2018-08-14 10:59:46
when should the initialization be done? What is the strategy you suggest?
@Homayoun Behzadian 2017-07-12 06:27:28
I'm using ORMLite and below code worked for me
Please note, The code extracts database file from a zip file in assets
@Ilya Gazman 2016-07-10 05:41:59
I wrote a library to simplify this process.
It will create a dataBase from
assets/databases/myDb.db
file. In addition you will get all those functionality:Clone it from github.
@Alex Jolig 2016-01-21 08:01:07
I guess the best and the newest way till today is using
SQLiteAssetHelper
class.This tutorial guides you perfectly through Importing and Using External Database in Android
In addition to this article you can download
SQLiteAssetHelper
here@LuisE 2016-10-29 16:10:51
I can totally skip step 9 right? Is there any backdraw if I just create the getInstance() method in the AssetHelper object?, and a little bit off-topic, what is the advantage singleton pattern offers in the DatabaseAccess class?
@Mateusz Konieczny 2018-02-13 17:38:21
Note that it is using github.com/jgilfelt/android-sqlite-asset-helper library with last update in 2014.
@Jaco 2009-12-21 10:12:08
If the required data is not too large (limits I don´t know, would depend on a lot of things), you might also download the data (in XML, JSON, whatever) from a website/webapp. AFter receiving, execute the SQL statements using the received data creating your tables and inserting the data.
If your mobile app contains lots of data, it might be easier later on to update the data in the installed apps with more accurate data or changes.
@Vaishak Nair 2015-03-26 14:56:20
My solution neither uses any third-party library nor forces you to call custom methods on
SQLiteOpenHelper
subclass to initialize the database on creation. It also takes care of database upgrades as well. All that needs to be done is to subclassSQLiteOpenHelper
.Prerequisite:
android_metadata
with an attributelocale
having the valueen_US
in addition to the tables unique to your app.Subclassing
SQLiteOpenHelper
:SQLiteOpenHelper
.private
method within theSQLiteOpenHelper
subclass. This method contains the logic to copy database contents from the database file in the 'assets' folder to the database created in the application package context.onCreate
,onUpgrade
andonOpen
methods ofSQLiteOpenHelper
.Enough said. Here goes the
SQLiteOpenHelper
subclass:Finally, to get a database connection, just call
getReadableDatabase()
orgetWritableDatabase()
on theSQLiteOpenHelper
subclass and it will take care of creating a db, copying db contents from the specified file in the 'assets' folder, if the database does not exist.In short, you can use the
SQLiteOpenHelper
subclass to access the db shipped in the assets folder just as you would use for a database that is initialized using SQL queries in theonCreate()
method.@goetzc 2016-09-24 21:31:29
This is the most elegant solution, using standard Androids APIs without the need of external libraries. As a note, I didn't included the android_metadata table and it works, newer Android versions might add it automatically.
@gorefest 2014-10-28 06:41:37
Android already provides a version-aware approach of database management. This approach has been leveraged in the BARACUS framework for Android applications.
It enables you to manage the database along the entire version lifecycle of an app, beeing able to update the sqlite database from any prior version to the current one.
Also, it allows you to run hot-backups and hot-recovery of the SQLite.
I am not 100% sure, but a hot-recovery for a specific device may enable you to ship a prepared database in your app. But I am not sure about the database binary format which might be specific to certain devices, vendors or device generations.
Since the stuff is Apache License 2, feel free to reuse any part of the code, which can be found on github
EDIT :
If you only want to ship data, you might consider instantiating and persisting POJOs at the applications first start. BARACUS got a built-in support to this (Built-in key value store for configuration infos, e.g. "APP_FIRST_RUN" plus a after-context-bootstrap hook in order to run post-launch operations on the context). This enables you to have tight coupled data shipped with your app; in most cases this fitted to my use cases.
@Hiep 2012-08-12 20:28:39
Shipping the database inside the apk and then copying it to
/data/data/...
will double the size of the database (1 in apk, 1 indata/data/...
), and will increase the apk size (of course). So your database should not be too big.@Suragch 2015-04-09 02:32:20
It does increase the apk size somewhat but it doesn't double it. When it is in assets it is compressed and so is a lot smaller. After copying it to the database folder it gets uncompressed.
@afsane 2014-02-14 09:03:58
Finally I did it!! I have used this link help Using your own SQLite database in Android applications, but had to change it a little bit.
If you have many packages you should put the master package name here:
private static String DB_PATH = "data/data/masterPakageName/databases";
I changed the method which copies the database from local folder to emulator folder! It had some problem when that folder didn't exist. So first of all, it should check the path and if it's not there, it should create the folder.
In the previous code, the
copyDatabase
method was never called when the database didn't exist and thecheckDataBase
method caused exception. so I changed the code a little bit.If your database does not have a file extension, don't use the file name with one.
it works nice for me , i hope it whould be usefull for u too
@Erum 2014-03-04 17:09:07
can u please let me know how to upgrade db if i want to replace old database with new one how can i delete old db
@afsane 2014-03-10 06:35:31
I haven`t need to do this untill now ,but if new app has been installed , the new db replace too
@Erum 2014-03-10 10:31:05
how to delete old database because i am adding new db in assets folder then how will i delete old db from the specified folder otherwise it will bring the contents of old db
@afsane 2014-03-11 10:50:26
I hope this would be usefull stackoverflow.com/questions/9109438/…
@Grinner 2014-09-09 13:37:22
Perfect, thank you! Just one comment, throwing the exception on checking the database causes the app to close, since the DB won't be there in the beginning and the method does not continue after the exception is thrown. I simply commented out the throw new Error("database dose not exist"); and now everything works perfectly.
@Danny Remington - OMS 2011-01-26 21:22:02
There are two options for creating and updating databases.
One is to create a database externally, then place it in the assets folder of the project and then copy the entire database from there. This is much quicker if the database has a lot of tables and other components. Upgrades are triggered by changing the database version number in the res/values/strings.xml file. Upgrades would then be accomplished by creating a new database externally, replacing the old database in the assets folder with the new database, saving the old database in internal storage under another name, copying the new database from the assets folder into internal storage, transferring all of the data from the old database (that was renamed earlier) into the new database and finally deleting the old database. You can create a database originally by using the SQLite Manager FireFox plugin to execute your creation sql statements.
The other option is to create a database internally from a sql file. This is not as quick but the delay would probably be unnoticeable to the users if the database has only a few tables. Upgrades are triggered by changing the database version number in the res/values/strings.xml file. Upgrades would then be accomplished by processing an upgrade sql file. The data in the database will remain unchanged except when its container is removed, for example dropping a table.
The example below demonstrates how to use either method.
Here is a sample create_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice the comment about the table required by Android.)
Here is a sample update_database.sql file. It is to be placed in the assets folder of the project for the internal method or copied into the "Execute SQL' of SQLite Manager to create the database for the external method. (NOTE: Notice that all three types of SQL comments will be ignored by the sql parser that is included in this example.)
Here is an entry to add to the /res/values/strings.xml file for the database version number.
Here is an activity that accesses the database and then uses it. (Note: You might want to run the database code in a separate thread if it uses a lot of resources.)
Here is the database helper class where the database is created or updated if necessary. (NOTE: Android requires that you create a class that extends SQLiteOpenHelper in order to work with a Sqlite database.)
Here's the FileHelper class that contains methods for byte stream copying files and parsing sql files.
@Sam 2011-07-16 10:48:36
i used the above code to to upgrade my db "upgrade_database.sql contains insert statement. some of the values have semicoln like insert into table_a values ('ss','ddd','aaaa;aaa'); " when i run the i noticed above mention insert not getting esecute because of the semicoln in the values any ides how to fix this.
@Jack BeNimble 2011-09-09 13:00:07
There is a third option - copy the db from the web. I've done this and it goes fairly quickly for a 4 meg db. It also solves the issue with 2.3, for which the first solution (copy db) doesn't work.
@George Baker 2012-02-27 03:44:29
Danny And Austyn - Your solution was perfect. I was having trouble with my home brewed solution and stumbled on yours. It really hit the spot. Thanks for taking the time to provide it.
@PSIXO 2014-03-10 12:31:32
I much prefer this answer against the top voted and accepted one. It has all information in one place (no see this link parts) and mentioned some Android specifics that I had no idea existed(like CREATE TABLE "android_metadata"). Also examples are written in great detail which is a plus. It's almost a copy paste solution which is not always good but explanations between the code are great.
@Pankaj 2015-03-25 04:15:47
I am using same method but I am facing one problem.How we can copy all the existing data from old to new db file in more easy way.
@Dzseti 2015-08-30 17:49:16
Worked for me - just two things: (i) I changed
<item type="string" name="databaseVersion" format="integer">1</item>
to<integer name="databaseVersion">1</integer>
and the reference to this resource toR.integer.databaseVersion
(ii) forprivate static String DB_DIR = "/data/data/android.example/databases/";
I get the recommendation to avoid hardcoding the path, but am not proficient enough yet to do this - any help anyone?@Will 2009-02-04 23:32:28
Currently there is no way to precreate an SQLite database to ship with your apk. The best you can do is save the appropriate SQL as a resource and run them from your application. Yes, this leads to duplication of data (same information exists as a resrouce and as a database) but there is no other way right now. The only mitigating factor is the apk file is compressed. My experience is 908KB compresses to less than 268KB.
The thread below has the best discussion/solution I have found with good sample code.
http://groups.google.com/group/android-developers/msg/9f455ae93a1cf152
I stored my CREATE statement as a string resource to be read with Context.getString() and ran it with SQLiteDatabse.execSQL().
I stored the data for my inserts in res/raw/inserts.sql (I created the sql file, 7000+ lines). Using the technique from the link above I entered a loop, read the file line by line and concactenated the data onto "INSERT INTO tbl VALUE " and did another SQLiteDatabase.execSQL(). No sense in saving 7000 "INSERT INTO tbl VALUE "s when they can just be concactenated on.
It takes about twenty seconds on the emulator, I do not know how long this would take on a real phone, but it only happens once, when the user first starts the application.
@Tamas Czinege 2009-03-06 19:35:42
How about pulling the SQL script from the web on the first run? This way there is no need to duplicate data.
@Dzhuneyt 2014-02-12 17:00:18
Yes, but the device will need to be connected to the internet. That's a serious drawback in some apps.
@masfenix 2009-02-04 20:14:43
From what I've seen you should be be shipping a database that already has the tables setup and data. However if you want (and depending on the type of application you have) you can allow "upgrade database option". Then what you do is download the latest sqlite version, get the latest Insert/Create statements of a textfile hosted online, execute the statements and do a data transfer from the old db to the new one.
@Rory 2010-11-16 19:52:41
> From what I've seen you should be be shipping a database that already has the tables setup and data. Yes but how do you do this?