HomeLearnHow-toSaving Data in Unity3D Using SQLite

Saving Data in Unity3D Using SQLite

Updated: Apr 29, 2022 |

Published: Apr 12, 2022

  • Realm
  • Mobile
  • C#
  • ...

By Dominic Frei

Rate this article

(Part 4 of the Persistence Comparison Series)

Our journey of exploring options given to use when it comes persistence in Unity will in this part lead to databases. More specificaclly: SQLite.

SQLite is a C-based database that is used in many areas. It has been around for a long time and also found its way into the Unity world. During this tutorial series, we have seen options like PlayerPrefs in Unity, and on the other side, File and BinaryWriter/BinaryReader provided by the underlying .NET framework.

Here is an overview of the complete series:

Similar to the previous parts, this tutorial can also be found in our Unity examples repository on the persistence-comparison branch.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/00_project_structure_0cd0367d9e.jpg

Each part is sorted into a folder. The three scripts we will be looking at in this tutorial are in the SQLite sub folder. But first, let's look at the example game itself and what we have to prepare in Unity before we can jump into the actual coding.

#Example game

Note that if you have worked through any of the other tutorials in this series, you can skip this section since we're using the same example for all parts of the series, so that it's easier to see the differences between the approaches.

The goal of this tutorial series is to show you a quick and easy way to make some first steps in the various ways to persist data in your game.

Therefore, the example we'll be using will be as simple as possible in the editor itself so that we can fully focus on the actual code we need to write.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/01_capsule_in_scene_4f20907410.jpg

A simple capsule in the scene will be used so that we can interact with a game object. We then register clicks on the capsule and persist the hit count.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/02_add_a_capsule_dbfbab93f9.jpg

When you open up a clean 3D template, all you need to do is choose GameObject -> 3D Object -> Capsule.

You can then add scripts to the capsule by activating it in the hierarchy and using Add Component in the inspector.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/03_add_script_044c78ede9.jpg https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/04_script_name_dc02632896.jpg

The scripts we will add to this capsule showcasing the different methods will all have the same basic structure that can be found in HitCountExample.cs.

1using UnityEngine;
2
3/// <summary>
4/// This script shows the basic structure of all other scripts.
5/// </summary>
6public class HitCountExample : MonoBehaviour
7{
8 // Keep count of the clicks.
9 [SerializeField] private int hitCount; // 1
10
11 private void Start() // 2
12 {
13 // Read the persisted data and set the initial hit count.
14 hitCount = 0; // 3
15 }
16
17 private void OnMouseDown() // 4
18 {
19 // Increment the hit count on each click and save the data.
20 hitCount++; // 5
21 }
22}

The first thing we need to add is a counter for the clicks on the capsule (1). Add a [SerilizeField] here so that you can observe it while clicking on the capsule in the Unity editor.

Whenever the game starts (2), we want to read the current hit count from the persistence and initialize hitCount accordingly (3). This is done in the Start() method that is called whenever a scene is loaded for each game object this script is attached to.

The second part to this is saving changes, which we want to do whenever we register a mouse click. The Unity message for this is OnMouseDown() (4). This method gets called every time the GameObject that this script is attached to is clicked (with a left mouse click). In this case, we increment the hitCount (5) which will eventually be saved by the various options shown in this tutorials series.

#SQLite

(See SqliteExampleSimple.cs in the repository for the finished version.)

Now let's make sure our hit count gets persisted so we can continue playing the next time we start the game.

SQLite is not included per default in a new Unity project and is also not available directly via the Unity package manager. We have to install two components to start using it.

First, head over to https://sqlite.org/download.html and choose the Precompiled Binaries for your operating system. Unzip it and add the two files—sqlite3.def and sqlite3.dll—to the Plugin folder in your Unity project.

Then, open a file explorer in your Unity Hub installation directory, and head to the following sub directory:

