Search Unity

  1. Unity 6 Preview is now available. To find out what's new, have a look at our Unity 6 Preview blog post.
    Dismiss Notice
  2. Unity is excited to announce that we will be collaborating with TheXPlace for a summer game jam from June 13 - June 19. Learn more.
    Dismiss Notice

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

Discussion in 'Documentation' started by cachetgames, Oct 7, 2021.

  1. cachetgames

    cachetgames

    Joined:
    Oct 18, 2019
    Posts:
    15
    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. }
     
    Boanerges_ and SerggioUa like this.
  2. Boanerges_

    Boanerges_

    Joined:
    Oct 8, 2021
    Posts:
    1
    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.
     
  3. makomarkus

    makomarkus

    Joined:
    Oct 28, 2020
    Posts:
    62
    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