Saving Data in Unity3D Using SQLite
.jpg&w=3840&q=75)
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
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:
- Part 1: PlayerPrefs
- Part 2: Files
- Part 3: BinaryReader and BinaryWriter
- Part 4: SQL (this tutorial)
- Part 5: Realm Unity SDK (coming soon)
- Part 6: Comparison of all these options
Similar to the previous parts, this tutorial can also be found in our Unity examples repository on the persistence-comparison branch.
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.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
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.
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.
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.(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:
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:
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).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.
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):
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: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:
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: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.(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: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:Now the
Update() function can look like this: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: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 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: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:
Now, let's open the DB browser again and this time choose the
HitCountTableExtended from the drop-down:
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.
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!
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.
Rate this code example