Question Relational databases

Is there a relational database that works well with Unity? I'm originall a webdeveloper and am used to working with MySQL databases. I was wondering if there's a possibility to work directly with a local database that's distributed along with the build. Most of the examples I find when Googling is about how to connect to a remote database, but my goal here is to be able to replace the database during development but not connect to something external. The build should not be able to change the data, just read from it.

Reason I'm asking is because I could potentially make JSON files from the tables or whatever, and while that would also ensure my ID's are all referring to each other correctly I'd be keen on removing that "export" step. I would also like to use things like MySQL Views. Can anyone push me in the right direction?

 

I've seen some SQLite content for Unity in the past. I don't know the details, but it might be somewhere to start. Good luck.

 

As mopthrow notes, good luck. Yes it exists. Is it seamless? No. There's lots of big ugly festering warts.

For instance I doubt any of the current instances work on WebGL.

Also, for Android, if you supply a database in your project, you must write code to extract the database binary from your StreamedAssets (using UnityWebRequest) and store it somewhere on the user's local device that the database library can get at.

If you're comfy in SQL, do your work there, but take the time to export it to something more open like JSON or even Unity's ScriptableObjects, which are FAR superior than a SQL table for authoring content in Unity.

Otherwise you're probably just creating a "Problem Project" for you to maintain long into the future when whatever SQL library you choose falls out of favor, breaks, mysteriously fails, or Google / Apple breaks compatibility with it, etc.

 

Hmm. Yeah, the reason I asked is because I am comfortable with SQL and I've not yet discovered a great way to couple things apart from assigning them to a field in the Editor. While that works with authoring the data itself, it also means I would have to add methods to search in this data instead of using a query and casting the result to concrete classes.

Imagine that all my guns have a trigger value that states how their input works. Tap would mean one shot per click, held would mean a sustained stream of bullets until the clip is empty, and so forth.

Say I would like a list of all guns that have a 'held' trigger.

GET * FROM 'guns' where trigger = held

I'm still looking for a good way to filter stuff like this that doesn't involve having to write methods that iterate through Lists or Arrays. Is there a native approach for such things that I might not be aware of?

 

I dunno about that whole approach... I suppose you could meet with success this way but it certainly seems a lot harder than simply learning how to use Unity the way everybody else does. Unity has structured storage for ANYTHING and all of it is treated like a First Class Citizen in the editor, with full inspectability and serialization.

Even if you had the ultimate database-to-unity pipeline already set up, you would still need to learn Unity to be effective. Games are far more than just a stream of SQL queries.

Put SQL out of your mind for now, use this approach understand how Unity works. THEN decide if you still want your data in SQL.

Imphenzia: How Did I Learn To Make Games:

Tutorials and example code are great, but keep this in mind to maximize your success and minimize your frustration:

How to do tutorials properly, two (2) simple steps to success:

Step 1. Follow the tutorial and do every single step of the tutorial 100% precisely the way it is shown. Even the slightest deviation (even a single character!) generally ends in disaster. That's how software engineering works. Every step must be taken, every single letter must be spelled, capitalized, punctuated and spaced (or not spaced) properly, literally NOTHING can be omitted or skipped.

Fortunately this is the easiest part to get right: Be a robot. Don't make any mistakes.
BE PERFECT IN EVERYTHING YOU DO HERE!!

If you get any errors, learn how to read the error code and fix your error. Google is your friend here. Do NOT continue until you fix your error. Your error will probably be somewhere near the parenthesis numbers (line and character position) in the file. It is almost CERTAINLY your typo causing the error, so look again and fix it.

Step 2. Go back and work through every part of the tutorial again, and this time explain it to your doggie. See how I am doing that in my avatar picture? If you have no dog, explain it to your house plant. If you are unable to explain any part of it, STOP. DO NOT PROCEED. Now go learn how that part works. Read the documentation on the functions involved. Go back to the tutorial and try to figure out WHY they did that. This is the part that takes a LOT of time when you are new. It might take days or weeks to work through a single 5-minute tutorial. Stick with it. You will learn.

