Godot-SQLite
2

Publisher

2shady4u

Godot-SQLite

Misc
SQLite GDNative Database Storage CrossPlatform Mobile HTML5 Management

GDNative wrapper for SQLite (Godot 4.X+), making it possible to use SQLite databases as data storage in all your future games.

Download Latest (4.5)
Source Repository

NOTE: This branch is compatible with Godot 4.x. Older versions of Godot (3.x) are supported on the godot-3.x-branch as found here.

godot-sqlite

This GDNative script aims to serve as a custom wrapper that makes SQLite3 available in Godot 4.0+. Additionally, it does not require any additional compilation or mucking about with build scripts.

Supported operating systems:

  • Mac OS X (universal)
  • Linux
  • Windows
  • Android (arm64)
  • iOS (arm64)
  • HTML5 (stability depends on browser)

How to install?

Re-building Godot from scratch is NOT required, the proper way of installing this plugin is to either install it through the Asset Library or to just manually download the build files yourself.

Godot Asset Library

Godot-SQLite is available through the official Godot Asset Library, and can be installed in the following way:

  • Click on the 'AssetLib' button at the top of the editor.
  • Search for 'godot-sqlite' and click on the resulting element.
  • In the dialog pop-up, click 'Download'.
  • Once the download is complete, click on the install button...
  • Once more, click on the 'Install' button.
  • Activate the plugin in the 'Project Settings/Plugins'-menu.
  • All done!

Manually

It's also possible to manually download the build files found in the releases tab, extract them on your system and run the supplied demo-project. Make sure that Godot is correctly loading the gdsqlite.gdextension-resource and that it is available in the res://-environment.

An example project, named "demo", can also be downloaded from the releases tab.

How to use?

Examples of possible usage can be found in the supplied demo-project as downloadable in the releases tab or the source code can be directly inspected here.

Additionally, a video tutorial by Mitch McCollum (finepointcgi) is available here:

Variables

  • path (String, default="default")

    Path to the database, should be set before opening the database with open_db(). If no database with this name exists, a new one at the supplied path will be created. Both res:// and user:// keywords can be used to define the path.

  • error_message (String, default="")

    Contains the zErrMsg returned by the SQLite query in human-readable form. An empty string corresponds with the case in which the query executed succesfully.

  • default_extension (String, default="db")

    Default extension that is automatically appended to the path-variable whenever no extension is detected/given.

    NOTE: If database files without extension are desired, this variable has to be set to "" (= an empty string) as to skip this automatic procedure entirely.

  • foreign_keys (Boolean, default=false)

    Enables or disables the availability of foreign keys in the SQLite database.

  • read_only (Boolean, default=false)

    Enabling this property opens the database in read-only modus & allows databases to be packaged inside of the PCK. To make this possible, a custom VFS is employed which internally takes care of all the file handling using the Godot API.

  • query_result (Array, default=[])

    Contains the results from the latest query by value; meaning that this property is safe to use when looping successive queries as it does not get overwritten by any future queries.

  • query_result_by_reference (Array, default=[])

    Contains the results from the latest query by reference and is, as a direct result, cleared and repopulated after every new query.

  • last_insert_rowid (Integer, default=0)

    Exposes the sqlite3_last_insert_rowid()-method to Godot as described here.
    Attempting to modify this variable directly is forbidden and throws an error.

  • verbosity_level (Integer, default=1)

    The verbosity_level determines the amount of logging to the Godot console that is handy for debugging your (possibly faulty) SQLite queries.

    Level Description
    QUIET (0) Don't print anything to the console
    NORMAL (1) Print essential information to the console
    VERBOSE (2) Print additional information to the console
    VERY_VERBOSE (3) Same as VERBOSE

    NOTE: VERBOSE and higher levels might considerably slow down your queries due to excessive logging.

Methods

  • Boolean success = open_db()

    Open a new database connection. Multiple concurrently open connections to the same database are possible.

  • Boolean success = close_db()

    Close the current database connection.

  • Boolean success = query( String query_string )

    Query the database using the raw SQL statement defined in query_string.

  • Boolean success = query_with_bindings( String query_string, Array param_bindings )

    Binds the parameters contained in the param_bindings-variable to the query. Using this function stops any possible attempts at SQL data injection as the parameters are sanitized. More information regarding parameter bindings can be found here.

    Example usage:

    var column_name : String = "name";
var query_string : String = "SELECT %s FROM company WHERE age  target_path= target_name=libgdsqlite enable_fts5=yes


#### B. Using Github Actions

Update the `common_flags`-field of the `.github/workflows/build_var.json`-file, as follows:

