PostgreSQL Database Layer: pg Pool and SQL Helpers

PostgreSQL stores all local application data — customers, products, payment methods, and various lookup tables. The connection is managed by a single pg.Pool instance created in src/database.js and exported as the default export db. Every controller that touches the database imports this shared pool, ensuring connection reuse across concurrent requests without manual connection management.

Connection Configuration

The pool is configured entirely through environment variables. The client_encoding is hardcoded to utf8 to guarantee correct character handling for Spanish-language strings (names, addresses, observations).

src/database.js

import pg from 'pg';

const ENV = process.env;

/**
 * Gestor de la base de datos
 */
const db = new pg.Pool({
  client_encoding: 'utf8',
  user: ENV.DB_USER,
  host: ENV.DB_HOST,
  database: ENV.DB_NAME,
  password: ENV.DB_PASSWORD,
  ssl: ENV.DB_SSL,
});

// db.query(`SET client_encoding = 'UTF8';`);
export default db;

Environment Variables

Variable	Description
`DB_USER`	PostgreSQL username
`DB_HOST`	Hostname or IP of the PostgreSQL server
`DB_NAME`	Name of the target database
`DB_PASSWORD`	Password for `DB_USER`
`DB_SSL`	Set to `true` to enable SSL/TLS for the connection

Set DB_SSL=true when connecting to a managed PostgreSQL service such as Azure Database for PostgreSQL, which enforces SSL by default. For a local development instance, DB_SSL=false is typically sufficient.

Tables

The application reads and writes the following tables via the generic query routes (/get-data/:table, /add-data/:table, etc.):

customer

Invoice recipients. Stores identification details, names, address, and foreign keys to lookup tables (municipality_id, type_id, id_org, tribute_id).

products

Product and service catalog. Includes code_reference, name, price, tax_rate, discount_rate, unit_measure_id, standard_code_id, tribute_id, and the is_excluded flag.

payment_method

Payment method lookup table used when creating an invoice. Fields: id, name.

municipality

City/municipality lookup. Fields: id, code, name. Referenced by the customer table.

identification_document

Document type lookup (e.g., NIT, CC). Fields: id, name. Used to identify customers.

legal_organization

Legal organization type lookup (e.g., natural person, legal entity). Fields: id, name.

customer_tribute

Tax regime lookup. Fields: id, name. Maps the tax treatment applicable to a customer.

Helper Functions

src/helpers/helpers.mjs exports several utility functions used by the query controllers to build SQL statements dynamically from arbitrary request bodies, avoiding hardcoded column lists.

`generate_str_of_dict(dict, key_value, restrict)`

Builds a SQL fragment from a plain JavaScript object. When key_value is true it produces a SET clause for UPDATE statements (col = value, ...). When false it produces a comma-separated values list for INSERT statements. The restrict parameter names a key that should be excluded from the output (typically the primary key id).

// UPDATE example — key_value: true
// Input:  { name: 'Acme', city: 'Bogotá' }
// Output: "name = 'Acme',city = 'Bogotá'"

// INSERT example — key_value: false
// Input:  { name: 'Acme', city: 'Bogotá' }
// Output: "'Acme','Bogotá'"

`get_keys_dict(dict, restrict)`

Returns an array of the keys of an object, optionally excluding one key. Used to build the column list for INSERT and UPDATE statements.

// Input:  { name: 'Acme', city: 'Bogotá' }
// Output: ['name', 'city']

`rd_key(limit)`

Generates a random alphanumeric key of limit characters by alternating uppercase ASCII letters (A–Y) and single digits. Used to produce unique reference_code values for invoices (length 10) and code_reference values for line items (length 5).

rd_key(10) // e.g. "A3C7E1G5I9"
rd_key(5)  // e.g. "B4D2F"

`validate_body(body)`

Validates the request body of a POST /factura request before it is forwarded to the Factus API. Returns a validation result object with an ok boolean and, on failure, arrays of missing_properties and require conflicts. Required fields and types:

Field	Type	Additional constraint
`observation`	`string`	Length must be ≤ 249 characters
`payment_method_code`	`number`	—
`customer`	`object`	—
`items`	`object` / array	Each item receives an auto-generated `code_reference`

On a successful validation the function also injects two fields into the body before returning:

body.numbering_range_id = 8
body.reference_code — a 10-character random key generated by rd_key(10)

src/helpers/helpers.mjs — validate_body

export function validate_body(body) {
  let ok = true;
  let message = '';
  let missing_properties = [];
  let require = [];
  const requeriments = [
    // ['reference_code', 'string'],
    ['observation', 'string'],
    ['payment_method_code', 'number'],
    ['customer', 'object'],
    ['items', 'object'],
  ];
  // Solo se admite un cuerpo en formato Objeto (JSON)
  if (typeof body !== 'object') {
    ok = false;
  }
  // Verificar propiedades
  for (let i = 0; i < requeriments.length; i++) {
    const requeriment = requeriments[i];
    // No tiene propiedad requerida
    if (!body.hasOwnProperty(requeriment[0])) {
      missing_properties.push(requeriment[0]);
      ok = false;
    } else {
      // Validar tipo de propiedad
      const type_received = typeof body[requeriment[0]];
      if (type_received !== requeriment[1]) {
        require.push({ property: requeriment[0], type_require: requeriment[1], type_received });
        ok = false;
      }
      if (requeriment[0] === 'items') {
        for (let item of body.items) item.code_reference = rd_key(5);
      }
      // Validar longitud de la propiedad "observación"
      if (requeriment[0] === 'observation') {
        const len_obs = body['observation'].length;
        if (len_obs > 249) {
          require.push({ property: 'observation', type_require: 'String, length <= 249', type_received: `${type_received}, length = ${len_obs}` });
          ok = false;
        }
      }
    }
  }
  // Agregar rango de numeración
  body.numbering_range_id = 8;
  body.reference_code = rd_key(10);
  missing_properties.length > 0 || require.length > 0 ? (message = 'Hay errores') : (message = 'OK');
  let validation = { ok, message };
  if (missing_properties.length > 0) validation.missing_properties = missing_properties;
  if (require.length > 0) validation.require = require;
  return validation;
}

Example Validation Response

{
  "ok": true,
  "message": "OK"
}

{
  "ok": false,
  "message": "Hay errores",
  "missing_properties": ["customer"],
  "require": [
    {
      "property": "payment_method_code",
      "type_require": "number",
      "type_received": "string"
    }
  ]
}

Get Started

Backend

Frontend

Deployment

PostgreSQL Database Layer: pg Pool and SQL Helpers

Connection Configuration

Environment Variables

Tables

customer

products

payment_method

municipality

identification_document

legal_organization

customer_tribute

Helper Functions

`generate_str_of_dict(dict, key_value, restrict)`

`get_keys_dict(dict, restrict)`

`rd_key(limit)`

`validate_body(body)`

Example Validation Response

​Connection Configuration

​Environment Variables

​Tables

customer

products

payment_method

municipality

identification_document

legal_organization

customer_tribute

​Helper Functions

​generate_str_of_dict(dict, key_value, restrict)

​get_keys_dict(dict, restrict)

​rd_key(limit)

​validate_body(body)

​Example Validation Response

Connection Configuration

Environment Variables

Tables

Helper Functions

`generate_str_of_dict(dict, key_value, restrict)`

`get_keys_dict(dict, restrict)`

`rd_key(limit)`

`validate_body(body)`

Example Validation Response