StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POClient-side Javascript to Call Postgresql Stored Procedure
text
Body
copied!<p>I have a desktop app with Postgresql as the database. The business logic is mostly in stored procedures. So the stored procedures act as an API for the desktop app.</p> <p>Now I want to create a web-based version of a subset of the app so that the users are able to run it on mobile devices. But client-side javascript cannot access database directly so I have to develop an HTTP web service. Because this is a LAN app I think developing a 'normal' web API to be consumed by at most 5 users is an overkill. So I'm thinking of creating an web service that simply parse stored procedure strings sent via HTTP from javascript and return tables formatted as JSON.</p> <p>Here's how it will work: Web browser + Javascript --> web service --> DB + stored procedures</p> <p>The client-side javascript will pass to the web service string with this format:</p> <pre><code>storproc_name; dbtype1, arg1; dbtype2, arg2; dbtype3, arg3; ... and so on </code></pre> <p>the web service parses the string into:</p> <pre><code>storproc_name(arg1, arg2, arg3, ...) </code></pre> <p>and returns a json message:</p> <pre><code>{ { { column_name: value} { column_name: value} { ... } } { ... } } </code></pre> <p>example:</p> <pre><code>fn_login; varchar(32), admin; varchar(50), password; </code></pre> <p>parsed by the web service into:</p> <pre><code>fn_login('admin', 'password') </code></pre> <p>and returns this:</p> <pre><code>{ { { code: 1 } { session_token: 'theusertokenusedtoaccessotherstoredprocedures' } } } </code></pre> <p>and then the user can perform other operations with the session token</p> <pre><code>fn_list_products_by_category_id; varchar(50), theusertokenusedtoaccessotherstoredprocedures; integer, 2; fn_list_products_by_category_id('theusertokenusedtoaccessotherstoredprocedures', 2) { { { product_id: 101 } { product_name: "book" } } { ... } } </code></pre> <p>I know this is a non-standard practice. I can't see any obvious pitfall to this approach. But any input will be greatly appreciated.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload