Simple SQLite in Unity for Android and iOS

Use the open-source SQLite4Unity3D library to save days of work

Cover image
by Tommy Leung on March 4th, 2020

Getting SQLite to work in Unity for Android and iOS can be a nightmare.

You might have to suffer for a week or more trying to implement support for Android or iOS.

Especially, if you aren't familiar with native app development. Everything can work in the Editor but fail with no discernable errors on device. 😭

The easiest solution is to buy a SQLite library from the Unity Asset Store.

But there is another option if you can't afford to or don't want to: an open-source solution called SQLite4Unity3D.

This library will save you days of work!

The documentation could be better so we will show you how to get started in this article!

Import SQLite4Unity3D

SQLite4Unity3D is not delivered as a Unity Package so you'll have to do some manual work. 😎

Download the zip file and then copy the files from the resulting folder into your Unity project.

There will be a Plugins folder and a SQLite.cs file.

Add a Database

The common use case for SQLite is to have a pre-populated database that the game will read from and write to so we will assume this in the following example.

First, create or move your existing SQLite database into your project's StreamingAssets folder.

You can create a new database with the command:

sqlite3 nameOfDB.db

Now we can connect to it.

Connect to the Database

At a high level, the connection code looks like this:

using SQLite4Unity3d;

string path = this.getDatabasePath("nameOfDB.db");
this.connection = new SQLiteConnection(path, SQLiteOpenFlags.ReadWrite);

The confusing parts are in getDatabasePath(string name) where we add logic for the Editor, iOS, and Android.

private string getDatabasePath(string name)
{
	string filePath = string.Format("{0}/{1}", Application.persistentDataPath, name);
	bool fileExists = File.Exists(filePath);

	switch (Application.platform)
	{
		default:
		{
			// alternatively implement an assumed fallback
			throw new NotSupportedException();
		}

		case RuntimePlatform.WindowsEditor:
		case RuntimePlatform.OSXEditor:
		case RuntimePlatform.LinuxEditor:
		{
			return string.Format("Assets/StreamingAssets/{0}", name);
		}

		case RuntimePlatform.Android:
		{
			if (fileExists)
			{
				return filePath;
			}

			// this is the path to your StreamingAssets in android
			string path = string.Format("jar:file://{0}!/assets/{1}", Application.dataPath, name);
			var req = UnityWebRequest.Get(path).SendWebRequest();

			// NOTE: may want to add some checks to this
			while (!req.isDone) { }

			File.WriteAllBytes(filePath, req.webRequest.downloadHandler.data);
			break;
		}

		case RuntimePlatform.IPhonePlayer:
		{
			if (fileExists)
			{
				return filePath;
			}

			// this is the path to your StreamingAssets in iOS
			string path = string.Format("/{0}Raw/{1}", Application.dataPath, name);
			File.Copy(path, filePath);
			break;
		}
	}

	return filePath;
}

We are doing the platform check at runtime but you can also use preprocessor directives like #UNITY_EDITOR, #UNITY_IOS, and #UNITY_ANDROID.

The majority of the code in getDatabasePath(string name) is copying a prepopulated database from StreamingAssets into the persistent data path.

If you are not shipping a pre-populated database then you don't need to do this and can simply create a database at filePath.


Now you can query the database like the example below assuming NPCs is a class we defined in our project.

NPCs entry = this.connection.Table<NPCs>().First();
// do something with entry

Check out the SQLite.cs file that came with SQLite4Unity3D to see all the available methods to interact with your database.

There are also videos from SQLite4Unity3D that might be helpful.

Let me know in the comments below if anything is unclear or if you'd like specific guides for running on Android or iOS.

Don't miss out on future time-saving Unity tips & techniques! Enter your email into the box below to subscribe.