Quickstart for SQLite in Unity

I had some troubles setting up SQLite for my Unity project for Android and iOS app. Let’s hope this quickstart helps you faster up to speed with your mobile app.

DISCLAIMER. This example is for OS/X development environment and Android and iOS builds. Never tried this for Windows but I guess installing sqlite3.dll should do the trick.

Step 1. Get wrapper API

First, get the SQLite library by @busta117 from Github: https://github.com/Busta117/SQLiteUnityKit

Step 2. Copy API files on your project

Copy the DataTable.cs and SqliteDatabase.cs somewhere under your projects Assets/Scripts/ folder. If you build also for android, then copy libsqlite3.so in your projects Assets/Plugins/Android/ folder. iOS does not need plugin as it has native support for sqlite.

Step 3. Create default database

This is the database that should contain the tables you need with any default data you may want to have. Default database is used to bootstrap the actual in app database.

Create folder Assets/StreamingAssets/. Then create your default template database with sqlite3.

$ sqlite3 Assets/StreamingAssets/default.db
SQLite version 3.8.8.3 2015-02-25 13:29:11
Enter ".help" for usage hints.
sqlite> create table example (
   ...> name string,
   ...> dummy int
   ...> );
sqlite> .schema example
CREATE TABLE example (
name string,
dummy int
);
sqlite> insert into example values ("hello world", 1);
sqlite> select * from example;
hello world|1
sqlite>.quit
$

Now you have default database and your project should have files like these.

./Assets/Plugins/Android/libsqlite3.so
./Assets/Scripts/SQLiteUnityKit/DataTable.cs
./Assets/Scripts/SQLiteUnityKit/SqliteDatabase.cs
./Assets/StreamingAssets/default.db

Step 4. Database initialization code.

Initialize the database in your main scripts Awake() method. This checks if database already exists and if not, it copies the default db as template.

SqliteDatabase sqlDB;

void Awake() 
{
    string dbPath = System.IO.Path.Combine (Application.persistentDataPath, "game.db");
    var dbTemplatePath = System.IO.Path.Combine(Application.streamingAssetsPath, "default.db");

    if (!System.IO.File.Exists(dbPath)) {
        // game database does not exists, copy default db as template
        if (Application.platform == RuntimePlatform.Android)
        {
            // Must use WWW for streaming asset
            WWW reader = new WWW(dbTemplatePath);
            while ( !reader.isDone) {}
            System.IO.File.WriteAllBytes(dbPath, reader.bytes);
        } else {
            System.IO.File.Copy(dbTemplatePath, dbPath, true);
        }		
    }
    sqlDB = new SqliteDatabase(dbPath);
}

You can use the script execution order setting to ensure that this code is always executed first.

Step 5. Use the database!

API supports normal selects, inserts and updates.

var result = sqlDB.ExecuteQuery("SELECT * FROM example");
var row = result.Rows[0];
print("name=" + (string)row["name"]);
print("dummy=" + (int)row["dummy"]);

API is simple to use, check detailed documentation from the https://github.com/Busta117/SQLiteUnityKit.