xyz_route.js

const env = require('../mod/env');

const sql_filter = require('../mod/pg/sql_filter');

const sql_fields = require('../mod/pg/sql_fields');

fastify.route({
  method: 'GET',
  url: '/api/layer/table',
  preValidation: fastify.auth([
    (req, res, next) => fastify.authToken(req, res, next, {
      public: true
    })
  ]),
  schema: {
    querystring: {
      type: 'object',
      properties: {
        token: { type: 'string' },
        locale: { type: 'string' },
        layer: { type: 'string' },
        table: { type: 'string' },
        filter: { type: 'string' },
      },
      required: ['locale', 'layer', 'table']
    }
  },
  preHandler: [
    fastify.evalParam.token,
    fastify.evalParam.locale,
    fastify.evalParam.layer,
    fastify.evalParam.roles,
    fastify.evalParam.layerTable,
  ],
  handler: async (req, res) => {

    let
      layer = req.params.layer,
      table = req.params.table,
      filter = req.params.filter,
      orderby = req.query.orderby || layer.qID,
      order = req.query.order || 'asc';
   
    const filter_sql = filter && await sql_filter(filter) || '';

    const fields = await sql_fields([], table.columns);
    
    var q = `
      SELECT
        ${layer.qID} AS qID,
        ${fields}
      FROM
        ${table.from}
        ${filter_sql}
      ORDER BY ${orderby} ${order}
      FETCH FIRST 99 ROW ONLY;`;

    var rows = await env.dbs[layer.dbs](q);

    if (rows.err) return res.code(500).send('Failed to query PostGIS table.');

    res.code(200).send(rows);
  }

});