Getting Down to the Nitty Gritty of Android Development

SQLite is at the heart of Android’s database support. This database was developed with embedded environments in mind – and is used not only by Android but also by Apple’s iOS and Blackberry’s system as well as lots of other systems with low memory footprint and comparatively little CPU horsepower.

Why SQLite in the first place?

Of course there is a reason why SQLite is so dominant in the embedded and also the mobile world. The main reasons are

Low memory consumption

Ease of use

Free availability

SQLite in Android consumes very little memory

While SQLite’s memory footprint starts at about 50 kilobyte it’s remains low even for bigger projects with more complex data structures (at about a few hundred kilobytes). Keep in mind: In the mobile world the memory per process as well as total usage of memory is limited compared to desktop systems. Gladly SQLite should not add too much burden to the memory consumption of your app.

SQLite is easy to use

SQLite is a serverless system. I will detail what this means in the next section, but it makes handling of the database that much easier. No need for configuration files or complicated commands. You definitely do do not want these on mobile systems. Those systems must run out of the box without forcing the user to manually configure anything or forcing the developers to consider additional constraints.

SQLite’s source code is released under the public domain

SQLite has a huge commercial backing by the likes of Google, Adobe, Mozilla or Bloomberg. And it is used in many, many products and open source projects. The project is maintained actively so one can expect further imrpovements as well as optimizations in the future. Android for example uses ever newer versions in its SDKs to make use of these improvements.

I will delve into each of these points a bit deeper – and add another one that’s only relevant if you want to support older Adroid versions.

SQLite is serverless

There is no SQLite process running at all. You use SQLite more like a library which helps you to access the database files. You do not need to configure the database in any way. No port configuration, no adding of users, no managing of access levels, no tablespace setup and what not. You simply create the database files when you need it. I will cover how to create a database in the next part of this tutorial series.

All data is stored in one single database file

SQLite uses one file to store all the contents of your database. This file contains the main data, as well as indices, triggers and any meta data needed by SQLite itself. Newer versions add a journal file which is used during transactions.

SQLite offers fewer datatypes

The following table shows all types supported by SQLite. If you use other types (like varchar) in your CREATE TABLE statement SQLite maps them as closely as possible to any of these types.

SQLite datatypes

Type

Meaning

NULL

The null value

INTEGER

Any number which is no floating point number

REAL

Floating-point numbers (8-Byte IEEE 754 – i.e. double precision)

TEXT

Any String and also single characters (UTF-8, UTF-16BE or UTF-16LE)

BLOB

A binary blob of data

The biggest problem here is the missing datetime type. The best thing to do is to store dates as Strings in the ISO 8601 format. The string to represent the 28th of March 2013 (the day of publishing this post) would be “2013-03-28”. Together with the publishing time it would look like this: “2013-03-27T07:58”. Stored this way SQLite offers some date/time functions to add days, change to the start of the month and things like that. Note: In contrast to ISO 8601 SQLite doesn’t offer any timezone support.

Also missing is a boolean type. Booleans have to be represented as numbers (with 0 being false and 1 being true).

Although a blob type is listed in the table above, you shouldn’t use it on Android. If you need to store binary data (e.g. media-files) store them on the file system and simply put the filename in the database. More on SQLite types can be found on the SQLite project page.

SQLite doesn’t use static typing

Any type information in SQLite is dependent on the value inserted, not on the data definition of the CREATE TABLE statement. Let’s say you create a column as an INTEGER column. Then you might still end up with TEXT entries in this column. That’s perfectly legal in SQLite – but to my knowledge in no other relational database management system.

This reliance on the value is called manifest typing – something in between static and dynamic typing. In Mike Owens’ book on SQLite you can find a very good and much more detailed explanation of SQLite’s typing.

SQLite has no fixed column length

If you look at the table above you see that there is only a definition for text, but not for varchar(xyz), where you can limit the column to an arbitrary length. In SQLite any TEXT value is simply as long as it is. SQLite adds no restrictions. Which might be pretty bad. To enforce a restriction, you have to do this in your code. SQLite won’t help you. On the other hand you will not get into any trouble if Strings get too long or numbers too large. Well, you will not get any SQLExceptions – though it might break your code in other ways or destroy your UI!

SQLite’s database files are cross-platform

You can take a file from a device put it on your laptop and start using it as if you created it on your laptop from the outset.

It might come handy to pull the database file from the device (or your emulator) and run queries from within your development machine. Especially if you want to use tools with a graphical user interface. One of the best know is the SQLite Manager extension for Firefox which you might prefer to sqlite3 in some cases (see screenshot).

SQLite Manager showing the results of an SELECT statetment

Also you sometimes might want to prepare the database on your development machine and put a database onto your device which contains the needed set of data like a very large dataset to test for performance or a defined database for starting automated tests.

