File-based embedded databases are usually considered bad performers. SSDs are erasing the restrictions that HDDs imposed: SSD performance is limited only by the SATA2 interface throughput. I decided to experiment with a file-based database Sqlite3 to see how it fits into a Java web application. I’ve chosen Sqlite3 b/c it is used in almost everything that we use: Firefox, iPhone, Android. In my previous post I tested the Sqlite’s performance in a web-application. Now I will go deeper into the development part. Sqlite is a very simple database. It doesn’t need any time to be spent on setup or configuration. That comes at a price of excessive simplicity: there are no data types in Sqlite, not even a Date. The major risk that you take when you use Sqlite from Java is its JDBC driver: sqlite3 jdbc. They don’t provide any support, and their mailing list is almost dead. However the driver itself seems to work just fine. Sqlite has no problem with multi-threaded queries, but it is limited when it comes down to updates. Just one single thread can write to the DB at a time. This restriction is easy to workaround: you just create a single synchronized method that is an entry point to all your update operations. The second restriction is the way you get the ID of a newly created record. There are no sequences in Sqlite, and auto-incremented columns’ usage is not very straight-through. To make a column auto-incremented is to create a column of type INTEGER and to make it a primary key. Then the only way to get the last inserted record’s ID is to issue a special query: select last_insert_rowid(). And this query needs to be issued right after the insert statement. I couldn’t find any other major issues with Sqlite3 and my web application yet. It works just fine. A JSP custom tag was created to fight the two problems. You just put the tag call on your JSP and it will take care of thread safety and get you the last inserted record’s ID. Software Development