Mojo::SQLite(3pm)

1Mojo::SQLite(3pm)     User Contributed Perl Documentation    Mojo::SQLite(3pm)
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 ($c) {
80           my $db = $c->sqlite->db;
81           $c->render(json => $db->query(q{select datetime('now','localtime') as now})->hash);
82         };
83
84         app->start;
85
86       In this example application, we create a "sqlite" helper to store a
87       Mojo::SQLite object. Our action calls that helper and uses the method
88       "db" in Mojo::SQLite to dequeue a Mojo::SQLite::Database object from
89       the connection pool. Then we use the method "query" in
90       Mojo::SQLite::Database to execute an SQL
91       <http://www.postgresql.org/docs/current/static/sql.html> statement,
92       which returns a Mojo::SQLite::Results object. And finally we call the
93       method "hash" in Mojo::SQLite::Results to retrieve the first row as a
94       hash reference.
95
96       All I/O and queries are performed synchronously, and SQLite's default
97       journal mode only supports concurrent reads from multiple processes
98       while the database is not being written. The "Write-Ahead Log" journal
99       mode allows multiple processes to read and write concurrently to the
100       same database file (but only one can write at a time). WAL mode is
101       enabled by the "wal_mode" option, currently enabled by default, and
102       persists when opening that same database in the future.
103
104         # Performed concurrently (concurrent with writing only with WAL journaling mode)
105         my $pid = fork || die $!;
106         say $sql->db->query(q{select datetime('now','localtime') as time})->hash->{time};
107         exit unless $pid;
108
109       The "no_wal" option prevents WAL mode from being enabled in new
110       databases but doesn't affect databases where it has already been
111       enabled. "wal_mode" may not be set by default in a future release. See
112       <http://sqlite.org/wal.html> and "journal_mode" in DBD::SQLite for more
113       information.
114
115       The double-quoted string literal misfeature
116        <https://sqlite.org/quirks.html#double_quoted_string_literals_are_accepted>
117       is disabled for all connections since Mojo::SQLite 3.003; use single
118       quotes for string literals and double quotes for identifiers, as is
119       normally recommended.
120
121       All cached database handles will be reset automatically if a new
122       process has been forked, this allows multiple processes to share the
123       same Mojo::SQLite object safely.
124
125       Any database errors will throw an exception as "RaiseError" is
126       automatically enabled, so use "eval" or Try::Tiny to catch them. This
127       makes transactions with "begin" in Mojo::SQLite::Database easy.
128
129       While passing a file path of ":memory:" (or a custom "dsn" with
130       "mode=memory") will create a temporary database, in-memory databases
131       cannot be shared between connections, so subsequent calls to "db" may
132       return connections to completely different databases. For a temporary
133       database that can be shared between connections and processes, pass a
134       file path of ":temp:" to store the database in a temporary directory
135       (this is the default), or consider constructing a temporary directory
136       yourself with File::Temp if you need to reuse the filename. A temporary
137       directory allows SQLite to create additional temporary files
138       <https://www.sqlite.org/tempfiles.html> safely.
139
140         use File::Spec::Functions 'catfile';
141         use File::Temp;
142         use Mojo::SQLite;
143         my $tempdir = File::Temp->newdir; # Deleted when object goes out of scope
144         my $tempfile = catfile $tempdir, 'test.db';
145         my $sql = Mojo::SQLite->new->from_filename($tempfile);
146

EXAMPLES

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

EVENTS

154       Mojo::SQLite inherits all events from Mojo::EventEmitter and can emit
155       the following new ones.
156
157   connection
158         $sql->on(connection => sub ($sql, $dbh) {
159           $dbh->do('pragma journal_size_limit=1000000');
160         });
161
162       Emitted when a new database connection has been established.
163

ATTRIBUTES

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

METHODS

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

DEBUGGING

367       You can set the "DBI_TRACE" environment variable to get some advanced
368       diagnostics information printed by DBI.
369
370         DBI_TRACE=1
371         DBI_TRACE=15
372         DBI_TRACE=SQL
373

REFERENCE

375       This is the class hierarchy of the Mojo::SQLite distribution.
376
377       • Mojo::SQLite
378
379       • Mojo::SQLite::Database
380
381       • Mojo::SQLite::Migrations
382
383       • Mojo::SQLite::Results
384
385       • Mojo::SQLite::Transaction
386

BUGS

388       Report any issues on the public bugtracker.
389

AUTHOR

391       Dan Book, "dbook@cpan.org"
392

CREDITS

394       Sebastian Riedel, author of Mojo::Pg, which this distribution is based
395       on.
396

COPYRIGHT AND LICENSE

398       Copyright 2015, Dan Book.
399
400       This library is free software; you may redistribute it and/or modify it
401       under the terms of the Artistic License version 2.0.
402