A Simple Mojolicious/DBI Example
A friend of mine is learning database administration, but for the first time needs to connect it to a webapp. Having done most of his work in MS Access, he has no idea what a web framework is, nor how one would connect a database to it. To help him learn I wrote up a little application using Mojolicious and SQLite.
In the end it turned about to be an interesting demonstration of Mojolicious’ templating and helpers as well as DBI and all that, so I thought I would post it here. Perhaps some of you may learn from it or maybe you want to use it as an example to others.
#!/usr/bin/env perl
use Mojolicious::Lite;
# connect to database
use DBI;
my $dbh = DBI->connect("dbi:SQLite:database.db","","") or die "Could not connect";
# shortcut for use in template
helper db => sub { $dbh };
# setup base route
any '/' => 'index';
my $insert;
while (1) {
# create insert statement
$insert = eval { $dbh->prepare('INSERT INTO people VALUES (?,?)') };
# break out of loop if statement prepared
last if $insert;
# if statement didn't prepare, assume its because the table doesn't exist
warn "Creating table 'people'\n";
$dbh->do('CREATE TABLE people (name varchar(255), age int);');
}
# setup route which receives data and returns to /
post '/insert' => sub {
my $self = shift;
my $name = $self->param('name');
my $age = $self->param('age');
$insert->execute($name, $age);
$self->redirect_to('/');
};
app->start;
__DATA__
@@ index.html.ep
% my $sth = db->prepare('SELECT * FROM people');
% $sth->execute;
<!DOCTYPE html>
<html>
<head><title>People</title></head>
<body>
<form action="<%=url_for('insert')->to_abs%>" method="post">
Name: <input type="text" name="name">
Age: <input type="text" name="age"
<input type="submit" value="Add">
</form>
<br>
Data: <br>
<table border="1">
<tr>
<th>Name</th>
<th>Age</th>
</tr>
% while (my $row = $sth->fetchrow_arrayref) {
<tr>
% for my $text (@$row) {
<td><%= $text %></td>
% }
</tr>
% }
</table>
</body>
</html>
Originally it was on GitHub but I disliked the syntax highlighting so I’m posting it directly here rather than linking.
EDIT
Given Aristotle’s concerns voiced below, I have made a second example with a little more proper style. It makes more heavy use of Mojolicious’s concept of helper methods and puts less burden on the template. This is more common form for MVC apps.
#!/usr/bin/env perl
use Mojolicious::Lite;
# connect to database
use DBI;
my $dbh = DBI->connect("dbi:SQLite:database.db","","") or die "Could not connect";
# add helper methods for interacting with database
helper db => sub { $dbh };
helper create_table => sub {
my $self = shift;
warn "Creating table 'people'\n";
$self->db->do('CREATE TABLE people (name varchar(255), age int);');
};
helper select => sub {
my $self = shift;
my $sth = eval { $self->db->prepare('SELECT * FROM people') } || return undef;
$sth->execute;
return $sth->fetchall_arrayref;
};
helper insert => sub {
my $self = shift;
my ($name, $age) = @_;
my $sth = eval { $self->db->prepare('INSERT INTO people VALUES (?,?)') } || return undef;
$sth->execute($name, $age);
return 1;
};
# if statement didn't prepare, assume its because the table doesn't exist
app->select || app->create_table;
# setup base route
any '/' => sub {
my $self = shift;
my $rows = $self->select;
$self->stash( rows => $rows );
$self->render('index');
};
# setup route which receives data and returns to /
any '/insert' => sub {
my $self = shift;
my $name = $self->param('name');
my $age = $self->param('age');
my $insert = $self->insert($name, $age);
$self->redirect_to('/');
};
app->start;
__DATA__
@@ index.html.ep
<!DOCTYPE html>
<html>
<head><title>People</title></head>
<body>
<form action="<%=url_for('insert')->to_abs%>" method="post">
Name: <input type="text" name="name">
Age: <input type="text" name="age">
<input type="submit" value="Add">
</form>
<br>
Data: <br>
<table border="1">
<tr>
<th>Name</th>
<th>Age</th>
</tr>
% foreach my $row (@$rows) {
<tr>
% foreach my $text (@$row) {
<td><%= $text %></td>
% }
</tr>
% }
</table>
</body>
</html>
Edit: This example continues as we add WebSockets to the mix for a no-refresh modern flavor here!
As I delve into the deeper Perl magic I like to share what I can.
Thanks Joel! I did not realize just how easy it was to start writing Mojolicious apps. I do webapp+database stuff all the time, and having a small but concrete example directly relevant to something I’m working on really helps.
I’ve posted a response using Plack.
Some comments, all of which supposing that this grows:
Leaving an SQL query prepared for the entire lifetime of the process is not a good idea to adopt as a style.
Having the template run SQL queries directly will soon lead to spaghetti.
In fact, even having the controller run SQL queries directly will soon lead to spaghetti.
The rationales for these are as follows:
Prepared SQL queries generally take up database server resources while they stick around, so if you have lots of different queries and/or lots of processes it’ll tax the biggest bottleneck you have.
The controller should be responsible for setting up all necessary data and passing it to the template through the stash.
Note that it’s fine for the template to fetch the data from a statement handle – it should just not be setting up the STH itself in that case, but expecting the controller to pass one in. Basically, the template’s job is only to render the data into some displayable form, but not to select which data it is going to show. That decision should be solely the controller’s.
The effect of this is that templates become a lot more self-contained. They do a lot less, so they can be used in a lot more situations. Aside from code reuse, this also means far easier testability – you can just pass it some test data (or if the template expects an STH it can be a DBD::Sponge one), instead of having to jigger up an entire environment including the entire database etc that you would need if the template insisted on doing that job itself.
The controller methods should not contain SQL either. Instead the database access should be collected in a separate module with methods named after their purpose. This is typically referred to as the model; or else call it the business logic. Most people use an ORM for this, but the concept is abstract and completely independent of how you implement it. A rough idea of it is that it is responsible for making all data-based decisions that depend on what is in the database.
The controller, then, should be thought as a thin layer that takes incoming HTTP requests and decides how to translate them into model method calls, whose results it then places on the stash for the template to render.
This way, you get to centralise decisions that have to be handled the same way several places, instead of strewing things all over the controller methods and duplicating logic.
So as a rule of thumb, the shorter your controller methods, the better you are doing.
You will note that these are all issues that only become important when the code starts to grow. I want to stress that if the above code is all there will ever be to the program, then it is fine as it is.
Hi Aristotle! Your points are well made and I encourage all future readers to heed them. I can explain the reason I did what I did.
So yes, this is not perhaps a “best practices” example, but neither was it meant to be. It was meant as a “my first webapp”.
I have added an additional example using a more consistent style. Just because the template CAN do lots doesn’t mean that it should :-)
Reply to Aristotle@here.and.now.com
Well written as always.
My way of explaining ‘The controller, then, should be thought as…’ is:
o The Controller is like a human manager, who know (1) what needs to be done, (2) why, and (3) when.
o Model and View are workers, who know (4) how things are done, and so do them.
In short, the manager takes decisions and the workers carry them out.
MVC then is just like any (functioning!) organization.
Cheers
Understood, and taken as such. My comment was likewise meant not as a voicing of concerns but as “To whomever found this useful to learn from”, as a map of what follows. That is why I wrote out the rationales and stressed that the example is not wrong – i.e. those things I listed are concepts not rules, to be applied with judgement.
(I made to add an example to illustrate the points concretely, but by then I had worked on the comment long enough to not bother yet, and just told myself I might come back to it at some point. Nice that you beat me to it – win win. :-))
Leaving an SQL query prepared for the entire lifetime of the process is not a good idea to adopt as a style.
This is highly dependent on the type of application you are writing and the RDBMS you are using. If you writing the type of application most people write, that just happens to use the database for occasional storage and retrieval of data, I agree that leaving a prepared statement around may be wasteful and may not be the best (depending on your RDBMS and environment).
But if you are the kind of soul that writes truly database-centric applications, that have data constantly coming in and going out your database, establishing prepared statements and keeping them around for the life of your process may be exactly what you want to do. It depends on your application, your data, and the RDBMS you are using.
Of course, as Joel said, the original idea was not to persist a prepared statement, but to make sure a SQL statement could be run. If the goal was to persist a prepared statement (and thus a database connection) across requests to the same process, DBI->connect_cached() and prepare_cached() are the proper methods to use, as connect_cached() would reuse the existing connection or re-establish the connection if it drops, and prepare_cached() would transparently re-prep the statement as needed. This might also necessitate moving the connect_cached() call to somewhere closer to the prepare_cached() call (as in, right before it to ensure the database is available).
This is a nice, simple example. Howoever, I think that any application written as an example of using DBI with Mojolicious should provide the good example of database connection management. Eventually that connection is going to be dropped.
That logic would almost certainly go in the
dbhelper. I think that would make for an excellent follow-up post! Go for it. In truth I use DBIC mostly so I don’t need to handle that myself, therefore I don’t trust myself to write it.Wonderful. I am looking for Mojolicious Application Example to understand it. This is so helpful :)
Thanks to aristotle for his very very valuable points for a new bie.
Hello!
For Unicode support there are few fixes needed:
Add:
use Encode; plugin Charset => {charset => 'utf-8'};modify / method to:
any '/' => sub { my $self = shift; my $rows = $self->select; # convert utf-8 octets -> unicode for my $Item ( @{ $rows } ){ map { $_ = decode('utf-8',$_) } @{ $Item }; } $self->stash( rows => $rows ); $self->render('index'); };Best regards
—Henryk Paluch
Before I ask a question, let me say thank you for all the examples and explanations (I see your name frequently).
I have gotten a handle on the fundamentals of Mojolicious. The one thing I can not find any information on is, creating a schema from and existing MS SQL database then connecting.
Everything I read is SQLite or any other version beside MS SQL. I’m starting to wonder if it’s similar to Corona SDK in that you create a sql database to store information, then push that data to your server DB.
If anyone else has any experience or information PLEASE let me know. I have been stuck on this for almost two weeks.
Thank you.
Before I answer, let me just say that commenting on an ancient post is unlikely to get great responses mostly because the visibility is so low. I only happened to notice because it was on the posting comment dashboard which I rarely look at. In the future, come talk with us http://mojolicious.org/perldoc#SUPPORT
To your actual question. I think very few of us use MS SQL, mostly just because most of us are on *nix. I think were I you, I’d look for tools like sqitch etc which are intentionally multi-db. I haven’t used it myself, nor am I sure that it can handle MS SQL, but that’s where I’d look first! Good luck!