SQLite and Unity: How to do it right.
Tutorials and best practices of using databases in mobile applications, with reference to my own entrepreneurial journey.
I had the opportunity to begin my own mobile application start-up. Where I learned the excruciating demand for locally stored data in the phone memory. Since the user will open and close the app several times, it is expected that previous sessions will be saved.
Of course, there are several ways to do this but the most useful and convenient method we ended up with was a local query based database. Our environment of choice was Unity. Which unfortunately has no official support (not at-least at the time) for databases like SQL.
Thanks to the web and many forums that exist, I conjured up this little plugin that will do the job. Today, I will show how to effectively use it in your mobile applications.
Unity: What you need to know?
For those of you who don’t know Unity, it is a tool to create complex graphic mobile applications, built over the native development tools of several platforms like Windows, Android and iOS etc. Meaning you can build and ship applications pertaining to these platforms. The existential purpose of Unity is to build games which demand more graphic rendering than data processing. Nevertheless, if you decide to create a regular app, it will still do the trick.
We choose Unity as the core platform for our product because: 1) Our product was a gaming platform. 2) We wanted to benefit from the fact that Unity is cross-platform, meaning we can hit both App Store and Play Store by coding only for one.
Unity officially supports two languages for scripting; C# and Javascript. Today, we shall use C#.
SQLite: Why we need it?
Databases are, essentially, data structures working under certain set of rules that we define ourselves. They free us from the hustle of micromanaging yet giving the freedom to shape data by our needs. There are two famous types of databases, one is called the Structured Query Language or SQL. Which places data in the form of tables. The other is called No-SQL, well you guessed, because it has no tables or structure, it stores data in the form of objects. Intuitively, No-SQL is more resource hungry than SQL, therefore, for mobile applications we prefer the latter.
Talking of resources, databases were made for computers and servers to store huge amounts of data. However, in case of mobile platforms we don’t really have that kind of computing resources and clearly we don’t need that much functionality either. This is where SQLite comes in, offering less functionality for saving computational resources. SQLite is light weight, has less data types and doesn't allow functionalities like multi-user access.
SQLite has been favored by mobile developers and recommended by Android official documentation. We haven’t found official support for this database by Unity itself yet. However, I conjured up a way to utilize the perks of SQLite for Unity applications on all mobile platforms (well didn't try for windows phones).
How to setup SQLite in Unity:
I assume that you are already familiar with Unity and some kind of databases. If you are new to the topic I suggest running at least the beginner tutorials before you continue.
Unity works by talking to the contents in it’s workspace, specifically the Assets folder where all your scripts, graphics and animation reside. Here you can create as many folders and scripts as you want and use them in a Scene whenever required. However, Unity looks for some particular folder names while doing its building process. For our purpose the Plugins folder is of importance. Here we put our pre-built libraries to be used by our scripts. Now this folder does not exist in the workspace when you create a new project but if you create it then Unity will understand it’s purpose.
Let’s dive right into the fun part. Follow these steps to setup Unity and SQLite.
- In an empty Unity project we will copy paste the Plugins folder from this link, which already contains the libraries for SQLite. Now, your workspace looks something like this:
- Let’s create a scene and a behavior script inside the workspace. Both tasks can be done by right clicking in the workspace and selecting Scene and C# Script respectively. I have named them MainScene and SqliteTest, respectively.
- Here comes the coding part, open up the SqliteTest file. It will open up in an editor. For me, I have configured Unity to choose VS Code as my editor. This step is just to import the SQLite libraries into our script. So, you can replace the top import statements by the ones below:
using UnityEngine;
using System.Data;
using Mono.Data.Sqlite;
using System.IO;- Now, we shall create a new database and open up a connection. Here I have named it My_Database for no particular reason. The connection dbcon serves as a means to communicate with your database. Any queries you write will pass through here and results will come the same way. Copy paste the following code in the Start() function of the SqliteTest script.
string connection = "URI=file:" + Application.persistentDataPath + "/My_Database";
IDbConnection dbcon = new SqliteConnection(connection);
dbcon.Open();- So far what we have done is opened up a database connection with our brand new database. Remember, the path we have specified here is within your installed application directory and you can access this database file anytime. Now, lets create a table in our database. Following the code above, add this to the Start() function.
IDbCommand dbcmd;
IDataReader reader;
dbcmd = dbcon.CreateCommand();
string q_createTable =
"CREATE TABLE IF NOT EXISTS my_table (id INTEGER PRIMARY KEY, val INTEGER )";
dbcmd.CommandText = q_createTable;
reader = dbcmd.ExecuteReader();- Now we have created a table by the name of my_table. Let’s try to put some values in there and then read them. Copy paste the following code after the last one.
IDbCommand cmnd = dbcon.CreateCommand();
cmnd.CommandText = "INSERT INTO my_table (id, val) VALUES (0, 5)";
cmnd.ExecuteNonQuery();- Now, let’s read all the table at once. Oh, and don’t forget to close the connection when done.
IDbCommand cmnd_read = dbcon.CreateCommand();
IDataReader reader;string query ="SELECT * FROM my_table";
cmnd_read.CommandText = query;
reader = cmnd_read.ExecuteReader();while (reader.Read()){
Debug.Log("id: " + reader[0].ToString());
Debug.Log("val: " + reader[1].ToString());
}dbcon.Close();
- With that done, this is what your SqliteTest should look like.
- Now select your MainScene in Unity, right click to add an empty GameObject in the loaded scene and drag-and-drop your SqliteTest script on this GameObject. This is how it looks:
- With that done hit the play button in the Unity Editor and you’ll see output in the Console like this:
Congratulations! you have successfully created, inserted and read from an SQLite database. This code is applicable to run on all your Android and iOS devices.
Continue reading if you like to know how you can do this better.
The problem here is that, it is not convenient to communicate with the database in this fashion. Multiple database connections could throw errors in your code and writing custom queries every time can be agonizing.
Do it like a pro:
I have used the following method, to communicate with the database, in native Android code using Java. I learned the technique from this tutorial and since, I have been using it in nearly all my applications. Which inspired me to create a similar abstraction in Unity. If you like to skip and see the whole code directly here it is.
The key is to group together the common methods and variables together in one class. Then extend the class using inheritance to create actual implementations. Below I have created a high level class SqliteHelper which can be extended to other classes, in order to access basic operations of any database. We call these basic operations CURD i.e Create, Update, Read and Delete.
Here, you can configure your database name and other important important functionalities. Moreover, I tend to use the same namespace for all database files i.e DataBank. I also store them in the same folder name within Unity workspace.
Example:
Let’s take an example of how we can utilize this SqliteHelper function in our code. Suppose we need to create a database table consisting of geographic location points (latitude, longitude). Apart from that, we will have a date, id and type component for each location. Let’s call this LocationEntry and make a C# class.
Such “Entity” classes are used for easy data handling. It’s not like everyone will remember the order of columns in their tables.
Now, lets create our location table. I will call this class LocationDb, however, it is not a database in itself just a table, which communicates with the SqliteHelper. The reason for this kind of naming convention is because it seems to act like a database and we shall treat it that way too.
Let’s put both LocationEntry and LocationDb into action. The following script shows the proper way to test both of these. Attach this script to another empty GameObject and hit play. The output should display a couple of ids from the database.
Using the above templates of LocationEntity and LocationDb, you can generate your own classes and entities. This method allows you to safely access your database without worrying about closing the connection or write complicated queries every time.
Conclusion:
If you have read through the whole article, then you have learned how to utilize SQLite with Unity in one of the best ways possible. Remember “databasing” is never a simple and easy job. That is why there are people in companies that are only responsible for managing databases. The plus side is, once you embrace the complexities of databases, it becomes really fun. I have also created a sample application using the techniques above, in case you like to explore. I hope this will be beneficial to your corporate projects or self started gigs.
Happy Coding!