REST API SERVER QUESTION
lazarus / 38-Networking and Web Programming

----------------------------------------------------------------------
heejit · 2018-02-27 19:56

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.

----------------------------------------------------------------------
Trenatos · 2018-03-01 16:29

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 · 2018-03-01 17:02

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.

----------------------------------------------------------------------
Trenatos · 2018-03-01 17:23

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 · 2018-03-01 18:38

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 · 2018-03-02 13:10

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i db_cons:
TFPObjectList; function TFPWebModule1.pull_con(): TSQLConnection;var 
 a_con: TSQLConnection;    a_tran: TSQLTransaction;begin    if
self.db_cons.Count = 0 then    begin        a_con  :=
TPQConnection.Create(nil);        a_tran :=
TSQLTransaction.Create(a_con);        a_con.HostName     :=
'127.0.0.1';        a_con.DatabaseName := 'dbname';       
a_con.Transaction  := a_tran;        a_con.UserName     := 'user';   
   a_con.Password     := 'pwd';        a_con.Connected    := True;   
end else    begin        a_con := self.db_cons.First as
TPQConnection;        self.db_cons.Remove(a_con);    end;    Result :=
a_con;end; function TFPWebModule1.push_con(acon: TSQLConnection):
boolean;begin    self.db_cons.Add(acon);    Result := True;   
writeln('New Count :' + self.db_cons.Count.ToString);end;