CODEWITHSUNDEEP
javascriptpostgresqlstored-procedures
VoidMain
VoidMain

Reputation: 155

Client-side Javascript to Call Postgresql Stored Procedure

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.

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.

Here's how it will work: Web browser + Javascript --> web service --> DB + stored procedures

The client-side javascript will pass to the web service string with this format:

storproc_name; dbtype1, arg1; dbtype2, arg2; dbtype3, arg3; ... and so on

the web service parses the string into:

storproc_name(arg1, arg2, arg3, ...)

and returns a json message:

{
    {
        { column_name: value}
        { column_name: value}
        { ... }
    }
    {
        ...
    }
}

example:

fn_login; varchar(32), admin; varchar(50), password;

parsed by the web service into:

fn_login('admin', 'password')

and returns this:

{
    {
        { code: 1 }
        { session_token: 'theusertokenusedtoaccessotherstoredprocedures' }
    }
}

and then the user can perform other operations with the session token

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" }
    }
    {
        ...
    }
}

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.

Upvotes: 2

Views: 3090

Answers (1)

Ketema
Ketema

Reputation: 6598

Have a look at https://github.com/brianc/node-postgres.git

--edit--

it is server side, but you really do not want your connection credentials on a client even if its a LAN app. With something like node-Postgres (there are plenty of others to look at) your client simply has to make http requests to your db server. This will save you the trouble of "parsing stored procedure strings" and manually trying to set everything up. I was playing around with something similar a few months ago and collaborated on a proof of concept: https://github.com/OrlandoPg/listen-notify. its not exactly what you want to do, but using it as a starting point you could accomplish your goal very quickly.

1) Install Node and dependencies on your DB server or on a connection pooler that connects to your db 2) Set up your node service to proxy all your SP calls. 3) Query from client and process results.

Node and socket.io take care of all the plumbing for you, including authentication if you wanted it. In your case instead of issuing a single listen query like I did, you could execute your stored procedures based on event name for example. I think an approach like this would make for much lighter client side code and also have less security risks than doing everything in the client.

Upvotes: 1

Related Questions