v3 geojson: "SELECT * with no tables specified is not valid"

[xlegs]

I am migrating from dirt v2 (hapi) to v3 (fastify). The GeoJSON route works fine on v2, but fails on v3. I noticed that the SQL is more involved in v3. Perhaps the issue lies there?

Here's my error. The Nearest route works fine, so dirt can definitely see the database.

$ curl -X GET "http://localhost:8123/v1/geojson/public.mytable?geom_column=geom&columns=*"

{"statusCode":500,"error":"Internal Server Error","message":"SELECT * with no tables specified is not valid"}/

[tobinbradley]

This is bad documenting on my part. You have to use explicit column names for this particular service. * won't work.

The geojson is built directly in Postgres now, so Postgres has to put the geometry and additional data (properties) in totally different parts of the JSON response, ala:

{
  "type": "FeatureCollection",
  "features": [
    {
      "type": "Feature",
      "geometry": {
        "type": "Point",
        "coordinates": [0, 0]
      },
      "properties": {
        "name": "null island"
      }
    }
  ]
}

Because of that, (1) the geometry column has to be handled separately, and (2) as the columns are named as keys in the JSON properties object, they have to be explicitly named in the request, via this bit of SQL:

SELECT 
      'Feature' AS type, 
      St_asgeojson(ST_Transform(lg.${query.geom_column}, 4326))::json AS geometry,
      ${query.columns ? ` 
      Row_to_json(
        (
          SELECT 
            l 
          FROM   
         (SELECT ${query.columns}) AS l
        )
      ) AS properties 
      ` : `'{}'::json AS properties`}
...

Which would be much easier for people if I document that. And...posted. Thanks for letting me know about the problem!