Wise Monks,
What is the best practice for dealing (rw) with a Database from a Web Framework like Mojolicious (Lite) in an efficient way but also safe?
My concern is that because of the use of threads in such frameworks, keeping a DB handle over time will lead to problems. (let's say I keep the handle for 100 queries, then refresh it)
My other concern is that getting a new DB handle EACH TIME can be expensive. In my case, and for now, I use SQLite to store the users table - perhaps that's a wrong decision but I try to avoid, at the moment, the hassle of setting up a "normal" db like mysql, pg etc. -- root passwords, grant privileges etc.
What I did is to create a DB class which handles connecting/disconnecting/querying the database. I can make this higher-level class to reconnect each time it is asked to do a new query. Fine. And I guess it is safe to create it once in my Mojo app and save it in a helper. The worst thing that can happen is to duplicate the object among the threads which is fine if it does not keep the db connections.
What are your opinions?
EDIT 20-Dec-2024
I want to clarify that by "threads" I mean a server which forks or creates new thread for handling connections from clients. And my question is: how to create a database handle in view of the fact that at some point in time in the life of the app (e.g. a Mojolicious Lite server "app") there is the possibility that a new webserver worker will be created (forked/thread'ed) to handle many simultaneous clients and hence the db handle duplicated. As simple as this. It is irrelevant if grepping the Mojolicious source code finds no "use threads" and likely I have added confusion by mentioning a web framework at all. The gist is:
how to handle database connection to a single database SQLite, or even + Pg, MySQL, from a possibly-forking program. Do I initiate the DB con +nection from each forked worker? Or can I share parent's DB handle am +ong the children/workers? The 1st option means that I connect/disconn +ect to the db each time a worker is spawned. How then is possible to +keep a DB connection alive "for-ever"?
Here is a simple Mojolicious server script which unfortunately does not fork even when I crashed it with a 100 simultaneous clients, it kept handling with one worker. Oh well perhaps someone can twig it to fork workers and then tell me what to do with the database handle.
In the meantime I think the safest thing to do is to use Mojo::Pg or Mojo::MySQL or DBIx::Connector (as fellow monks suggested below) and obtain the db handle from that module instead of initiating the connection myself (via DBI->connect()). Thank you monks.
# run: hypnotoad myapp.pl # then (for 6 simultaneous clients) # for i in {1..6}; do echo $i; done | parallel -j 6 "echo '$0 : call +ed with pid=$$ and param={}'; curl 'http://localhost:4444/' -o 'a{}'" # with no GNU parallel, do this: # for i in {1..6}; do wget 'http://localhost:4444/' -O a$i & done use Mojolicious::Lite; use Mojolicious::Plugin::OnFork; my $PORT = 4444; plugin OnFork => sub { print "FORKED with pid=$$ !\n"; }; get '/' => sub { my $c = $_[0]; sleep(1); $c->render( 'text' => "hello there from pid=$$\n" ); }; my $daemon = Mojo::Server::Daemon->new( app => app, listen => ["http://*:${PORT}"], single_accept => 0, keep_alive_timeout => 1, ); print "$0 : started listening on port ${PORT} (pid $$) ...\n"; $daemon->run;
bw, bliako
In reply to Database access and async web framework (like Mojolicious) by bliako
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |