# perldoc > DBD::SQLite

---
type: CommandReference
command: DBD::SQLite
mode: perldoc
section: ""
source: perldoc
---

## Quick Reference

- `DBI->connect("dbi:SQLite:dbname=$dbfile","","")` — connect to SQLite database (file or `:memory:`)
- `$dbh->do("PRAGMA journal_mode=WAL")` — set journal mode
- `$dbh->do("PRAGMA foreign_keys=ON")` — enable foreign key enforcement
- `$dbh->do("PRAGMA synchronous=OFF")` — improve performance (risk of corruption)
- `$dbh->begin_work` — begin transaction (issues `BEGIN IMMEDIATE`)
- `$dbh->commit` / `$dbh->rollback` — end transaction
- `$dbh->sqlite_create_function("now",0,sub{time})` — register custom SQL function
- `$dbh->sqlite_create_aggregate("variance",1,"variance")` — register custom aggregate
- `$dbh->sqlite_backup_to_file($filename)` — backup database to file
- `$dbh->sqlite_backup_from_file($filename)` — restore database from file

## Name

DBD::SQLite - Self-contained RDBMS in a DBI Driver

## Synopsis

perl
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbfile","","");
## Options

### Database Handle Attributes

- `sqlite_version` — (read-only) version of bundled SQLite library, e.g. "3.26.0"
- `sqlite_string_mode` — controls Unicode/bytes handling. Set to one of `DBD_SQLITE_STRING_MODE_BYTES`, `DBD_SQLITE_STRING_MODE_UNICODE_FALLBACK`, `DBD_SQLITE_STRING_MODE_UNICODE_STRICT`, `DBD_SQLITE_STRING_MODE_UNICODE_NAIVE`, `DBD_SQLITE_STRING_MODE_PV` (default but deprecated)
- `sqlite_unicode` (deprecated) — if truthy, equivalent to `DBD_SQLITE_STRING_MODE_UNICODE_NAIVE`
- `sqlite_allow_multiple_statements` — if true, `do()` processes multiple SQL statements at once
- `sqlite_use_immediate_transaction` — set to false to use `BEGIN DEFERRED` instead of `BEGIN IMMEDIATE` (default true since 1.38_01)
- `sqlite_see_if_its_a_number` — if true, bind values that look numeric are not quoted
- `sqlite_extended_result_codes` — if true, use extended SQLite result codes
- `sqlite_defensive` — if true, prohibit SQL features that could corrupt the database
- `sqlite_open_flags` — passed at connect time, e.g. `SQLITE_OPEN_READONLY` (see `DBD::SQLite::Constants`)
- `ReadOnly` — set to true at connect to make database read-only

### Statement Handle Attributes

- `TYPE` — returns arrayref of string column types (or integers if `sqlite_prefer_numeric_type` is true)
- `sqlite_unprepared_statements` — returns leftover SQL after `prepare()` (used with `sqlite_allow_multiple_statements`)

## Examples

### Connect to an in-memory database

perl
my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:");
### Enable foreign keys

perl
$dbh->do("PRAGMA foreign_keys = ON");
### Register a custom SQL function

perl
$dbh->sqlite_create_function('now', 0, sub { return time });
### Use REGEXP operator (built-in)

sql
SELECT * FROM table WHERE column REGEXP '\bA\w+'
### Store and retrieve a BLOB

perl
use DBI qw(:sql_types);
my $blob = `cat foo.jpg`;
my $sth = $dbh->prepare("INSERT INTO mytable VALUES (1, ?)");
$sth->bind_param(1, $blob, SQL_BLOB);
$sth->execute();
# retrieval
$sth = $dbh->prepare("SELECT * FROM mytable WHERE id = 1");
$sth->execute();
my $row = $sth->fetch;
my $blobo = $row->[1];
### Handle transaction rollback with unfinished SELECT

perl
$sth = $dbh->prepare("SELECT * FROM t");
$dbh->begin_work;
eval {
    $sth->execute;
    $row = $sth->fetch;
    die "For some reason";
};
if($@) {
   $sth->finish;  # needed before rollback in SQLite
   $dbh->rollback;
} else {
   $dbh->commit;
}
### Create a custom aggregate (variance)

perl
package variance;
sub new { bless [], shift; }
sub step { my ($self, $value) = @_; push @$self, $value; }
sub finalize {
    my $self = shift;
    my $n = @$self;
    return undef unless $n && $n > 1;
    my $mu = 0; $mu += $_ for @$self; $mu /= $n;
    my $sigma = 0; $sigma += ($_ - $mu)**2 for @$self;
    return $sigma / ($n - 1);
}
$dbh->sqlite_create_aggregate("variance", 1, 'variance');
### Backup an in-memory database to a file

perl
$dbh->sqlite_backup_to_file("/path/to/backup.db");
## See Also

- [DBI](https://metacpan.org/pod/DBI) — Perl Database Interface
- [DBD::SQLite::Fulltext_search](https://metacpan.org/pod/DBD::SQLite::Fulltext_search) — full-text search extension
- [DBD::SQLite::VirtualTable](https://metacpan.org/pod/DBD::SQLite::VirtualTable) — virtual tables in Perl
- [DBD::SQLite::Constants](https://metacpan.org/pod/DBD::SQLite::Constants) — SQLite constants for Perl
- [DBD::SQLite::Cookbook](https://metacpan.org/pod/DBD::SQLite::Cookbook) — recipes and examples
- [SQLite Documentation](https://www.sqlite.org/docs.html) — official SQLite site
- [DBD::SQLite on GitHub](https://github.com/DBD-SQLite/DBD-SQLite) — source repository