@aquapha/aq-orm

A lightweight, type-safe ORM for FiveM using oxmysql.

Table of Contents

• Prerequisites
• Installation
• Quick Start
• Schema
  • Defining Tables
  • Column Types
  • Column Modifiers
  • Type Inference
• Query Builder
  • Select
  • Insert
  • Update
  • Delete
• Conditions
  • Comparison
  • Logical
  • Other
• Aggregates
• SQL Functions
  • sqlIf
  • jsonObject
  • jsonExtract
• Transactions
• Raw SQL
• Driver
• License

Prerequisites

This library requires @overextended/oxmysql as the database driver in your FiveM server.

Installation

pnpm add @aquapha/aq-orm

Quick Start

import { oxmysql } from "@overextended/oxmysql";
import {
  Database,
  Driver,
  table,
  int,
  varchar,
  boolean,
  eq,
} from "@aquapha/aq-orm";

// Define schema
const users = table("users", {
  id: int("id").primaryKey().autoIncrement(),
  name: varchar("name", 255).notNull(),
  active: boolean("active").default(true),
});

// Create database instance
const db = new Database(new Driver(oxmysql));

// Query
const activeUsers = await db
  .select(users)
  .where(eq(users.columns.active, true))
  .execute();

Schema

Defining Tables

import { table, int, varchar, text, boolean, timestamp, json, enumCol } from "@aquapha/aq-orm";

const users = table("users", {
  id: int("id").primaryKey().autoIncrement(),
  name: varchar("name", 255).notNull(),
  email: varchar("email").notNull().unique(),
  role: enumCol("role", ["admin", "user", "moderator"]).notNull(),
  active: boolean("active").default(true),
  createdAt: timestamp("created_at").notNull(),
});

const posts = table("posts", {
  id: int("id").primaryKey().autoIncrement(),
  userId: int("user_id").notNull(),
  title: varchar("title", 255).notNull(),
  content: text("content"),
  metadata: json<{ tags: string[] }>("metadata"),
});

Column Types

| Builder | MySQL Type | TypeScript Type | | ----------------------------------- | ------------ | --------------- | | int(name) | INT | number | | bigint(name) | BIGINT | number | | varchar(name, length?) | VARCHAR(n) | string | | text(name) | TEXT | string | | boolean(name) | TINYINT(1) | boolean | | date(name) | DATE | Date | | timestamp(name) | TIMESTAMP | Date | | datetime(name) | DATETIME | Date | | json<T>(name) | JSON | T | | decimal(name, precision?, scale?) | DECIMAL(p,s) | number | | enumCol<T>(name, values) | VARCHAR(255) | T (union) |

Column Modifiers

int("id").primaryKey();       // PRIMARY KEY
int("id").autoIncrement();    // AUTO_INCREMENT
varchar("name").notNull();    // NOT NULL
varchar("email").unique();    // UNIQUE
boolean("active").default(true); // DEFAULT value

Type Inference

Infer TypeScript types directly from your schema:

import type { Infer } from "@aquapha/aq-orm";

type User = Infer<typeof users>;
// { id: number | null; name: string; email: string; active: boolean; createdAt: Date }

Query Builder

Select

Basic select — returns all columns:

const allUsers = await db.select(users).execute();

Select specific columns:

const names = await db
  .select(users)
  .columns({ id: users.columns.id, name: users.columns.name })
  .execute();

Where conditions (multiple .where() calls are ANDed):

const filtered = await db
  .select(users)
  .where(eq(users.columns.active, true))
  .where(gt(users.columns.age, 18))
  .execute();

Distinct:

const uniqueNames = await db
  .select(users)
  .columns({ name: users.columns.name })
  .distinct()
  .execute();

Order, limit, offset:

const page = await db
  .select(users)
  .orderBy(users.columns.createdAt, "DESC")
  .limit(10)
  .offset(20)
  .execute();

Joins:

const results = await db
  .select(users)
  .columns({
    userName: users.columns.name,
    postTitle: posts.columns.title,
  })
  .innerJoin(posts, eq(users.columns.id, posts.columns.userId))
  .execute();

Table aliases (useful for self-joins):

const e = employees.as("e");
const m = employees.as("m");

const withManagers = await db
  .select(e)
  .columns({
    employeeName: e.columns.name,
    managerName: m.columns.name,
  })
  .leftJoin(m, eq(e.columns.managerId, m.columns.id))
  .execute();

Group by and having:

const stats = await db
  .select(users)
  .columns({ status: users.columns.status })
  .groupBy(users.columns.status)
  .having(gt(count(), 5))
  .execute();

Insert

// Single insert
await db
  .insert(users)
  .values({ name: "John", email: "[email protected]" })
  .execute();

// Batch insert
await db
  .insert(users)
  .values([
    { name: "John", email: "[email protected]" },
    { name: "Jane", email: "[email protected]" },
  ])
  .execute();

// Upsert (INSERT ON DUPLICATE KEY UPDATE)
await db
  .insert(users)
  .values({ id: 1, name: "John", email: "[email protected]" })
  .onDuplicateKeyUpdate(["name", "email"])
  .execute();

Update

await db
  .update(users)
  .set({ active: false })
  .where(eq(users.columns.id, 1))
  .execute();

Delete

await db.delete(users).where(eq(users.columns.id, 1)).execute();

Conditions

All condition functions are used with .where() and .having().

import { eq, neq, gt, gte, lt, lte, and, or, not, like, inArray, notInArray, isNull, isNotNull, between } from "@aquapha/aq-orm";

Comparison

| Function | SQL | Example | | -------- | ------------ | ------------------------------------------ | | eq | = | eq(col, value) or eq(colA, colB) | | neq | != | neq(col, value) | | gt | > | gt(col, value) | | gte | >= | gte(col, value) | | lt | < | lt(col, value) | | lte | <= | lte(col, value) |

// Value comparison
.where(eq(users.columns.id, 1))
// WHERE users.id = 1

// Column-to-column comparison
.where(eq(orders.columns.userId, users.columns.id))
// WHERE orders.user_id = users.id

Logical

| Function | SQL | Description | | -------- | ----- | ------------------------------- | | and | AND | All conditions must be true | | or | OR | At least one must be true | | not | NOT | Negates a condition |

.where(and(
  eq(users.columns.active, true),
  gte(users.columns.age, 18)
))
// WHERE (users.active = 1 AND users.age >= 18)

.where(or(
  eq(users.columns.role, "admin"),
  eq(users.columns.role, "moderator")
))
// WHERE (users.role = 'admin' OR users.role = 'moderator')

.where(not(eq(users.columns.status, "banned")))
// WHERE NOT (users.status = 'banned')

Other

| Function | SQL | Example | | ------------ | --------------- | ------------------------------------------ | | like | LIKE | like(col, "John%") | | inArray | IN | inArray(col, ["a", "b"]) | | notInArray | NOT IN | notInArray(col, ["a", "b"]) | | isNull | IS NULL | isNull(col) | | isNotNull | IS NOT NULL | isNotNull(col) | | between | BETWEEN | between(col, 10, 100) |

.where(like(users.columns.name, "John%"))
// WHERE users.name LIKE 'John%'

.where(inArray(users.columns.role, ["admin", "moderator"]))
// WHERE users.role IN ('admin', 'moderator')

.where(isNull(users.columns.deletedAt))
// WHERE users.deleted_at IS NULL

.where(between(products.columns.price, 10, 100))
// WHERE products.price BETWEEN 10 AND 100

Aggregates

| Function | SQL | Example | | --------- | ---------- | ------------------------------- | | count | COUNT | count() or count(col) | | sum | SUM | sum(col) | | avg | AVG | avg(col) | | min | MIN | min(col) | | max | MAX | max(col) |

import { count, sum, avg, min, max } from "@aquapha/aq-orm";

Basic aggregation:

const totals = await db
  .select(products)
  .columns({
    total: count(),
    lowest: min(products.columns.price),
    highest: max(products.columns.price),
  })
  .execute();

With GROUP BY, HAVING, and WHERE:

const revenue = await db
  .select(orders)
  .columns({
    customerId: orders.columns.customerId,
    totalSpent: sum(orders.columns.amount),
    orderCount: count(),
  })
  .where(eq(orders.columns.status, "completed"))
  .groupBy(orders.columns.customerId)
  .having(gte(sum(orders.columns.amount), 1000))
  .orderBy(sum(orders.columns.amount), "DESC")
  .limit(10)
  .execute();

SQL Functions

sqlIf

Conditional logic using MySQL's IF() function:

import { sqlIf } from "@aquapha/aq-orm";

const results = await db
  .select(users)
  .columns({
    id: users.columns.id,
    status: sqlIf<string>(
      gt(users.columns.balance, 1000),
      "premium",
      "standard"
    ),
  })
  .execute();
// SELECT users.id, IF(users.balance > 1000, 'premium', 'standard') AS status

jsonObject

Build JSON objects from columns and values:

import { jsonObject } from "@aquapha/aq-orm";

const results = await db
  .select(users)
  .columns({
    userData: jsonObject({
      name: users.columns.name,
      email: users.columns.email,
    }),
  })
  .execute();
// SELECT JSON_OBJECT('name', users.name, 'email', users.email) AS userData

jsonExtract

Extract values from JSON columns:

import { jsonExtract } from "@aquapha/aq-orm";

const results = await db
  .select(posts)
  .columns({
    tags: jsonExtract(posts.columns.metadata, "tags"),
  })
  .execute();
// SELECT JSON_EXTRACT(posts.metadata, '$.tags') AS tags

// Full JSON path syntax also supported
jsonExtract(posts.columns.metadata, "$.author.name");
jsonExtract(posts.columns.metadata, "$.tags[0]");

Transactions

await db
  .transaction()
  .add(db.insert(users).values({ name: "John" }))
  .add(
    db
      .update(accounts)
      .set({ balance: 100 })
      .where(eq(accounts.columns.userId, 1)),
  )
  .execute();

Raw SQL

import { sql } from "@aquapha/aq-orm";

// Using sql tagged template
const userId = 1;
const query = sql`SELECT * FROM users WHERE id = ${userId}`;
await db.raw(query.sql, query.params);

// Direct raw query
await db.raw("SELECT * FROM users WHERE id = ?", [1]);

Driver

import { oxmysql } from "@overextended/oxmysql";
import { Driver } from "@aquapha/aq-orm";

const driver = new Driver(oxmysql);

// Methods
driver.execute(sql, params);  // Execute query
driver.prepare(sql, params);  // Prepared statement
driver.transaction(queries);  // Execute transaction

License

MIT