Using SQLite in Unity (Unity + SQLite)

Hello everyone, this post will focus on working with Unity's SQLite Embedded Relational Database. This article is written by a novice for beginners with the goal of showing you how to work with SQLite, it is assumed that you know the basics of SQL. Since there is no clear tutorial for beginners on the Internet, 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, maintaining different tables).

What is SQLite and why do we need it?

SQLite is a compact embedded relational database management system that is quite popular. An important plus of SQLite is its cross-platform, so we can use SQLite for various platforms. SQLite can be used when speed and compactness are needed, therefore, in case of a data storage problem, I decided to solve it using this DBMS.

How to work with SQLite?

To create and edit our database there are a large number of free utilities and plug-ins for browsers, I personally will use DB Browser (SQLite), it hooked me with its simplicity, and it seemed to me not very convenient to work with various plug-ins in the browser. In general, whoever wants it works like that. Using DB Browser, you can safely create tables, make connections between them and fill them with data without resorting to using SQL. Also, in DB Browser, you can do everything with pens using SQLite, so here it’s more convenient for someone.

Create and populate a test database

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

1) The “Player” table, 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) The Scores table, which was introduced to increase the level of database normalization

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 the Assets / StreamingAssets of our project (I have db.bytes), then we need to connect the libraries to work with this database. Download sqlite3.dll file from the official site for working with SQLite in Windows. What would make friends given SKBD with Android it took me a couple of days, as the library referred to in this article was not working, personally, I have not come to work with her on Android, constantly climbed error on this pour found somewhere in the expanses of the Internet this version of the library for Android. We place the libraries here - Assets / Plugins / sqlite.dll and Assets / Plugins / Android / sqlite.so .

After all these manipulations, copy System.Data.dlland Mono.Data.Sqlite.dll with 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, Unity may write that System.Data.dll is already connected and there is a conflict of two identical files. Actually, this is solved simply, we do not delete the newly inserted System.Data.dll .

The library structure should be as follows:

Assets / Plugins / Mono.Data.Sqlite.dll - just need it :)
Assets / Plugins / System.Data.dll - a similar reason for
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, create a 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 good, of course, but we won’t be able to work with the database. To work with the database, we must get the path to it, I propose to make a static constructor, which will just get the path to the database (I recall that the database lies in StreamingAssets).

static MyDataBase()
{
    DBPath = GetDatabasePath();
}
/// 
 Возвращает путь к БД. Если её нет в нужной папке на Андроиде, то копирует её с исходного apk файла. 
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
}
/// 
 Распаковывает базу данных в указанный путь. 
///  Путь в который нужно распаковать базу данных. 
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 the build, has the ReadOnly attribute (except Android) and we won’t be able to write anything then in the database. Actually, in order to be able to write something to the database, we unpack our database. It is said in detail which paths, under which platform to use in this article .

We will write methods for opening a connection and closing, as well as a method that will execute a request that does not require returning values, for example, INSERT, UPDATE, CREATE, DELETE, DROP.

/// 
 Этот метод открывает подключение к БД. 
private static void OpenConnection()
{
    connection = new SqliteConnection("Data Source=" + DBPath);
    command = new SqliteCommand(connection);
    connection.Open();
}
/// 
 Этот метод закрывает подключение к БД. 
public static void CloseConnection()
{
    connection.Close();
    command.Dispose();
}
/// 
 Этот метод выполняет запрос query. 
///  Собственно запрос. 
public static void ExecuteQueryWithoutAnswer(string query)
{
    OpenConnection();
    command.CommandText = query;
    command.ExecuteNonQuery();
    CloseConnection();
}

Miraculously, now our script can execute 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 for data selection should be of the DataTable type or string, if you want to get 1 value. To do this, write 2 methods:

/// 
 Этот метод выполняет запрос query и возвращает ответ запроса. 
///  Собственно запрос. 
///  Возвращает значение 1 строки 1 столбца, если оно имеется. 
public static string ExecuteQueryWithAnswer(string query)
{
    OpenConnection();
    command.CommandText = query;
    var answer = command.ExecuteScalar();
    CloseConnection();
    if (answer != null) return answer.ToString();
    else return null;
}
/// 
 Этот метод возвращает таблицу, которая является результатом выборки запроса query. 
///  Собственно запрос. 
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 data modification and selection. Let's write the ScoreManager script now. Which will receive a table of the best results sorted in descending order. And, for verification, 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()} очков.");
    }
}

Here's what we get at startup:



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