Step 2 is the part everybody seems to miss. Without Step 2 you are simply a code-typing monkey and outside of the specific tutorial you did, you will be completely lost. If you want to learn, you MUST do Step 2.

Of course, all this presupposes no errors in the tutorial. For certain tutorial makers (like Unity, Brackeys, Imphenzia, Sebastian Lague) this is usually the case. For some other less-well-known content creators, this is less true. Read the comments on the video: did anyone have issues like you did? If there's an error, you will NEVER be the first guy to find it.

Beyond that, Step 3, 4, 5 and 6 become easy because you already understand!

Finally, when you have errors, don't post here... just go fix your errors! Here's how:

Remember: NOBODY here memorizes error codes. That's not a thing. The error code is absolutely the least useful part of the error. It serves no purpose at all. Forget the error code. Put it out of your mind.

The complete error message contains everything you need to know to fix the error yourself.

The important parts of the error message are:

- the description of the error itself (google this; you are NEVER the first one!)
- the file it occurred in (critical!)
- the line number and character position (the two numbers in parentheses)
- also possibly useful is the stack trace (all the lines of text in the lower console window)

Always start with the FIRST error in the console window, as sometimes that error causes or compounds some or all of the subsequent errors. Often the error will be immediately prior to the indicated line, so make sure to check there as well.

Look in the documentation. Every API you attempt to use is probably documented somewhere. Are you using it correctly? Are you spelling it correctly?

All of that information is in the actual error message and you must pay attention to it. Learn how to identify it instantly so you don't have to stop your progress and fiddle around with the forum.

 

Well, writing SQL queries is also code you have to write. Also you can not simply "cast" the result to a certain class. An SQL interface may provide automatic deserialization into concrete classes, but that's not casting.

In C# you have LINQ (Language Integrated Query) which provides convenient methods to work with and filter collections on the fly. LINQ not only provides many extension methods that most people are familiar with, it even introduces a new syntax that is quite similar to SQL, but more specific to the C# usecase.

LINQ is widely used in business application but is sparsely used in games as it comes with some performance and garbage overhead.. In most cases you get way better results by using dictionaries when you have a large number of objects you need to look up. When a database creates an index for a certain column, that's actually quite similar to creating a dictionary with that value as key.

 

LINQ (Query Syntax):

Code (csharp):

1. Gun[] guns = ...
2.  
3. Gun[] gunsTrigger = (from g in guns
4.                      where g.TriggerHeld == true
5.                      select g).ToArray();

LINQ (Method Syntax):

Code (csharp):

1. Gun[] gunsTrigger = guns.Where(g => g.TriggerHeld == true).ToArray();

 

My bad, I indeed meant "automatic deserialization". I've worked with LINQ before, I believe to filter and order data.

I appreciate your contribution, but It's the limitation's of that approach that I'm not completely happy with. The developers of Pillars of Eternity state that the way this approach works in Unity is one of the reasons their game has such a slow inventory UI. There are thousands of different items with individual icons and descriptions. Sure, I could make a thousand Scriptable Objects and store them all in a folder. But I would then have to assign each one manually to something exposed in the Editor using the "standard" approach. Not going to work well for my purposes.

Since I have a background in web development, I'm used to automatically serializing of query results to search / filter data and was just wondering if Unity has similar options. LINQ seems to do what I want, albeit at a cost.

Ryiah's example code does exactly what I would want, as long as I could generate Gun[] without having to assign every Gun in the Editor. Imagine there are three thousand individual Guns, and they would all need to be placed inside this Gun[] array, so the "standard" method of assigning things in Editor fields becomes a problem. On top of that:

...I would want to use more then just a index as primary key. In Ryiah's example, TriggerHeld is a boolean property. But I would also want to be able to filter the Guns based on (for example) a BulletSize, which corresponds to an entry in another table (that contains Bullets which have a a property called Size). In SQL you'd refer to the ID of the Bullet. I was hoping such a methodoloy could be implemented in Unity.

 

