Database

A plugin keeps its data in MySQL or MariaDB with Database since v0.3.1, a global - no import line. Describe a table once, and query it in TypeScript:

import { 
asc
, 
double
, 
eq
, 
int
, 
table
, 
varchar
 } from "@amxts/core/database";

export const 
records
 = 
table
("records", {
    
id
: 
int
().
primaryKey
().
autoincrement
(),
    
steamId
: 
varchar
(32).
notNull
(),
    
map
: 
varchar
(64).
notNull
(),
    
time
: 
double
().
notNull
(),
});

const 
db
 = new 
Database
(
env
("MYPLUGIN_DB", ""));

server
.
addCommand
("/top", async ({ 
player
 }) => {
    const 
top
 = await 
db
.
select
().
from
(
records
).
where
(
eq
(
records
.
map
, 
server
.
map
)).
orderBy
(
asc
(
records
.
time
)).
limit
(10);
    
top
.
forEach
((
record
, 
i
) => 
player
.
print
(`${
i
 + 1}. ${
record
.
steamId
} ${
record
.
time
.
toFixed
(2)}`));
});

A query runs in the background. The game does not wait for it: its result arrives on a later server frame, never inside the call that sent it.

The address

new Database(url) takes the database's address:

mysql://user:password@host:3306/database?ssl-mode=preferred

The port is 3306 when left out. A user or password with @, : or / in it is written with %40, %3A, %2F. An address with jdbc: before it, as a game panel's database page shows it (jdbc:mysql://...), works as it is.

Keep the address out of the code: read it with env from the server's addons/amxts/.env, so the password stays on the server:

MYPLUGIN_DB=mysql://myplugin:s3cret@127.0.0.1:3306/myplugin

ssl-mode says whether the connection is encrypted, with the names MySQL's own clients use:

MySQL 8 users (caching_sha2_password) and older ones (mysql_native_password) both sign in, with or without TLS.

Tables

table(name, columns) from @amxts/core/database describes a table of the database: each field is a column, its builder says what the column holds, and the row's type comes from the columns - no interface to keep beside them. A misspelt column is an error in the editor.

import { 
boolean
, 
int
, 
json
, 
table
, 
text
, 
unique
, 
varchar
 } from "@amxts/core/database";

interface Settings {
    
sound
: boolean;
}

export const 
players
 = 
table
("players", {
    
id
: 
int
().
primaryKey
().
autoincrement
(),
    
steamId
: 
varchar
(32).
notNull
().
unique
(),
    
name
: 
varchar
(64).
notNull
(),
    
points
: 
int
().
notNull
().
default
(0),
    
vip
: 
boolean
(),
    
title
: 
text
(),
    
settings
: 
json
<Settings>(),
});

type PlayerRow = typeof 
players
.
$inferSelect
;   // { id: number; steamId: string; ...; vip: boolean | null; ... }
type NewPlayer = typeof 
players
.
$inferInsert
;   // steamId and name required, the rest may be left out

A column holds null unless it is notNull() - its field is then T | null. Then, in any order:

• primaryKey() - the table's primary key, NOT NULL too;
• autoincrement() - of int() and bigint(): the database numbers new rows;
• notNull(), unique();
• default(value) - the value of a row an insert leaves the column out of; a column of bytes has none but null.

An insert has to give the notNull() columns without a default or autoincrement(); it may leave out the rest.

A column is named in the database in snake case - steamId is steam_id. A column of another name gets it first: int("PlayerID"), varchar("Name", 32).

A key of several columns is the third argument:

import { 
double
, 
index
, 
table
, 
unique
, 
varchar
 } from "@amxts/core/database";

export const 
records
 = 
table
("records", {
    
steamId
: 
varchar
(32).
notNull
(),
    
map
: 
varchar
(64).
notNull
(),
    
time
: 
double
().
notNull
(),
}, (
t
) => [
unique
().
on
(
t
.
steamId
, 
t
.
map
), 
index
().
on
(
t
.
map
, 
t
.
time
)]);

await db.create(players) creates the table, with its keys, unless the database has it already (CREATE TABLE IF NOT EXISTS) - once, when the plugin starts, is enough. It does not change a table that is there.

Reading rows

db.select().from(table) reads rows, every column. What follows it narrows the query - each is optional, in any order:

const 
top
 = await 
db
.
select
().
from
(records)
    .
where
(eq(records.map, 
server
.
map
))
    .