Thanks to SQLite’s cross platform file format it is also possible to deliver a prefilled database with your app to your users. Jeff Gilfelt has written a library to help you with it. You can find his Android SQLite Asset helper library on github.

SQLite offers a special kind of table for fast text searches

To help developers create fast text searches SQLite offers also a special kind of database table. The so called FTS3/FTS4 tables. FTS stands for “full text search”. You have to create special tables for it to work and use slightly different SELECT statements (and rarely special INSERT statements) to use them efficiently. But if you do so, you gain tremendous performance improvements for text only search. I will cover FTS in an extra blog post.

Older versions of SQLite do not support referential integrity

The version of SQLite integrated into older versions of Android (3.4 in the early days, later on 3.5.9) doesn’t support referential integrity. This changed with Android 2.2. Thus this problem should fade away pretty soon and is only relevant if you want to support API level 7 or lower. In this case this limitation forces you to take special care when using foreign keys within tables. Since databases on Android are usually way less complex than those of enterprise projects this might not be as bad a problem as it sounds. But still, you have to be careful. Of course being careful is never wrong 🙂

For more information on how SQLite differs from other database go to the SQLite website.

Of course what is not different from other SQL Database systems is the use of SQL to create tables, and query and update them. And of course SQLite is relational – that is, you deal with tables which store your data and the results of your queries also take the form of tables.

Where are those database files on Android?

As I have mentioned, a database in SQLite is more or less simply a file accessed through the SQLite API. In Android these files are by default stored within the

/data/data/&lt;<em>package-name</em>&gt;/databases

directory. Thus if your package is called com.grokkingandroid.android and your database is called “sample.db” the actual file would be /data/data/com.grokkingandroid.android/databases/sample.db.

Keep security in mind

As usual in Android the access rights of the database file determine who can use your database. If you follow the standard way presented in the following posts of this series, your database file will be located within the private directory of your app. This means that your app owns the database file and no one else can access it. Even using the other less common ways to create the database you can only grant access to the file. Thus others can access all of your database or nothing. There is no middle ground.

Still: You should never rely on data being safe from prying eyes in the database. Any sensitive data should be encrypted. Very sensitive data should not be stored on the device at all. Keep in mind that if the device gets lost, any misbehaving finder of the device can gain access to the database file as well as to your app. On a rooted device all files can be read. Apps like SQLite Editor make it easy to read even sensitive data – if they are not encrypted:

SQLite Editor showing the two databases of the contacts app

In cases where data privacy is of utmost importance, you have to revert to secured services or force the user to enter a secret every time before encrypting and storing the data or reading and decrypting them respectively.

Android differs from the standard Java way

Apart from SQLite’s own peculiarities there is also the way Android deals with this database. First of all SQLite is an integral part of Android. Every app developer can rely on SQLite being present on an Android system. Though which version of SQLite is dependent of the SDK which the device uses. – which of course is a good thing, since SQLite is developed actively and future Android versions should make use of those improvements.

The biggest Android-speciality of course is how Android treats the database. Android doesn’t use JDBC. And so also no JDBC driver for SQLite. This means that you are stuck with using SQLite the Android way or using another database which you have to include in the download of your app (though I see no need for any other database). It also means that you have to learn a new way to deal with databases. Any prior JDBC-knowledge is of no use in the Android world. The rest of this series will be about the special API Android provides to deal with SQLite in your JAVA-code.

Lessons learned

You have seen why Google chose SQLite as the underlying database for Android’s apps. It has many advantages, most of all it’s low memory footprint and it’s ease of use.

Furthermore you learned about how SQLite differs from most other relational database systems and what implications this might have.

With this knowledge you are well prepared to start using SQLite. In the next installments of this series I’m going to show you how to create the database, how to insert, update and delete data and how to query those records. I’m also going to post about SQLite’s full text searches feature and how to use it.

Disclaimer: This post contains a link with my Amazon.com referral ID. For each sale this provides me with a small commission. Thank you for your support!

Wolfram Rittmeyer lives in Germany and has been developing with Java for many years.

In recent years he shifted his attention to Android and blogs about anything interesting that came up while developing for Android.

I haven’t yet planned on addressing encryption. To be on the safe side, you have to use a server-side component – or another factor outside of the device. Otherwise you are only obscuring sensitive data. That’s because people can get access to encryption method and – much worse – to the key by decompiling your app.

Depending on the type of data obscuring them might be enough – but for really sensitive data, that’s not sufficient. That’s nothing new and not specific to apps on handsets/tablets. It affects laptops with desktop only apps as well.

Great intorduction to SQLite on Android – all at one place. Trust me – this is useful not only for beginners but for more exprienced Android developers as well!

