Mojo::Pg

[sri]

I've mentioned last week that i was working on a little something to make using PostgreSQL with Mojolicious more fun.

    https://github.com/kraih/mojo-pg

It's still very much experimental, and support for migrations is missing. The idea is to embrace SQL, and make asynchronous queries super simple.

I'm intentionally using the word "asynchronous" here, because it is based on DBD::Pg, so all I/O operations are blocking, but it allows us to let the event loop do other work (like handling other HTTP requests) while we wait for long running queries. Since database connections usually have a very low latency, the performance with blocking I/O and asynchronous queries can be quite excellent.

It also has some other fun properties that i'm currently experimenting with.

    # Sequential but asynchronous

    my $db = $pg->db;

    $db->query('select * from table1' => sub {...});

    $db->query('select * from table2' => sub {...});

A DBD::Pg connection can only handle one query at a time, this includes asynchronous ones, so the example above would normally crash and burn. But Mojo::Pg currently puts the second query into a waiting list and will perform it on the same connection once the first query has been finished. The example below on the other hand uses two DBD::Pg connections to actually perform the queries concurrently.

    # Concurrent and asynchronous

    $pg->db->query('select * from table1' => sub {...});

    $pg->db->query('select * from table2' => sub {...});

Of course i couldn't resist mojo-ifying the API a bit, so there's Mojo::Collection objects too. :)

    $pg->db->query('select foo, baz from table3 where foo = ?', 'bar')->hashes->map(sub { $->{baz} })->join->say;

I'm quite happy with the results so far, but it's still just an experiment for now.

--

sebastian

[sri]

It also has some other fun properties that i'm currently experimenting with.

Haha, i didn't actually mention all the normal stuff... like automatic caching of database and statement handles, or cleaning up cached connections after a fork(). I think that stuff should really just work, and you shouldn't ever have to think about it.

--

sebastian 

[Nacho B]

...so you are quite happy with the results! Well, you can't imagine how happy I am. :-)

Just right now I am in trying to switch the framework (Cat. to Mojolicious), switch the database (My to Pg), and return to proper SQL sentences after three years in a boring full schema ORM way of life. Three in a row!

Thank you very much. This is the extra push I needed:
Nacho B

[Ben van Staveren]

Nice work! Gave it a little whirl and it pretty much does what's
advertised on the box. Solves a few of my own problems right there too.

sri++

(as usual)

[David Stevenson]

Ooh, nice one, looking forward to giving this a go very soon. Thanks :)

On 3 Oct 2014, at 18:24, sri <kra...@googlemail.com> wrote:

> I've mentioned last week that i was working on a little something to make using PostgreSQL with Mojolicious more fun.
>
> https://github.com/kraih/mojo-pg
>

> It's still very much experimental, and support for migrations is missing. The idea is to embrace SQL, and make asynchronous queries super simple….

[Николай Турнавиотов]

Can somebody help me with code which adds using a db helper from startup method from a full mojo application in a my own model Users?

in growing manual we have a $USERS hash helper, which must be changed to $self->db-> ... query to current db helper, but I can't write this fragment of code, because newbie in perl and mojo.

thanks

[sri]

Can somebody help me with code which adds using a db helper from startup method from a full mojo application in a my own model Users?

in growing manual we have a $USERS hash helper, which must be changed to $self->db-> ... query to current db helper, but I can't write this fragment of code, because newbie in perl and mojo.

Please don't hijack threads, open a new one for questions that are not directly related to the original topic.

--

sebastian

[Paul Williams]

This is fantastic. I'm quite excited about this!

I have some small Mojolicious::Lite applications using DBD::Pg and DBIx::Connector, so will give this a whirl over the next couple of days.

The thing that caught my eye was the fact that you've implemented notifications. This will make the combination of Mojo + Pg a lot more powerful.

--

kwa

[sri]

...and support for migrations is missing.

Experimenting with something now.

    https://github.com/kraih/mojo-pg/commit/bbb90e1576f7e97600958a6cf66626ac7d766521

Simple migrations in the DATA section are really fun. :)

    https://gist.github.com/anonymous/b166b9a1ef898dafe7c9

--

sebastian 

[sri]

Experimenting with something now.

    https://github.com/kraih/mojo-pg/commit/bbb90e1576f7e97600958a6cf66626ac7d766521

Simple migrations in the DATA section are really fun. :)

    https://gist.github.com/anonymous/b166b9a1ef898dafe7c9

And released with Mojo::Pg 0.04.

    https://github.com/kraih/mojo-pg/compare/v0.03...v0.04#diff-c112bb3542e98308d12d5ecb10a67abcR2

--

sebastian 

[sri]

And released with Mojo::Pg 0.04.

This is no guarantee migrations will stay though, i'm still not 100% sure about them.

P.S.: And before anyone asks... nope there is no chance they will go into a separate distribution and become database agnostic, i'm using PostgreSQL features quite extensively.

--

sebastian 

[sri]

P.S.: And before anyone asks... nope there is no chance they will go into a separate distribution and become database agnostic, i'm using PostgreSQL features quite extensively.

P.P.S: PostgreSQL totally rocks! :)

--

sebastian 

[Paolo Saudin]

Hi,

I'm trying this module but I get an error on the non-blocking sample, while the other methods works fine :

# Select all rows non-blocking

Mojo::IOLoop->delay(

  sub {

    my $delay = shift;

    $db->query('select * from names' => $delay->begin);

  },

  sub {

    my ($delay, $err, $results) = @_;

    $results->hashes->pluck('name')->join("\n")->say;

  }

)->wait;

Mojo::Reactor::Poll: Timer be5d85eb26e795680ba9a94d8d356dcc failed: Can't use an undefined value as a symbol reference at C:/Perl64/site/lib/Mojo/Reactor/Poll.pm line 16.

Windows 8

Perl : (v5.16.3, MSWin32)
Mojolicious : 5.49
Mojo-Pg : 0.06

Is there something I am doing wrong ?

regards

paolo saudin

--
You received this message because you are subscribed to the Google Groups "Mojolicious" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mojolicious...@googlegroups.com.
To post to this group, send email to mojol...@googlegroups.com.
Visit this group at http://groups.google.com/group/mojolicious.
For more options, visit https://groups.google.com/d/optout.

[sri]

Windows 8

Perl : (v5.16.3, MSWin32)
Mojolicious : 5.49
Mojo-Pg : 0.06

Could be a Windows problem, it works fine for me on OS X.

--

sebastian 

[sri]

Funny thing, finding a good pattern for transaction management was actually harder than implementing migrations. The problem is that with RaiseError the $db object goes out of scope and puts the database handle, which looks fine, back into the connection cache with a transaction that's still in progress. So what i ended up with is the transaction scope guard approach from DBIx::Class, which as it turns out, works extremely well with async queries as well.

  # Commit

  {

    my $tx = $db->begin;

    $db->query('insert into foo values (?)', 'one');

    $db->query('insert into foo values (?)', 'two');

    $tx->commit;

  };

 If $tx goes out of scope before $tx->commit has been called, it will trigger a rollback, super simple.

  # Rollback

  {

    my $tx = $db->begin;

    $db->query('insert into foo values (?)', 'one');

    $db->query('insert into foo values (?)', 'two');

  };

This is the latest addition in Mojo::Pg 0.07, which i've just released.

--

sebastian

[sri]

Mojo::Reactor::Poll: Timer be5d85eb26e795680ba9a94d8d356dcc failed: Can't use an undefined value as a symbol reference at C:/Perl64/site/lib/Mojo/Reactor/Poll.pm line 16.

Oh i just remembered something, PostgreSQL might be using named pipes on Windows, which don't work with the poll() system call.

--

sebastian

[sri]

Oh i just remembered something, PostgreSQL might be using named pipes on Windows, which don't work with the poll() system call.

Might be interesting to see what $db->dbh->pg_socket returns.

--

sebastian 

[Paolo Saudin]

I added this line in the script - print Dumper( $dbh->{pg_socket} ); - and I get 

$VAR1 = 196;

thanks

paolo

--

[sri]

I added this line in the script - print Dumper( $dbh->{pg_socket} ); - and I get 

$VAR1 = 196;

Interesting, and if you do "fileno(IO::Handle->new_from_fd($dbh->{pg_socket}, 'r'))"?

--

sebastian

[Paolo Saudin]

the code I added :

print Dumper( $dbh->{pg_socket} );

if ( defined ( fileno(IO::Handle->new_from_fd($dbh->{pg_socket}, 'r')) ) ) {

  print "defined";

} else {

  print "undefined";

}

and I get :

104

undefined

paolo

--