Training: SQLite

A little intro.  The path of Android feels a lot like the path of web, rolled back about 10 years.  When the web first rolled up, databases were weird, clunky, proprietary, and expensive.  Most web apps would try to use files, or whatever, to avoid databases. Free/open source dbs started gaining ground, but even then, it took a long while for databases to gain ground.

These days, imagine making a web app without a database.  To be perfectly honest, I can’t imagine trying. Databases have become as necessary as the HTTP stack itself. (yeah, you could hit me with “NOSQL”, but I’ve got a long blog post coming about that too)

To be clear, this is not a post about how to implement basic SQLite on Android.  Plenty of info about that out in the interweb. This is (mostly) about the stuff you don’t get everywhere. What a real production app developer needs to know.

All of this assume you’re using the Android standard SQLite implementation. If not, god speed.

The Basics

SQLite databases are stored in the home directory of your app, under the “databases” directory.  If your app id is “com.myapp.heyo”, look in “/data/data/com.myapp.heyo/databases”.

The database file, like pretty much all of the files in your app directory are private to the app user.  Users are basic linux users, with basic linux user rights.  These file are GENERALLY safe from reading.  However, if your phone is rooted, or if there has been some type of security exploit, these files are fair game.  Depending on the security requirements of your app, keep this in mind.  There are projects to disk encrypt your databases, but are WAY beyond our scope here.  Summary, most data is OK, but if this is a sensitive app, use caution in what is stored.

You can get a reference to the database file directly through the android Context.  Method: getDatabasePath(String: name).  Supply the database name, and you’ll get the File reference.

Generally, create and manage your database through SQLiteOpenHelper.  Don’t get too fancy.  Use the version number, onCreate and onUpdate to manage the DB through SQL DDL commands.  There are APIs that try to wrap this in some way, but life is short. SQL won’t kill you.

If you want to start with a seeded database, copy the file from assets (or whatever).  Make sure you do this before creating a SQLiteOpenHelper instance.

SQLiteOpenHelper

SQLiteOpenHelper is your friend.  Once again, don’t get too fancy.  Don’t try to use management APIs that attempt to hide the “complexity”, unless you understand what’s happening under the hood.

Very little official documentation has been created around how you should manage SQLite.  Here, in a nutshell, is best practice.

1. Create 1, and exactly 1, SQLiteOpenHelper instance for one database. You can keep this in a static variable, or in a custom Application instance. I cannot stress this enough.
2. Do not worry about “closing” the database “connection”. These are not network connections. SQLite is a local database, and a “connection” is just a file handle. File handles will close themselves when the process ends.
3. You (basically) cannot corrupt a SQLite file programmatically. This can only happen if there’s a problem with your storage, or if SQLite itself has a bug. Either way, there is nothing you could have done to prevent it.

You want one connection because the “connection” is a file handle. While you can’t corrupt the db, multiple threads trying to write will clobber each other, and if you call “insert” rather than “insertOrThrow”, you won’t even get an exception. See here (by yours truly).

Concurrent access is managed by the SQLiteDatabase class.  If you look at the code, you’ll see java synchronization wrapping the calls. If multiple threads are calling at the same time (not literally, but in a multithreaded OS kind of way), the SQLiteDatabase will manage things for you.

And full circle, SQLiteOpenHelper maintains exactly one instance of SQLiteDatabase. Again, its your friend. Let it help you.

Little known fact, although you can request a readable db connection, in all but the rarest of circumstances, you’ll ALWAYS get a writeable db connection.  SQLiteOpenHelper just returns the main “connection”.

Why is it OK to simply not close your connection?  On reads, it obviously doesn’t matter.  On writes, the changes get flushed to disk. The whole process is very resilient. If db’s could easily be corrupted by crashing apps, Android would be a mess. They have quietly led you to the pit of success. Just go with it.

Multiple Writes