You don't need to load SOs manually. Just put all of them in a special folder called "Resources", then you can call Resources.LoadAll<YourSoType>() which will return an array of all your SOs. You can then transform that array and index it as you wish/need (using Dictionaries for eg. for very quick access, a lot faster than any SQL db can offer).

That's exactly what I'm doing in my game. I created a ResourcesManager which loads all the necessary resources, and offers all the functionalities I need for querying the data I need, with 0 manual work. Preloading hundreds of SO is very fast (less than a half second here), the only possible downside you may find with this method is memory consumption, as you'll need to preload everything in memory, but I don't think it'll be a problem for 99% of the games we're making here plus the current modern hardware with plenty of RAM available, and this can be fixed with Addressables or Assets Bundles.

There is a reason why nearly no game (even AAA) uses a relational db, it's just an overkill, and in most cases, a lot slower than preloading all data in memory.

Gamers are used to loading screens, use that as an advantage.

 

Be careful with this reasoning:

"PoE used system X said that was why their inventory was slow. Therefore I must use SQL."

I highly doubt that even if PoE had one million unique inventory items that it would taken even the slightest tiniest bit of time to load that data up.

Without even seeing the PoE codebase, just going from general performance rules of thumb, loading time is almost certainly just from streaming textures off disk.

That's not gonna change by using SQL.

 

Well, it did. But I'll take the warning to heart, it wasn't the best example on why I want to use something like a relational database. I believe the issue was with their usage of many small images in the Resource folder and not with the data itself. I understand I won't get better performance. What I am after is building relationships between instances of one Collection with instances of the other Collection without assigning them manually in an editor. I want to use a dataset I can use for more then just the Unity project, so that's why I was hoping something like a DB was more commonly used.

That sounds like an acceptable middle road. It still won't allow me to supply the data from an external source and I would still need to configure relationships in the Editor itself, but perhaps I can make the SO's the single source of truth I desire instead of an external database. Perhaps even export JSON for other sources to consume, basically working the other way around.

 

Using SQLite for handling save data is not unheard of but can get you in trouble easily for various reasons.

It feels like the approach you are taking is odd.
Game dev and backend dev are very different domains, trying to shoehorn workflows you are used to from one in the other without deeply understanding the common patterns in the new domain is not a good practice.

Using relational patterns to manage runtime objects is going to lead very hard to read and maintain code at best.
You will have to interact with your middleware, in this case unity, which does not work this way.
Also the type of relationships runtime objects in game dev have are typically very different than what you would expect in web backend.

In the context of the gun example above, you would not want to check for every object ever in your project to see if a trigger signal is available, typically you will have a player object running an update loop triggering/checking the specific referenced object and nothing else.

In case of a large inventory, you typically want to load everything you will eventually need in update loops into memory beforehand and optimize your data layout.
If you really require iterating over many objects look into the job system and write an efficient job for that.

The performance of this will be much higher than any third party database system optimized to deal with data stored on disk.

 

I understand. That is why I am asking questions here first. I fully understand that at when it comes to using guns, you'd typically only check the specific referenced object for it's trigger type. I was interested in a way to define guns and then load them all into memory with their relationships (which don't change during gameplay) intact. I had hoped I could have used a DB to store this information, so multiple people could work on the data during development by creating new DB records through an online interface. The idea is not to reference this DB directly during runtime. The idea was to define properties in a constrained DB so multiple people could work on the data in a collaborative but controlled effort. That way objects would have guaranteed unique ID's and can refer to one another using said IDs.

Defining everything in SO's means one developer at a time can go into the Editor to add or modify the data. I was more or less looking for a way around that, since games are still just desktop applications. I used DB's all the time when I still used Abobe Air and the same question on the Unreal forum was answed with "yeah there's a plugin for that". So I'm just a bit surprised people are dissuading me from using a DB this strongly, but I will take it to heart.

 

For storing persistant data during development also use ideally 1 file per data blob, like unity scriptable objects or json files.

The problem here is that you will have to be compatible with your VCS, typically git, and you want to use a text based format to be able to merge conflicts between different developers.

A SQLite db stored inside your project is not going to be able to handle merges or reverts properly.

 

All of these can be achieved using a simple Google Spreadsheet or other collaborative, ready to use solutions. When you think it's time to refresh your game's data (entering playmode and build) just download the data and convert to a proper format (probably JSON in the editor for debugging and binary serialized in build for speed).
I know you're a web developer and it is tempting to spend a lot of time around these, but you shouldn't. You're making a game, do that instead of one-shot web applications. Unless you do something very special and you will be using those tools for years, don't worth the effort.

Edit:
oh and the "oh only one person can edit things locally" is usually an illusion. If you're using SOs for example, you can use a proper version control system and lock and/or check out files so other people won't be able to edit it temporarily. Also the argument is that you have to touch all of your data is true no matter what solution you end up with.
If you want a nice way to handle inventory data management, check out Odin, it has an example building inventory editor on SO base, makes it super-easy.

 

Okay, I'm going to admit it, I was lazy and passed the quoted text to GPT-4. I made exactly one change consisting of converting

Console.WriteLine

to

Debug.Log

.

Code (csharp):

1. public class Gun
2. {
3.     public int Id { get; set; }
4.     public bool TriggerHeld { get; set; }
5.     public int BulletId { get; set; }  // Foreign key to Bullet table
6. }
7.  
8. public class Bullet
9. {
10.     public int Id { get; set; }
11.     public string Size { get; set; }
12. }

Code (csharp):

1. var bulletSize = "Large";  // or whatever size you want to filter on
2.  
3. var query = from gun in guns
4.             join bullet in bullets on gun.BulletId equals bullet.Id
5.             where gun.TriggerHeld == true && bullet.Size == bulletSize
6.             select new { Gun = gun, Bullet = bullet };
7.  
8. foreach (var item in query)
9. {
10.     Debug.Log($"GunId: {item.Gun.Id}, BulletSize: {item.Bullet.Size}");
11. }

 

Excellent point: this is also a huge limitation of a giant SQL database because you won't have any idea which commit broke your Epic Sword Of Great Epicness and made it into a Splintered Broomstick.

You will spend probably 10x as much time debugging your game as actually creating it. Creating it is trivial. Debugging is where the real work is. Part of debugging is good source control forensics.

Another great point: We use google sheets all the time for collaborative design inputs, such as tables of strings, powerups, dialog, etc. Inventories would be just fine in sheets too. Just be sure to sanitize your external data! Don't let a simple extra linefeed or a blank field to bring your game down in flames.

We still "suck down" the spreadsheet into internal data formats, such that within the project we have a trackable repeatable CI/CD repository, not an unholy hodgepodge of git simultaneously with a spreadsheet, which could easily get out of sync with branching, which the spreadsheet (at least externally) does not support.

 

It hasn't changed. I know you want it to be different but it isn't.

You have a bog-boring-standard yawner of a nullref. Go fix it.

The answer is ALWAYS the same... ALWAYS!

How to fix a NullReferenceException error

https://forum.unity.com/threads/how-to-fix-a-nullreferenceexception-error.1230297/

Three steps to success:
- Identify what is null <-- any other action taken before this step is WASTED TIME
- Identify why it is null
- Fix that

 

I think I renamed the offending FireMode SO and I shouldn't have. It caused a Type mismatch and cleared the Editor fields, causing the NullReferenceError.

Code (CSharp):

1. public void Test()
2.     {
3.         DefGun[] gunsWithHoldFireMode = gundefs.Where(g => g.fireMode.title == "Hold").ToArray();
4.         Debug.Log(gunsWithHoldFireMode);
5.         foreach (DefGun def in gunsWithHoldFireMode)
6.         {
7.             Debug.Log(def.name + " has Hold!");
8.         }
9.     }

The LINQ approach works to filter SO's through the properties of other SO's. Nice. Thank you @Ryiah, as the relationships were most important to me. I understand your example used simpler classes, but I wanted to test whether or not SO's would work similarly and they did.