How to create and install a pre-populated database

Copy space. New arrows painted on asphalt. Direction future.

Editor’s note: This blog post is a basic tutorial. The most up-to-date version is available in the wiki.

Using the Titanium.Database.install method allows you to install a database that is pre-populated and packaged with the application in the resources directory. When the Titanium.Database.install method is called, it will install that database, or if the database is already installed, it will simply open the database.

  • You can create a SQLite database in many ways – the easiest way might be to use the SQLite Manager plugin for Firefox located https://addons.mozilla.org/en-US/firefox/addon/5817.

This will allow you to create a new database, run SQL against it, and end up with a pre-populated database.

  • Place the resulting file in your resources folder and you will be able to use it with the install function:
var db = Titanium.Database.install('testdb.db','myDB');

This will install the testdb.db database as myDB.sql in the databases directory which is available using:

Ti.Filesystem.applicationSupportDirectory+'/database'.

From there you can now open that database using Ti.Database.open(‘myDB’);

Always remember to close your database and result set when you are done accessing them.

1 COMMENT

  1. Thanks for that.

    Its also important to remember that its an SQLITE database and not an SQL database as the former ofetn throws errors (eg with a CONSTRAINT an error will cause Titanium to fail to compile unless ON CONFLICT REPLACE or ON CONFLICT IGNORE is used when declaring the database).

    Also the Vacuum command using db.execute(‘VACUUM’) is necessary as this compacts the database especially if data is deleted from it as a bloated file can be a problem with the limited space on a mobile device.

  2. I started with this idea for my app, but in the end went for syncing a local sqlite database on the server and just downloading it ‘pre-packaged’ and full of data from the server. Much quicker than having an empty shell, using a web-service and then getting a device to fill the database with the payload..