Using SQLite with Unreal

By Luke Simpson

June 25, 2024

Introduction

SQLite is a powerful database that can handle thousands of operations per second. My game reads from a SQLite database with ~500,000 rows multiple times every tick at 60 fps without any problem. In this post, I’ll explain how you can add the full power of a relational database to your game.

Enable the SQLite plugins

Go to Edit > Plugins and search for “sqlite”.

Check “SQLite” and “SQLite Support”. This will add the following content to your .uproject file:

{
    "Name": "SQLiteCore",
    "Enabled": true
},
{
    "Name": "SQLiteSupport",
    "Enabled": true
}

Add the necessary private dependencies to your <Game>.Build.cs file:
```
PrivateDependencyModuleNames.AddRange(new string[] { "SQLiteCore", "SQLiteSupport" });
```
This is so we can #include "SQLiteDatabase.h".

Adding the database to your project

For this, I will assume you have already created a database.

Open your project’s Content folder and add a new folder for your database. I called mine Databases. We won’t be able to import our database through the Unreal editor, so just open your file manager and drop it into that folder.
To ensure the folder gets packaged with your game, go to Edit > Project Settings > Packaging > Advanced > Additional Non-Asset Directories to Copy.
Add an array element, and type the name of your folder, e.g. “Databases”. This may add a bunch of entries to your DefaultGame.ini file, notably this line:
```
+DirectoriesToAlwaysStageAsNonUFS=(Path="Databases")
```

Add a C++ class to interface with the database

In the editor, go to Tools > New C++ Class.
Add an empty class for your database. I called mine EarthDatabase since my project is called Earth.
Since you added a new class, you may want to go to Tools > Refresh Visual Studio Code Project or regenerate project files so intellisense works again.

.h

We’ll need some private variables, a constructor, and a destructor. Here is what my EarthDatabase.h file looks like:


#pragma once

#include "CoreMinimal.h"

class FSQLiteDatabase;
class FSQLitePreparedStatement;

/**
 *
 */
class EARTH_API EarthDatabase
{
private:
	FSQLiteDatabase* CountriesDb;
	FSQLitePreparedStatement* GetCountryByIdStmt;

public:
	EarthDatabase();
	~EarthDatabase();

	FString GetCountryById(int id);
};

.cpp

In the .cpp include the header:

#include "SQLiteDatabase.h"

Initialize your database and prepared statement in the constructor:

EarthDatabase::EarthDatabase()
{
    FString dbPath = FPaths::ProjectContentDir() + "Databases/countries.db";
    CountriesDb = new FSQLiteDatabase();
	if (!CountriesDb->Open(*dbPath, ESQLiteDatabaseOpenMode::ReadOnly))
    {
        UE_LOG(LogTemp, Fatal, TEXT("%s"), TEXT("Couldn't open db."));
    }

    GetCountryByIdStmt = new FSQLitePreparedStatement();
	GetCountryByIdStmt->Create(*CountriesDb, TEXT("select name_en from country where id = ?1"), ESQLitePreparedStatementFlags::Persistent);
}

A UE_LOG of Fatal will crash the engine if the database can’t be opened. Since my game can’t work without a database, that is what I want to happen.

For our prepared statement, we set up a parameter binding with ?1. This will be replaced with whatever ID we pass in. You could also specify a named parameter with, e.g. @id.

If you want, you can use the returned bool from Create to do some error handling. Here’s what that might look like:

GetCountryByIdStmt = new FSQLitePreparedStatement();
if (!GetCountryByIdStmt->Create(*CountriesDb, TEXT("select name_en from country where id = ?1"), ESQLitePreparedStatementFlags::Persistent))
{
    UE_LOG(LogTemp, Log, TEXT("%s"), TEXT("Couldn't create prepared statement for GetCountryByIdStmt."));
}

Most commands return a bool like this to handle errors.

Now create the destructor:

EarthDatabase::~EarthDatabase()
{
    // Destroy prepared statements first.
    GetCountryByIdStmt->Destroy();
    delete GetCountryByIdStmt;

    if (CountriesDb->Close()) {
        delete CountriesDb;
    } else {
        UE_LOG(LogTemp, Warning, TEXT("%s"), TEXT("Couldn't close db."));
    }
}

It’s important to destroy any prepared statements first, otherwise the database will not close properly.

And here is my GetCountryById method:

FString EarthDatabase::GetCountryById(int id)
{
    FString value = "";

    GetCountryByIdStmt->Reset();
    if (!GetCountryByIdStmt->SetBindingValueByIndex(1, id)) {
        return value;
    }

    if (GetCountryByIdStmt->Step() == ESQLitePreparedStatementStepResult::Row) {
        GetCountryByIdStmt->GetColumnValueByIndex(0, value);
    }

    return value;
}

I initialize value with an empty string. If any data is returned from the query, it will be overwritten. If an error occurs, I just return the empty string.

If a prepared statement will be used many times, it’s important to reset it first. SetBindingValueByIndex with an index of 1 binds to ?1. If we had used the @id named parameter I mentioned before, we would set the binding like this: SetBindingValueByName(TEXT("@id"), id)).

Step() is how we get results from our query. If the step result was a Row, then we can proceed to get the column data from the row. Since I’m only expecting one row from this query, I used an if statement, however if you’re expecting multiple rows, use a while.

Since we only asked for one column, it’s easy enough to get the value at column index zero. If you had many columns, you could get the value by column name, e.g. GetColumnValueByName(TEXT("column_name"), value).

How do we use this thing?

Okay, so we have everything we need to connect to our database and retrieve data from it, but who should own it? In my case, I decided it would be the game mode, so that is what I’ll demonstrate.

Add a new private variable to your game mode:

YourDatabase* Db;

Initialize the database in PostInitializeComponents:

void AYourGameMode::PostInitializeComponents()
{
    Super::PostInitializeComponents();
    Db = new YourDatabase();
}

And now you can query the database using the method you defined earlier:

FString value = Db->GetValueById(5);

Now, it’s very important to call the destructor, otherwise the database will not close properly and attempting to rerun your game in the editor will cause a crash. We do this in the game mode’s EndPlay method:

void AYourGameMode::EndPlay(const EEndPlayReason::Type EndPlayReason)
{
    Super::EndPlay(EndPlayReason);
    Db->~YourDatabase();
}

As long as you properly close the database, you can load new levels and game modes that use the same database.

What about inserts, updates, and deletes?

It’s all done the exact same way with prepared statements. Just be sure to open the database for reading and writing with ESQLiteDatabaseOpenMode::ReadWrite. Instead of Step(), you can use Execute(), though either one will work. Use Execute() when no result state is required.

That’s it!

Support the blog! Buy a t-shirt or a mug!

Tags:

Unreal Engine