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.

result = sqlDB.ExecuteQuery("SELECT * FROM example");
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.

2 Responses to Quickstart for SQLite in Unity

  1. Hezly says:

    for the result, the type is DataTable

    DataTable result = sqlDB.ExecuteQuery(“SELECT * FROM example”);

    For the row, the type is DataRow

    DataRow row = result.Rows[0];

  2. Rawle says:

    Thank you very much. I got this to work on Android and iOS. I wasted an entire day trying various obsolete code or code that only works on Windows.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: