class: center, middle # SQL Database Access from Perl 6 Curt Tilmes *curt@tilmes.org* *Philadelphia Perl Mongers* 2019-01-14 (edited!) --- # Introduction * SQL Databases are a very common backend data store. * Perl 5 has long had very solid database access modules. * There are some pretty good attempts at ports to Perl 6, but they have various drawbacks for some use cases. (They work great for others.) * I am proposing a new model, but it is of yet immature. --- # Perl 5 * Perl 5 has long had the widely used (and acclaimed) **DBI** (https://dbi.perl.org) * **DBI** provides a very generic interface to many SQL databases, and underlies many Perl 5 Database Abstraction modules and Object Relational Models (**ORM**s) * **DBI** uses a two tier structure with a matching **DBD** underneath. Each type of database implments a **DBD** Database Driver. .center[<img src="dbi.png" width="400">] --- # Perl 5 - Mojolicious * Sebastian Riedel and many others have developed a comprehensive overall real-time web framework called **Mojolicious**. https://mojolicious.org * They reimplemented the front-end to the existing **DBD** back-ends to fit more cleanly into the **Mojolicious** framework. * These include **Mojo::Pg** and **Mojo::mysql**. * They offer a friendly, easy to use interface, and provide some features like automatic caching of database connections and prepared statement handles. --- # Perl 6 * Martin Berends started with **MiniDBI** (https://github.com/mberends/MiniDBI) - _a subset of Perl 5 DBI ported to Perl 6 to use while experts build the Real Deal_ * Tim Bunce has a project called **DBDI** (https://github.com/timbunce/DBDI) that was announced as the future in 2010. It appears to have stalled. * Moritz Lenz developed **DBIish** (https://github.com/perl6/DBIish), by far the most popular Perl 6 Database access method to date. It isn't exactly DBI, but is sort of DBIish. * I developed **DB::Pg** (https://github.com/CurtTilmes/perl6-dbpg), a PostgreSQL specific database access module. - **DB::Pg** fixes a number of fundamental bugs in **DBDish::Pg** and brings an interface more similar to **Mojo::Pg** for those familiar with that. - **DB::Pg** is decidedly not generic, but it also provides access to some very valuable PostgreSQL specific features. --- # Why **DB::Pg**? * Perl 6 is fundamentally multi-threaded. **DBIish** (at least **DBDish::Pg** and probably others) has some bugs in that environment that can lead to crashes. * I tried to build on top of the existing **DBDish::Pg** the way **Mojo::Pg** did, but couldn't get it to work. * Perl 5 uses reference counting for memory management. You are reliably notified when any object leaves scope. **Mojo::Pg** uses this to manage connection cachine. * Perl 5 uses Garbage Collection (GC) that periodically runs in a background thread. Data structures can move around in memory on you without notification. Objects that are no longer needed will sit around until the GC decides to deal with them. * **DBIish** gets around this by requiring you to explicitly close/free/etc. handles when you are finished with them. Not as easy to use. * **DBIish** with its very generic interface makes it harder to access database specific features. --- # What next? * **DB::Pg** has received some interest and is now recommended over **DBIish** in some cases. * Some are asking about a similar approach for other databases. -- # The Plan * Extract generic bits out of **DB::Pg** into a **DB**, then build new database specific modules for others. * Starting with **DB::SQLite** and **DB::MySQL**. * I'd like to eventually re-factor **DB::Pg** itself to use the new **DB**, but I want to be careful not to break anything. --- # Overall Structure for DB * **DB** - A single 'top level' object. * **DB::Connection** - An active database connection * **DB::Statement** - A prepared database SQL query * **DB::Result** - Results from a database query, ready to provide them in various ways. At each level, the objects hold pointers back up to the parent object. So when the **Result** is finished, it tells the **Statement** to return its **Connection** to the top level **DB** cache. --- # DB * It holds the connection information and can manufacture new database connections on demand. When someone returns a connection, it holds them in a cache to give to the next requester. * It has some shortcut methods that simply take a connection, perform a query with it, then return immediately to the cache. These are very easy to use without the traditional **DBIish** boilerplate. * `.db` - Creates a **DB::Connection**, or returns one from cache. * `.query` - Normal, cached, prepared, database query that returns results. * `.execute` - Non-prepared (no placeholders) query, usually doesn't return results (it does for MySQL). * `.finish` - Frees up all the resources from cached connections (often not needed). --- # DB::Connection * Very similar to **DBIish**. * It also maintains a cache of prepared statements. If you make the same query again, it will use the cached statement rather than re-preparing. * `.execute` - execute a query without preparation. * `.prepare` - to create a new prepared statement (and cache it). * `.query` - a shortcut to `prepare`, then `execute` * `.finish` - Return the Connection to the connection cache. --- # DB::Statement * Very similar to **DBIish**. - `.execute` - to execute the prepared query, with specified arguments - `.finish` - just forwards the finish back up to the Connection. --- # DB::Result * Modeled more after **Mojo::Pg** and friends than **DBI** or **DBIish**. * Has a number of methods for returning the resulting information: - `.value` - Return a single result. - `.array` - Return a single row from the query. - `.hash` - Return a single row from the query, but as a hash. - `.arrays` - Return all the rows from the query. - `.hashes` - Return all the rows from the query, but as hashes. --- # Examples ```perl6 use DB::MySQL; my $mysql = DB::MySQL.new; # Can pass in various options $mysql.execute('create table foo (x int)'); $mysql.execute('insert into foo values (42)'); say $mysql.query('select 42').value; # 42 say $mysql.query('select ?', 42).value; # 42 say $mysql.query('select ?, ?', 42, 'this').array; # (42 this) say $mysql.query('select ? as a, ? as b', 42, 'this').hash; # { a => 42, b => this } ``` * Each of those gets a database handle, performs the action, then returns the handle to the cache. You can run multiple commands like this in different threads safely. --- # Explicit connection handling ```perl6 use DB::MySQL; my $mysql = DB::MySQL.new; # Can pass in various options my $db = $mysql.db; # Request a database connection say $db.query('select 42').value; .say for $db.query('select * from foo').hashes; $db.finish; # Explicitly return the handle ``` * Avoid the overhead of getting a handle for each query if you have a bunch of them to perform. * This is more like traditional **DBIish**. --- # Explicit statement preparation ```perl6 use DB::MySQL; my $mysql = DB::MySQL.new; # Can pass in various options my $db = $mysql.db; # Request a database connection my $sth = $db.prepare('select ?'); say $sth.execute(42).value; say $sth.execute('this').value; $db.finish; # Explicitly return the handle ``` * More efficient than pulling the connection/statement from the cache each time. Perl 6 `will` phasers on variables can be nice: ```perl6 my $db will leave {.finish } = $mysql.db; ``` --- # Transactions ```perl my $db = $mysql.db; my $sth = $db.prepare('insert into foo(x,y) values (?,?)'); $db.begin; $sth.execute(1, 'this'); $sth.execute(2, 'that'); $db.commit; # or $db.rollback $db.finish; ``` * `.finish` without `.commit` will automatically `.rollback` * If you are doing just one transaction, you can commit and finish together with `$db.commit.finish;` --- # Conclusion * **DB::Pg** is pretty mature, but the other **DB::*** modules are not yet. * **DB::SQLite** and **DB::MySQL** need more documentation and testing, but should have a preliminary release to CPAN soon. -- ##.center[Thank You!]