SQLite in Android

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.

SQLite is not like any other database

Though SQLite offers quite an impressive feature set given its size, it differs in many aspects from a conventional database system:

  • SQLite is serverless
  • SQLite stores data in one database file
  • SQLite offers only a few data types
  • SQLite uses manifest typing instead of static types
  • SQLite has no fixed column length
  • SQLite uses cross-platform database files

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

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 hHelper 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/<package-name>/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

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!

Share this article:

You can leave a response, or trackback from your own site.

27 Responses to “SQLite in Android”

  1. Vipul says:

    Great artical for Novice android developers.Would really appreciate your post on
    Storing data in encrypted formats inside database or SharedPreference.

    • 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.

  2. Kiran Rao says:

    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.

      • Kiran Rao says:

        Uh oh! This is going to require some digesting. I can think of ways to work around this limitation – but really, it should be taken care by the multi-user support provided by the platform.

      • Daniel Cachapa says:

        Are you positive about the database sharing between users?

        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

        • You are absolutrely correct, Daniel – and I was totally mistaken :-)

          Android does provide separate databases. The new folders are /data/user/userid and so on. The first user is the user “0″ and the folder of this user (/data/user/0) is a symlink to /data/data.

    • SergySurkv says:

      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);

  3. Daniel Cachapa says:

    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.

  4. Lars Vogel says:

    The problem with encryption is IMHO that you either have to store the key somewhere or you have to asked the user for an entry. Both approaches are bad.

    • 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.

  5. chris says:

    As a follow up one shall have a look at ORMLite which is ORM lib wrapping around SQLite which is also compatible with android. Using it in my current project and I’m liking it so far.

  6. Petros says:

    Great article, it has definitely answered some burning questions I’ve had.Looking forward to your next installment

  7. Can you expand on your “Don’t use blobs” maxim? Is it from http://www.sqlite.org/intern-v-extern-blob.html ?

  8. Linden says:

    pretty sure it’s not always safe to copy a database file to different devices as the implementations can vary at a low level between manufacturers.

  9. A.K. says:

    “SQLite is easy to use” > “You definitely do do not want these on mobile systems.”

    Awesome article though. I’m familiar with how things are done on Android, so this was a nice overview/review!

  10. James says:

    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).

      • Linden says:

        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.

  11. Here is a library you might find interesting. It is intended for Android Apps to communicate with a DB through only URIs: https://github.com/novoda/SQLiteProvider#readme

  12. Scott says:

    Thanks for doing this series. I’m a novice Android developer, and SQLite and Content Providers are my current obstacle.

    I know enough about SQL, but not so much when it comes to performing CRUD operations within the Android classes.

  13. SergySurkv says:

    Hi. There aren’t many articles about non-gaming development for Android. Nice article.

    Could you review my CRUD-application example? I think it helps to understand what Android is for enterprise/office.
    Google Play link:
    https://play.google.com/store/apps/details?id=northwind.ui.example
    It available with source code.

  14. pavan says:

    Hi , thats really very good article about sqlite in android and i also found one more article at ( http://www.pavanh.com/2013/11/sqlite-database-with-cursoradapter.html ) , which is also too awesome for sqlite in android very well explained

  15. Amit says:

    thanx it helped a lot…

Leave a Reply

You can also subscribe without commenting.

Subscribe to RSS Feed My G+-Profile Follow me on Twitter!