orderBy
(asc(records.time))
    .
limit
(10)
    .
offset
(20);

• where(condition) - only the rows the condition holds for;
• orderBy(...) - by columns, smallest first, or asc(column) and desc(column);
• limit(n) and offset(n) - at most n rows, after leaving out n;
• groupBy(...columns) - one row for each value of the columns.

db.select({ ... }) reads the columns chosen, by the names the row gives them, and aggregates of them:

import { 
count
, 
max
, 
min
 } from "@amxts/core/database";

const 
maps
 = await 
db
.
select
({ 
map
: records.map, 
runs
: 
count
(), 
best
: 
min
(records.time) })
    .
from
(records)
    .
groupBy
(records.map);
// maps[0].map: string, maps[0].runs: number, maps[0].best: number | null

count() counts rows, count(column) the rows that hold a value in it; sum, avg, min and max of a column are null when no row is there to work them out of.

Conditions

A value is checked against the column's type; another column goes in its place, lt(records.time, records.best).

Changing rows

const { 
insertId
 } = await 
db
.
insert
(records).
values
({ 
steamId
: 
player
.
steamId
, 
map
: 
server
.
map
, 
time
 });
await 
db
.
insert
(records).
values
([first, second]);   // several rows at once

const { 
affectedRows
 } = await 
db
.
update
(records)
    .
set
({ 
time
, 
title
: null })
    .
where
(and(eq(records.steamId, 
player
.
steamId
), eq(records.map, 
server
.
map
)));

await 
db
.
delete
(records).
where
(eq(records.id, id));

Each gives { insertId, affectedRows }: the AUTO_INCREMENT id of the row inserted, 0 when there was none, and how many rows the statement inserted, changed or deleted - for an update, the rows it matched. set changes the columns it names; null makes a column NULL. An update or a delete without where changes every row.

A row whose unique key is taken already - an upsert:

await 
db
.
insert
(records)
    .
values
({ 
steamId
: 
player
.
steamId
, 
map
: 
server
.
map
, 
time
 })
    .
onConflictDoUpdate
({ 
target
: [records.steamId, records.map], 
set
: { 
time
 } });

await 
db
.
insert
(players).
values
({ 
steamId
: 
player
.
steamId
, 
name
: 
player
.
name
 }).
onConflictDoNothing
({ 
target
: players.steamId });

target is the column, or the columns, of the unique key; set what to change on the row there.

Raw SQL

db.query<T>(sql, params) and db.execute(sql, params) run SQL as it is written, beside the builder - for what it does not say: joins, functions, statements of your own. Each ? takes the next value of params:

interface Rank {
    
place
: number;
}

const 
found
 = await 
db
.
query
<Rank>("SELECT COUNT(*) + 1 AS place FROM records WHERE map = ? AND time < ?", [
server
.
map
, time]);
const { 
insertId
 } = await 
db
.
execute
("INSERT INTO records (steam_id, map, time) VALUES (?, ?, ?)", [
player
.
steamId
, 
server
.
map
, time]);

query gives the rows as T[] - T an interface of the columns, declared beside your code. A column the interface does not name is skipped. A field a row lacks keeps the default the interface gives it, stays undefined when it is optional, and is a TypeError naming it otherwise; a column that is NULL is null. A value is text, a number, a boolean, a Uint8Array or null, and one list may hold all of them: [player.name, time, true, null].

What a column becomes:

execute gives { insertId, affectedRows }, as the builder's changes do.

Bytes

A file, a recording of a run, a picture is kept whole in a column of bytes - longblob() in a table, a Uint8Array field in raw SQL - and comes back byte for byte:

import * as 
fs
 from "@amxts/core/fs";
import { 
eq
, 
int
, 
longblob
, 
table
, 
varchar
 } from "@amxts/core/database";

export const 
recordings
 = 
table
("recordings", {
    
id
: 
int
().
primaryKey
().
autoincrement
(),
    
map
: 
varchar
(64).
notNull
(),
    
frames
: 
longblob
().
notNull
(),
});

async function keep(
path
: string): Promise<number> {
    const 
frames
 = await 
fs
.
readBytes
(
path
);
    const { 
insertId
 } = await 
db
.
insert
(
recordings
).
values
({ 
map
: 
server
.
map
, 
frames
 });
    return 
insertId
;
}