If you think you might write more than one record (insert or update, and delete?), do this in a “transaction”.  I put “transaction” in quotes because it doesn’t really mean the same thing that it would in a server database context. I am admittedly light in this area, but I suspect SQLite simply keeps “transaction” writes in memory, and flushes them out on commit.

Flash memory is VERY SLOW. VERY SLOW. Again, VERY SLOW. I’ll have to dig it up, but I have example code that shows how slow this is.  Write 50 records without a transaction, and it’ll take a long time.  Write 50 with a transaction, and it’ll take about as long as 1.  My theory is that in a transaction, all edits are done in memory, then written out.  Without a transaction, the app must load the db, edit, write and flush, load, edit, write and flush. Rinse and repeat.

In summary, multiple writes go in a transaction (single writes might as well also, but not critical).

Reads, however, do not benefit so much from a transaction.

Also, from a logic perspective, if you don’t want partial writes, a transaction is the smart way to go 😉

However! If your logic does a lot of stuff in processing, do not do everything in a transaction. Your other calls will wait. If possible, do your logic and changes in memory, then start a transaction, write, and commit.

Don’t over optimize

This is a general rule, not just for SQLite.  If you follow the rules.  If you have one connection, and put multiple writes in a transaction, and still have performance issues, think hard about what you’re doing. I would suspect something else is wrong, or your design is a mess.

Vacuum

Disclaimer, I’ve not tested this yet. When you delete rows, the db will not reclaim space, so you may need to vacuum.

Space

DB’s are not magic. They take up space in your app’s home directory. If you create piles of data, you will take up space. On newer phones, this is not a huge deal.  Older phones, however, are often starved for free space. Keep this in mind. I saw a twitter client with 25 megs or more of DB usage. As a developer, some of your most valuable skills are situational awareness and common sense. Use them.

“insert” and “insertOrThrow”

If I had a wall of shame for Android API design, this would be at the top. As a general rule, in all of app-dom, DO NOT EAT EXCEPTIONS. If a tree falls in the wood, you should know about it.  The raw “insert” method will only give you some LOGCAT. If you’re lucky, you might see it. More likely, your user will wonder why things are missing.

ORMLite

Way back when, I did the initial port of ORMLite to Android.  Gray Watson has been diligently running this for a few years now, and should be seriously commended. All please send him a thank you message if you use it.

ORMLite is exactly as it sounds. A light ORM framework. Originally, it was for JDBC clients only. After much OOP abstraction, the Android mappings were added. This is not a full blown ORM tool like Hibernate: by design. You would not want to be running abstracted management environments, pulling full object graphs, etc. Plus, most of these tools use bytecode instrumentation, which isn’t even possible in the Dalvik runtime.

ORMLite maps your tables to classes, and provides relatively basic relational mapping. That’s it. While limited in scope, you aren’t relegated to tedious (and error prone) methods of manually doing this. Please use.

At the same time, some heavy operations should use direct SQL. I would suggest coding in ORMLite, then picking the small handful of operations that would seriously benefit from joins and direct SQL.  For everything else, “good enough” is exactly that. Good enough.

Content Providers

Are you sharing specific data to outside applications with a published, stable protocol? Literally, do you expect external apps that you wrote, or apps that you don’t know, to read and interact with your data? If not, and you’re using a Content Provider, you have wasted time and effort. Period.

Again. If you are NOT sharing data outside of your Linux process, a Content Provider is a waste of time. If you don’t know the answer to that question, you are not ready to develop serious apps, but to give you a heads up, you’re not.

I will be happy to debate this. Get in touch.

Summary

Use 1 SQLiteOpenHelper. It can be static because static variables only go away when the VM dies.

If you are doing multiple writes, use a transaction.

If you are doing lots of deletes, investigate vacuum.

Content Providers are crap (unless you’re sharing outside of your app).

Don’t Panic. 99% of apps are low volume, and as long as you’re not doing database ops in the main thread, none of this matters (except the single connection 😉