SQL Snippets

Common SQL patterns for FiveM databases. Review column sizes and constraints against the framework’s real identifiers before running migrations. Apply schema changes through versioned migrations, back up production data first, and parameterize every application query. Test both forward migration and restore procedures on a non-production copy.

User Table


CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(50) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    money INT NOT NULL DEFAULT 0,
    bank INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB; -- UNIQUE already indexes identifier

Items Table


CREATE TABLE items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    label VARCHAR(100) NOT NULL,
    weight DECIMAL(10,2) NOT NULL DEFAULT 0
) ENGINE=InnoDB;

Inventory Table


CREATE TABLE inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    item_name VARCHAR(50) NOT NULL,
    count INT NOT NULL DEFAULT 1 CHECK (count >= 0),
    UNIQUE KEY uq_user_item (user_id, item_name), -- one row per item; also indexes user_id
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (item_name) REFERENCES items(name)
) ENGINE=InnoDB;