How to setup SQLite database with EF Core and Godot
EF Core is a popular high level object relational mapper (ORM) which can have various databases at the backend.
As a database noobie, I recently had a bit of a trouble setting it up with Godot and SQLite. There was very little practical information available from a beginner’s perspective for setting it up successfully with Godot.
I hit a dead end several times trying to follow the official MS docs and bunch of tutorials, but eventually with a help from a friend got it working.
So I thought I might as well write a quick article about it - from my own unique database noob perspective. In case someone else is having the same challenges.
If you’re looking for educated, accurate, precise and scientific description of how this piece of… technology… works, avert your eyes and never return!
At the time of writing this, Godot 4.2.2 has been out for a while and Godot 4.3 hasn’t yet been released. I’m running on .NET 8 and using C# 12. These instructions have been written for that in mind and specifically for Rider IDE. If you’re dealing with another IDE or the command line, you will have to figure those parts out for yourself.
Setting up a class library
Since we’re going to use the Migrations feature of EF Core, to get things working properly with Godot’s SDK, you’re going to need to create your database as a separate project, as a class library.
To do that, right click your solution in Rider’s solution explorer and select Add > New Project and make it a Class Library. It will be created directly under your main project for easy access. You can call it “Database”, or what ever you like.
Linking
To be able to do anything with the Database project it needs to be linked with your main project. So right click in Rider’s solution explorer your main project and select Add > Reference, select the box for Database and click the Add button. Done.
Dealing with AssemblyInfo duplicate attributes
You might have noticed that there’s now a funny new folder in your main project, which is from the Database project. You might be tempted to exclude it, and if you do, you’re on your own dealing with potential issues that might arise from that.
In this tutorial we’re going to edit the your main project’s .csproj file and add inside the <PropertyGroup></PropertyGroup> tags near the top the following mystical tags:
<GenerateAssemblyInfo>false</GenerateAssemblyInfo>
And its good buddy:
<GenerateTargetFrameworkAttribute>false</GenerateTargetFrameworkAttribute>
And by doing this magical ritual you will get rid of compilation errors caused by not excluding the aforementioned folders from your Database project - by doing which you will avoid another set of problems.
Just do it. You can change your setup later on your own if you’re not happy with it. Let’s first get things working.
Dealing with NuGet
The three packages you need to install from NuGet are:
Microsoft.EntityFrameworkCore.SqliteMicrosoft.EntityFrameworkCore.DesignMicrosoft.EntityFrameworkCore.Tools
You will need to add these to your Database project. You will likely not need them in your main project.
Let’s add some classes
We’re going to need some code to be able to do anything with the database.
SaveGameManifest.cs
You can arrange your projects how ever you like, but I recommend you create in the Database project a folder named DatabaseSets. This folder will hold your C# classes which essentially represent your database tables.
For fun let’s add SaveGameManifest.cs file in that folder which will represent the data in your table. It’s basically a schema or “entity”. It’s the stuff you want to store in the database.
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;
namespace Database;
[Table("SaveGameManifest")] // Name of the table in the database for this class.
[PrimaryKey(nameof(DatabaseId))] // They key you can directly fetch the data with.
public sealed class SaveGameManifest {
// The primary Guid based key to access this save game manifest.
[Column(nameof(DatabaseId))] // The database column name.
public Guid DatabaseId { get; set; }
// The name of the save game as inputted by user.
[Column(nameof(SaveName)), MaxLength(128),] // EF Core doesn't like strings of unrestricted length.
public string SaveName { get; set; } = string.Empty;
// The date when the save game was last saved.
[Column(nameof(LastSaved))]
public DateTime LastSaved { get; set; } = DateTime.Now;
// Required for database. Do not remove.
public SaveGameManifest() {}
public SaveGameManifest(Guid databaseId, string saveName) {
DatabaseId = databaseId;
SaveName = saveName;
}
}
And that’s it. We decorated our class and properties with attributes which instruct EF Core what everything is, ie. how it should be saved in the database. SQLite database is basically just a fancy a big Excel sheet. Tables tell you what type of stuff is in there in general (they are kind like file system folders), and Columns tell you what should go in here, and rows of the columns contain the actual data, data, data and more data.
DatabaseConnection.cs
I like to name the DbContext class DatabaseConnection, it makes it easier for me to comprehend what is going on.
There are lots of ways you could set up this file, so don’t be mistaken that this is the only way or even necessarily a good way. This is the database noob way that I came up with, the way which made sense to me personally.
This class essentially represents your database / gives you access to everything.
So in my setup you would call DatabaseConnection.Instance.SaveGameManifests, for example, to gain access to the SaveGameManifest tables via the DbSet. And you might call DatabaseConnection.Instance.SaveChanges() to save the database after making changes, etc.
using Database;
using Microsoft.EntityFrameworkCore;
namespace YourMainProjectNameSpace;
// We need to inherit DbContext. This allows us to connect to the database.
public sealed class DatabaseConnection : DbContext {
// I set this up as a singleton. But you can do what ever you like.
private static DatabaseConnection _instance;
/*
To be able to pass the user folder path to our database (where our database
is saved) I created a Configure method which must be called before using
the database for the first time. This is all about that.
*/
private static bool _isConfigured = false;
public static void Configure(string userFolder) {
if (_instance == null) {
_instance = new DatabaseConnection(userFolder);
_isConfigured = true;
}
}
public static DatabaseConnection Instance {
get {
if (!_isConfigured) throw new InvalidOperationException("DatabaseHandler is not configured. Call Configure() before accessing Instance.");
return _instance;
}
}
// These are used in setting up the paths.
private string _userFolder;
private string _databaseFolder = "database";
private string _databasePath = "database.db";
private string _fullDatabaseFilePath;
/*
DbSets contain essentially the tables of the database.
You should probably have all your tables defined here in this class,
Instead of trying to spread them around or anything like that. I hear it's
a common practice, so it should be good enough for you too.
So below here, in production code, would be a big list of DbSet<T>s.
*/
public DbSet<SaveGameManifest> SaveGameManifests { get; set; }
// Again, empty constructor is needed or everything fails to work.
public DatabaseConnection() {}
/*
And here is the constructor that we use to build things up.
Notice that we take in the user:// folder path here. But if you prefer
an alternate method, do what ever you like.
*/
public DatabaseConnection(string userFolder) {
_userFolder = userFolder;
// Console.WriteLine("Initializing DatabaseHandler.");
// Generate the file paths.
GeneratePaths();
// Console.WriteLine("Database file paths generated.");
// Attempt to create the database file if it doesn't exist yet.
TryCreateDatabaseFile(_fullDatabaseFilePath);
}
/*
This bit of code is needed to configure that we're using SQLite and set the path.
Feel free to throw in more config options if you need them.
*/
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
optionsBuilder.UseSqlite($"Data Source={_fullDatabaseFilePath}");
}
private void GeneratePaths() {
//Console.WriteLine("Generating database file paths.");
// Get the full path to the database file.
_fullDatabaseFilePath = Path.Combine(_userFolder, _databaseFolder, _databasePath);
}
/// <summary>
/// If the database file does not exist, create it.
/// </summary>
private static void TryCreateDatabaseFile(string fullDatabaseFilePath) {
//Console.WriteLine("Trying to create the database file.");
// If the database exists already, we don't need to create it.
bool databaseExists = File.Exists(fullDatabaseFilePath);
if (databaseExists) return;
//Console.WriteLine("Database does not exist. Trying to create a new SQLite database file.");
// Try to create the database folder first and then the database file.
try {
string fullFolderPath = Path.GetDirectoryName(fullDatabaseFilePath);
Directory.CreateDirectory(fullFolderPath ?? throw new InvalidOperationException());
File.Create(fullDatabaseFilePath).Close();
} catch (Exception e) {
Console.WriteLine(e);
throw;
}
// Check if the database file exists. It should.
if (!File.Exists(fullDatabaseFilePath)) {
throw new Exception("Failed to create the database file.");
}
//Console.WriteLine("SQLite database file created successfully.");
}
}
Main.cs
In our test this file resides in your main project - or where ever, but what I’m trying to say, it’s not in the Database project in this example.
The Main.cs represents some kind of controller or other class which does something practical with the database.
For our purposes we simply want to output something to see that everything works.
using Database;
using Godot;
using Microsoft.EntityFrameworkCore;
using YourMainProjectNamespace.Database; // (Unless you excluded the database folder)
namespace YourMainProjectNamespace;
// Oh yes, we're likely in Godot now doing something fun with the database.
public partial class Main : Node3D {
/*
In this example we store a reference to the connection, but normally you might
just use a using block for the duration of the database operation you want
to perform. You can do it both ways depending on the requirements.
*/
private DatabaseConnection _databaseConnection;
public override async void _Ready() {
/*
Configure the database connection. We need to run configure
and pass the user data directory from Godot to the database
class library before we can access the instance.
(Simply because we happened to code it that way in this example.
You do what you like. It doesn't matter.)
*/
DatabaseConnection.Configure(OS.GetUserDataDir());
_databaseConnection = DatabaseConnection.Instance;
try {
// Run migrate to create/update the database and tables if they don't exist/aren't up-to-date.
await _databaseConnection.Database.MigrateAsync();
// Now we can use the database after we've run Migrate. Not before.
// Add test data to the database so we have something to show.
await AddTestDataEntriesAsync();
// Do something for fun.
await RunDatabaseTests();
} catch (Exception e) {
GD.PrintErr(e);
throw;
}
}
/// <summary>
/// Add test data to the database.
/// </summary>
private async Task AddTestDataEntriesAsync() {
// Instantiate test data.
SaveGameManifest saveGameManifest = new (Guid.NewGuid(), "Test Save Game");
// Add test data to database.
await DatabaseConnection.Instance.AddAsync(saveGameManifest);
// Save changes to database to disc.
await DatabaseConnection.Instance.SaveChangesAsync();
}
/// <summary>
/// Run database tests to see if it's working.
/// </summary>
private async Task RunDatabaseTests() {
// Get data from database and output it.
Console.WriteLine("Fetching SaveGameManifests from database...");
// Fetch all SaveGameManifest entries from the database table.
DbSet<SaveGameManifest> results = DatabaseConnection.Instance.SaveGameManifests;
// Output the results.
Console.WriteLine("Results:");
await foreach (SaveGameManifest manifest in results.AsAsyncEnumerable()) {
Console.WriteLine($"DatabaseId: {manifest.DatabaseId}, SaveName: {manifest.SaveName}, LastSaved: {manifest.LastSaved}");
}
Console.WriteLine($"Results count: {results.Count()}");
}
}
Database tab
At this point you might like to, or need to, connect the database in the database tab in Rider. Among other things this will allow you to inspect the contents of the SQLite database, which can be very helpful in debugging.
Migrations
Now that we’re done with all of that, create a Migrations folder in the Database project.
Right click on the Database project in the Solution Explorer and there should be entry for Entity Framework Core near the top of the context menu that pops open.
Click Add Migration.
The name can be anything you like which helps you understand what the migration is.
Migration project and Startup project should be Database.
DbContext class should be DatabaseConnection.
The Migrations folder has to point to the Migrations folder you created.
Target Framework should probably be set to the .NET version you’re using.
Everything else can be at defaults. Hit OK.
If all goes well you should see DatabaseConnectionModelSnapshot.cs file being generated and also a file called something like 20240702182449_Initial.Designer.cs.
You should now have a fully functional setup ready for running.
How to use migrations?
Every time you’ve changed the schema, ie. your tables, columns, added new types, removed columns, tables, changed types, changed names, etc. In other words every time your data definitions change and your database types don’t anymore reflect your types in code, you should run Entity Framework Core > Add Migration.
This will update the migration path of your data and tell your database how it should update and change between different versions.
Problems
The database will be created in Godot’s user:// folder. If you have trouble finding it you can open Godot and go to Editor > Open Editor Data Folder and look it up from there.
You can always just delete the database file and delete the Migrations files when testing things. Don’t do that in production when you’ve released something. You should only add new migrations then, and never remove them, if they have already been released out to the wild.
Unless I forgot something or you’ve got a different kind of setup, these instructions should more or less work. I did not, however, specifically go out of my way to test these instructions step by step to insure that they work on a clean system. I wrote the instructions from memory. So there may be factual errors. If that’s the case, you can contact me on Star and Serpent’s Discord. The link is on Star and Serpent’s home page.