```json
"common_flags": "enable_fts5=no"

"common_flags": "enable_fts5=yes"

Afterwards, push a new commit (containing this change) to the master-branch of your forked remote. This commit triggers a new workflow that re-compiles the plugin's binaries with FTS5 enabled.

6. Does this plugin support some kind of encryption?

Database encryption of any kind is not supported in this plugin. Nor are there any plans to support this feature in an upcoming or future release.

The addition of database encryption might be up for reconsideration if, and only if, a future release of SQLite introduces native support of this feature without requiring the purchase of a license.

A modification of this plugin, which allows encryption, was done by abcjjy by swapping out SQLite with SQLCipher and can be found here. Direct all issues and feature requests regarding this implementation to its own dedicated repository.

NOTE: The natively supported SQLite Encryption Extension (SEE) is not applicable as it requires the purchase of a license for the one-time fee of 2000$

How to export?

The exporting strategy is dependent on the nature of your database.

Read-only databases

If your database serves as static data storage i.e. there's no persistent dynamic data that needs to be saved, then your database is a read-only database. For example, a database that contains a table of monster descriptions and data (experience gained on kill, health points) probably never changes during normal gameplay.

In this case, the database can be packaged in the *.pck-file together with all other assets without any hassle.
To enable this behaviour following conditions need to be met:

  • The database file has to be added manually to the include_filter of your export_presets.cfg-file to package the file on export.
  • The connection has to be opened in read-only mode by setting the read_only variable to True.

You can also open databases in read-only mode that are not packaged, albeit under some restrictions such as the fact that the database files have to copied manually to user://-folder on mobile platforms (Android & iOS) and for web builds.

NOTE: The contents of your PCK file can be verified by using externally available tools as found here.

Read and write databases

NOTE: On mobile platforms (Android & iOS) and for web builds, the method discussed here is not possible and the contents of the res://data/-folder have to be copied to the user://-folder in its entirety instead (see FAQ above).

If your database serves as dynamic data storage i.e. there's persistent dynamic data that needs to be saved during gameplay, then your database is a read and write database. For example, a database that contains a table of character levels and experience dynamically changes whenever the player levels up and/or gains experience.

In this case, the database cannot be packaged in the *.pck-file as the contents of this file are static and cannot be dynamically modified during normal operation.

All *.db-files (and *.json-files if you choose not to include them in the *.pck) should preferably be part of the same folder. For example, in the case of the demo-project this is the data/-folder. During export this folder has to be copied in its entirety and placed alongside the executable that is created by Godot's export command line utilities.
To simplify and automate this process, a script with the name export_data.gd can be found in the demo-project and allows to automatically copy the data-folder's contents to the export folder.

The commands to succesfully export a working executable for the demo-project are:

mkdir build
godot -s export_data.gd
godot --export-debug "Windows Desktop" "build/SQLite Demo.exe"

("Windows Desktop" should be replaced by whatever the name of your relevant build template is.)

For the "Windows Desktop" export target this results in following folder/file-structure:

data/test_backup_base64_old.json
data/test_backup_old.json
libgdsqlite.windows.template_release.x86_64.dll
'SQLite Demo.console.exe'
'SQLite Demo.exe'
'SQlite Demo.pck'

How to contribute?

Using GitHub Actions CI/CD

Fork the repository to your GitHub own account by following the recommended procedure.

Afterwards you can push your own code to the master-branch of this forked repository and GitHub Actions will automatically build new versions for all the supported platforms. These binaries then have to be downloaded and placed inside of the addons/godot-sqlite/bin/-folder, as found in your Godot project, alongside any existing binaries.

NOTE: Compiled binaries for the latest development version of godot-sqlite are available here.

Using your own device

First clone the project and initialize the godot-cpp-submodule as such:

git clone https://github.com/2shady4u/godot-sqlite.git
git submodule update --init

Secondly, SCons, Python and a viable C++ compiler have to be installed on your device. On Windows, both Visual Studio Community (2017+) and/or MinGW-w64 can be used to compile the plugin from scratch. More detailed instructions for compiling the plugin on Windows, Linux and other supported platforms can be found here in the Official Godot documentation.

Depending on the wanted target platform, some additional dependencies have to be installed:

  • In the case of Android, the Android NDK needs to be installed on a Linux host to allow building for Android targets.

Afterwards, the SContruct file found in this repository should be sufficient to build this project's C++ source code for Linux, Mac OS X, Windows, iOS and android with the help of following command:

scons platform= target_path= target_name=libgdsqlite

In the case of both Android and iOS, an additional arch-parameter has to be supplied to specify the CPU architecture. In the case of android, valid values for the architecture are 'arm64' (= default), 'arm32', 'x86_64' and/or 'x86_32', and in the case of iOS, the valid values are 'universal' (= default), 'arm64' and/or 'x86_64'.

Additionally, in the case of Android, the ANDROID_NDK_ROOT-parameter has to be set to the location of the Android NDK installed previously.

For uncertainties regarding compilation & building specifics, please do check out the .github\workflows\*.yml-scripts and the SConstruct-file as found in this repository.

Tutorials for making and extending GDExtension scripts are available here in the Official Godot Documentation.