Mojo::SQLite(3pm)

1Mojo::SQLite(3)       User Contributed Perl Documentation      Mojo::SQLite(3)
2
3
4

NAME

6       Mojo::SQLite - A tiny Mojolicious wrapper for SQLite
7

SYNOPSIS

9         use Mojo::SQLite;
10
11         # Select the library version
12         my $sql = Mojo::SQLite->new('sqlite:test.db');
13         say $sql->db->query('select sqlite_version() as version')->hash->{version};
14
15         # Use migrations to create a table
16         $sql->migrations->name('my_names_app')->from_string(<<EOF)->migrate;
17         -- 1 up
18         create table names (id integer primary key autoincrement, name text);
19         -- 1 down
20         drop table names;
21         EOF
22
23         # Use migrations to drop and recreate the table
24         $sql->migrations->migrate(0)->migrate;
25
26         # Get a database handle from the cache for multiple queries
27         my $db = $sql->db;
28
29         # Use SQL::Abstract to generate simple CRUD queries for you
30         $db->insert('names', {name => 'Isabel'});
31         my $id = $db->select('names', ['id'], {name => 'Isabel'})->hash->{id};
32         $db->update('names', {name => 'Bel'}, {id => $id});
33         $db->delete('names', {name => 'Bel'});
34
35         # Insert a few rows in a transaction with SQL and placeholders
36         eval {
37           my $tx = $db->begin;
38           $db->query('insert into names (name) values (?)', 'Sara');
39           $db->query('insert into names (name) values (?)', 'Stefan');
40           $tx->commit;
41         };
42         say $@ if $@;
43
44         # Insert another row with SQL::Abstract and return the generated id
45         say $db->insert('names', {name => 'Daniel'})->last_insert_id;
46
47         # JSON roundtrip
48         say $db->query('select ? as foo', {json => {bar => 'baz'}})
49           ->expand(json => 'foo')->hash->{foo}{bar};
50
51         # Select one row at a time
52         my $results = $db->query('select * from names');
53         while (my $next = $results->hash) {
54           say $next->{name};
55         }
56
57         # Select all rows with SQL::Abstract
58         say $_->{name} for $db->select('names')->hashes->each;
59

DESCRIPTION

61       Mojo::SQLite is a tiny wrapper around DBD::SQLite that makes SQLite
62       <https://www.sqlite.org/> a lot of fun to use with the Mojolicious
63       <https://mojolico.us> real-time web framework. Use all SQL features
64       <http://sqlite.org/lang.html> SQLite has to offer, generate CRUD
65       queries from data structures, and manage your database schema with
66       migrations.
67

BASICS

