connection¶

  $sql->on(connection => sub ($sql, $dbh) {
    $dbh->do('pragma journal_size_limit=1000000');
  });

Emitted when a new database connection has been established.

abstract¶

  my $abstract = $sql->abstract;
  $sql         = $sql->abstract(SQL::Abstract->new);

SQL::Abstract object used to generate CRUD queries for Mojo::SQLite::Database, defaults to a SQL::Abstract::Pg object with "name_sep" set to "." and "quote_char" set to """.

  # Generate WHERE clause and bind values
  my($stmt, @bind) = $sql->abstract->where({foo => 'bar', baz => 'yada'});

SQL::Abstract::Pg provides additional features to the SQL::Abstract query methods in Mojo::SQLite::Database such as "-json" and "limit"/"offset". The "for" feature is not applicable to SQLite queries.

  $sql->db->select(['some_table', ['other_table', foo_id => 'id']],
    ['foo', [bar => 'baz'], \q{datetime('now') as dt}],
    {foo => 'value'},
    {order_by => 'foo', limit => 10, offset => 5, group_by => ['foo'], having => {baz => 'value'}});
  # Upsert supported since SQLite 3.24.0
  $sql->db->insert('some_table', {name => $name, value => $value},
    {on_conflict => [name => {value => \'"excluded"."value"'}]});

auto_migrate¶

  my $bool = $sql->auto_migrate;
  $sql     = $sql->auto_migrate($bool);

Automatically migrate to the latest database schema with "migrations", as soon as "db" has been called for the first time.

database_class¶

  my $class = $sql->database_class;
  $sql      = $sql->database_class('MyApp::Database');

Class to be used by "db", defaults to Mojo::SQLite::Database. Note that this class needs to have already been loaded before "db" is called.

dsn¶

  my $dsn = $sql->dsn;
  $sql    = $sql->dsn('dbi:SQLite:uri=file:foo.db');

Data source name, defaults to "dbi:SQLite:dbname=" followed by a path to a temporary file.

max_connections¶

  my $max = $sql->max_connections;
  $sql    = $sql->max_connections(3);

Maximum number of idle database handles to cache for future use, defaults to 1.

migrations¶

  my $migrations = $sql->migrations;
  $sql           = $sql->migrations(Mojo::SQLite::Migrations->new);

Mojo::SQLite::Migrations object you can use to change your database schema more easily.

  # Load migrations from file and migrate to latest version
  $sql->migrations->from_file('/home/dbook/migrations.sql')->migrate;

options¶

  my $options = $sql->options;
  $sql        = $sql->options({AutoCommit => 1, RaiseError => 1});

Options for database handles, defaults to setting "sqlite_string_mode" to "DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK", setting "AutoCommit", "AutoInactiveDestroy" and "RaiseError", and deactivating "PrintError". Note that "AutoCommit" and "RaiseError" are considered mandatory, so deactivating them would be very dangerous. See "ATTRIBUTES COMMON TO ALL HANDLES" in DBI and "DRIVER PRIVATE ATTRIBUTES" in DBD::SQLite for more information on available options.

parent¶

  my $parent = $sql->parent;
  $sql       = $sql->parent(Mojo::SQLite->new);

Another Mojo::SQLite object to use for connection management, instead of establishing and caching our own database connections.

new¶

  my $sql = Mojo::SQLite->new;
  my $sql = Mojo::SQLite->new('file:test.db);
  my $sql = Mojo::SQLite->new('sqlite:test.db');
  my $sql = Mojo::SQLite->new(Mojo::SQLite->new);

Construct a new Mojo::SQLite object and parse connection string with "from_string" if necessary.

  # Customize configuration further
  my $sql = Mojo::SQLite->new->dsn('dbi:SQLite:dbname=test.db');
  my $sql = Mojo::SQLite->new->dsn('dbi:SQLite:uri=file:test.db?mode=memory');
  # Pass filename directly
  my $sql = Mojo::SQLite->new->from_filename($filename);

db¶

  my $db = $sql->db;

Get a database object based on "database_class" (which is usually Mojo::SQLite::Database) for a cached or newly established database connection. The DBD::SQLite database handle will be automatically cached again when that object is destroyed, so you can handle problems like connection timeouts gracefully by holding on to it only for short amounts of time.

  # Add up all the money
  say $sql->db->select('accounts')
    ->hashes->reduce(sub { $a->{money} + $b->{money} });

from_filename¶

  $sql = $sql->from_filename('C:\\Documents and Settings\\foo & bar.db', $options);

Parse database filename directly. Unlike "from_string", the filename is parsed as a local filename and not a URL. A hashref of "options" may be passed as the second argument.

  # Absolute filename
  $sql->from_filename('/home/fred/data.db');
  # Relative to current directory
  $sql->from_filename('data.db');
  # Temporary file database (default)
  $sql->from_filename(':temp:');
  # In-memory temporary database (single connection only)
  my $db = $sql->from_filename(':memory:')->db;
  # Additional options
  $sql->from_filename($filename, { PrintError => 1 });
  
  # Readonly connection without WAL mode
  $sql->from_filename($filename, { ReadOnly => 1, no_wal => 1 });
  
  # Strict unicode strings and WAL mode
  use DBD::SQLite::Constants ':dbd_sqlite_string_mode';
  $sql->from_filename($filename, { sqlite_string_mode => DBD_SQLITE_STRING_MODE_UNICODE_STRICT, wal_mode => 1 });

from_string¶

  $sql = $sql->from_string('test.db');
  $sql = $sql->from_string('file:test.db');
  $sql = $sql->from_string('file:///C:/foo/bar.db');
  $sql = $sql->from_string('sqlite:C:%5Cfoo%5Cbar.db');
  $sql = $sql->from_string(Mojo::SQLite->new);

Parse configuration from connection string or use another Mojo::SQLite object as "parent". Connection strings are parsed as URLs, so you should construct them using a module like Mojo::URL, URI::file, or URI::db. For portability on non-Unix-like systems, either construct the URL with the "sqlite" scheme, or use "new" in URI::file to construct a URL with the "file" scheme. A URL with no scheme will be parsed as a "file" URL, and "file" URLs are parsed according to the current operating system. If specified, the hostname must be "localhost". If the URL has a query string, it will be parsed and applied to "options".

  # Absolute filename
  $sql->from_string('sqlite:////home/fred/data.db');
  $sql->from_string('sqlite://localhost//home/fred/data.db');
  $sql->from_string('sqlite:/home/fred/data.db');
  $sql->from_string('file:///home/fred/data.db');
  $sql->from_string('file://localhost/home/fred/data.db');
  $sql->from_string('file:/home/fred/data.db');
  $sql->from_string('///home/fred/data.db');
  $sql->from_string('//localhost/home/fred/data.db');
  $sql->from_string('/home/fred/data.db');
  # Relative to current directory
  $sql->from_string('sqlite:data.db');
  $sql->from_string('file:data.db');
  $sql->from_string('data.db');
  # Connection string must be a valid URL
  $sql->from_string(Mojo::URL->new->scheme('sqlite')->path($filename));
  $sql->from_string(URI::db->new->Mojo::Base::tap(engine => 'sqlite')->Mojo::Base::tap(dbname => $filename));
  $sql->from_string(URI::file->new($filename));
  # Temporary file database (default)
  $sql->from_string(':temp:');
  # In-memory temporary database (single connection only)
  my $db = $sql->from_string(':memory:')->db;
  # Additional options
  $sql->from_string('data.db?PrintError=1&sqlite_allow_multiple_statements=1');
  $sql->from_string(Mojo::URL->new->scheme('sqlite')->path($filename)->query(sqlite_see_if_its_a_number => 1));
  $sql->from_string(URI::file->new($filename)->Mojo::Base::tap(query_form => {PrintError => 1}));
  # Readonly connection without WAL mode
  $sql->from_string('data.db?ReadOnly=1&no_wal=1');
  # String unicode strings and WAL mode
  use DBD::SQLite::Constants ':dbd_sqlite_string_mode';
  $sql->from_string(Mojo::URL->new->scheme('sqlite')->path('data.db')
    ->query(sqlite_string_mode => DBD_SQLITE_STRING_MODE_UNICODE_STRICT, wal_mode => 1));