Raw SQL Queries

The rawQuery method executes raw SQL statements directly against your connected PostgreSQL databases. Use this for complex joins, aggregations, or specific PostgreSQL features not supported by the standard CRUD methods.

⚠️PostgreSQL Only

rawQuery is only supported for external PostgreSQL connections. It cannot be used with internal Manta Tables.

Basic Usage

To execute a query, provide the database connection name, the SQL string, and any required parameters.

TypeScript

const response = await manta.rawQuery({
  db: "pg_main", // Your Connection Name from Manta Studio
  query: "SELECT * FROM users WHERE email = $email",
  params: {
    email: "user@example.com",
  },
});

Parameters

The rawQuery method accepts the following properties:

Parameter	Type	Required	Description
db	string	Yes	The connection name of the target PostgreSQL database.
query	string	Yes	The raw SQL query string. Use $paramName for named parameters.
params	Record	No	An object containing values for the named parameters.
validation	Record	No	Rules to validate parameters before execution.
options	Object	No	Execution settings like transactions and timeouts.

Execution options

• transaction (boolean): When set to true, the SDK wraps your SQL in a BEGIN...COMMIT/ROLLBACK block.
  • Success: If the query executes without errors, the changes are committed to the database.
  • Failure: If the query fails, all changes are automatically rolled back, leaving your data in its original state.
  • Default: false.
• timeoutMs (number): Defines a safety limit for query execution in milliseconds.
  • Purpose: This prevents "long-running" or "hanging" queries from consuming database resources indefinitely.
  • Example: Set to 5000 for a 5-second limit.

Parameter Validation

Validation rules protect your database by checking data before execution. Use the validation object to define constraints for each parameter.

Rule	Type	Description
required	boolean	Parameter must be defined and not null.
format	string	Must match: email, uuid, url, date, datetime, number, integer, boolean, string.
regex	RegExp | string	Value must match the specified regex pattern.
minLength	number	Minimum string length.
maxLength	number	Maximum string length.
greaterThan	number	Value must be > threshold.
lessThan	number	Value must be < threshold.
equals	any	Value must strictly equal (===) the expected value.
in	any[]	Value must be present in the specified array.
custom	fn	A custom validation function for advanced logic.

Transactions

Use the transaction option to ensure an "all-or-nothing" operation. If you enable this, your SQL is wrapped in a BEGIN...COMMIT/ROLLBACK block. This ensures that if any part of your query fails or hits a timeout, every change is automatically undone, and your data remains exactly as it was before the query started.

TypeScript

await manta.rawQuery({
  db: "pg_main",
  query: "INSERT INTO logs (msg) VALUES ($msg)",
  params: { msg: "System Audit" },
  options: { transaction: true, timeoutMs: 5000 },
});

Response format

The updateRecords method returns a standardized response. The results of your SQL query (rows) are available in the data array.

JSON

{
  "status": 200,
  "message": "Query executed successfully",
  "data": [
    { "id": 1, "username": "alice" },
    { "id": 2, "username": "bob" }
  ]
}

Error handling

If a query fails, the SDK returns detailed information about the failure. This includes specific PostgreSQL constraint violations such as unique key conflicts, foreign key mismatches, or not null errors.

TypeScript

try {
  await manta.rawQuery({ ... });
} catch (error) {
  // If a unique constraint is violated:
  // error.response.data:
  // {
  //   "message": "Unique constraint violation",
  //   "errors": [{ "detail": "(email)=(taken@example.com) already exists." }]
  // }
}

Examples

Insert with Validation and Transactions

This example demonstrates how to securely insert data using validation rules and an atomic transaction.

TypeScript

const newUser = await manta.rawQuery({
  db: "pg_main",
  query: `
    INSERT INTO users (username, email, age)
    VALUES ($username, $email, $age)
    RETURNING id, created_at
  `,
  params: {
    username: "johndoe",
    email: "john@example.com",
    age: 25,
  },
  validation: {
    username: { required: true, minLength: 3 },
    email: { required: true, format: "email" },
    age: { greaterThan: 18 },
  },
  options: {
    transaction: true,
    timeoutMs: 5000,
  },
});