69       Database and statement handles are cached automatically, so they can be
70       reused transparently to increase performance. And you can handle
71       connection timeouts gracefully by holding on to them only for short
72       amounts of time.
73
74         use Mojolicious::Lite;
75         use Mojo::SQLite;
76
77         helper sqlite => sub { state $sql = Mojo::SQLite->new('sqlite:test.db') };
78
79         get '/' => sub {
80           my $c  = shift;
81           my $db = $c->sqlite->db;
82           $c->render(json => $db->query(q{select datetime('now','localtime') as now})->hash);
83         };
84
85         app->start;
86
87       In this example application, we create a "sqlite" helper to store a
88       Mojo::SQLite object. Our action calls that helper and uses the method
89       "db" in Mojo::SQLite to dequeue a Mojo::SQLite::Database object from
90       the connection pool. Then we use the method "query" in
91       Mojo::SQLite::Database to execute an SQL
92       <http://www.postgresql.org/docs/current/static/sql.html> statement,
93       which returns a Mojo::SQLite::Results object. And finally we call the
94       method "hash" in Mojo::SQLite::Results to retrieve the first row as a
95       hash reference.
96
97       All I/O and queries are performed synchronously. However, the "Write-
98       Ahead Log" journal is enabled for all connections, allowing multiple
99       processes to read and write concurrently to the same database file (but
100       only one can write at a time). You can prevent this mode from being
101       enabled by passing the option "no_wal", but note that this is
102       incompatible with SQLite databases that have already had WAL mode
103       enabled. See <http://sqlite.org/wal.html> and "journal_mode" in
104       DBD::SQLite for more information.
105
106         # Performed concurrently
107         my $pid = fork || die $!;
108         say $sql->db->query(q{select datetime('now','localtime') as time})->hash->{time};
109         exit unless $pid;
110
111       The double-quoted string literal misfeature
112        <https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted>
113       is disabled for all connections since Mojo::SQLite 3.003; use single
114       quotes for string literals and double quotes for identifiers, as is
115       normally recommended.
116
117       All cached database handles will be reset automatically if a new
118       process has been forked, this allows multiple processes to share the
119       same Mojo::SQLite object safely.
120
121       Any database errors will throw an exception as "RaiseError" is
122       automatically enabled, so use "eval" or Try::Tiny to catch them. This
123       makes transactions with "begin" in Mojo::SQLite::Database easy.
124
125       While passing a file path of ":memory:" (or a custom "dsn" with
126       "mode=memory") will create a temporary database, in-memory databases
127       cannot be shared between connections, so subsequent calls to "db" may
128       return connections to completely different databases. For a temporary
129       database that can be shared between connections and processes, pass a
130       file path of ":temp:" to store the database in a temporary directory
131       (this is the default), or consider constructing a temporary directory
132       yourself with File::Temp if you need to reuse the filename. A temporary
133       directory allows SQLite to create additional temporary files
134       <https://www.sqlite.org/tempfiles.html> safely.
135
136         use File::Spec::Functions 'catfile';
137         use File::Temp;
138         use Mojo::SQLite;
139         my $tempdir = File::Temp->newdir; # Deleted when object goes out of scope
140         my $tempfile = catfile $tempdir, 'test.db';
141         my $sql = Mojo::SQLite->new->from_filename($tempfile);
142
143       SQL::Abstract::Pg can provide additional features to the SQL::Abstract
144       query methods in Mojo::SQLite::Database. The "on_conflict" and "for"
145       features are not applicable to SQLite queries.
146
147         use SQL::Abstract::Pg;
148         my $sql = Mojo::SQLite->new(abstract => SQL::Abstract::Pg->new(name_sep => '.', quote_char => '"'));
149         $sql->db->select(['some_table', ['other_table', foo_id => 'id']],
150           ['foo', [bar => 'baz'], \q{datetime('now') as dt}],
151           {foo => 'value'},
152           {order_by => 'foo', limit => 10, offset => 5, group_by => ['foo'], having => {baz => 'value'}});
153

EXAMPLES

155       This distribution also contains a well-structured example blog
156       application <https://github.com/Grinnz/Mojo-
157       SQLite/tree/master/examples/blog> you can use for inspiration. This
158       application shows how to apply the MVC design pattern in practice.
159

EVENTS

161       Mojo::SQLite inherits all events from Mojo::EventEmitter and can emit
162       the following new ones.
163
164   connection
165         $sql->on(connection => sub {
166           my ($sql, $dbh) = @_;
167           $dbh->do('pragma journal_size_limit=1000000');
168         });
169
170       Emitted when a new database connection has been established.
171

ATTRIBUTES

