SQLite Tips and Tricks for Mobile Developers

saqlite_tips

Recently I have caught myself dealing a LOT with the low-level SQLite engine, trying to squeeze the most out of this amazing and flexible database provider. I have learnt a couple of things that I did not know, and I would love to share some of it with the mobile developer community!

First of all, the thing we all want out of our apps and database access, performance.

1. Performance

Speed in CRUD operations is what we expect out of database engines, SQLite has a few easy to configure tweaks that you can set up that will bump it up as you have never seen:

Temporary files – a.k.a. (Journals)

SQLite is basically a file, and like any other file if you just inconsequently write things to it, it will all be written straight into DISK which is terribly slow, even if you wrap your SQL statements a.k.a. Insert / Update / Delete in transactions, this is the problem Journals were meant to solve!

Journals are temporary files that SQLite engine makes use of, there are 9 different types and you can read all about them here, but the one we are looking for is the Write-Ahead Logging (WAL). In a nutshell, the way WAL journal works is that all the transactions you execute are appended to this “In Memory” temp file, and when you close the current connection with the database or the “.wal” file reaches 1000 pages (4mb), all info is written to the SQLite file by the SQLite Engine, and you don;t have to worry about it..

You can turn this puppy on as simple as executing the following query against your SQLite connection:

PRAGMA journal_mode=WAL;

If you use SQLite-Net Frank Krueger is already making use of it, so you don’t have to worry.

In Memory Data Base

Another option for you is, instead of using the journal, to actually bring the entire database file to memory, all you need to do is open the connection to the database with the following connection string file::memory:” and voilá!

Loading an existing Data Base from disk to memory can be tricky though, here are the steps you would need to follow.

  1. Create an in-memory Data Base.
  2. Create a connection to your on-disk database.
  3. Backup your on-disk database to the in-memory database.
  4. Use the in-memory database as you please.
  5. When you are done with it, save it back to disk (SQLite backup engine).

As you can see, there are a lot of steps to it, and even though it is faster than setting the SQLite file journal type to WAL, in my opinion, the gains do not justify the trouble of loading/saving the file from disk to memory.

Transactions

SQLite is a transactional database! So you want to make sure you are using Begin Transaction + Commit Transaction || Rollback Transactions,  executing your Inserts/Updates/Deletes with transactions will deliver you amazing results!

One Insert Statement

In case you are Inserting a lot of info into one table, remember to have one single insert statement, instead of executing many inserts queries, that can give you a few more valuable milliseconds of performance.

No:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Allen', 25, 'Texas', 15000.00 );

Yes:

INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY)
VALUES 
(1, 'Paul', 32, 'California', 20000.00 ),
(2, 'Allen', 25, 'Texas', 15000.00 );

2. Pragma Queries and other Tricks

Pragma gives you the power to modify SQLite operation as we have seen with Journals, but also lets you query SQLite for internal data! One thing to be aware of is that PRAGMA queries do not throw exceptions, so if you have a typo or is using the wrong syntax, the query is just ignored by SQLite engine.

Get table info

This is a great tip, in case you need to retrieve information from the tables you have in your SQLite database, execute the command:

PRAGMA table_info(TABLE_NAME)

It will give you a result like in the picture bellow, in this case, I have a table on my SQLite file called “TABLE_NAME” – haha:Screen Shot 2019-06-28 at 12.17.33 PM

Attach Databases

As the title explains, you can attach multiple database files together and execute queries between them, This is quite powerful since it allows you to do operations across different databases.

ATTACH DATABASE 'FILE/LOCATION' AS DBAliasName;
DETACH DATABASE DBAliasName;

So if you attach two different files, with the same schema, to select data from  the same table from both different SQLite files you would do

Main database:

SELECT * FROM TABLE_NAME;

Attached Database:

SELECT * FROM DBAliasName.TABLE_NAME;

Merge Databases/Tables

For this, you will need the databases to use the Attach and Detach commands and do something like:

INSERT OR REPLACE INTO TABLE_NAME (Id, Name, Salary) SELECT Id, Name, Salary FROM DBAliasName.TABLE_NAME;

You can also do * for this query, but the order and names of the field must match between tables.

You will notice that we used an INSERT OR REPLACE, there are a few options for you to choose, should conflicts happen during the operations, you can check them out here.

Backup Data Base

I mentioned before that you can do backups in your SQLite file, but why exactly is it? Since SQLite is just a file, can’t we just make copies of the existing database?

Well, here is the thing, depending on how you are operating with the engine, your data might not yet be stored in the main database, or the main database might not even exist in disk yet (in-memory). So there is a Backup API in SQLite engine. It can be as simple as calling

VACUUM INTO 'Existing/Backup/File/Path.db';

Or you can call the backup function from SQLite Online Backup API. Good news is that Frank Krueger is working on a Backup method for the next version of SQLite-net. And if you really need to make use of it System.Data.SQLite namespace has this method for you.

3. Tools

And at last but not least, I have a couple of tools to suggest, that will make dealing with SQLite Much easier!

DB Browser for SQLite

This is basically an IDE to see and easily query your SQLite file, it is cross-platform, so you can have it on your Mac, Windows, Linux etc! IT IS FREE  AND OPEN SOURCE!

Rider SQLite Navigation

If you are using JetBrains Rider, you should check out their SQLite Interface, my good friend Chase Florell loves it and is always talking about it 🙂

Conclusion

This is it guys, I hope this post brings some light into your lives, I love using SQLite in my Xamarin Apps, it is lightweight, easy to set up and to use, perfect for a client application!

throw new CauserException();

References

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s