async function restore(
id
: number, 
path
: string) {
    const 
found
 = await 
db
.
select
().
from
(
recordings
).
where
(
eq
(
recordings
.
id
, 
id
));
    if (
found
.
length
 > 0) 
fs
.
writeFileSync
(
path
, 
found
[0].
frames
);
}

A Uint8Array is a value of a query too - eq(recordings.frames, bytes), [map, bytes] in raw SQL - and it goes to the server as bytes.

Errors

A query that fails rejects its promise with a DatabaseError: message is the server's, code its error number (1062 for a duplicate key) and sqlState its SQLSTATE ("23000"). When the query did not reach the server - no connection, a wrong address - code is 0 and message says why.

try {
    await 
db
.
insert
(players).
values
({ 
steamId
: 
player
.
steamId
, 
name
: 
player
.
name
 });
} catch (
error
) {
    if ((
error
 as DatabaseError).
code
 == 1062) return;   // already there
    
console
.
error
(`could not save ${
player
.
name
}: ${
error
.message}`);
}

Transactions

db.transaction(fn) runs fn with a transaction tx: what fn sends through tx - tx.select(), tx.insert(), tx.query(), the rest - is committed when fn returns, and rolled back when it throws - the error goes on to the caller. The promise gives what fn returned:

const 
runId
 = await 
db
.
transaction
(async (
tx
) => {
    const { 
insertId
 } = await 
tx
.
insert
(runs).
values
({ 
steamId
: 
player
.
steamId
, 
time
 });
    await 
tx
.
execute
("UPDATE players SET runs = runs + 1 WHERE steam_id = ?", [
player
.
steamId
]);
    return 
insertId
;
});

A transaction has a connection of its own while it runs; up to four run at once for one address, and a fifth waits for one of them to end.

Other databases

The scheme of the address picks who answers it. mysql:// is built in; another database - a file, a web service, a Pawn module's natives - is a provider a library or the plugin registers with Database.register, and a plugin then opens its addresses as it opens MySQL's - tables and queries are written the same way for any of them.

A provider is a function that gives a connection for an address:

import { SqlDialect } from "@amxts/core/database";

class SqliteDialect extends SqlDialect {
    get 
name
(): string {
        return "sqlite";
    }
}

const 
sqlite
 = new 
SqliteDialect
();

class LogConnection implements DatabaseConnection {
    constructor(public 
url
: string) {}

    get 
dialect
(): SqlDialect {
        return 
sqlite
;
    }

    
query
(
sql
: string, 
params
: DatabaseValue[]): Promise<string> {
        return 
Promise
.
resolve
<string>("[]");   // rows as JSON text: [{"name":"a","time":1.5}]
    }

    
execute
(
sql
: string, 
params
: DatabaseValue[]): Promise<ExecuteResult> {
        
console
.
log
(`${
sql
} ${
JSON
.
stringify
(
params
)}`);
        return 
Promise
.
resolve
<ExecuteResult>({ 
insertId
: 0, 
affectedRows
: 0 });
    }

    
begin
(): Promise<DatabaseTransaction> {
        return 
Promise
.
reject
<DatabaseTransaction>(new 
DatabaseError
("the log has no transactions"));
    }

    
close
(): void {}
}

Database.
register
("log", (
url
: string): DatabaseConnection => new 
LogConnection
(
url
));

const 
log
 = new 
Database
("log://anything");

• dialect is how the database writes SQL, which the builder writes its statements in. SqlDialect writes standard SQL - names in "...", values as ?, ON CONFLICT for an upsert; a dialect overrides what its database writes otherwise (quote, placeholder - $1 for PostgreSQL - onConflict, limit, columnType for create), and refuses what it cannot do with this.unsupported(what, instead), which the query rejects with. mysql from @amxts/core/database is MySQL's.
• query gives the rows as JSON text, an array of objects, a JSON column's value as text, bytes as base64 text - what JSON.stringify writes of a Uint8Array.
• execute gives { insertId, affectedRows }.
• begin gives a DatabaseTransaction - dialect, query, execute, commit() and rollback() - or rejects when the database has none.
• A connection runs its queries in the order it is given them.

JSON.stringify(params) writes the values as a JSON array, ready to send to a web service.

A provider in a library registers itself when the library is imported, so a plugin that uses it writes one line:

import "@myorg/sqlite";

const 
db
 = new 
Database
("sqlite://data/myplugin.db");

An address whose scheme has no provider rejects every query with a DatabaseError that names the schemes there are.