Saving Data in Unity3D Using SQLite
Rate this code example
(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
PlayerPrefsin Unity, and on the other side,
BinaryReaderprovided by the underlying .NET framework.
Here is an overview of the complete series:
- Part 4: SQL (this tutorial)
- Part 5: Realm Unity SDK (coming soon)
- Part 6: Comparison of all these options
Each part is sorted into a folder. The three scripts we will be looking at in this tutorial are in the
SQLitesub 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.
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.
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.
When you open up a clean 3D template, all you need to do is choose
You can then add scripts to the capsule by activating it in the hierarchy and using
Add Componentin the inspector.
The scripts we will add to this capsule showcasing the different methods will all have the same basic structure that can be found in
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
hitCountaccordingly (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
GameObjectthat 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.
SqliteExampleSimple.csin 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.
Then, open a file explorer in your Unity Hub installation directory, and head to the following sub directory:
In there, you will find the file
Mono.Data.Sqlite.dllwhich also needs to be moved to the
Pluginsfolder in your Unity project. The result when going back to the Editor should look like this:
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# scriptand name it
When opening it, the first thing we want to do is import SQLite by adding
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 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
MyDatabasefor 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
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.
Let's create a new table and define some columns using the following command (7):
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:
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
INTEGERwhich is our
PRIMARY KEY. The second one defines a column
After assigning this statement as the
CommandText, we need to call
dbCommandCreateTableto run it.
Now back to
OnMouseClicked(). With the
dbConnectioncreated, 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:
Let's decipher this one too:
INSERT OR REPLACE INTOadds 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
0for the key and use whatever the current
hitCountis 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.
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.sqlitefile. If you then choose the
HitCountTableSimple, the result should look something like this:
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
valuecolumn 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 databy using:
In this case,
read the following values, followed by a
*which indicates to read all the data. The keyword
FROMthen 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 , 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.
IDataReaderaddresses its content in an index fashion, where the ordering matches one of the columns in the SQL table. So in our case,
idhas index 0, and
hitCounthas 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
valueof that row to the
hitCountusing its index 1. The
valueis of type
INTEGERso we need to use to read it and specify the index of the field we want to read as a parameter,
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
hitCountthat is read from the database.
SqliteExampleExtended.csin 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:
Let's start by creating a new script
SqliteExampleExtended.csand attach it to the capsule. Copy over the code from
SqliteExampleSimpleand apply the following changes to it. First, defie the three hit counts:
Detecting which key is pressed (in addition to the mouse click) can be done using the 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:
Update()function can look like this:
First, we check if the
LeftControlkey was pressed (1) and if so, save the corresponding
modifier. Note that you can use the
stringname of the key that you are looking for or the more type-safe
In case neither of those two keys were pressed (3), we define this as the
unmodifiedstate and just set
modifierback to its
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
idintroduced 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
Now, let's look at how detecting mouse clicks needs to be modified to account for those keys:
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
Now, all we need to change in the second part of this function is the
idthat we set statically to
0before and instead use the
KeyCode. The updated SQL statement should look like this:
VALUEStuple now needs to set
(int)modifier(note that the
enumneeds to be casted to
hitCountas 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:
Now, let's open the DB browser again and this time choose the
HitCountTableExtendedfrom the drop-down:
As you can see, there are three rows, with the
valuebeing equal to the hit counts you see in the Inspector. In the
idcolumn, we see the three entries for
Finally, let's read those values from the database when restarting the game.
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
For the next part, we now need to read each row (5) and then check which
KeyCodeit belongs to. We grab the
0(6) and the
1(7) as before. Then, we check the
KeyCode(8) and assign it to the corresponding
Now restart the game and try it out!
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
PlayerPrefsthat 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—
Fileare 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.
Building a Crypto News Website in C# Using the Microsoft Azure App Service and MongoDB Atlas
Jun 13, 2023