This will be a practical guide to using SQLite in Unity3d. I will tell you what it is, for which it may be required, how to implement it, and I will recommend tools for convenient work. The article focuses on primary and intermediate levels. At the end you will find a small bonus. All interested welcome under cat.

What is it?

SQLite is a lightweight, embedded, and completely self-contained relational database. It is the most common SQL-database, there are at least 500 million installations in the world, compared with 100 million deployments of other SQL-databases. Used in projects such as Mozilla Firefox, Chrome, Skype, Windows Phone 8, iOS, Android, Symbian, and others, and others ... And most importantly, it is free, open source, and also has 100% test coverage. All this you can find on the official website of SQLite and in posts on Habré .

What for?

I note that in our small department almost everyone has different operating systems, and since interoperability is important for us, we tried to choose cross-platform solutions.
For us, SQLite was a good option for storing external information - resources, configuration settings, localization. We immediately refused external xml-like files, due to problems with transferring to different devices, bloating and slowness. The expediency of using SQLite for your project can be found here .

How?

To create and edit the database, our choice fell on SQLite Manager - this is a plug-in for Firefox, it is free, convenient and cross-platform. The only thing I didn’t like was that I had to write a SQL request to create a table to add foreign keys, as well as the impossibility of editing records in a multi-table VIEW. And the rest is all very clear. You can even experiment with SQL queries to your database. At the output, you get one file, the extension of which you can specify yourself (.db, .sqlite, .bytes, etc.), but Unity understands only ".bytes".
')
Another important point. Placing binaries like images, music, videos in the database is a bad practice. Roughly speaking, everything that weighs more than 100 kb should be in the local folder, and you only write paths in the database. Otherwise, the speed of reading a binary from the database becomes greater than loading it locally.

Now it's time to place it all in our Unity project. To use SQLite, download the sqlite.dll library (for Win, iOS and MacOS) and sqlite.os (for Android). We place libraries here - Assets/Plugins/sqlite.dll and Assets/Plugins/Android/sqlite.so . If there are no Plugins , then create it. We put the created database (let's call it for example db.bytes ) in the Assets/StreamingAssets daddy (create it if it is absent). As a result, after deploying, our database will be here:

Win and Mac OS:

 Application.dataPath + "/StreamingAssets/db.bytes" 

iOS:

 Application.dataPath + "/Raw/db.bytes" 

but on Android it will be packaged in an apk file of the application:

 "jar:file://" + Application.dataPath + "!/assets/db.bytes". 

You will need to use the WWW class to load the database, and then copy it to the Application.persistentDataPath + "/" folder.

But these directories have only read permissions (except Android), if you need to write something to the database, you need to move it here:

Win and Mac OS:

 Application.dataPath + "/db.bytes" 

iOS:

 string path = Application.dataPath.Substring(0, Application.dataPath.LastIndexOf("/")); path = path.Substring(0, path.LastIndexOf("/")) + "/Documents/db.bytes"; 

or so

 string path = Application.dataPath.Substring(0, Application.dataPath.length - 5); path = path.Substring(0, path.LastIndexOf("/")) + "/Documents/db.bytes"; 

or so

 string path = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + "/db.bytes"; 

About Android is written above.

Then we connect the System.IO package and write

 File.Copy(openPath, savePath); 

for Android, respectively

 WWW www = new WWW(openPath); while(!www .isDone) {} //   ,   File.WriteAllBytes(savePath, www.bytes); 

Well, resource binaries: models, videos, music, etc., we put Assets/Resources/ in the daddy. After the build, they are packaged in binary resources.assets . And in the application, we load them - Resources.Load (String path) . Well, for example, we put the sound in the Assets/Resources/Sounds/sound1.mp3 , in the database we have such a record Sounds/sound1.mp3 , and in the application it will look like

 AudioClip a = Resources.Load("Sounds/sound1.mp3") as AudioClip; 

Now it is time to read data from our database. I don’t like to write SQL queries and parse raw data into objects, so especially for SQLite we found the open source project SQLite-net ORM, this is a library of object-relational mapping. It is a technology that links a database with object-oriented programming concepts. There is support for Linq, for example, you can make a sample:

 public class Favorite { [PrimaryKey, AutoIncrement] public int Id { get; set; } public int UserId { get; set; } public string Url { get; set; } } public Favorite[] GetFavorites(SQLiteConnection c, int id) { var q = from f in c.Table<Favorite>() where f.UserId == id select f; return q.ToArray(); } 

or edit:

 public void AddFavorite(SQLiteConnection c, string url, int id) { var fav = new Favorite() { UserId = id, Url = url };  c.Insert(fav); } 

But Linq is not fully supported, for example, JOIN is not supported, so for complex queries you will still have to write SQL code.

Where is my bonus?

Well, in principle, the most important thing I wrote. Now the promised bonus. I suggest you try SQLite in your project to solve the localization problem.
We have a database:

To view, edit and create, we developed a special tool for localizers, which I also post (Adobe Air build for Win and Mac OS): link . Dare!

Thank you for your attention, waiting for your feedback.