POST /get-join — Execute Raw SQL for JOINs and Lookups

This endpoint accepts a raw SQL string in the request body and executes it directly against the PostgreSQL database via db.query(body.query). It exists to support use-cases that the other generic CRUD endpoints cannot handle — most importantly JOIN queries that span multiple tables, and targeted lookups by non-primary-key columns. In the reference implementation, the frontend uses this endpoint to resolve a product by code_reference and to fetch a single customer by id when assembling an invoice payload.

Endpoint

POST /get-join

Request Body

string

required

The complete, valid SQL query to execute. The string is passed directly to the pg driver with no preprocessing, parameterisation, or sanitisation.

{ "query": "SELECT * FROM products WHERE code_reference = 'F7W1G'" }

Responses

200 — OK

Returned when at least one row is found (rowCount > 0).

{
  "status": 200,
  "message": "Datos encontrados",
  "data": [ /* array of row objects */ ]
}

number

Always 200 on success.

string

"Datos encontrados"

array

Array of row objects returned by PostgreSQL. The shape of each object depends entirely on the columns selected by the SQL query.

404 — Not Found

Returned when rowCount === 0 — the query executed without error but returned no rows.

{
  "status": 404,
  "message": "No se encontraron datos"
}

500 — Internal Server Error

Returned when the pg driver throws an exception (syntax error in the SQL, missing table, connection failure, etc.).

{
  "status": 500,
  "message": "Error al obtener los datos",
  "error": { /* pg error object */ }
}

object

Raw error from the pg driver. Check error.code and error.message for PostgreSQL diagnostics.

How the Frontend Uses This Endpoint

The reference frontend makes two specific calls to /get-join when building an invoice payload:

Look up a product by code_reference — after the user enters or scans a product code, the frontend resolves the full product record:
{ "query": "SELECT * FROM products WHERE code_reference = 'F7W1G'" }
Look up a customer by id — to attach complete customer details to the invoice:
{ "query": "SELECT * FROM customer WHERE id = '42'" }

Both calls return the row(s) inside the data array, and the frontend picks data[0] for the single-result case.

Examples

curl -X POST http://localhost:3000/get-join \
  -H "Content-Type: application/json" \
  -d '{
    "query": "SELECT c.id, c.names, c.email, m.name AS municipality FROM customer c JOIN municipality m ON c.municipality_id = m.id WHERE c.id = 42"
  }'

curl -X POST http://localhost:3000/get-join \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT * FROM products WHERE code_reference = '\''F7W1G'\''"}'

curl -X POST http://localhost:3000/get-join \
  -H "Content-Type: application/json" \
  -d '{"query": "SELECT * FROM customer WHERE id = '\''42'\''"}'

const response = await fetch('http://localhost:3000/get-join', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    query: `
      SELECT c.id, c.names, c.email, m.name AS municipality
      FROM customer c
      JOIN municipality m ON c.municipality_id = m.id
      WHERE c.id = 42
    `,
  }),
});

const result = await response.json();
console.log(result.data);
// [{ id: 42, names: 'Santiago Rivera', email: '...', municipality: 'Armenia' }]

const response = await fetch('http://localhost:3000/get-join', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    query: "SELECT * FROM products WHERE code_reference = 'F7W1G'",
  }),
});

const result = await response.json();
const product = result.data[0];

Example Response — JOIN query

{
  "status": 200,
  "message": "Datos encontrados",
  "data": [
    {
      "id": 42,
      "names": "Santiago Rivera",
      "email": "[email protected]",
      "municipality": "Armenia"
    }
  ]
}

This endpoint executes raw, unsanitized SQL. The query string from the request body is passed directly to db.query() with absolutely no validation, parameterisation, or escaping. A malicious caller can run arbitrary SQL — including DROP TABLE, DELETE FROM, or data-exfiltration queries — against any table in the connected PostgreSQL database. Never expose this endpoint without:

Strong authentication (verify the caller’s identity before the route is reached).
Input validation (restrict the allowed SQL patterns or use an allowlist of pre-approved query templates).
A read-only database role if write access is not required.

This endpoint is intended for trusted internal use only.

In the reference implementation, the frontend uses /get-join for exactly two purposes: resolving a single product record by code_reference and resolving a single customer record by id when constructing an invoice payload before submission to the Factus billing API. All other data fetching goes through the safer GET /get-data/:table endpoint.

Invoice Endpoints

Database Endpoints

POST /get-join — Execute Raw SQL for JOINs and Lookups

Endpoint

Request Body

Responses

200 — OK

404 — Not Found

500 — Internal Server Error

How the Frontend Uses This Endpoint

Examples

Example Response — JOIN query

​Endpoint

​Request Body

​Responses

​200 — OK

​404 — Not Found

​500 — Internal Server Error

​How the Frontend Uses This Endpoint

​Examples

​Example Response — JOIN query

Endpoint

Request Body

Responses

200 — OK

404 — Not Found

500 — Internal Server Error

How the Frontend Uses This Endpoint

Examples

Example Response — JOIN query