LogoLogo
SupportServer Hosting
  • Sonoran CAD
  • 📱Download The App
    • Steam Browser - Workaround
    • Web Browser - Clear Cache
    • Desktop App - Clear Cache
    • GTA RP Tablet - Clear Cache
    • Steam Browser - Clear Cache
  • Why Choose Sonoran CAD?
    • 🏆Why Choose Sonoran CAD?
  • Other Products
    • 🖥️FiveM Hosting
    • 📝Sonoran CMS
    • 📡Sonoran Radio
  • Tutorials
    • Getting Started
      • Registering Your Account
      • Registering Your Community
      • Inviting Users to Your CAD
      • Granting Account Permissions
      • View Your Limits
      • Community ID and API Keys
      • Troubleshooting
    • Customization
      • Custom Domain & Login Page
      • Customizing Your Layout
      • Community Branding and Settings
      • Creating Custom Record and Report Types
      • Sharing Custom Records
      • Custom Search Types
      • Geographical Settings
      • Penal Codes
      • 10-Codes
      • Addresses and Street Names
      • Unit Status Codes
      • Tone Board
      • Custom Audio Notifications
      • Custom Emails
      • Quick Links
      • Custom Page Colors
      • User Account Restrictions
      • Configuring Multiple Servers
      • Advanced Query Strings
    • Dispatching
      • Creating a Call
      • Self Dispatch
      • Timers
      • Command Line Interface (CLI)
    • Records Management
      • Record Lookups
      • Adding a Record
      • PDF Records
      • Scene Diagram Builder
    • Administration
      • System Logs
      • Remove User Account
      • Deleting or Transferring your CAD
    • Other Features
      • Configurable Hotkeys
      • Voice Commands
      • Volume and Language
  • Integration Capabilities
    • 🗃️Paid Integration Resources
      • Mobile Command Center
      • ATM Robbery
      • In-Vehicle Radar
      • Evidence Camera
      • Power Grid
      • Speed Cameras
      • Shot Spotter
    • 🛠️Integrated Vendors
    • 📺Twitch Bodycam, Bot, & Lighting Integration
    • Database Sync and Merge
      • Database Sync Credentials
      • QB Core Mapping Tutorial
      • Troubleshooting
    • Discord Bot Integration
    • Discord Rich Presence
    • Discord Webhooks
    • TeamSpeak 3 Integration
    • Stream Deck Integration
    • 🎮FiveM
      • FiveM Installation
        • Submodule Configuration
          • Submodule Troubleshooting
          • Submodule Configuration Updates
        • FiveM Submodules
          • Body Camera
          • Call Commands
          • Check API ID
          • Civilian Integration
          • Dispatch Notify
          • Emergency Response Simulator (ERS)
          • Framework Support (ESX/QBCore) and Auto Fines
          • FivePD
          • ForceReg
          • Kick
          • Locations
          • Live Map
          • Lookups
          • Postals
          • Smart Signs
          • Sonoran Radio (sonrad)
          • Station Alert
          • Tablet & Mini-CAD
          • TeamSpeak 3
          • Traffic Stop
          • Unit Status
          • VehReg
          • WraithV2
      • FiveM Development Documentation
        • Client Functions
        • Shared Functions
        • Server Functions
        • Server Events
        • HTTP Request Handling
      • Error Codes
    • 🎮Roblox ER:LC
  • Developer API Documentation
    • 📖API Integration
      • Getting Started
        • Retrieving Your Credentials
        • Integration API ID
        • Account Secret ID
      • Push Events
        • Event Restrictions
        • Record Events
          • Record Added
          • Record Edited
          • Record Removed
        • Unit Events
          • Unit Panic
          • Unit Login
          • Unit Logoff
          • Unit Status Update
          • Unit Group Name Change
          • Unit Group Add
          • Unit Group Remove
        • Dispatch Events
          • New Dispatch
          • Modified Dispatch
          • Closed Dispatch
          • Dispatch Note
          • Attach Unit
          • Detach Unit
          • Toggle Bodycam
        • Event 911
        • Remove 911
        • Sign Updated
        • Tone Played
        • Civilian
          • Character Selected
        • ERS
          • New Callout
        • Station Alert
          • New Station Alert
      • API Endpoints
        • API Restrictions
        • Emergency
          • Identifiers
            • Get Identifiers
            • Modify Identifier
            • Set Identifier
            • Unit Panic
            • Kick Unit
            • Unit Status
            • Get Active Units
            • Update Unit Location
          • Dispatch and Emergency Calls
            • Get Calls
            • New Dispatch
            • Attach Unit
            • Detach Unit
            • Update Call Postal
            • Add Call Note
            • Update Call Primary
            • Close Dispatch
            • New 911 Call
            • Remove 911
          • Custom Blips
            • Get Map Blips
            • Add Blip
            • Modify Blip
            • Remove Blip
          • Street Signs
            • Set Street Sign Config
            • Update Street Sign
          • ERS
            • Set Available Callouts
          • Station Alert
            • Set Stations
        • Civilian
          • Set Character
          • Get Characters
          • New Character
          • Edit Character
          • Remove Character
          • DB Sync: Get Character Links
          • DB Sync: Link Character
        • General
          • Get Servers
          • Set Servers
          • Get Version
          • Set Penal Codes
          • Set API IDs
          • Custom Records
            • API Options for Adding and Modifying Records
            • Get Record Templates
            • New Record
            • Edit Record
            • Remove Record
          • Lookup By Value
          • Lookup Name or Plate
          • Get Account
          • Get Accounts
          • Check API ID
          • Apply Permission Key
          • Modify Account Permissions
          • Kick or Ban User
          • Verify Secret
          • Auth Street Signs
          • Set Postal Config
          • Set Clock Time
          • Send Photo
          • Leave Community
          • Join Community
        • Local Endpoints
          • Smart Lighting
        • Internal
          • FiveM Download
      • API Examples
        • Emergency
          • LUA Examples
            • Dispatch and Emergency Calls
              • New 911 Call
              • Remove 911
              • Add Call Note
              • Set Call Postal
            • Custom Blips
              • Add Blip
              • Add Blips
              • Add Temp Blip
              • Get Map Blips
              • Remove Blip
              • Remove Blip With Subtype
            • General
              • Lookup Name or Plate
          • JavaScript Examples
            • Dispatch and Emergency Calls
              • New 911 Call
              • Remove 911
              • Add Call Note
              • Set Call Postal
            • Custom Blips
              • Add Blip
              • Add Blips
              • Add Temp Blip
              • Get Map Blips
              • Remove Blip
              • Remove Blip With Subtype
            • General
              • Lookup Name or Plate
      • Development Branch
    • 💵Developer Bounties
    • 🌎Translation Support
  • Pricing
    • Pricing FAQ
      • Military and First Responder Discount
      • Expert Installation
      • Accessing the Payment Center
      • Create and Manage a Subscription
      • Request Discord Role
    • View and Compare Plans
  • Roadmap
    • 📋Changelog
  • Other
    • AI QA
    • Contact Us
    • Policy
      • Rocket CAD Closure
      • Terms of Service
      • Privacy Policy
      • Refund and Purchase Policy
      • Discord Bot
    • Archive
      • Fire Siren
