Databases

MySQL

If you’d like to use MySQL it’s pretty simple, if you’re using ExtendedMode than you won’t have to follow these steps. ExtendedMode will handle as a database intergration itself.

Download esplugin_mysql and extract it to your resources folder
Make sure you have mysql-async properly working, or a equivelant database tool
Import the SQL file provided with this project, esplugin_mysql.sql (You can also use FXMigrant by uncommenting lines in fxmanifest.lua)
Add the following convar in your server configuration file: set es_enableCustomData 1 Make sure you put it directly under mysql_connection_string
Make your load order is correct. Below is an example

start mysql-async
start essentialmode
start esplugin_mysql

# Other EssentialMode addons below

CouchDB

If you’d like to continue using CouchDB then you can do so by setting the following convars before starting EssentialMode:

set es_couchdb_url "ip"
set es_couchdb_port port
set es_couchdb_password "username:password"
set es_defaultDatabase 2

Example

set es_couchdb_url "127.0.0.1"
set es_couchdb_port 5379
set es_couchdb_password "root:password"
set es_defaultDatabase 2

Your own database

EssentialMode has a system in place where you can literally use any database you’d like, you would have to write a conversion layer for this. Luckily EssentialMode exposes the needed events to make this happen!

-- es_db:firstRunCheck
AddEventHandler('es_db:firstRunCheck', function(ip, port) end)
 
-- es_db:doesUserExist
AddEventHandler('es_db:doesUserExist', function(identifier, callback) end)
 
-- es_db:retrieveUser
AddEventHandler('es_db:retrieveUser', function(identifier, callback) end)
 
-- es_db:createUser
AddEventHandler('es_db:createUser', function(identifier, license, cash, bank, callback) end)
 
-- es_db:retrieveLicensedUser
AddEventHandler('es_db:retrieveLicensedUser', function(license, callback) end)
 
-- es_db:doesLicensedUserExist
AddEventHandler('es_db:doesLicensedUserExist', function(license, callback) end)
 
-- es_db:updateUser
AddEventHandler('es_db:updateUser', function(identifier, new, callback) end)

SQLite

EssentialMode, by default, uses SQLite for its data storage (unless set es_enableCustomData is set or set es_defaultDatabase). The source of this is below.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CitizenFX.Core;
using System.Data.SQLite;
using System.Threading.Tasks;
using Newtonsoft.Json;
using System.IO;
 
namespace SQLite
{
    public class Main : BaseScript
    {
        SQLiteConnection database;
 
        public Main()
        {
            if (!System.IO.File.Exists("essentialmode.db"))
            {
                SQLiteConnection.CreateFile("essentialmode.db");
 
                database = new SQLiteConnection("Data Source=essentialmode.db;Version=3;");
 
                database.Open();
 
                string sql = "CREATE TABLE players (identifier STRING, license STRING, `group` STRING, bank INTEGER, money INTEGER, permission_level INTEGER, roles STRING)";
                SQLiteCommand command = new SQLiteCommand(sql, database);
                command.ExecuteNonQuery();
            }
            else
            {
                database = new SQLiteConnection("Data Source=essentialmode.db;Version=3;");
 
                database.Open();
            }
 
            // Event handlers
            EventHandlers["es_sqlite:createUser"] += new Action<string, string, int, int, string, int, string, dynamic>(CreateUser);
            EventHandlers["es_sqlite:updateUserData"] += new Action<string, string, dynamic>(UpdateUser);
            EventHandlers["es_sqlite:doesUserExist"] += new Action<string, dynamic>(DoesUserExist);
            EventHandlers["es_sqlite:retrieveUser"] += new Action<string, dynamic>(RetrieveUser);
        }
 
        private void CreateUser(string identifier, string license, int cash, int bank, string group, int permission_level, string roles, dynamic callback)
        {
            string sql = String.Format("INSERT INTO players(identifier, license, money, bank, permission_level, roles, `group`) VALUES('{0}', '{1}', {2}, {3}, '{4}', '{5}', '{6}')", identifier, license, cash, bank, permission_level, roles, group);
            SQLiteCommand command = new SQLiteCommand(sql, database);
 
            Task.Run(async () =>
            {
                await command.ExecuteNonQueryAsync();
 
                string json = JsonConvert.SerializeObject(new
                {
                    identifier = (string)identifier,
                    license = (string)license,
                    group = (string)group,
                    bank = (int)bank,
                    money = (int)cash,
                    permission_level = (int)permission_level,
                    roles = (string)roles
                });
 
                callback(json, true);
            });
        }
 
        private void UpdateUser(string identifier, string key, dynamic value)
        {
            string sql = String.Format("UPDATE players SET `{0}`='{1}' WHERE identifier='{2}'", key, value, identifier);
            SQLiteCommand command = new SQLiteCommand(sql, database);
 
            Task.Run(async () =>
            {
                await command.ExecuteNonQueryAsync();
            });
        }
 
        private void DoesUserExist(string identifier, dynamic callback)
        {
            string sql = String.Format("SELECT * FROM players WHERE identifier='{0}'", identifier);
            SQLiteCommand command = new SQLiteCommand(sql, database);
            SQLiteDataReader reader = command.ExecuteReader();
 
            if (!reader.HasRows)
            {
                callback(false);
            }
            else
            {
                callback(true);
            }
        }
 
        private void RetrieveUser(string identifier, dynamic callback)
        {
            string sql = String.Format("SELECT * FROM players WHERE identifier='{0}'", identifier);
            SQLiteCommand command = new SQLiteCommand(sql, database);
            SQLiteDataReader reader = command.ExecuteReader();
 
            if (!reader.HasRows)
            {
                callback(false);
            }
            else
            {
                while (reader.Read())
                {
                    string json = JsonConvert.SerializeObject(new
                    {
                        identifier = (string)reader["identifier"],
                        license = (string)reader["license"],
                        group = (string)reader["group"],
                        bank = (Int64)reader["bank"],
                        money = (Int64)reader["money"],
                        permission_level = (Int64)reader["permission_level"],
                        roles = (string)reader["roles"]
                    });
 
                    callback(json, true);
                }
            }
        }
    }
}

Last modified July 11, 2020: Fixed mysql async link as it was 404 (1a5d53b)