Recent

Author Topic: REST API SERVER QUESTION  (Read 2903 times)

heejit

  • Full Member
  • ***
  • Posts: 245
REST API SERVER QUESTION
« on: February 27, 2018, 08:56:22 pm »
I am writing MULTIUSER REST API Server.

Do I have to create a new db connection for each request
or I can share the same connection between multiple user.

How server will handle if multiple user trying to request
same time.

I am using fclweb.

Please let me know if more information required.
« Last Edit: February 27, 2018, 09:11:56 pm by jshah »

Trenatos

  • Hero Member
  • *****
  • Posts: 535
    • MarcusFernstrom.com
Re: REST API SERVER QUESTION
« Reply #1 on: March 01, 2018, 05:29:09 pm »
This is an issue I've been looking into as well.

You really want to use a database pool of some sort.

From what I've seen, that means you should use Tiopf2, mORMot, or DevArt stuff.

I haven't found any decent quality standalone database pool libraries for FPC.

tudi_x

  • Hero Member
  • *****
  • Posts: 532
Re: REST API SERVER QUESTION
« Reply #2 on: March 01, 2018, 06:02:34 pm »
on each node you can open a connection for each request (in a new thread) to the db.
use a load balancer in order not to congest each node.
you have a counter for reaching maximum number of threads.
you configure the db to allow the max number of threads x number nodes.

above is for persistence but caching should be used with cockroach db.
on each node a cockroach instance.

rdbms nowadays support a lot of connections. you just ingest the time to open a connection or move to the cache if bottleneck.


Lazarus 2.0.2 64b on Debian LXDE 10

Trenatos

  • Hero Member
  • *****
  • Posts: 535
    • MarcusFernstrom.com
Re: REST API SERVER QUESTION
« Reply #3 on: March 01, 2018, 06:23:31 pm »
It's not hard to roll a simple pool, but that's a far cry from using something like HikariCP (Java DB pool), throwing in the word "just" is not doing anyone a favor.

Opening a new connection takes time, so you could create a singleton on startup that creates db connections. But then you might create say.. 200 connections, but your app isn't popular and only needs 4 connections, so you're wasting 196 which could be used by a different application.

You also need to deal with connections going up and down, failovers, dealing with timeouts or runaway threads hogging connections.

This is just random stuff off the top of my head.

It's not quite so easy to create a good database pooling library as it is to create a basic one.

Leledumbo

  • Hero Member
  • *****
  • Posts: 8746
  • Programming + Glam Metal + Tae Kwon Do = Me
Re: REST API SERVER QUESTION
« Reply #4 on: March 01, 2018, 07:38:11 pm »
You can share the connection, but not the query and transaction. It's up to you to use a connection pool or not. You may need to extend the connection class to be resilient against disconnection, though, as the default SQLdb doesn't have automatic connection recovery. You will simply end up in "server has gone away" exception or something if it happens, and with single shared connection, subsequent requests will always fail.

heejit

  • Full Member
  • ***
  • Posts: 245
Re: REST API SERVER QUESTION
« Reply #5 on: March 02, 2018, 02:10:30 pm »
I am trying to use following two function
every new request I call pull_con to get connection from the list
if list of connection is empty then create new and return
once work done push_con to add connection back to list.

Is this ok I have not tested yet with multiple request

Code: Pascal  [Select][+][-]
  1. db_cons: TFPObjectList;
  2.  
  3. function TFPWebModule1.pull_con(): TSQLConnection;
  4. var
  5.     a_con: TSQLConnection;
  6.     a_tran: TSQLTransaction;
  7. begin
  8.     if self.db_cons.Count = 0 then
  9.     begin
  10.         a_con  := TPQConnection.Create(nil);
  11.         a_tran := TSQLTransaction.Create(a_con);
  12.         a_con.HostName     := '127.0.0.1';
  13.         a_con.DatabaseName := 'dbname';
  14.         a_con.Transaction  := a_tran;
  15.         a_con.UserName     := 'user';
  16.         a_con.Password     := 'pwd';
  17.         a_con.Connected    := True;
  18.     end else
  19.     begin
  20.         a_con := self.db_cons.First as TPQConnection;
  21.         self.db_cons.Remove(a_con);
  22.     end;
  23.     Result := a_con;
  24. end;
  25.  
  26. function TFPWebModule1.push_con(acon: TSQLConnection): boolean;
  27. begin
  28.     self.db_cons.Add(acon);
  29.     Result := True;
  30.     writeln('New Count :' + self.db_cons.Count.ToString);
  31. end;  
  32.  

 

TinyPortal © 2005-2018