title: Client-side Javascript to Call Postgresql Stored Procedure
body: <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>