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 "for" feature is not applicable to SQLite
181       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         # Upsert supported since SQLite 3.24.0
189         $sql->db->insert('some_table', {name => $name, value => $value},
190           {on_conflict => [name => {value => \'"excluded"."value"'}]});
191
192   auto_migrate
193         my $bool = $sql->auto_migrate;
194         $sql     = $sql->auto_migrate($bool);
195
196       Automatically migrate to the latest database schema with "migrations",
197       as soon as "db" has been called for the first time.
198
199   database_class
200         my $class = $sql->database_class;
201         $sql      = $sql->database_class('MyApp::Database');
202
203       Class to be used by "db", defaults to Mojo::SQLite::Database. Note that
204       this class needs to have already been loaded before "db" is called.
205
206   dsn
207         my $dsn = $sql->dsn;
208         $sql    = $sql->dsn('dbi:SQLite:uri=file:foo.db');
209
210       Data source name, defaults to "dbi:SQLite:dbname=" followed by a path
211       to a temporary file.
212
213   max_connections
214         my $max = $sql->max_connections;
215         $sql    = $sql->max_connections(3);
216
217       Maximum number of idle database handles to cache for future use,
218       defaults to 1.
219
220   migrations
221         my $migrations = $sql->migrations;
222         $sql           = $sql->migrations(Mojo::SQLite::Migrations->new);
223
224       Mojo::SQLite::Migrations object you can use to change your database
225       schema more easily.
226
227         # Load migrations from file and migrate to latest version
228         $sql->migrations->from_file('/home/dbook/migrations.sql')->migrate;
229
230   options
231         my $options = $sql->options;
232         $sql        = $sql->options({AutoCommit => 1, RaiseError => 1});
233
234       Options for database handles, defaults to setting "sqlite_string_mode"
235       to "DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK", setting "AutoCommit",
236       "AutoInactiveDestroy" and "RaiseError", and deactivating "PrintError".
237       Note that "AutoCommit" and "RaiseError" are considered mandatory, so
238       deactivating them would be very dangerous. See "ATTRIBUTES COMMON TO
239       ALL HANDLES" in DBI and "DRIVER PRIVATE ATTRIBUTES" in DBD::SQLite for
240       more information on available options.
241
242   parent
243         my $parent = $sql->parent;
244         $sql       = $sql->parent(Mojo::SQLite->new);
245
246       Another Mojo::SQLite object to use for connection management, instead
247       of establishing and caching our own database connections.
248

METHODS

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

DEBUGGING

371       You can set the "DBI_TRACE" environment variable to get some advanced
372       diagnostics information printed by DBI.
373
374         DBI_TRACE=1
375         DBI_TRACE=15
376         DBI_TRACE=SQL
377

REFERENCE

379       This is the class hierarchy of the Mojo::SQLite distribution.
380
381       • Mojo::SQLite
382
383       • Mojo::SQLite::Database
384
385       • Mojo::SQLite::Migrations
386
387       • Mojo::SQLite::Results
388
389       • Mojo::SQLite::Transaction
390

BUGS

392       Report any issues on the public bugtracker.
393

AUTHOR

395       Dan Book, "dbook@cpan.org"
396

CREDITS

398       Sebastian Riedel, author of Mojo::Pg, which this distribution is based
399       on.
400

COPYRIGHT AND LICENSE

402       Copyright 2015, Dan Book.
403
404       This library is free software; you may redistribute it and/or modify it
405       under the terms of the Artistic License version 2.0.
406