There is a single page application which sends POST HTTP requests with payload in JSON:
{"product": { "name": "product A", "quantity": 100 }}
There is a Postgres database which has tables and stored procedures:
create table product {
product_id serial primary key,
name text,
quantity numeric,
description text
}
create function insert_product (product product) returns product as $$
-- This function accepts a product type as an argument
Is there a solution in any language that would sit on a server, handle HTTP requests, call stored procedures and automatically convert JSON objects to proper Postgres row types?
In pseudo-Express.js
app.post('/product', (req, res) =>
db.query('select insert_product($1)', [convertToPostgresPlease(req.body.product)])
What I don't consider solutions:
- destructuring JSON object and feeding Postgres every key by the teaspoon or handling JSON inside stored procedure (SP should accept a row type)
- duplicating information from Postgres schema (solution must use Postgres introspection capabilities)
- Manually concatenating
'(' + product.name + ',' + ...
I know stored procedures are often frowned upon, but for small projects I honestly think they're great. SQL is an amazing DSL for working with data and Postgres is advanced enough to handle any data-related task.
In any case, what is the most simple way to connect JSON HTTP request with a proper SQL RDBMS?
Found solutions (almost):
- postgraphql (works, but too much magic)
- Chicken Scheme (list-unparser, didn't try yet)