Using SQLite in Unity (Unity + SQLite)

Hello everyone, this publication will be devoted to working with the embedded SQLite relational database in Unity. This article is written by a newbie for newbies with a view to showing how to work with SQLite, it is assumed that you know the basics of SQL. Since there is no clear tutor on the Internet for beginners, I decided to occupy this niche. In this article we will write a simple class for working with this DBMS, which can be used to solve a wide range of tasks (localization, data storage, maintenance of different tables).

What is SQLite and why do we need it?

SQLite is a compact embedded relational DBMS, which is still quite popular. An important plus of SQLite is cross-platform, so we can use SQLite for various platforms. SQLite can be used when you need speed and compactness, so if I have a data storage problem, I decided to solve it using this DBMS.

How to work with SQLite?

To create and edit our database there is a large number of free utilities and browser plugins, I personally will use DB Browser (SQLite), it hooked me up with its simplicity, and working with various plugins in the browser seemed to me not very convenient. In general, whoever wants, and works. Using DB Browser, you can easily create tables, make connections between them and fill them with data without resorting to using SQL. Also, in the DB Browser, you can do everything with SQLite handles, so it’s convenient for anyone.

Creating and populating a test database

We create a database in Assets / StreamingAssets of our project (I have this db.bytes, since Unity only understands * .bytes for databases, we will use this extension). Purely for example, I created such a database with the following tables:
')
1) Table "Player", which describes the essence of the player:

CREATE TABLE "Player" ( "id_player" INTEGER NOT NULL, "nickname" TEXT NOT NULL, PRIMARY KEY("id_player") ); 

Filled it with the following data:



2) Table "Scores", which is introduced to increase the level of normalization of the database

 CREATE TABLE "Scores" ( "id" INTEGER NOT NULL, "id_player" INTEGER NOT NULL, "score" INTEGER NOT NULL, PRIMARY KEY("id"), FOREIGN KEY("id_player") REFERENCES "Player"("id_player") ); 

Filled it with the following data:

Connecting libraries

We create a database in Assets / StreamingAssets of our project (I have this db.bytes), then we need to connect libraries to work with this database. Downloading the sqlite3.dll file from the official site for working with SQLite in Windows. What would make friends of this SKBD with Android it took me a couple of days, since the library indicated in this article was not working, I personally did not work with it on Android, I constantly got errors, on this I found something found somewhere on the Internet version of the library for Android. We place libraries here - Assets / Plugins / sqlite.dll and Assets / Plugins / Android / sqlite.so .

After all these manipulations, copy the System.Data.dll and Mono.Data.Sqlite.dll from C: \ Program Files (x86) \ Unity \ Editor \ Data \ Mono \ lib \ mono \ 2.0 and insert the Assets / Plugins of your Unity project. I want to note that in the 2018 version of Unity can write that System.Data.dll is already connected and a conflict of two identical files occurs. Actually, this is solved simply, we do not delete the System.Data.dll just inserted.

The library structure should be as follows:

Assets / Plugins / Mono.Data.Sqlite.dll - just need :)
Assets / Plugins / System.Data.dll - a similar reason
Assets / Plugins / sqlite3.dll - for working with SQLite on Windows
Assets / Plugins / Android / libsqlite3.so - for working with SQLite on Android

Writing a script to work with the database

And finally, we can start writing a script to work with the created database. First of all, let's create the MyDataBase file and connect the System.Data , Mono.Data.Sqlite , System.IO libraries, make the MyDataBase class static and, of course, remove the inheritance from MonoBehaviour. Add 3 private variables and a constant with the name of the database file. We should get something like this:

 using UnityEngine; using System.Data; using Mono.Data.Sqlite; using System.IO; static class MyDataBase { private const string fileName = "db.bytes"; private static string DBPath; private static SqliteConnection connection; private static SqliteCommand command; } 

This is all well and good, but we still cannot work with the database. To work with the database, we have to get the path to it, I propose to make a static constructor, which will just get the path to the database (I remind you that the database is in StreamingAssets).

 static MyDataBase() { DBPath = GetDatabasePath(); } /// <summary>    .        ,      apk . </summary> private static string GetDatabasePath() { #if UNITY_EDITOR return Path.Combine(Application.streamingAssetsPath, fileName); #if UNITY_STANDALONE string filePath = Path.Combine(Application.dataPath, fileName); if(!File.Exists(filePath)) UnpackDatabase(filePath); return filePath; #elif UNITY_ANDROID string filePath = Path.Combine(Application.persistentDataPath, fileName); if(!File.Exists(filePath)) UnpackDatabase(filePath); return filePath; #endif } /// <summary>      . </summary> /// <param name="toPath">       . </param> private static void UnpackDatabase(string toPath) { string fromPath = Path.Combine(Application.streamingAssetsPath, fileName); WWW reader = new WWW(fromPath); while (!reader.isDone) { } File.WriteAllBytes(toPath, reader.bytes); } 

Note. We need to unpack the database in the specified paths ( Application.dataPath / db.bytes for Windows and Application.persistentDataPath / db.bytes for Android) since the StreamingAssets folder, after assembly, has the ReadOnly attribute (except Android) and we will not be able to record something then in the database. Actually, in order to record anything in the database, we unpack our database. It says in detail what ways, under what platform to use in this article .

Let's write methods for opening a connection and closing, as well as a method that will execute a query that does not require returning values, say, INSERT, UPDATE, CREATE, DELETE, DROP.

 /// <summary>      . </summary> private static void OpenConnection() { connection = new SqliteConnection("Data Source=" + DBPath); command = new SqliteCommand(connection); connection.Open(); } /// <summary>      . </summary> public static void CloseConnection() { connection.Close(); command.Dispose(); } /// <summary>     query. </summary> /// <param name="query">  . </param> public static void ExecuteQueryWithoutAnswer(string query) { OpenConnection(); command.CommandText = query; command.ExecuteNonQuery(); CloseConnection(); } 

Miraculously, now our script can fulfill requests for data modification. But what about a very important SELECT? I decided that the return value of the method that should execute the query to fetch the data should be of type DataTable or string if you want to get 1 value. To do this, we write 2 methods:

 /// <summary>     query    . </summary> /// <param name="query">  . </param> /// <returns>   1  1 ,   . </returns> public static string ExecuteQueryWithAnswer(string query) { OpenConnection(); command.CommandText = query; var answer = command.ExecuteScalar(); CloseConnection(); if (answer != null) return answer.ToString(); else return null; } /// <summary>    ,      query. </summary> /// <param name="query">  . </param> public static DataTable GetTable(string query) { OpenConnection(); SqliteDataAdapter adapter = new SqliteDataAdapter(query, connection); DataSet DS = new DataSet(); adapter.Fill(DS); adapter.Dispose(); CloseConnection(); return DS.Tables[0]; } 

Done, now we have a simple script that can make requests for modification and selection of data. Let's write the ScoreManager script now. Which will receive a table of the best results sorted descending. And, for verification, let's display the leader's nickname and his points in Debug.Log.

 using System.Collections; using System.Collections.Generic; using System.Data; using UnityEngine; public class ScoreManager : MonoBehaviour { private void Start() { //     DataTable scoreboard = MyDataBase.GetTable("SELECT * FROM Scores ORDER BY score DESC;"); //  id   int idBestPlayer = int.Parse(scoreboard.Rows[0][1].ToString()); //     string nickname = MyDataBase.ExecuteQueryWithAnswer($"SELECT nickname FROM Player WHERE id_player = {idBestPlayer};"); Debug.Log($"  {nickname}  {scoreboard.Rows[0][2].ToString()} ."); } } 

This is what we get at startup:



Thank you for your attention, I will gladly accept constructive criticism.