@pma-network/sql

MySQL wrapper built on mysql2 with promise-based async operations, connection pooling, and named parameter support.

Features

• Auto-configuration from environment variables
• Promise-based async operations
• Connection pooling
• Named parameters (:paramName) and positional (?) placeholders
• Full TypeScript support
• Transaction handling with automatic rollback
• Resource-specific connection strings for FiveM

Installation

pnpm add @pma-network/sql

Or with npm:

npm install @pma-network/sql

Quick Start

Set the connection string environment variable:

# server.cfg (resource-specific):
set pma_characters_connection_string "mysql://user:password@localhost:3306/fivem"

# server.cfg (global fallback):
set mysql_connection_string "mysql://user:password@localhost:3306/fivem"

# Node.js:
export mysql_connection_string="mysql://user:password@localhost:3306/mydb"

Import and use:

import db from '@pma-network/sql';

const users = await db.query('SELECT * FROM users WHERE job_name = :job', { job: 'police' });
const insertId = await db.insert(
  'INSERT INTO users (identifier_id, char_data_id, first_name, last_name, inventory_id) VALUES (:identifier_id, :char_data_id, :first_name, :last_name, :inventory_id)',
  {
    identifier_id: 1,
    char_data_id: 1,
    first_name: 'John',
    last_name: 'Doe',
    inventory_id: 1
  }
);

Usage

Custom Instances

Create multiple database connections:

import { MySQL } from '@pma-network/sql';

const db1 = new MySQL({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydb',
});

const db2 = new MySQL({
  connectionString: 'mysql://user:password@remote:3306/otherdb',
});

API Reference

Configuration

Configuration is read using GetConvar (FiveM) with process.env fallback.

Priority order:

1. Resource-specific: {resource_name}_connection_string

   • Resource name detected via GetCurrentResourceName()
   • Dashes converted to underscores: pma-characters → pma_characters_connection_string

2. Global fallback: mysql_connection_string

Connection string format: mysql://username:password@hostname:port/database

Required: mysql:// protocol, username, hostname, database name

Optional: password, port (default: 3306)

Examples

Named parameters:

await db.query('SELECT * FROM users WHERE first_name = :first_name AND job_name = :job', {
  first_name: 'John',
  job: 'police',
});

Positional parameters:

await db.query('SELECT * FROM users WHERE first_name = ? AND job_name = ?', ['John', 'police']);

Raw queries (no parameter processing - no named params, no @ conversion, no undefined->null):

// Use raw methods when you need direct control
const users = await db.rawQuery('SELECT * FROM users WHERE uid = ?', [1]);
const insertId = await db.rawInsert(
  'INSERT INTO users (identifier_id, char_data_id, first_name, last_name, inventory_id) VALUES (?, ?, ?, ?, ?)',
  [1, 1, 'John', 'Doe', 1]
);

// Regular methods process parameters automatically:
// - Convert @ to :
// - Support named parameters (:name)
// - Convert undefined to null
const users2 = await db.query('SELECT * FROM users WHERE uid = :uid', { uid: 1 });

TypeScript with type definitions:

import db from '@pma-network/sql';

interface User {
  uid: number;
  identifier_id: number;
  bank: number;
  char_data_id: number;
  first_name: string;
  last_name: string;
  job_name: string;
  job_rank: number;
  slot_id: number | null;
  model: string | null;
  inventory_id: number;
  x: number;
  y: number;
  z: number;
  is_deleted: boolean | null;
}

const users = await db.query<User[]>('SELECT * FROM users WHERE job_name = :job', { job: 'police' });
const user = await db.single<User>('SELECT * FROM users WHERE uid = :uid', { uid: 1 });

Transactions with success/failure:

const { success, result, error } = await db.transaction(async (execute) => {
  await execute('UPDATE users SET bank = bank + :amount WHERE uid = :uid', { amount: 1000, uid: 1 });
  await execute('UPDATE users SET job_name = :job, job_rank = :rank WHERE uid = :uid', {
    job: 'police',
    rank: 1,
    uid: 1
  });
  return { uid: 1 };
});

if (success) {
  console.log('Transaction committed:', result);
} else {
  console.error('Transaction rolled back:', error);
}

Batch operations for bulk inserts/updates:

const { success, result } = await db.batch([
  {
    query: 'INSERT INTO users (identifier_id, char_data_id, first_name, last_name, inventory_id) VALUES (:identifier_id, :char_data_id, :first_name, :last_name, :inventory_id)',
    parameters: { identifier_id: 1, char_data_id: 1, first_name: 'John', last_name: 'Doe', inventory_id: 1 }
  },
  {
    query: 'INSERT INTO users (identifier_id, char_data_id, first_name, last_name, inventory_id) VALUES (:identifier_id, :char_data_id, :first_name, :last_name, :inventory_id)',
    parameters: { identifier_id: 2, char_data_id: 2, first_name: 'Jane', last_name: 'Smith', inventory_id: 2 }
  },
  {
    query: 'UPDATE users SET job_name = :job WHERE uid = :uid',
    parameters: { job: 'police', uid: 1 }
  }
]);

if (success) {
  console.log(`Executed ${result.length} queries`);
}