Installing SQLite on MacOS (and probably for Windows) (Unity 2021.1.16)

After spending a few hours perusing old videos and posts, I finally figured out how to install SQLite on my Mac version of Unity. I am using Unity version 2021.1.16. Not sure how this will work with other versions.

1. You need to get the current version of SQLite itself.

• Go to https://sqlite.org/download.html
• Look under "Precompiled Binaries for Windows" (I know this sounds strange, but it works)
• Download sqlite-dll-win64-x64-3360000.zip .
• Extract the files. There should be 2 files:
  • sqlite3.def
  • sqlite3.dll
• Copy these 2 files to Assets/Plugins in your Unity project. (Obviously, create Plugins folder if not there).

2. You need to find this file: Mono.Data.Sqlite.dll

• Go to where your working version of the Unity application is installed. Mine happens to be in a directory called 2021.1.16f1, but it will of course depend on your actual version.
• Go into the directory Unity.app/Contents/MonoBleedingEdge/lib/mono/unityjit
• Find the file Mono.Data.Sqlite.dll in that directory.
• Copy the file to Assets/Plugins in your Unity project.

Magically, things should work now.

A small (and dumb) example of a program using SQLite to prove things are somewhat working:

Code (CSharp):

1. using System.Data;
2. using UnityEngine;
3. using IDbCommand = System.Data.IDbCommand;
4. using IDbConnection = System.Data.IDbConnection;
5. using Mono.Data.Sqlite;
6.  
7. public class CreateTable : MonoBehaviour
8. {
9.    
10.     string conn;
11.     string sqlQuery;
12.     IDbConnection dbconn;
13.     IDbCommand dbcmd;
14.     IDataReader dbreader;  // not used in this example
15.     string DATABASE_NAME = "/mydatabase.s3db";
16.     void Start()
17.     {
18.         string filepath = Application.dataPath + DATABASE_NAME;
19.         Debug.Log($"filepath={filepath}");
20.         conn = "URI=file:" + filepath;
21.        
22.         CreateATable();
23.        
24.     }
25.  
26.     private void CreateATable()
27.     {
28.         using (dbconn = new SqliteConnection(conn))
29.         {
30.             dbconn.Open();
31.             dbcmd = dbconn.CreateCommand();
32.             sqlQuery = "CREATE TABLE IF NOT EXISTS [my_table] (" +
33.                        "[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT," +
34.                        "[name] VARCHAR(255)  NOT NULL," +
35.                        "[age] INTEGER DEFAULT '18' NOT NULL)";
36.             dbcmd.CommandText = sqlQuery;
37.             dbcmd.ExecuteScalar();
38.             dbconn.Close();
39.         }
40.     }
41. }

 

Thanks for the help. Although you didn't specify, it makes sense to use the Mono.Data.Sqlite.dll from 'unityjit-win32' and not 'unity' or 'unityjit-macos' given the binaries you recommended to downloaed are also for windows.

 

For anybody else finding this as this ranks high on Google - I wasn't happy with Unity's Mono.Data.Sqlite.dll performance, especially when trying to add 100k of entries at once it would take half a minute or more. Tried different solutions like batching etc., was very frustrating.

Instead added SQLite by following these simple steps - and this solution turned out also super fast as it uses your native libraries if present. Adding 100k entries now happened within a second :

https://github.com/praeclarum/sqlite-net/issues/1023#issuecomment-821950695