Regarding the location of the SQLite database file – how does multi-user support on JB impact the location? I would imagine there is going to be a separate .db file for *each user* on the system? The /data/data path is probably pre-fixed (or suffixed) with a user-specific path segment.

Kiran, that’s one of the weaknesses of multi-user support as it stands now. Apps have just one single database. Really weird. Not quite as bad as it sounds, because you probably do not grant access to total strangers, but for many apps, this is a serious draw. For example for all apps related to gifts, this is a terrible weakness – because now your partner might see about your plans for her/him. I know, because we are working on one on an on and off basis 🙂

I very much hope that Google improves upon its multi-user support! There are some other things that are also not right, yet. This, though, is probably the most important one.

I just tried it here with a Nexus 7, and none of the apps I tried seem to be looking at the same databases when running them from different profiles.
I tried with my own app which I know isn’t doing anything special with the databases, and it works perfectly.

Although it does seem weird that when I look at the file system, there doesn’t seem to be separate app directories for the second user.
Those files must be stored somewhere other than under /data/data

just place database file into a folder on the SDCard. For example
/sdcard/somedb/db.sqlite
May be you need MODE_WORLD_WRITEABLE instead of MODE_PRIVATE
myActivity.openOrCreateDatabase(“/sdcard/somedb/db.sqlite”,Context.MODE_PRIVATE, null);

Regarding the storage of dates, I prefer to store them as longs (the INTEGER type in SQLite can store longs) under the format: [year][month][day][hour][minute][second]
For example, as I type this: 20130328101734

The advantage of this system is that if you can also use this column as an efficient index (as long as you can guarantee that your all will never have duplicate times), and you can also run quick queries on top of dates.
For example, to get every entry in the month of March 2013 you’d select all where date > 20130300000000 and date < 20130400000000
(That's pseudo-SQL. My SQL is rusty and I'm too lazy to look it up).

The disadvantage is that you'll need to convert the number to/from your date object manually, but that's pretty easy.

Absolutely. That’s why I think that really critical data do not belong on the device. I’ve said something along this line in my reply to Vipul’s comment. Encryption with an ondevice or in-app key is only obscuring – unless you add another factor (online or via user input) to it.

Have you come across the SQLCipher project? It’s an extension/replacement to SQLite which offers full database encryption. I like that it handles the encryption for you transparently would polluting application code.

Haven’t used it yet. I agree that it probably helps to keep some of the code cleaner, but unless most of the content is highly sensitive, I’m not so sure it’s very efficient. Most often only some parts of the data might be critical. In this case encrypting and decrypting every db-access might cause unnecessary load.

First of all though the main problem remains: It’s only secure if the key is not stored on the device (and as such also not part of your app’s code).

security isn’t black or white like that. Using SQLCipher with the password kept in plaintext on the device is still “more secure” than having everything unencrypted. Programmatically generating the password adds abstraction, again making it “more secure”. Having the user enter the password so its never “at rest” makes it even “more secure”. But nothing is ever simply secure.

i have a workshop with an android project called portal portal i want make another android project called portalAdmin…is it possible for portalAdmin to access the database created by portal and also change(add/delete/update) information that is inside if possible how do i do it?

One would be to make the database world-readable/writable. I think that’s a bad idea.

The other would be to use a content provider. You would have to export your provider. In this case – depending on your need – you can still limit access to it by setting appropriate permissions (e.g. use a protectionLevel of signature to limit access to your apps).

I don’t know why you use ORMLite. While you certainly can do so, you can also use SQLite directly. So if you are working on a project that happens to use ORMLite, ask your colleagues why the chose ORMLite.

ORMLite is one of many libraries to help you use proper objects in your code. I like ORMLite, since it’s very powerful – but also sometimes overly complex.

There are, of course many alternatives. Another library that I like to use for a more object-oriented handling of persistent data is cupboard.

For those looking how to hack using SQLite CLI (Command Line Interface) with adb shell in any Android device:

How to build the sqlite3 binary and library yourself.

I have put together some build scripts to compile SQLite for Android Native Code using the Android NDK. It builds the SQLite CLI in two versions: Statically and Dynamically Linked, as well as it’s Static and Shared Libraries. You may get the scripts from my GitHub and build the binaries yourself:

great stuff. I have a concern and needs your advice. I am developing an app. I want to know every user that downloads the app and uses it. So i will want that my database will keep info about the user. which database do i use. SQLite or another needs your recommendation

What you are talking about is a central database that collects data from your app. SQLite surely is not the best choice for that. Depending on what else you need, you fist should decide what kind of DB you need (an SQL database is just one option), should expose only the relevant part via some kind of web interface and upload the data to that endpoint.

SQLite is great for a database local to an app on the device. But not for collecting all kind of information about all users of your app.