1Unity/Hub/Editor/2021.2.11f1/Editor/Data/MonoBleedingEdge/lib/mono/unity

In there, you will find the file Mono.Data.Sqlite.dll which also needs to be moved to the Plugins folder in your Unity project. The result when going back to the Editor should look like this:

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/05_sqlite_in_project_hierarchy_bf73af7d7b.png

Now that the preparations are finished, we want to add our first script to the capsule. Similar to the HitCountExample.cs, create a new C# script and name it SqliteExampleSimple.

When opening it, the first thing we want to do is import SQLite by adding using Mono.Data.Sqlite; and using System.Data; at the top of the file (1).

Next we will look at how to save whenever the hit count is changed, which happens during OnMouseDown(). First we need to open a connection to the database. This is offered by the SQLite library via the IDbConnection class (2) which represents an open connection to the database. Since we will need a connection for loading the data later on again, we will extract opening a database connection into another function and call it private IDbConnection CreateAndOpenDatabase() (3).

In there, we first define a name for our database file. I'll just call it MyDatabase for now. Accordingly, the URI should be "URI=file:MyDatabase.sqlite" (4). Then we can create a connection to this database using new SqliteConnection(dbUri) (5) and open it with dbConnection.Open() (6).

1using Mono.Data.Sqlite; // 1
2using System.Data; // 1
3using UnityEngine;
4
5public class SqliteExampleSimple : MonoBehaviour
6{
7 // Resources:
8 // https://www.mono-project.com/docs/database-access/providers/sqlite/
9
10 [SerializeField] private int hitCount = 0;
11
12 void Start() // 13
13 {
14 // Read all values from the table.
15 IDbConnection dbConnection = CreateAndOpenDatabase(); // 14
16 IDbCommand dbCommandReadValues = dbConnection.CreateCommand(); // 15
17 dbCommandReadValues.CommandText = "SELECT * FROM HitCountTableSimple"; // 16
18 IDataReader dataReader = dbCommandReadValues.ExecuteReader(); // 17
19
20 while (dataReader.Read()) // 18
21 {
22 // The `id` has index 0, our `hits` have the index 1.
23 hitCount = dataReader.GetInt32(1); // 19
24 }
25
26 // Remember to always close the connection at the end.
27 dbConnection.Close(); // 20
28 }
29
30 private void OnMouseDown()
31 {
32 hitCount++;
33
34 // Insert hits into the table.
35 IDbConnection dbConnection = CreateAndOpenDatabase(); // 2
36 IDbCommand dbCommandInsertValue = dbConnection.CreateCommand(); // 9
37 dbCommandInsertValue.CommandText = "INSERT OR REPLACE INTO HitCountTableSimple (id, hits) VALUES (0, " + hitCount + ")"; // 10
38 dbCommandInsertValue.ExecuteNonQuery(); // 11
39
40 // Remember to always close the connection at the end.
41 dbConnection.Close(); // 12
42 }
43
44 private IDbConnection CreateAndOpenDatabase() // 3
45 {
46 // Open a connection to the database.
47 string dbUri = "URI=file:MyDatabase.sqlite"; // 4
48 IDbConnection dbConnection = new SqliteConnection(dbUri); // 5
49 dbConnection.Open(); // 6
50
51 // Create a table for the hit count in the database if it does not exist yet.
52 IDbCommand dbCommandCreateTable = dbConnection.CreateCommand(); // 6
53 dbCommandCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS HitCountTableSimple (id INTEGER PRIMARY KEY, hits INTEGER )"; // 7
54 dbCommandCreateTable.ExecuteReader(); // 8
55
56 return dbConnection;
57 }
58}

Now we can work with this SQLite database. Before we can actually add data to it, though, we need to set up a structure. This means creating and defining tables, which is the way most databases are organized. The following screenshot shows the final state we will create in this example.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/06_sqlite_tables_bcebcfd101.png

