how to setup a database in Unity

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

 

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.

 

That seems like major overkill; the usual method is to use PlayerPrefs for items like that.

--Eric

 

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.

 

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#

 

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/

 

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!

 

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.

 

I'm using SQLIte in my current project for a client, and it works very well. I've had no real problems and actually recommend it for more complex data storage.

See these posts:
http://forum.unity3d.com/viewtopic.php?t=30249&highlight=sqlite
http://forum.unity3d.com/viewtopic.php?t=8432&highlight=sqlite

Cheers.

 

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.

 

@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?

 

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.

 

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.

 

Hello,
pardon my ignorance but why you need to create a database with Unity?

I mean here is a tutorial for creating a MySQL database and howto access it.

[mod edit: removed old link that no longer works]

And here is another approach
http://forum.unity3d.com/viewtopic.php?t=25533

Or is this a security or performance related reason to integrate this within Unity?

Thanks

 

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.

 

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.

 

Hi all,

would this work for IPhone?

 

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.

 

Tanky very much!! really Thank you! this worked like charm!

 

http://answers.unity3d.com/questions/585102/how-to-change-from-login-scene-to-character-costum.html please help me out with this question !

 

http://www.google.com/cse/home?cx=001712401338047450041:csfhqk-trfa

 

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.

 

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.

 

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

 

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/

 

Here is my

plugin for SQLite , anyone can buy this. Full and Free support is guaranteed.
http://u3d.as/content/devesh-pandey/sqlite-database

 

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

 

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....

 

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

 

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

 

Thanks, just giving it a look.

 

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.

 

Cost.

One way or another ...

Option 1 (on-premise): You can't install it locally without hitting that "Contact us" button which is Internet business speak for "Yes, you can! But it'll cost you.".

Option 2 (cloud): You get a free plan for cloud usage but it will cost, too. In terms of time spent working with and waiting for the database. Everything has to happen remotely, accessing it as well as editing the data. Every connection, every query will cost you fractions of a second at least, possibly more (free plans are also limited by CPU time, memory, bandwidth, support), and over the course of a game's development this can stack up quite considerably. Cloud-hosted software also often limits adjustable settings, often to ensure bulletproof security but also preventing one virtual machine eating up too many CPU cycles. So if you plan on doing things like on-database transactional SQL hooks you can expect this to be rather slow on a virtual machine, most likely slowed down artificially. Almost all free cloud software wants you to hit snags eventually for which there's a dead-easy solution: you just need to pay for it.

I would always prefer and strongly recommend running any database locally (on the same same machine if you're the only dev or solely in charge of the data) or on-premise (same gigabit LAN as the dev machines) and if necessary, make it accessible for remote workers.

You can put that database up on the cloud just before release. During development however, you want your database to be local/on-premise ALL THE TIME!

Sorry for caps, I can't stress this enough.