DBSync
The DBSync is used to sync character/player, vehicles, and other data to your FiveNet instance.
Reasons why you would use the DBSync are:
- You are not using the FiveM ESX framework.
- To run FiveNet in "standalone" mode independent of the gameserver.
- You would not be able to make the gameserver's database server reachable by FiveNet (e.g., restricted gameserver hosting, firewalls you can't change).
- You are using the FiveNet Cloud offering.
Installation
Configuration
The configuration file for the DBSync by default is called dbsync.yaml.
The DBSync process stores the "sync state" in a file called dbsync.state.yaml by default. This file is used to keep track of the last synced data, it should persisted as to not cause the DBSync to re-sync everything again.
To get started make sure that you have enabled the sync and configured a sync API token in your FiveNet's config.yaml file.
The destination: section is where you need to configure the URL to your FiveNet instance, example:
destination:
# Host + port to your FiveNet instance (requires HTTPS/valid TLS certs, unless insecure is set to `true`)
url: "fivenet.example.com"
token: "YOUR_SYNC_API_TOKEN"
# Disable TLS verification (not recommended)
insecure: false
# Sync Interval can also be specified per table in the `tables:` section
syncInterval: 5s
The source: section holds the database connection details of your gameserver database which holds the tables you want to sync to FiveNet.
# Changes to the source require a restart of the dbsync
source:
# Refer to https://github.com/go-sql-driver/mysql#dsn-data-source-name for details
# Please note that the `parseTime` parameter is allows set to true
dsn: "DB_USER:DB_PASS@tcp(DB_HOST:DB_PORT)/DB_NAME?collation=utf8mb4_unicode_ci&loc=Europe%2FBerlin"
The tables: section is used to configure the queries for the tables you want to sync to FiveNet. Please see the examples below for the ESX and QBCore frameworks.
Should you be using another framework, you will need to adjust the queries accordingly.
Database User for Source
The user only needs read access to the gameserver's database. Example queries to create a separate user and grant read access to the database (make sure to replace the username and password in the CREATE USER, and gameserver database name in the GRANT statement):
CREATE USER 'fivenet_dbsync'@'localhost' IDENTIFIED BY 'YOUR_DBSYNC_USER_PASSWORD';
GRANT SELECT ON `your_gameserver_db`.* TO 'fivenet_dbsync'@'localhost';
Table Queries
The queries for the tables must return the columns in the format specified per table, e.g., to return user info the format must be user.COLUMN (e.g., user.id, user.firstname, user.lastname).
An important point to note is that it is especially important to return a consistent id and identifier field for the users/characters.
The id field is used to identify the user in the database, and the identifier field is used to identify the user in FiveNet.
In addition to being used to "bind" multiple users/chars to one account, e.g., identifier char1:LICENSE and char2:LICENSE and the account only has the LICENSE set on it.
Usage
To run the DBSync process use the fivenet command/binary and run it like this fivenet dbsync .
In case you need to specify different config files, check the help menu via fivenet dbsync --help.
You should run the dbsync as a service on your server, e.g., on Linux via a systemd unit, Windows service.
Example Configs
ESX Framework
tables:
# The example queries here are for the ESX framework
jobs:
# Must return `job.name`, `job.label` columns
enabled: true
query: |
SELECT
`jobs`.`name` AS `job.name`,
`jobs`.`label` AS `job.label`
FROM
`jobs`
LIMIT $limit
OFFSET $offset;
jobGrades:
# Must return `job_grade.job_name`, `job_grade.grade`, `job_grade.label` columns (with condition for the `job_name`)
enabled: true
query:
SELECT
`job_grades`.`job_name` AS `job_grade.job_name`,
`job_grades`.`grade` AS `job_grade.grade`,
`job_grades`.`name` AS `job_grade.name`,
`job_grades`.`label` AS `job_grade.label`
FROM
`job_grades`
WHERE
`job_grades.job_name` = $jobName
LIMIT $limit
OFFSET $offset;
licenses:
# Must return `type`, `label` columns
enabled: true
query: |
SELECT
`license.type`,
`license.label`
FROM
`licenses`
LIMIT $limit
OFFSET $offset;
users:
# Must return `user.id`, `user.identifier`, `user.group`, `user.firstname`, `user.lastname`, `user.dateofbirth`, `user.job`, `user.job_grade`, `user.sex`, `user.phone_number` columns
# Optional columns: `user.height`, `user.visum`, `user.playtime`
enabled: true
# If your "players"/"users" data has a timestamp/datetime column that is updated on update,
# configure it here so that after the initial sync only the changed data is synced again.
#updatedTimeColumn: "last_seen"
# The identifier column should be a smart combination of values when using a multichar system.
# E.g., it is prefered to use a ESX like identifier layout for that case `charX:LICENSE`
query: |
SELECT
`users`.`id` AS `user.id`,
`users`.`identifier` AS `user.identifier`,
`users`.`group` AS `user.group`,
`users`.`firstname` AS `user.firstname`,
`users`.`lastname` AS `user.lastname`,
`users`.`dateofbirth` AS `user.dateofbirth`,
`users`.`job` AS `user.job`,
`users`.`job_grade` AS `user.job_grade`,
`users`.`sex` AS `user.sex`,
`users`.`phone_number` AS `user.phone_number`,
`users`.`height` AS `user.height`,
`users`.`visum` AS `user.visum`,
`users`.`playtime` AS `user.playtime`
FROM
`users`
$whereCondition
LIMIT $limit
OFFSET $offset;
# If a lastname is not returned by the query, attempt to split the firstname into "two" parts
splitName: true
# Normalize date of birth value to the output format if possible
# Please see the Go `time` pkg documentation on potential date format layouts: https://pkg.go.dev/time#Layout
dateOfBirth:
formats:
- "2006-01-02" # 2000-10-18 = YYYY-MM-DD
- "02/01/2006" # 09/06/1993 = MM/DD/YYYY
- "02.01.2006" # 01.08.1982 = DD.MM.YYYY
outputFormat: "02.01.2006" # DD.MM.YYYY
valueMapping:
# This allows to map values to different values for FiveNet
# E.g., the sex of a char must be either `m`, `f`, or `d`
sex:
fallback: 'm'
values:
'1': 'f'
userLicenses:
# Must return `user_license.type`, `user_license.owner` columns
enabled: true
query: |
SELECT
`user_licenses`.`type` AS `user_license.type`,
`user_licenses`.`owner AS `user_license.owner``
FROM
`user_licenses`
WHERE
`owner` = $identifier
LIMIT $limit
OFFSET $offset;
vehicles:
# Must return `vehicle.owner_id` or `vehicle.owner_identifier`, `vehicle.plate`, `vehicle.type`, `vehicle.model` columns
enabled: true
query: |
SELECT
`owned_vehicles`.`owner` AS `vehicle.owner_identifier`,
`owned_vehicles`.`plate` AS `vehicle.plate`,
`owned_vehicles`.`type` AS `vehicle.type`,
`owned_vehicles`.`model` AS `vehicle.model`
FROM
`owned_vehicles`
LIMIT $limit
OFFSET $offset;
QBCore Framework
tables:
jobs:
# Must return `name`, `label` columns
enabled: false
query: |
SELECT
`jobs`.`name` AS `job.name`,
`jobs`.`label` AS `job.label`
FROM
`jobs`
LIMIT $limit
OFFSET $offset;
jobGrades:
# Must return `job_name`, `grade`, `label` columns (with condition for the `job_name` (`$jobName`))
enabled: false
query: |
SELECT
`job_grades`.`job_name` AS `job_grade.job_name`,
`job_grades`.`grade` AS `job_grade.grade`,
`job_grades`.`label` AS `job_grade.label`
FROM
`job_grades`
WHERE
`job_name` = $jobName
LIMIT $limit
OFFSET $offset;
licenses:
# Must return `type`, `label` columns
enabled: false
query: |
SELECT
`licenses`.`type` AS `license.type`,
`licenses`.`label` AS `license.label`
FROM
`licenses`
LIMIT $limit
OFFSET $offset;
users:
# Must return `id`, `identifier`, `group`, `firstname`, `lastname`, `dateofbirth`, `job`, `job_grade`, `sex`, `phone_number` columns
# Optional `height`, `visum`, `playtime` columns
enabled: false
#updatedTimeColumn: "last_seen"
# The identifier column should be a smart combination of values when using a multichar system.
# E.g., it is prefered to use a ESX like identifier layout for that case `charX:LICENSE`
query: |
SELECT
`players`.`id` AS `user.id`,
CONCAT(`players`.`cid`, ':', `players`.`citizenid`) AS `user.identifier`,
'user' AS `user.group`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`charinfo`, '$.firstname')) AS `user.firstname`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`charinfo`, '$.lastname')) AS `user.lastname`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`charinfo`, '$.birthdate')) AS `user.dateofbirth`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`charinfo`, '$.height')) AS `user.height`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`job`, '$.name')) AS `user.job`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`job`, '$.grade.level')) AS `user.job_grade`,
JSON_UNQUOTE(JSON_EXTRACT(`players`.`charinfo`, '$.gender')) AS `user.sex`,
FROM
`players`
$whereCondition
LIMIT $limit
OFFSET $offset;
# If a lastname is not returned by the query, attempt to split the firstname into "two" parts
splitName: true
# Normalize date of birth value to the output format if possible
# Please see the Go `time` pkg documentation on potential date format layouts: https://pkg.go.dev/time#Layout
dateOfBirth:
formats:
- "2006-01-02" # 2000-10-18 = YYYY-MM-DD
- "02/01/2006" # 09/06/1993 = MM/DD/YYYY
- "02.01.2006" # 01.08.1982 = DD.MM.YYYY
outputFormat: "02.01.2006" # DD.MM.YYYY
valueMapping:
sex:
fallback: 'm'
values:
'1': 'f'
userLicenses:
# Must return `type`, `owner` columns
enabled: false
query: |
SELECT
`user_licenses`.`type` AS `user_license.type`,
`user_licenses`.`owner` AS `user_license.owner`
FROM
`user_licenses`
WHERE
`owner` = $identifier
LIMIT $limit
OFFSET $offset;
vehicles:
# Must return `owner_id` (DB User ID) or `owner_identifier`, `plate`, `type`, `model` columns
enabled: true
query: |
SELECT
IF(`player_vehicles`.job_vehicle=0, CONCAT(players.cid, ':', players.`citizenid`), NULL) AS `vehicle.owner_identifier`,
`player_vehicles`.`plate` AS `vehicle.plate`,
`player_vehicles`.`garage_type` AS `vehicle.type`,
`player_vehicles`.`vehicle` AS `vehicle.model`,
IF(`player_vehicles`.`job_vehicle`=1, `player_vehicles`.`citizenid`, NULL) AS `vehicle.job`
FROM
`player_vehicles`
LEFT JOIN `players` ON (`players`.`citizenid` = `player_vehicles`.`citizenid`)
LIMIT $limit
OFFSET $offset;