When accessing or modifying the database, we use IDbCommand (6), which represents an SQL statement that can be executed on a database.

Let's create a new table and define some columns using the following command (7):

1"CREATE TABLE IF NOT EXISTS HitCountTableSimple (id INTEGER PRIMARY KEY, hits INTEGER )"

So, what does this statement mean? First, we need to state what we want to do, which is CREATE TABLE IF NOT EXISTS. Then, we need to name this table, which will just be the same as the script we are working on right now: HitCountTableSimple.

Last but not least, we need to define how this new table is supposed to look. This is done by naming all columns as a tuple: (id INTEGER PRIMARY KEY, hits INTEGER ). The first one defines a column id of type INTEGER which is our PRIMARY KEY. The second one defines a column hits of type INTEGER.

After assigning this statement as the CommandText, we need to call ExecuteReader() (8) on dbCommandCreateTable to run it.

Now back to OnMouseClicked(). With the dbConnection created, we can now go ahead and define another IDbCommand (9) to modify the new table we just created and add some data. This time, the CommandText (10) will be:

1"INSERT OR REPLACE INTO HitCountTableSimple (id, hits) VALUES (0, " + hitCount + ")"

Let's decipher this one too: INSERT OR REPLACE INTO adds a new variable to a table or updates it, if it already exists. Next is the table name that we want to insert into, HitCountTableSimple. This is followed by a tuple of columns that we would like to change, (id, hits). The statement VALUES (0, " + hitCount + ") then defines values that should be inserted, also as a tuple. In this case, we just choose 0 for the key and use whatever the current hitCount is as the value.

Opposed to creating the table, we execute this command calling ExecuteNonQuery() (11) on it.

The difference can be defined as follows:

ExecuteReader is used for any result set with multiple rows/columns (e.g., SELECT col1, col2 from sometable). ExecuteNonQuery is typically used for SQL statements without results (e.g., UPDATE, INSERT, etc.).

All that's left to do is to properly Close() (12) the database.

How can we actually verify that this worked out before we continue on to reading the values from the database again? Well, the easiest way would be to just look into the database. There are many tools out there to achieve this. One of the open source options would be https://sqlitebrowser.org/.

After downloading and installing it, all you need to do is File -> Open Database, and then browse to your Unity project and select the MyDatabase.sqlite file. If you then choose the Table HitCountTableSimple, the result should look something like this:

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/07_db_browser_1abb0a86e8.png

Go ahead and run your game. Click a couple times on the capsule and check the Inspector for the change. When you then go back to the DB browser and click refresh, the same number should appear in the value column of the table.

The next time we start the game, we want to load this hit count from the database again. We use the Start() function (13) since it only needs to be done when the scene loads. As before, we need to get a hold of the database with an IDbConnection (14) and create a new IDbCommand (15) to read the data. Since there is only one table and one value, it's quite simple for now. We can just read all data by using:

1"SELECT * FROM HitCountTableSimple"

In this case, SELECT stands for read the following values, followed by a * which indicates to read all the data. The keyword FROM then specifies the table that should be read from, which is again HitCountTableSimple. Finally, we execute this command using ExecuteReader() (17) since we expect data back. This data is saved in an IDataReader, from the documentation:

Provides a means of reading one or more forward-only streams of result sets obtained by executing a command at a data source, and is implemented by .NET data providers that access relational databases.

IDataReader addresses its content in an index fashion, where the ordering matches one of the columns in the SQL table. So in our case, id has index 0, and hitCount has index 1. The way this data is read is row by row. Each time we call dataReader.Read() (18), we read another row from the table. Since we know there is only one row in the table, we can just assign the value of that row to the hitCount using its index 1. The value is of type INTEGER so we need to use GetInt32(1) to read it and specify the index of the field we want to read as a parameter, id being 0 and value being 1.

As before, in the end, we want to properly Close() the database (20).

When you restart the game again, you should now see an initial value for hitCount that is read from the database.

