Use sqlite in Unity

In this tutorial you will see how to use sqlite in Unity, but first why use a database for a game ?
In most games you have data to store about the levels, the characters, and the monsters you can use files of course but sometime it’s easier to use a database.
Using a database does not means it’s slower, you can read the benchmark of sqlite againt the filesystem here.

Install Sqlite4Unity3d

We will use the plugin sqlite4unity3d for this tutorial.
The first thing to do is to create a project you can either choose 2d or 3d.
Then you can download the latest version of the plugin and import the content inside the folder Assets/Plugins.
Copy the file SQLite.cs file into your scripts folder.
To use the database you will need to create a few script to be able to connect to it.

DataService class

You will need to create a class like this one where you have a SQLiteConnection, you also need to create a StreamingAssets folder inside Assets for the final database. Here we simply instanciate the database by first creating the file for the database and establishing a connection on it.

using SQLite4Unity3d;
using UnityEngine;
#if !UNITY_EDITOR
using System.Collections;
using System.IO;
#endif
using System.Collections.Generic;

public class DataService  {

	private SQLiteConnection _connection;

	public DataService(string DatabaseName){

#if UNITY_EDITOR
            var dbPath = string.Format(@"Assets/StreamingAssets/{0}", DatabaseName);
#else
        // check if file exists in Application.persistentDataPath
        var filepath = string.Format("{0}/{1}", Application.persistentDataPath, DatabaseName);

        if (!File.Exists(filepath))
        {
            Debug.Log("Database not in Persistent path");
            // if it doesn't - >
            // open StreamingAssets directory and load the db - >

#if UNITY_ANDROID 
            var loadDb = new WWW("jar:file://" + Application.dataPath + "!/assets/" + DatabaseName);  // this is the path to your StreamingAssets in android
            while (!loadDb.isDone) { }  // CAREFUL here, for safety reasons you shouldn't let this while loop unattended, place a timer and error check
            // then save to Application.persistentDataPath
            File.WriteAllBytes(filepath, loadDb.bytes);
#elif UNITY_IOS
                 var loadDb = Application.dataPath + "/Raw/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);
#elif UNITY_WP8
                var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
                // then save to Application.persistentDataPath
                File.Copy(loadDb, filepath);

#elif UNITY_WINRT
		var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
		// then save to Application.persistentDataPath
		File.Copy(loadDb, filepath);
		
#elif UNITY_STANDALONE_OSX
		var loadDb = Application.dataPath + "/Resources/Data/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
		// then save to Application.persistentDataPath
		File.Copy(loadDb, filepath);
#else
	var loadDb = Application.dataPath + "/StreamingAssets/" + DatabaseName;  // this is the path to your StreamingAssets in iOS
	// then save to Application.persistentDataPath
	File.Copy(loadDb, filepath);

#endif

            Debug.Log("Database written");
        }

        var dbPath = filepath;
#endif
            _connection = new SQLiteConnection(dbPath, SQLiteOpenFlags.ReadWrite | SQLiteOpenFlags.Create);
        Debug.Log("Final PATH: " + dbPath);     

	}
}

The database is empty maybe you want to have a function to clean and fill the database, for that you can create a function CreateDbObject :

public void CreateDBObj(){
		_connection.DropTable <Obj > ();
		_connection.CreateTable <Obj > ();

		_connection.InsertAll (new[]{
			new Obj{
				Id = 1,
				Name = "Eraser"
			},
			new Obj{
				Id = 2,
				Name = "Pencil"
			},
			new Obj{
				Id = 3,
				Name = "Pen"
			}
		});
	}

But the class Obj is not defined ! That’s right you also need to define the class Obj :

using SQLite4Unity3d;

public class Obj  {

	[PrimaryKey, AutoIncrement]
	public int Id { get; set; }
	public string Name { get; set; }

	public override string ToString ()
	{
		return string.Format ("[Obj: Id={0}, Name={1}]", Id, Name);
	}
}

Get the object

You also need to create a function to get the objects, you can simply use the Table function :

public IEnumerable <Obj > GetObj(){
	return _connection.Table <Obj >();
}

Create DB script

To use the createDBObj function and also display the content of the database you will need to create another script :

using UnityEngine;
using System.Collections.Generic;
using UnityEngine.UI;

public class CreateDBObjScript : MonoBehaviour {

	void Start () {
		var ds = new DataService("tempObjDatabase.db");
                ds.CreateDBObj();
        
                var obj = ds.GetObj ();
                ToConsole (obj);
	}
	
	private void ToConsole(IEnumerable <Obj > obj){
	        foreach (var o in obj) {
			Debug.Log(o.ToString());
		}
	}
}

Database viewer

To visualize the database content doing a print is not really efficient you can download a database viewer, I use SQLiteStudio which is free, open-source and cross platform. The interface looks something like this :

Test the script

To test the script you need to attach the script to an empty Gameobject in the scene.

Download the project sqlite in unity

DatabaseTest