Grokking Android

Getting Down to the Nitty Gritty of Android Development

SQLite in Android

By 37 Comments

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

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:

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


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

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

You can find him on Google+ and Twitter.

37 thoughts on “SQLite in Android”

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

    1. Wolfram Rittmeyer

      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.

      1. Any thoughts about using SQLCipher to encrypt the entire db? The biggest drawback I see is that you still have to be careful with how you store the passphrase.

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

    1. Wolfram Rittmeyer

      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.

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

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

        1. Wolfram Rittmeyer

          You are absolutrely correct, Daniel – and I was totally mistaken 🙂

          Android does provide separate databases. The new folders are /data/user/<em>userid</em> 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.

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

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

    1. I just use milliseconds since epoch, since that’s what typically backs date/time anyway.

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

    1. Wolfram Rittmeyer

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

    1. Wolfram Rittmeyer

      I plan on doing something about object relational mapper tools for Android. But I’m not sure when this will be ready.

  6. 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 ?

  8. 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. “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. 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.

    1. Wolfram Rittmeyer

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

      1. 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:

  12. 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. 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:
    It available with source code.

  14. Hi , thats really very good article about sqlite in android and i also found one more article at ( ) , which is also too awesome for sqlite in android very well explained

  15. thanx it helped a lot…

  16. 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?

    1. Wolfram Rittmeyer

      There are two ways to do that.

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

      1. Thanks bruh! I’ll try the second one. ..

  17. Hello, i am novice for android ,so it is good for me to understand the SQLite.
    But i want to know, why we use ORMLite instead of SQLite in andriod?
    Is there any specific reasons?
    Pls elaborate..

    1. Wolfram Rittmeyer

      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.

  18. Rogério Schneider

    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:

    Hope this will be useful for someone.

    1. nice

  19. GETMETHOD():

    public class MainActivity extends AppCompatActivity {

    ListView listView;
    Webservice web;
    protected void onCreate(Bundle savedInstanceState) {
    listView = (ListView) findViewById(;
    StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
    web = new Webservice();
    ArrayList al = web.doInBackground();
    Custom cus = new Custom(MainActivity.this, al);

    class Custom extends BaseAdapter {

    private final MainActivity ctx;
    private final ArrayList aa;

    public Custom(MainActivity mainActivity, ArrayList al) {
    this.ctx = mainActivity;
    this.aa = al;

    public int getCount() {
    return aa.size();

    public Object getItem(int position) {
    return null;

    public long getItemId(int position) {
    return 0;

    public View getView(int position, View convertView, ViewGroup parent) {
    LayoutInflater inflater = (LayoutInflater) ctx.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
    convertView = inflater.inflate(R.layout.cus, parent, false);
    TextView textView, textView1;
    ImageView imageView;
    textView = (TextView) convertView.findViewById(;
    textView1 = (TextView) convertView.findViewById(;
    imageView = (ImageView) convertView.findViewById(;
    HashMap hm = new HashMap();
    hm = aa.get(position);
    textView.setText(“” + hm.get(“cou_id”));
    textView1.setText(“” + hm.get(“cou_name”));
    imageView.setImageBitmap((Bitmap) hm.get(“cou_image”));
    return convertView;

    class Webservice extends AsyncTask<String, String, ArrayList> {

    ArrayList list = new ArrayList();

    protected void onPreExecute() {

    protected ArrayList doInBackground(String… params) {
    HttpClient client = new DefaultHttpClient();
    HttpGet get = new HttpGet(

    try {
    HttpResponse response = client.execute(get);
    HttpEntity entries = response.getEntity();
    InputStream inputstream = entries.getContent();
    String datas = getStringFromInputStream(inputstream);
    Log.d(“abc”, datas);
    JSONObject jsonObject = new JSONObject(datas);
    JSONArray array = jsonObject.getJSONArray(“country_data”);
    for (int i = 0; i <= array.length(); i++) {
    JSONObject object = array.getJSONObject(i);
    HashMap hm = new HashMap();
    String id = object.getString("cou_id");
    Log.d("id", id);
    hm.put("cou_id", id);
    String name = object.getString("cou_name");
    Log.d("name", name);
    hm.put("cou_name", name);
    String image = object.getString("cou_image");
    hm.put("cou_image", getBitmapFromURL(image));
    Log.d("img", image);
    } catch (ClientProtocolException e) {
    } catch (IOException e) {
    } catch (JSONException e) {
    return list;

    protected void onProgressUpdate(String… values) {

    protected void onPostExecute(ArrayList hashMaps) {


    private Object getBitmapFromURL(String image) {
    try {
    URL url = new URL(image);
    HttpURLConnection connection = (HttpURLConnection) url
    InputStream input = connection.getInputStream();
    Bitmap myBitmap = BitmapFactory.decodeStream(input);
    return myBitmap;
    } catch (IOException e) {
    return null;

    private String getStringFromInputStream(InputStream inputstream) {
    BufferedReader br = null;
    StringBuilder sb = new StringBuilder();

    String line;
    try {

    br = new BufferedReader(new InputStreamReader(inputstream));
    while ((line = br.readLine()) != null) {

    } catch (IOException e) {
    } finally {
    if (br != null) {
    try {
    } catch (IOException e) {

    return sb.toString();


    compile sdkversion 22
    buildtoolsversion 22.0.1
    targerversion 22

    network state

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

    1. Wolfram Rittmeyer

      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.

Leave a Reply

Your email address will not be published. Required fields are marked *