Mojolicious-Lite Mojo-Sqlite CRUD Example #1842

pdkakoba · 2021-09-01T17:30:47Z

pdkakoba
Sep 1, 2021

Inspired by Joel Berger's blog of October 2012 on a Mojolicious-Lite Sqlite example, but here Mojo-Sqlite has been use to give a fuller CRUD demonstration, together with helpers and with templates linked to w3.css. Here is the code:

use Mojolicious::Lite;
# connect to database
use Mojo::SQLite;
my $sql = Mojo::SQLite->new('database.db') or die "Could not connect";

# add helper methods for interacting with database
helper db => sub { $sql->db };

helper create_table => sub {
  my $self = shift;
  warn "Creating table 'posts'\n";
  $sql->migrations->name('mojosqlite2')->from_string(<<EOF)->migrate;
-- 1 up
create table posts (id integer primary key autoincrement, title text, body  text);
-- 1 down
drop table posts;
EOF
};

helper select => sub {
  my $self = shift;
  my $sth = eval { $self->db->select('posts') } || return undef;
  return $sth->hashes->to_array;
};

helper insert => sub {
  my $self = shift;
  my ($title, $body) = @_;
  my $sth = $self->db->insert('posts', {title => $title, body => $body})->last_insert_id;
  return 1;
};

helper delete => sub {
  my $self = shift;
  my ($id) = @_;
  my $sth = $self->db->delete('posts', {id => $id});
  return 1;
};

helper edit => sub {
  my $self = shift;
  my ($id) = @_;
  my $sth = $self->db->select('posts', undef, {id => $id});
  return $sth->hash;
};

helper update => sub {
  my $self = shift;
  my ($id, $title, $body) = @_; 
  my $sth = $self->db->update('posts', {title => $title, body => $body}, {id => $id});
  return 1;
};
# if statement didn't succeed, assume its because the table doesn't exist
app->select || app->create_table;

# setup base route
any '/' => sub {
  my $self = shift;
  my $posts = $self->select;
  $self->stash( posts => $posts );
  $self->render('index');
};

# setup route which receives data and returns to /
any '/insert' => sub {
  my $self = shift;
  my $title = $self->param('title');
  my $body = $self->param('body');
  my $insert = $self->insert($title, $body);
  $self->redirect_to('/');
};

post '/delete' => sub {
  my $self = shift;
  my $id = $self->param('id');
  my $del = $self->delete($id);
  $self->redirect_to('/');
};

get '/edit' => sub {
  my $self = shift;
  my $id = $self->param('id');
  my $post = $self->edit($id);
  $self->stash( post => $post );
  $self->render('edit');
};

put '/update' => sub {
  my $self = shift;
  my $id = $self->param('id');
  my $title = $self->param('title');
  my $body = $self->param('body');
  my $update = $self->update($id, $title, $body);
  $self->redirect_to('/');
 };

app->start;

__DATA__

@@ index.html.ep
<!DOCTYPE html>
<html>
<link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> 
<head><title>Posts</title></head>
<body>
  <form class="w3-container" action="<%=url_for('insert')->to_abs%>" method="post">
    Title: <input class="w3-input w3-border-1" type="text" name="title"> 
    Body: <input class="w3-input w3-border-1" type="text" name="body"> 
    <input class="w3-button w3-hover-black" type="submit" value="Add">
  </form>
  <br>
    <form class="w3-container" action="<%=url_for('delete')->to_abs%>" method="post">
    Id: <input class=class="w3-input w3-border-1" type="text" name="id" style="width:5%">  
    <input class="w3-button w3-hover-black" type="submit" value="Delete">
  </form>
      <form class="w3-container" action="<%=url_for('edit')->to_abs%>">
    Id: <input class=class="w3-input w3-border-1" type="text" name="id" style="width:5%">  
    <input class="w3-button w3-hover-black" type="submit" value="Edit">
  </form>
  
  <br>
  Data: <br>
  <table class="w3-table">
    <tr>
	  <th>Id</th>
      <th>Title</th>
      <th>Body</th>
    </tr>
    % for my $post (@$posts) {
      <tr>
          <td><%= $post->{id} %></td>
		  <td><%= $post->{title} %></td>
		  <td><%= $post->{body} %></td>
      </tr>
    % }
  </table>
</body>
</html>

@@edit.html.ep
<head><title>Edit-Post</title></head>
<body>
  %= form_for 'update' => begin
  %= text_field id => $post->{id}
  %= label_for title => 'Title'
  <br>
  %= text_field title => $post->{title}
  <br>
  %= label_for body => 'Body'
  <br>
  %= text_area body => $post->{body}
  <br>
  %= submit_button 'Update'
% end
</body>
</html>

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Mojolicious-Lite Mojo-Sqlite CRUD Example #1842

{{title}}

Replies: 0 comments

Select a reply

Mojolicious-Lite Mojo-Sqlite CRUD Example #1842

pdkakoba Sep 1, 2021

Replies: 0 comments

pdkakoba
Sep 1, 2021