Powered by GitBook
On this page
  • Video Configuration Tutorial
  • Written Configuration Guide
  • Connection Credentials
  • 1. Retrieve your SQL Connection Credentials
  • 2. Enable Database Sync and Merge
  • 3. Enter the required fields below.
  • 3. Test the Connection
  • Character Mapping
  • 1. What is DB Sync Mapping?
  • 2. Table Columns and Name
  • 3. Enable, Save and Test
  • License and Vehicle Mapping
  • 1. Table Columns and Names
  • 2. Enable, Save and Test
  • JSON Columns
  • 1. View the JSON Data
  • 2. Nested JSON Values
  • External Keys
  • Introduction
  • Example: DB Layout
  • Example: CAD Config
  • 2. Set the JSON Column and Key
  • Friendly Mapping
  • 1. Find Values to "Friendly Map"
  • 2. Configure the Friendly Mapping
  • Import via CSV
  • Import from JSON
  • Custom Record Fields
  • 1. Edit your Custom Record
  • 2. Configure the Newly Mapped Field

Was this helpful?

Edit on GitHub
  1. Integration Capabilities

Database Sync and Merge

Database Sync is a highly advanced feature allowing you to automatically pull all character, license, and vehicle registration data from your own in-game database directly to Sonoran CAD.

PreviousTwitch Bodycam, Bot, & Lighting IntegrationNextDatabase Sync Credentials

Last updated 18 days ago

Was this helpful?

Database Sync requires the plus version of Sonoran CAD or higher. Database Merge requires the pro version of Sonoran CAD.

For more information, see our or view how to check your community .

Looking for VPS, web, or dedicated hosting? Check out our official !

Database Sync is a highly advanced feature allowing you to automatically pull all character, license, and vehicle registration data from your own in-game database directly to Sonoran CAD.

This feature is specifically designed for frameworks like ESX and QBus/QBCore, but is compatible with any MySQL database.

Video Configuration Tutorial

Written Configuration Guide

Configuring Sonoran CAD's Database Sync may seem complicated, but you are simply specifying your table names and column values where your in-game data is stored.

REQUIRED fields in the CAD are shown in red. All other fields are optional and are not required to work properly.

Connection Credentials

In order for Sonoran CAD to connect to your SQL database, connection credentials must be configured properly.

1. Retrieve your SQL Connection Credentials

Sonoran CAD requires an external connection to your database.

View our guide on creating a new read-only SQL user for external use.

2. Enable Database Sync and Merge

Expand the SQL Connection Credentials section of the configuration, and toggle on Database Sync and the optional Database Merge feature.

What is Database Merge?

Database Merge is an additional feature that allows you to save off additional, manually specified data in the CAD.

Example:

DB Merge pulls a character record into the CAD, but the address field in your custom record is blank (your DB doesn't have this info). DB merge allows you to manually edit the record pulled from your database and edit any blank field.

Next time you look up this character, it will pull the same character info from your database and then merge this with the manually specified data saved in Sonoran CAD.

3. Enter the required fields below.

Field

Description

Type

This is the type of SQL database your server is using (MySQL, PostgreSQL, etc.)

Host/Address

This is the external IP address or domain address to your SQL database.

Port

This is the port your database is accessible from. Typically, the default port for MySQL is 3306.

Database

This is the database or schema name that contains your community’s character, license and vehicle registration tables.

Username

This is the SQL account username.

Password

This is the password for the SQL account.

3. Test the Connection

Once the required SQL connection fields have been specified, select the “Test Connection” button. This will query your database for the version. If you see an alert similar to the following, your connection is successful:

Character Mapping

Sonoran CAD requires character mapping to be properly configured for the additional license and vehicle registration mapping.

1. What is DB Sync Mapping?

The DB sync configuration is designed to show Sonoran CAD the specific tables and columns that data can be pulled from in your database.

2. Table Columns and Name

The table name field will contain the name of your database table containing character records.

The column name field will contain the name of the specific column in the character records table containing data for this row.

The Character Mapping Column contains a unique ID for the specific character. This unique identifier will also map records in your license and vehicle registration tables back to the character that owns them. Typically, this is a Steam ID or license ID.

Character records can also pull data from multiple different tables, such as a properties table to add address information. Just be sure that those additional tables have a proper identifier column to map back to the other characters table.

Example:

In our database, the characters table contains our character records. The identifier column contains the character's unique ID, the firstname column contains the characters first name, the lastname character contains the characters last name, etc.

3. Enable, Save and Test

Be sure that you have enabled character mapping via the toggle. Once your character mapping has been completed, hit the save button and then the test button. The test button will attempt to select a single random character with the mapping configuration specified.

If you see "Success!" move onto the next section.

License and Vehicle Mapping

Licenses and Vehicle registrations can also be automatically pulled via CAD search with Database Sync.

1. Table Columns and Names

Similar to the character mapping, specify the table name containing your vehicle registrations or licenses. Unlike character mapping, data for these records can only be pulled from a single table.

The vehicle and license mapping will also need to have the Character Mapping Column specified. Again, this is the name of the column in your license/vehicle table containing a unique ID that maps back to the character that owns it.

Example:

In our database, the owned_vehicles table contains our stored vehicles. The owner column contains the character's unique ID that owns the vehicle, and the plate column contains the vehicle's license plate.

2. Enable, Save and Test

Be sure that you have enabled the license/vehicle mapping via the toggle. Once your mapping has been completed, hit the save button and then the test button. The test button will attempt to select a single random license or vehicle with the mapping configuration specified.

If you see "Success!" move onto the next section.

JSON Columns

Many databases store data in a JSON formatted column. Sonoran CAD can also parse these columns for data.

1. View the JSON Data

In our database, the accounts column stores JSON formatted data. For this example, we want to display the bank money in the custom character record.

We can see that the JSON "key" for the bank account amount is bank.

2. Nested JSON Values

Nested JSON values are also supported. Here, the eye_color is a JSON object, with the item property of 0.

In the DB Sync config, we list the JSON key as eye_color.item

External Keys

Introduction

In some cases, your license or vehicle registration tables may not directly contain a Character Mapping ID column (a column with an ID that directly maps back to the character/civilian record), but may contain a unique ID that maps back to a specific character in another table.

Example: DB Layout

In this example, the vehicle table contains all of the vehicle information, but does not contain a CharacterID column. Instead, the vehicle table contains a VehicleRegistrationID column.

The VehicleRegistrationId column then maps to the vehicleregistration table. The vehicleregistration table then contains a corresponding CharacterId column, which maps back to the character table.

Example: CAD Config

In the CAD, the configuration is simple.

Toggle on the External Key checkbox, as the vehicle table's VehicleId needs to be mapped to an external table to be turned into the proper characterId.

Specify the external key's table (vehicleregistration) and the external key's column CharacterId.

2. Set the JSON Column and Key

Back in the mapping panel, we toggle the field as a JSON Column and set the column name to accounts as this is the column in our character table that contains the JSON data.

We can then set the JSON Key for this data as bank.

Friendly Mapping

Friendly Mapping requires the pro version of Sonoran CAD.

Friendly mapping allows you to convert any raw database value to a more user friendly value. Ex: drive_license in your database is converted to Driver's License.

1. Find Values to "Friendly Map"

MySQL tinyint column types may be displayed as numerical values 0 and 1 but be read as True and False by Sonoran CAD. Instead of mapping the "Database Vaue" as 0 or 1 you will need to map False and True.

In our SQL table, we can see the character's job columns has text values that can be improved. The taxi job value can be automatically converted to Taxi Driver in DB Sync records, and the cardealer jon can be automatically converted to Car Dealer.

2. Configure the Friendly Mapping

In our character table mapping, we can select Modify on the job field's friendly mapping.

In the editor, we can now map the raw database value of taxi to a friendly value of Taxi Driver and the raw db value of cardealer to Car Dealer.

Be sure to hit save in the friendly mapping editor, and then save the configuration for your mapping section.

Import via CSV

1. Copy the Google Sheet

You may ONLY use the Google sheet directly. Editing this via Excel or any other program is NOT supported.

2. Add your Friendly Mappings

Be sure to leave the top header line as it is. Below the header, add in your friendly mapping keys and values.

3. Download the CSV

In Google Sheets, navigate to File > Download > Comma Separated Values (.csv) to download the file.

4. Import the CSV File

In the friendly mapping editor select Import > CSV > Select your downloaded Google spreadsheet

Then, save the mapping and save the database sync config.

Import from JSON

You can also build and format your friendly mapping from raw JSON and paste them directly into the UI.

1. Format the JSON Structure

The JSON structure is an object array. Be sure to strictly follow the format.

[
  {
    "dbValue": "0",
    "friendlyValue": "Brown"
  },
  {
    "dbValue": "1",
    "friendlyValue": "Green"
  },
  {
    "dbValue": "2",
    "friendlyValue": "Blue"
  }
]

2. Import the JSON Structure

In the friendly mapping editor select Import > JSON > Paste your JSON formatted structure

Custom Record Fields

Sonoran CAD's records are entirely customizable, this includes database sync records! You can easily enable database sync mapping for any custom field you add to a character, license, or vehicle registration record.

1. Edit your Custom Record

Navigate to Admin > Customization > Custom Records

Select your custom character, license, or vehicle registration record to open the editor.

Simply add a field if the desired field doesn't already exist and note the Label name for the next step. For this example, we'll enable database sync for a new job field in our character's table.

Be sure to save your custom record format after enabling this!

2. Configure the Newly Mapped Field

Back in our database sync editor, we can now see the new Job field has been added. We can map this new field to our database as any other field.

View our on enabling Database Sync.

Note: This IP will never be localhost or 127.0.0.1. This must be the external IP or domain to reach your database.

If you see an error message, it's likely you have not or opened the database port for external use.

To more easily view the JSON data, we can copy it from the cell and paste it into a .

In the , we can paste it and select Format.

Then, we can use to convert the eye color 0 value to an actual color, like "brown".

For more information, see our or view how to check your community .

These new friendly mapped values will even work with !

Navigate to our and make a copy. Using a copy of our official sheet ensures your friendly mapping is formatted correctly.

video tutorial
Database Sync Credentials
properly setup your new SQL user's credentials
JSON formatter
JSON formatter
pricing
limits
custom search types
official friendly mapping Google Sheet
friendly mapping
Learn more about creating an external SQL connection.
pricing
limits
server hosting
Sonoran CAD - DB Sync SQL Credentials
DB Sync - Connection Successful
SQL Table Example
DB Sync Character Mapping Example
SQL Vehicle Table Example
DB Sync - Vehicle Mapping Example
SQL - JSON Column Example
Database - Nested JSON Value
Sonoran CAD - External Key DB Layout
Sonoran CAD - External Key
DB Sync - JSON Column
SQL Table - Unfriendly Values
DB Sync - Modify Friendly Mapping
Friendly Mapping Editor
Sonoran CAD - Copy Friendly Mapping CSV
Sonoran CAD - Edit Friendly Mapping CSV
Sonoran CAD - Download Friendly Mapping CSV
Friendly Mapping - Import via JSON
Custom Records - DB Sync Mapping Toggle
Database Sync - Custom Field Mapping