#Extended example

(See SqliteExampleExtended.cs in the repository for the finished version.)

In the previous section, we looked at the most simple version of a database example you can think of. One table, one row, and only one value we're interested in. Even though a database like SQLite can deal with any kind of complexity, we want to be able to compare it to the previous parts of this tutorial series and will therefore look at the same Extended example, using three hit counts instead of one and using modifier keys to identify them: Shift and Control.

Let's start by creating a new script SqliteExampleExtended.cs and attach it to the capsule. Copy over the code from SqliteExampleSimple and apply the following changes to it. First, defie the three hit counts:

1[SerializeField] private int hitCountUnmodified = 0;
2[SerializeField] private int hitCountShift = 0;
3[SerializeField] private int hitCountControl = 0;

Detecting which key is pressed (in addition to the mouse click) can be done using the Input class that is part of the Unity SDK. Calling Input.GetKey(), we can check if a certain key was pressed. This has to be done during Update() which is the Unity function that is called each frame. The reason for this is stated in the documentation:

Note: Input flags are not reset until Update. You should make all the Input calls in the Update Loop.

The key that was pressed needs to be remembered when recieving the OnMouseDown() event. Hence, we need to add a private field to save it like so:

1private KeyCode modifier = default;

Now the Update() function can look like this:

1private void Update()
2{
3 // Check if a key was pressed.
4 if (Input.GetKey(KeyCode.LeftShift)) // 1
5 {
6 // Set the LeftShift key.
7 modifier = KeyCode.LeftShift; // 2
8 }
9 else if (Input.GetKey(KeyCode.LeftControl)) // 1
10 {
11 // Set the LeftControl key.
12 modifier = KeyCode.LeftControl; // 2
13 }
14 else // 3
15 {
16 // In any other case reset to default and consider it unmodified.
17 modifier = default; // 4
18 }
19}

First, we check if the LeftShift or LeftControl key was pressed (1) and if so, save the corresponding KeyCode in modifier. Note that you can use the string name of the key that you are looking for or the more type-safe KeyCode enum.

In case neither of those two keys were pressed (3), we define this as the unmodified state and just set modifier back to its default (4).

Before we continue on to OnMouseClicked(), you might ask what changes we need to make in the database structure that is created by private IDbConnection CreateAndOpenDatabase(). It turns out we actually don't need to change anything at all. We will just use the id introduced in the previous section and save the KeyCode (which is an integer) in it.

To be able to compare both versions later on, we will change the table name though and call it HitCountTableExtended:

1dbCommandCreateTable.CommandText = "CREATE TABLE IF NOT EXISTS HitCountTableExtended (id INTEGER PRIMARY KEY, hits INTEGER)";

Now, let's look at how detecting mouse clicks needs to be modified to account for those keys:

1private void OnMouseDown()
2{
3 var hitCount = 0;
4 switch (modifier) // 1
5 {
6 case KeyCode.LeftShift:
7 // Increment the hit count and set it to PlayerPrefs.
8 hitCount = ++hitCountShift; // 2
9 break;
10 case KeyCode.LeftControl:
11 // Increment the hit count and set it to PlayerPrefs.
12 hitCount = ++hitCountControl; // 2
13 break;
14 default:
15 // Increment the hit count and set it to PlayerPrefs.
16 hitCount = ++hitCountUnmodified; // 2
17 break;
18 }
19
20 // Insert a value into the table.
21 IDbConnection dbConnection = CreateAndOpenDatabase();
22 IDbCommand dbCommandInsertValue = dbConnection.CreateCommand();
23 dbCommandInsertValue.CommandText = "INSERT OR REPLACE INTO HitCountTableExtended (id, hits) VALUES (" + (int)modifier + ", " + hitCount + ")";
24 dbCommandInsertValue.ExecuteNonQuery();
25
26 // Remember to always close the connection at the end.
27 dbConnection.Close();
28}

