Search Unity

how to setup a database in Unity

Discussion in 'Scripting' started by DylanWest, Oct 6, 2009.

  1. DylanWest

    DylanWest

    Joined:
    Sep 30, 2009
    Posts:
    32
    I am new to Unity and I've scoured the forums and documentation for a clear tutorial on how to setup a database in Unity. Everything I find is unclear, incomplete or assumes the reader already knows most of the steps. Can someone please explain in a simple step-by-step fashion how to do this? I would like to use a sqlite database to store game data like ammo, items collected, etc...

    Thanks
     
  2. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,603
    Unity has no support for databases in any way.

    you would have to use the mono / .net support for this and add in the required additional assemblies.
     
  3. Eric5h5

    Eric5h5

    Volunteer Moderator Moderator

    Joined:
    Jul 19, 2006
    Posts:
    32,172
    That seems like major overkill; the usual method is to use PlayerPrefs for items like that.

    --Eric
     
  4. DylanWest

    DylanWest

    Joined:
    Sep 30, 2009
    Posts:
    32
    I have found the posts that mention using dlls for mono, but those posts are not written like a tutorial to someone unfamiliar with it. Can you give me some steps to take for setting up mono for unity?

    @Eric5h5: And no, it's not overkill to use a database for what I need. PlayerPrefs will not fit the bill for my game.
     
  5. Dreamora

    Dreamora

    Joined:
    Apr 5, 2008
    Posts:
    26,603
    you don't need to setup mono. Unity already has it, you are working with mono all the time.

    actually you couldn't even use a downloaded and setup mono if you wanted to.

    What you need to do is check the documentations of mono / MSDN for informations on database support and get an idea what you are looking for using them within unity through C#
     
  6. MatthewW

    MatthewW

    Joined:
    Nov 30, 2006
    Posts:
    1,355
    As mentioned, you probably don't want an actual database. PlayerPrefs or state serialization or something far simpler will be a better solution.

    But, just so you can kill some time, you should know that sqlite has been ported to C#. I don't think anyone has tried this in Unity's environment, yet, but feel free to be the first: http://code.google.com/p/csharp-sqlite/
     
  7. DylanWest

    DylanWest

    Joined:
    Sep 30, 2009
    Posts:
    32
    I keep hearing that I don't need a database. Does that mean that if my player collects items, builds up levels, etc and turns off the game, then 5 weeks later turns it back on under that game file, the player prefs and state serialization will store that data for them. I'm under the impression that those methods of data storage don't persist after the player quits. Also, can I run select statements against player prefs and data stored in state serialization? My game will also teach a foreign language, which requires me to constantly evaluate the player's current proficiency with the target language in terms of their mastery of the newest vocabulary and grammar points. I'm thinking I'll need sql queries to do that kind of analysis.

    @Dreamora - how do I use the built in database in Unity? I have tried to follow several postings on this and each time it doesn't work.

    By the way, thanks to everyone for your quick responses!
     
  8. andeeeee

    andeeeee

    Joined:
    Jul 19, 2005
    Posts:
    8,768
    It's likely that you do need SQL-type functionality for what you are trying to do - Matthew's suggestion of using the C# SQLite library sounds pretty good, although I've no idea whether this library is reliable.
     
  9. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    705
  10. Hilm

    Hilm

    Joined:
    Nov 2, 2007
    Posts:
    338
    In answer to the player prefs surviving after the game is quit tho - yeah... You can save data in player prefs and it's just essentially a registry entry, the data is saved and can be pulled in easily when the game resumes.

    If you are after gameplay that alters based on regional factors however; yeah trying to find a way to integrate sql is prolly the way to go.
     
  11. DylanWest

    DylanWest

    Joined:
    Sep 30, 2009
    Posts:
    32
    @thylaxene- I've tried using the code in that first forum post you included. I saved it as a javascript file in my project and then I created a sqlite db file using the sqlite browser and put that in my project folder. Then i got errors about the class name not matching the file name or something like that in Unity. What am I doing wrong?
     
  12. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    705
    Just make sure the actual file name matches the class name specified in the script. In this case the file name should be: dbAccess.js

    Cheers.
     
  13. DylanWest

    DylanWest

    Joined:
    Sep 30, 2009
    Posts:
    32
    Just want to thank everyone for your help. I finally got the sqlite db working. The only thing I was missing was to download Mono.Data.SqliteClient.dll and System.Data.dll and put them in the Assets folder of my project. Oh, and the file name matching the classname. And if you don't have the sqlite3.dll in your C:\Program Files\Unity\Editor directory, you'll have to put one there,as well.
     
    turosteel likes this.
  14. Barock

    Barock

    Joined:
    Dec 5, 2009
    Posts:
    15
  15. thylaxene

    thylaxene

    Joined:
    Oct 10, 2005
    Posts:
    705
    Yes the MySQL approach is valid... but you need an internet connection for that... what if you want you standalone player to be internet independent, yet still store complex data and do complex SQL joins and data searches? In that case SQLite is perfect solution.

    I actually use a mix of both. When I have a internet connection my standalone will backup the SQLite data to MySQL so other users of the standalone around the world can get access to the data that other user created.

    Cheers.
     
    nubhokage1991 likes this.
  16. zumwalt

    zumwalt

    Joined:
    Apr 18, 2007
    Posts:
    2,206
    Actually to work both with Pro or Indy, do the following:

    Download ADO.NET 2.0 Provider for SQLite
    http://sourceforge.net/projects/sqlite-dotnet2/files/SQLite for ADO.NET 2.0/

    Next, install that package, associate it with your Visual Studio 2005/2008 or what ever version you have installed, then open Unity and your game, now double click on any C# script file, expand References, you need to add in 2 references here for the intellesense, Add System.Data and System.Data.SQLite, once you have done this, add to your list of Using the following 3 items:

    Code (csharp):
    1.  
    2. using System;
    3. using System.Data;
    4. using System.Data.SQLite;
    5.  
    Your not done yet, Unity still has issues with the objects in the MonoCompiler.framework folder so this is what you have to do until they fix it, browse to the following folder:
    Code (csharp):
    1.  
    2. C:\Program Files\Unity\Editor\Data\MonoCompiler.framework
    3.  
    Copy the System.Data.dll from that folder and place it in the root of your assets folder so that Unity can know and understand what System.Data is, now you need to browse to the next folder:
    Code (csharp):
    1.  
    2. C:\Program Files\SQLite.NET\bin\
    3.  
    Copy the System.Data.SQLite.dll file into the root of your assets folder, this is a .NET assembly and will work with the Free version of Unity, so will the System.Data.dll found in that framework folder, now you are almost done.

    You also need to place a copy of the System.Data.SQLite.dll in the following folder:
    Code (csharp):
    1.  
    2. C:\Program Files\Unity\Editor
    3.  
    Otherwise you will get a dllunkownexception error (yea, you get that with System.Data also if you don't place it in your assets folder) [NOTE: ALWAYS place a copy of any DLL's in the editor folder when you get the dllnotfound or dllunknownexception, that will fix it every time]

    Take this bit of code and place it in a C# file, name the class what ever you want and attach it to a game object:

    Code (csharp):
    1.  
    2. using UnityEngine;
    3. using System.Collections;
    4. using System;
    5. using System.Data;
    6. using System.Data.SQLite;
    7.  
    8. public class CaptureObjects : MonoBehaviour
    9. {
    10.   // local variables
    11.   SQLiteConnection con = new SQLiteConnection("Data Source=gamedata.db;Version=3;New=False;Compress=True;");
    12.   SQLiteCommand cmd;
    13.  
    14.   string buildTableInfo;
    15.   string sql;
    16.  
    17.     // Use this for initialization
    18.     void Start () {
    19.     buildTableInfo = string.Empty;
    20.     sql = string.Empty;
    21.     StartCoroutine(SetupDatabase());
    22.     }
    23.    
    24.     // Update is called once per frame
    25.     void Update () {
    26.    
    27.     }
    28.  
    29.   void FixedUpdate()
    30.   {
    31.  
    32.   }
    33.  
    34.   IEnumerator SetupDatabase()
    35.   {
    36.     con.Open();
    37.     cmd = con.CreateCommand();
    38.     cmd.CommandText = "create table IF NOT EXISTS tblMASTER(PID, OJBECTS, VALUE);";
    39.     cmd.ExecuteNonQuery();
    40.     // Test its creation or if it exists see row count
    41.     cmd.CommandText = "SELECT COUNT(*) FROM tblMASTER;";
    42.     int result = Convert.ToInt32(cmd.ExecuteScalar());
    43.     con.Close();
    44.     Debug.Log(result);
    45.     yield return new WaitForSeconds(0);
    46.   }
    47.  
    48.   void OnApplicationQuit()
    49.   {
    50.     // cleanup anythin gopen
    51.     if (con != null)
    52.     {
    53.       con.Close();
    54.       con.Dispose();
    55.     }
    56.     if(cmd != null)
    57.       cmd.Dispose();
    58.   }
    59. }
    60.  
    Now this will give you the basic setup for how to create a database and use it cleanly. When you build your project and run it, it will create the database in the root of the game folder. I am not sure where it goes when you run the game while in the editor, haven't really searched for it, but it actually does create it.

    Edit:
    Found where it places the file when you are in editor mode, it puts it one level above the Assets folder, same place it creates the Visual Studio project file.
     
  17. mohydineName

    mohydineName

    Joined:
    Aug 30, 2009
    Posts:
    301
    Hi all,

    would this work for IPhone?
     
    Last edited: Mar 10, 2011
  18. danrayson

    danrayson

    Joined:
    Mar 3, 2013
    Posts:
    10
    For those that are reading this thinking "It shouldn't be this hard." Well, I came across this. It's a wrapper around all of this DLL HELL that means you just have to give the System.Data.SQLite wrapper they give you and it just works. I haven't tested this on any platform other than Windows, however.

    Bare in mind I've spent about 3/4 days trying to get a database working at all with data flowing in and out of unity, and I'm typing this after about 60 seconds of having found I can run queries and get data back.
     
    Last edited: May 6, 2013
  19. Noztradamuz

    Noztradamuz

    Joined:
    May 15, 2013
    Posts:
    6
    Tanky very much!! really Thank you! :D this worked like charm! :D
     
  20. mbivol10

    mbivol10

    Joined:
    Nov 6, 2013
    Posts:
    3
  21. saddam751

    saddam751

    Joined:
    Nov 6, 2013
    Posts:
    41
  22. judy3turn

    judy3turn

    Joined:
    Aug 16, 2012
    Posts:
    13
    I want a REAL database, not SQL Lite and, in fact, not SQL at all if possible. LDAP would be nice but Unity doesn't support directory services (curse you). I need an extremely fast look-up running on my server as the game is data heavy. Anyone have any suggestions? I used db4o for the prototype but it can't scale well enough. I'm so ticked off about LDAP because I've done all this work to getting the server up and running only to find out I can't use it now.
     
  23. Joviex

    Joviex

    Joined:
    Jan 23, 2011
    Posts:
    41
    I am sorry, I do not understand what you are saying here.

    LDAP is not a database, its an application layer, a protocol, INTO a service layer atop an actual database, like, crazy thought here, SQL.

    SQL Lite is a real database. All databases (most) are stored and indexed files. Some can float up and live entirely in memory, others are indexed and sharded across many harddrives/machines.

    I see a lot of mis-understanding of what database technology is, how it is used, how best to use it, when to use it and why you use it in this request thread. I suggest anyone really wanting to understand the tech, search google, read a book, something.

    Otherwise, as was pointed out serveral times, a simple state persist to a file (amazingly how most databases actually work) is perfectly fine, and will indeed survive a period of XYZ minutes, hours, days, weeks, months and years, unless the storage device itself dies.

    Cheers.
     
  24. chillersanim

    chillersanim

    Joined:
    Mar 31, 2013
    Posts:
    214
    How is it with cross plattform support? If I use SQL/SQLite, would that run on Apple/Linux or even consoles/mobile?
    How much can I store, do I have full suport for data access like on normal database solutions?
    As someone who has only a smal understanding of databases, I would like to know where the limits are.

    I have someone in my group that works a loot with databases, but he is new to unity and isn't sure about the answer.

    Greetings
    Chillersanim
     
  25. sudhaMR

    sudhaMR

    Joined:
    Jun 17, 2015
    Posts:
    12
    It has been a long time. But these pages seem to be missing. Can you tell me what these links directed to? I am working on a project that needs to use data from db to populate the scenes and create new scenes.
    http://forum.unity3d.com/threads/au...nd-elements-from-a-database-or-a-file.334169/
     
  26. DeveshPandey

    DeveshPandey

    Joined:
    Sep 30, 2012
    Posts:
    206
    Here is my
    plugin for SQLite , anyone can buy this. Full and Free support is guaranteed.
    http://u3d.as/content/devesh-pandey/sqlite-database
     
  27. Makhdoom

    Makhdoom

    Joined:
    Mar 21, 2016
    Posts:
    1
    I am working on kinect V1 with unity and make a gesture game in unity so the problem is that how can I save my game data like score in unity and which database will be best for that

    please do response earlier
     
  28. Wingzrooke

    Wingzrooke

    Joined:
    Jan 27, 2016
    Posts:
    3
    So I have not worked with a database before, but find myself needing to add a database-like functionality to my game. Can anyone tell me where/what version of SQLite to download? It looks like it's free on the SQlite.org website, but there are so many different options that I'm not certain which will work with Unity....
     
  29. laurentlavigne

    laurentlavigne

    Joined:
    Aug 16, 2012
    Posts:
    2,125
    I used iBoxDB, it's fast and easy to use, zero setup.
    http://forum.unity3d.com/threads/iboxdb-lightweight-embedded-database.217097/

     
  30. Madeindreams

    Madeindreams

    Joined:
    Feb 8, 2018
    Posts:
    1
    Well. After searching the web for an hour about "Installing a database for unity" This is where I end up. I'm still seeking.

    My first idea, as I was getting Unity seted up on Linux with MonoDevelop and reading on all that.

    Why not use MongoDB?

    I hear Unity is already working with Json
    MongoDB works well with Json
    Most of what I program uses Json

    Plus if you plan on modding your game and building API with web socket app and such it sound to me like a nice common ground to handle your data. And best of all its rreally easy to read.


    How can I use MongoDB with Unity? (Under Linux)

    I am using Ubuntu 17.10
    Unity 2017.3.0p2
     
    Last edited: Feb 8, 2018
  31. decapator

    decapator

    Joined:
    Sep 2, 2013
    Posts:
    8

    Thanks, just giving it a look.
     
  32. JeffDUnity3D

    JeffDUnity3D

    Unity Technologies

    Joined:
    May 2, 2017
    Posts:
    6,138
    Tip, don't ever connect directly to a database from a client, it will use up one connection per user. Instead, use a proper web service that in turn talks to the db over a single connection. Much more scalable and secure. SQLite for a local mobile db solution, as well as Couchbase Mobile if you need both connected and disconnected mobile and hosted.
     
    atiwari1 and laurentlavigne like this.