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('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('select datetime("now","localtime") as time')->hash->{time};
109         exit unless $pid;
110
111       All cached database handles will be reset automatically if a new
112       process has been forked, this allows multiple processes to share the
113       same Mojo::SQLite object safely.
114
115       Any database errors will throw an exception as "RaiseError" is
116       automatically enabled, so use "eval" or Try::Tiny to catch them. This
117       makes transactions with "begin" in Mojo::SQLite::Database easy.
118
119       While passing a file path of ":memory:" (or a custom "dsn" with
120       "mode=memory") will create a temporary database, in-memory databases
121       cannot be shared between connections, so subsequent calls to "db" may
122       return connections to completely different databases. For a temporary
123       database that can be shared between connections and processes, pass a
124       file path of ":temp:" to store the database in a temporary directory
125       (this is the default), or consider constructing a temporary directory
126       yourself with File::Temp if you need to reuse the filename. A temporary
127       directory allows SQLite to create additional temporary files
128       <https://www.sqlite.org/tempfiles.html> safely.
129
130         use File::Spec::Functions 'catfile';
131         use File::Temp;
132         use Mojo::SQLite;
133         my $tempdir = File::Temp->newdir; # Deleted when object goes out of scope
134         my $tempfile = catfile $tempdir, 'test.db';
135         my $sql = Mojo::SQLite->new->from_filename($tempfile);
136
137       SQL::Abstract::Pg can provide additional features to the SQL::Abstract
138       query methods in Mojo::SQLite::Database. The "on_conflict" and "for"
139       features are not applicable to SQLite queries.
140
141         use SQL::Abstract::Pg;
142         my $sql = Mojo::SQLite->new(abstract => SQL::Abstract::Pg->new(name_sep => '.', quote_char => '"'));
143         $sql->db->select(['some_table', ['other_table', foo_id => 'id']],
144           ['foo', [bar => 'baz'], \q{datetime('now') as dt}],
145           {foo => 'value'},
146           {order_by => 'foo', limit => 10, offset => 5, group_by => ['foo'], having => {baz => 'value'}});
147

EXAMPLES

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

EVENTS

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

ATTRIBUTES

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

METHODS

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

DEBUGGING

350       You can set the "DBI_TRACE" environment variable to get some advanced
351       diagnostics information printed by DBI.
352
353         DBI_TRACE=1
354         DBI_TRACE=15
355         DBI_TRACE=SQL
356

REFERENCE

358       This is the class hierarchy of the Mojo::SQLite distribution.
359
360       · Mojo::SQLite
361
362       · Mojo::SQLite::Database
363
364       · Mojo::SQLite::Migrations
365
366       · Mojo::SQLite::Results
367
368       · Mojo::SQLite::Transaction
369

BUGS

371       Report any issues on the public bugtracker.
372

AUTHOR

374       Dan Book, "dbook@cpan.org"
375

CREDITS

377       Sebastian Riedel, author of Mojo::Pg, which this distribution is based
378       on.
379

COPYRIGHT AND LICENSE

381       Copyright 2015, Dan Book.
382
383       This library is free software; you may redistribute it and/or modify it
384       under the terms of the Artistic License version 2.0.
385