db Module - MARS Engine Database API Overview

The db module in MARS Engine provides a robust interface for interacting with the project's local database schema. All data access is automatically wrapped in a transaction unless explicitly committed or rolled back.

db.query(sql, ...params)

Direct execution of SQL with support for parameter binding.

There are three ways of parameter binding in MARS Engine.

1. Order based token

This means that the parameters you send to the functions will be bound to '?' tokens in the same order. Eg. First question-mark to first parameter.

let users = db.query("SELECT * FROM users WHERE usr_id = ?", 5);

2. Index based token

This binds the parameters you send to the query in the order allowing you to decouple the parameter order from the query.

let users = db.query(`SELECT * FROM users WHERE
usr_name = ?1 OR usr_lastname = ?2`,
"John","Doe")

Note: You can repeat the any parameter placeholder (eg. ?2) to repeatedly use the same parameter in two slots.

3. Named parameter query

This means that the parameters you send in an object will be bound to strings prefixed with ':'.

let users = db.query(`SELECT * FROM users WHERE
usr_name = :firstname OR usr_lastname = :lastname`,
{firstname: "John", lastname: "Doe"})

Key Notes:

• Uses positional placeholders (?) for parameters.
• SELECT queries return results as arrays of objects.
• INSERT and UPDATE statements return an array of affected row IDs, shaped like [{ id: 1 }, { id: 2 }].
• Transaction is open at script start, and will rollback if the script fails.

Insert and update return values

Any database insert or update executed through db returns an array of IDs for the rows that were inserted or updated. This applies to direct SQL with db.query(), query builder .exec(), and other db insert/update helpers.

const inserted = db.query(
  "INSERT INTO users (usr_name) VALUES (?)",
  "Alice"
);

// inserted is shaped like:
// [{ id: 1 }]

const updated = db.query(
  "UPDATE users SET usr_name = ? WHERE usr_id IN (?, ?)",
  "Alice Updated",
  1,
  2
);

// updated is shaped like:
// [{ id: 1 }, { id: 2 }]

Query Builder (db.select(), db.insert()...)

Chainable interface for building SQL queries programmatically.

let users = db.select("*")
  .from("users")
  .where("usr_name", "Tom")
  .exec();

View all query builder examples here

When an INSERT or UPDATE query builder chain is executed with .exec(), it returns the same affected-ID array as direct db operations:

const inserted = db.insert("users")
  .set("usr_name", "Alice")
  .exec();

// inserted is shaped like:
// [{ id: 1 }]

Available Functions:

Function	Description
select()	Start a SELECT query
insert()	Start an INSERT query
update()	Start an UPDATE query
delete()	Start a DELETE query
where()	Add condition
orWhere()	Add OR condition
innerJoin()	Add INNER JOIN
leftJoin()	Add LEFT JOIN
rightJoin()	Add RIGHT JOIN
condition()	Conditional logic builder
toSql()	Return the raw SQL string
exec()	Execute the built query

NOTE: Must always call .exec() at the end to run the query.

db.commit()

Forces MARS Engine to persist all changes immediately.

let q = db.query("INSERT INTO logs SET message = ?", "Done");
db.commit();

Normally, MARS Engine will auto-rollback if the script crashes. Use db.commit() if you want to persist data before potential failure (e.g., after file upload but before risky JSON parsing).

db.rollback()

Explicitly cancels all changes done so far in the current script.

try {
  JSON.parse(param("invalidJson"));
} catch (e) {
  db.rollback();
  write("error", "Bad JSON");
}

Useful for custom error handling where you want to manually revert committed changes.