npm package: @pma-network/sql
Description: MySQL wrapper with promise-based async operations, connection pooling, and named parameters support
Latest version: 1.2.1

# @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

```bash
pnpm add @pma-network/sql
```

Or with npm:

```bash
npm install @pma-network/sql
```

## Quick Start

Set the connection string environment variable:

```bash
# 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:

```typescript
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:

```typescript
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:
```typescript
await db.query('SELECT * FROM users WHERE first_name = :first_name AND job_name = :job', {
  first_name: 'John',
  job: 'police',
});
```

Positional parameters:
```typescript
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):
```typescript
// 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:
```typescript
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:
```typescript
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:
```typescript
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`);
}
```