First, we need to check which modifier was used in the last frame (1). Depending on this, we increment the corresponding hit count and assign it to the local variable hitCount (2). As before, we count any other key than LeftShift and LeftControl as unmodified.

Now, all we need to change in the second part of this function is the id that we set statically to 0 before and instead use the KeyCode. The updated SQL statement should look like this:

1"INSERT OR REPLACE INTO HitCountTableExtended (id, hits) VALUES (" + (int)modifier + ", " + hitCount + ")"

The VALUES tuple now needs to set (int)modifier (note that the enum needs to be casted to int) and hitCount as its two values.

As before, we can start the game and look at the saving part in action first. Click a couple times until the Inspector shows some numbers for all three hit counts:

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/08_inspector_sqlite_extended_6ae4def3db.png

Now, let's open the DB browser again and this time choose the HitCountTableExtended from the drop-down:

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/09_hit_count_table_extended_f5c574fec3.png

As you can see, there are three rows, with the value being equal to the hit counts you see in the Inspector. In the id column, we see the three entries for KeyCode.None (0), KeyCode.LeftShift (304), and KeyCode.LeftControl (306).

Finally, let's read those values from the database when restarting the game.

1void Start()
2{
3 // Read all values from the table.
4 IDbConnection dbConnection = CreateAndOpenDatabase(); // 1
5 IDbCommand dbCommandReadValues = dbConnection.CreateCommand(); // 2
6 dbCommandReadValues.CommandText = "SELECT * FROM HitCountTableExtended"; // 3
7 IDataReader dataReader = dbCommandReadValues.ExecuteReader(); // 4
8
9 while (dataReader.Read()) // 5
10 {
11 // The `id` has index 0, our `value` has the index 1.
12 var id = dataReader.GetInt32(0); // 6
13 var hits = dataReader.GetInt32(1); // 7
14 if (id == (int)KeyCode.LeftShift) // 8
15 {
16 hitCountShift = hits; // 9
17 }
18 else if (id == (int)KeyCode.LeftControl) // 8
19 {
20 hitCountControl = hits; // 9
21 }
22 else
23 {
24 hitCountUnmodified = hits; // 9
25 }
26 }
27
28 // Remember to always close the connection at the end.
29 dbConnection.Close();
30}

The first part works basically unchanged by creating a IDbConnection (1) and a IDbCommand (2) and then reading all rows again with SELECT * (3) but this time from HitCountTableExtended, finished by actually executing the command with ExecuteReader() (4).

For the next part, we now need to read each row (5) and then check which KeyCode it belongs to. We grab the id from index 0 (6) and the hits from index 1 (7) as before. Then, we check the id against the KeyCode (8) and assign it to the corresponding hitCount (9).

Now restart the game and try it out!

#Conclusion

SQLite is one of the options when it comes to persistence. If you've read the previous tutorials, you've noticed that using it might at first seem a bit more complicated than the simple PlayerPrefs. You have to learn an additional "language" to be able to communicate with your database. And due to the nature of SQL not being the easiest format to read, it might seem a bit intimidating at first. But the world of databases offers a lot more than can be shown in a short tutorial like this!

One of the downsides of plain files or PlayerPrefs that we've seen was having data in a structured way—especially when it gets more complicated or relationships between objects should be drawn. We looked at JSON as a way to improve that situation but as soon as we need to change the format and migrate our structure, it gets quite complicated. Encryption is another topic that might be important for you—PlayerPrefs and File are not safe and can easily be read. Those are just some of the areas a database like SQLite might help you achieve the requirements you have for persisting your data.

In the next tutorial, we will look at another database, the Realm Unity SDK, which offers similar advantages to SQLite, while being very easy to use at the same time.

Please provide feedback and ask any questions in the Realm Community Forum.

Rate this article
MongoDB logo
© 2021 MongoDB, Inc.

About

  • Careers
  • Investor Relations
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.