173       Mojo::SQLite implements the following attributes.
174
175   abstract
176         my $abstract = $sql->abstract;
177         $sql         = $sql->abstract(SQL::Abstract->new);
178
179       SQL::Abstract object used to generate CRUD queries for
180       Mojo::SQLite::Database, defaults to setting "name_sep" to "." and
181       "quote_char" to """. SQL::Abstract::Pg may be used to provide
182       additional features.
183
184         # Generate WHERE clause and bind values
185         my($stmt, @bind) = $sql->abstract->where({foo => 'bar', baz => 'yada'});
186
187   auto_migrate
188         my $bool = $sql->auto_migrate;
189         $sql     = $sql->auto_migrate($bool);
190
191       Automatically migrate to the latest database schema with "migrations",
192       as soon as "db" has been called for the first time.
193
194   database_class
195         my $class = $sql->database_class;
196         $sql      = $sql->database_class('MyApp::Database');
197
198       Class to be used by "db", defaults to Mojo::SQLite::Database. Note that
199       this class needs to have already been loaded before "db" is called.
200
201   dsn
202         my $dsn = $sql->dsn;
203         $sql    = $sql->dsn('dbi:SQLite:uri=file:foo.db');
204
205       Data source name, defaults to "dbi:SQLite:dbname=" followed by a path
206       to a temporary file.
207
208   max_connections
209         my $max = $sql->max_connections;
210         $sql    = $sql->max_connections(3);
211
212       Maximum number of idle database handles to cache for future use,
213       defaults to 1.
214
215   migrations
216         my $migrations = $sql->migrations;
217         $sql           = $sql->migrations(Mojo::SQLite::Migrations->new);
218
219       Mojo::SQLite::Migrations object you can use to change your database
220       schema more easily.
221
222         # Load migrations from file and migrate to latest version
223         $sql->migrations->from_file('/home/dbook/migrations.sql')->migrate;
224
225   options
226         my $options = $sql->options;
227         $sql        = $sql->options({AutoCommit => 1, RaiseError => 1});
228
229       Options for database handles, defaults to activating "sqlite_unicode",
230       "AutoCommit", "AutoInactiveDestroy" as well as "RaiseError" and
231       deactivating "PrintError". Note that "AutoCommit" and "RaiseError" are
232       considered mandatory, so deactivating them would be very dangerous. See
233       "ATTRIBUTES COMMON TO ALL HANDLES" in DBI and "DRIVER PRIVATE
234       ATTRIBUTES" in DBD::SQLite for more information on available options.
235
236   parent
237         my $parent = $sql->parent;
238         $sql       = $sql->parent(Mojo::SQLite->new);
239
240       Another Mojo::SQLite object to use for connection management, instead
241       of establishing and caching our own database connections.
242

METHODS

244       Mojo::SQLite inherits all methods from Mojo::EventEmitter and
245       implements the following new ones.
246
247   new
248         my $sql = Mojo::SQLite->new;
249         my $sql = Mojo::SQLite->new('file:test.db);
250         my $sql = Mojo::SQLite->new('sqlite:test.db');
251         my $sql = Mojo::SQLite->new(Mojo::SQLite->new);
252
253       Construct a new Mojo::SQLite object and parse connection string with
254       "from_string" if necessary.
255
256         # Customize configuration further
257         my $sql = Mojo::SQLite->new->dsn('dbi:SQLite:dbname=test.db');
258         my $sql = Mojo::SQLite->new->dsn('dbi:SQLite:uri=file:test.db?mode=memory');
259
260         # Pass filename directly
261         my $sql = Mojo::SQLite->new->from_filename($filename);
262
263   db
264         my $db = $sql->db;
265
266       Get a database object based on "database_class" (which is usually
267       Mojo::SQLite::Database) for a cached or newly established database
268       connection. The DBD::SQLite database handle will be automatically
269       cached again when that object is destroyed, so you can handle problems
270       like connection timeouts gracefully by holding on to it only for short
271       amounts of time.
272
273         # Add up all the money
274         say $sql->db->select('accounts')
275           ->hashes->reduce(sub { $a->{money} + $b->{money} });
276
277   from_filename
278         $sql = $sql->from_filename('C:\\Documents and Settings\\foo & bar.db', $options);
279
280       Parse database filename directly. Unlike "from_string", the filename is
281       parsed as a local filename and not a URL. A hashref of "options" may be
282       passed as the second argument.
283
284         # Absolute filename
285         $sql->from_filename('/home/fred/data.db');
286
287         # Relative to current directory
288         $sql->from_filename('data.db');
289
290         # Temporary file database (default)
291         $sql->from_filename(':temp:');
292
293         # In-memory temporary database (single connection only)
294         my $db = $sql->from_filename(':memory:')->db;
295
296         # Additional options
297         $sql->from_filename($filename, { PrintError => 1 });
298
299         # Readonly connection without WAL mode
300         $sql->from_filename($filename, { ReadOnly => 1, no_wal => 1 });
301
302   from_string
303         $sql = $sql->from_string('test.db');
304         $sql = $sql->from_string('file:test.db');
305         $sql = $sql->from_string('file:///C:/foo/bar.db');
306         $sql = $sql->from_string('sqlite:C:%5Cfoo%5Cbar.db');
307         $sql = $sql->from_string(Mojo::SQLite->new);
308
309       Parse configuration from connection string or use another Mojo::SQLite
310       object as "parent". Connection strings are parsed as URLs, so you
311       should construct them using a module like Mojo::URL, URI::file, or
312       URI::db.  For portability on non-Unix-like systems, either construct
313       the URL with the "sqlite" scheme, or use "new" in URI::file to
314       construct a URL with the "file" scheme. A URL with no scheme will be
315       parsed as a "file" URL, and "file" URLs are parsed according to the
316       current operating system. If specified, the hostname must be
317       "localhost". If the URL has a query string, it will be parsed and
318       applied to "options".
319
320         # Absolute filename
321         $sql->from_string('sqlite:////home/fred/data.db');
322         $sql->from_string('sqlite://localhost//home/fred/data.db');
323         $sql->from_string('sqlite:/home/fred/data.db');
324         $sql->from_string('file:///home/fred/data.db');
325         $sql->from_string('file://localhost/home/fred/data.db');
326         $sql->from_string('file:/home/fred/data.db');
327         $sql->from_string('///home/fred/data.db');
328         $sql->from_string('//localhost/home/fred/data.db');
329         $sql->from_string('/home/fred/data.db');
330
331         # Relative to current directory
332         $sql->from_string('sqlite:data.db');
333         $sql->from_string('file:data.db');
334         $sql->from_string('data.db');
335
336         # Connection string must be a valid URL
337         $sql->from_string(Mojo::URL->new->scheme('sqlite')->path($filename));
338         $sql->from_string(URI::db->new->Mojo::Base::tap(engine => 'sqlite')->Mojo::Base::tap(dbname => $filename));
339         $sql->from_string(URI::file->new($filename));
340
341         # Temporary file database (default)
342         $sql->from_string(':temp:');
343
344         # In-memory temporary database (single connection only)
345         my $db = $sql->from_string(':memory:')->db;
346
347         # Additional options
348         $sql->from_string('data.db?PrintError=1&sqlite_allow_multiple_statements=1');
349         $sql->from_string(Mojo::URL->new->scheme('sqlite')->path($filename)->query(sqlite_see_if_its_a_number => 1));
350         $sql->from_string(URI::file->new($filename)->Mojo::Base::tap(query_form => {PrintError => 1}));
351
352         # Readonly connection without WAL mode
353         $sql->from_string('data.db?ReadOnly=1&no_wal=1');
354

DEBUGGING

356       You can set the "DBI_TRACE" environment variable to get some advanced
357       diagnostics information printed by DBI.
358
359         DBI_TRACE=1
360         DBI_TRACE=15
361         DBI_TRACE=SQL
362

REFERENCE

364       This is the class hierarchy of the Mojo::SQLite distribution.
365
366       • Mojo::SQLite
367
368       • Mojo::SQLite::Database
369
370       • Mojo::SQLite::Migrations
371
372       • Mojo::SQLite::Results
373
374       • Mojo::SQLite::Transaction
375

BUGS

377       Report any issues on the public bugtracker.
378

AUTHOR

380       Dan Book, "dbook@cpan.org"
381

CREDITS

383       Sebastian Riedel, author of Mojo::Pg, which this distribution is based
384       on.
385

COPYRIGHT AND LICENSE

387       Copyright 2015, Dan Book.
388
389       This library is free software; you may redistribute it and/or modify it
390       under the terms of the Artistic License version 2.0.
391