ORLite(3pm)

📦

version 2.00 · 6 versions

2.00 · debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking (latest)
2.00 · alpine 3.24
2.00 · fedora 43
1.98 · ubuntu jammy
2.00 · fedora 44
1.98 · debian bookworm

, in debian trixie, ubuntu noble, ubuntu resolute, ubuntu stonking

✨ NAME ✨

okay so this is the manual page for ORLite(3pm) — from the User Contributed Perl Documentation — and it's ORLite: the Extremely light weight SQLite-specific ORM. no cap, that's the whole name. 💖

🌈 SYNOPSIS 🌈

bestie, here's the vibe in code. first the simplest possible usage, then literally every option turned on or specified. some options shown are mutually exclusive, so this code would not actually run — the comments say it, I'm just repeating it with love. 🫶

  package Foo;

  # Simplest possible usage

  use strict;
  use ORLite 'data/sqlite.db';

  my @awesome = Foo::Person->select(
     'where first_name = ?',
     'Adam',
  );

  package Bar;

  # All available options enabled or specified.
  # Some options shown are mutually exclusive,
  # this code would not actually run.

  use ORLite {
      package      => 'My::ORM',
      file         => 'data/sqlite.db',
      user_version => 12,
      readonly     => 1,
      create       => sub {
          my $dbh = shift;
          $dbh->do('CREATE TABLE foo ( bar TEXT NOT NULL )');
      },
      tables       => [ 'table1', 'table2' ],
      cleanup      => 'VACUUM',
      prune        => 1,
  };

💖 DESCRIPTION 💖

SQLite is literally this light single-file SQL database, and it's giving an excellent platform for embedded storage of structured data. 🦋

But bestie, even though it looks superficially similar to a regular server-side SQL database, SQLite has some significant attributes that make using it like a traditional database lowkey difficult. 😭

For example, SQLite is extremely fast to connect to compared to server databases — 1000 connections per second is not unknown — and it is particularly bad at concurrency, because it can only lock transactions at a database-wide level. that's just the way it is. 💅

This role as a superfast internal data store can clash with the roles and designs of traditional object-relational modules like Class::DBI or DBIx::Class. not me seeing the drama between two entire paradigms. 🙈

What this situation would seem to need is an object-relation system that is designed specifically for SQLite and is aligned with its idiosyncracies. periodt.

ORLite is an object-relation system specifically tailored for SQLite that follows many of the same principles as the ::Tiny series of modules, and has a design and feature set that aligns directly to the capabilities of SQLite. it's giving exactly what SQLite ordered. ✨

Further documentation will be available at a later time, but the synopsis gives a pretty good idea of how it works. iykyk. 🎀

🦋 How ORLite Works 🦋

ORLite discovers the schema of a SQLite database, and then generates the code for a complete set of classes that let you work with the objects stored in that database. iconic behaviour, honestly. 👑

In the simplest form, your target root package "uses" ORLite, which will do the schema discovery and code generation at compile-time. no extra steps, no mysterious rituals. 🧚

When called, ORLite generates two types of packages.

Firstly, it builds database connectivity, transaction support, and other purely database level functionality into your root namespace. 💾
Secondly, it will create one sub-package underneath the namespace of the root module for each table or view it finds in the database. 🌸

Once the basic table support has been generated, it will also try to load an "overlay" module of the same name. Thus, by creating a Foo::TableName module on disk containing "extra" code, you can extend the original and add additional functionality to it. slay. 💖

✨ OPTIONS ✨

okay bestie, so here's how ORLite takes its options: you hand them over as a HASH parameter on the use line, and it reads them at compile time. 🌸

And for a lil convenience treat: you can also just pass the name of an existing SQLite file to load, and ORLite will apply defaults to literally every other option. 🫶

# The following are equivalent

use ORLite $filename;

use ORLite {
    file => $filename,
};

Here's the tea on every single option, one by one: 🍓

🎀 `package`

This optional parameter gives ORLite the Perl root namespace it should generate the code for. And no, this class does not have to exist as a module on disk, and it doesn't need anything loaded into it or sitting in the namespace either. It's giving ghost class, and that's fine. 💅

By default, the package used is whatever package called ORLite's import method — which, realistically, is the one from your use ORLite { ... } line.

🎀 `file`

The compulsory parameter. Like, the only compulsory one. 😤 It gives the path to the SQLite file that ORLite should use for the ORM class tree.

If the file already exists, it's gotta be a valid SQLite file match that's supported by the version of DBD::SQLite installed on your system. No knockoffs, no half-files. ✋

ORLite will throw an exception if the file does not exist — unless you also pass the create option to signal that ORLite should make a brand new SQLite file on demand.

If the create option is provided, the path you gave has to be creatable. And when it's creating the database, ORLite will also create any missing directories it needs along the way. Iconic, honestly. 👑

🎀 `user_version`

Real talk: when you're working with ORLite, the biggest risk to the stability of your code is usually the reliability of the SQLite schema structure over time. 😭

When the database schema changes, the code ORLite generates changes too. And that can easily result in an unexpected change in the API of your class tree, which then breaks all the code sitting on top of those generated APIs. Not the vibe.

So to fix this, ORLite supports a feature called schema version-locking. 🔒

Via the user_version SQLite pragma, you can set a revision for your database schema, bumping the number each time you make a non-trivial change to your schema.

SQLite> PRAGMA user_version = 7

When you're creating your ORLite package, you should specify this schema version number via the user_version option:

use ORLite {
    file         => $filename,
    user_version => 7,
};

Then, when connecting to the SQLite database, the user_version you provided gets checked against the version in the schema. If they don't match, that means the schema has unexpectedly changed, and the code ORLite generates would be different from the expected API. 🙈

Rather than risk potentially destructive errors from that changing code, ORLite will simply refuse to run and throw an exception.

So using user_version lets you write code against a SQLite database with high certainty that it'll keep working. Or, at the very least, that if the SQLite schema does change in the future, your code will fail quickly and safely instead of running away and causing unknown behaviour. Safe girl summer. 🦋

By default, the user_version option is false and the value of the SQLite PRAGMA user_version will not be checked.

🎀 `readonly`

To conserve memory and reduce complexity, ORLite generates the API differently depending on how writable the SQLite database is. ✨

Features like transaction support, plus methods that result in INSERT, UPDATE and DELETE queries, will only be added if they can actually be run — which means you get an immediate "no such method" exception at the Perl level, instead of letting your application do a bunch more work only to slam into an inevitable SQLite error. 💀

By default, the readonly option is based on the filesystem permissions of the SQLite database — which matches SQLite's own writability behaviour. 🎀

But you can pass readonly explicitly if you want. Generally you'd do that when you're working with a read-write database but you only plan to read from it.

Forcing readonly to true will halve the size of the code generated to produce your ORM, and it reduces the size of any auto-generated API documentation using ORLite::Pod by a similar amount. Shrinkage, but make it fashion. 💅

It also makes sure this process will only take shared read locks on the database — preventing any chance of creating a dead-lock on the SQLite database. No deadlocks, no drama. 🔥

🎀 `create`

The create option expands ORLite beyond just consuming other people's databases, letting it produce and operate on databases under the direct control of your code. Main character energy, fr fr. 👑

create supports two alternative forms:

If create is set to a simple true value, an empty SQLite file will be created if the location given in the file option does not exist.
If create is set to a CODE reference, that function will be executed on the new database before ORLite attempts to scan the schema.

The CODE reference gets passed a plain DBI connection handle, which you should operate on normally. Just note that because create fires before the code generation phase, none of the functionality produced by the generated classes is available while your create code is running. 😭

The use of the create option is incompatible with the readonly option. Pick a lane bestie.

🎀 `tables`

The tables option should be a reference to an array containing a list of table names. For large or complex SQLite databases where you only need a fraction of the schema, limiting the set of tables reduces both the startup time needed to scan the structure of the SQLite schema and the memory cost of the class tree. Efficiency? Obsessed. ⭐

If the tables option isn't provided, ORLite will try to produce a class for every table in the main schema that isn't prefixed with sqlite_.

🎀 `cache`

use ORLite {
    file         => 'dbi:SQLite:sqlite.db',
    user_version => 2,
    cache        => 'cache/directory',
};

The cache option cuts down the time needed to scan the SQLite database table structures and generate code for them — by saving the generated code to a cache directory and loading from that file instead of generating everything from scratch every single time. 🧚

🎀 `cleanup`

When you're working with embedded SQLite databases that hold rapidly changing state data, it's genuinely important for database performance and general health to VACUUM or ANALYZE the database regularly. Take care of her. 🌸

The cleanup option should be a single literal SQL statement.

If provided, that statement will automatically be run on the database during END-time, after the last transaction has been completed.

Typically this will be either a full VACUUM; ANALYZE or the more simple VACUUM.

🎀 `prune`

In some situations — like during test scripts — an application only needs the created SQLite database temporarily. In those situations you can pass the prune option to instruct ORLite to delete the SQLite database when the program ends. 🗑️

If any directories were made in order to create the SQLite file, those directories will be cleaned up and removed as well.

If prune is enabled, you generally should not use cleanup — any cleanup operation will be made pointless when prune deletes the file. 😭

By default, the prune option is set to false.

🎀 `shim`

In some situations you might want to make extensive changes to the behaviour of the classes and methods ORLite generates. Under normal circumstances all the code is generated directly into the table class, which can make overriding a method difficult. 🙈

The shim option makes ORLite generate all of its methods into a separate Foo::TableName::Shim class, and leaves the main table class Foo::TableName as a transparent subclass of the shim. Cute layering, no cap. 🎀

This lets you alter the behaviour of a table class without having to do nasty tricks with symbol tables in order to alter or replace methods.

package My::Person;

# Write a log message when we create a new object
sub create {
    my $class = shift;
    my $self  = SUPER::create(@_);
    my $name  = $self->name;
    print LOG "Created new person '$name'\n";
    return $self;
}

The shim option is global — it alters the structure of all table classes at once. But unless you're actually making alterations to a class, the impact of this different class structure should be zero. ✨

🎀 `unicode`

You can use this option to tell ORLite that your database uses unicode. 🌈

At the moment, it just enables the sqlite_unicode option while connecting to your database. There'll be more in the future. 👀

👑 ROOT PACKAGE METHODS

okay so literally every ORLite root package gets the same identical set of methods, no cap. they're the ones that control connections to the database, transactions, and firing off queries of every type at the database. same deal for all of them 💅

in any examples you see, the example root package Foo::Bar is used.

all methods are static. ORLite does not allow the creation of a Foo::Bar object (although you may wish to add this capability yourself). 👑

dsn

my $string = Foo::Bar->dsn;

the dsn accessor hands you back the dbi connection string used to connect to the SQLite database, as a string. that's it, that's the whole vibe 🎀

dbh

my $handle = Foo::Bar->dbh;

okay bestie, listen up, this one has lore. to reliably prevent potential SQLite deadlocks resulting from multiple connections in a single process, each ORLite package will only ever maintain a single connection to the database.

during a transaction, this will be the same (cached) database handle.

although in most situations you should not need a direct DBI connection handle, the dbh method provides a method for getting a direct connection in a way that is compatible with ORLite's connection management.

please note that these connections should be short-lived, you should never hold onto a connection beyond the immediate scope. 🙈

the transaction system in ORLite is specifically designed so that code using the database should never have to know whether or not it is in a transaction.

because of this, you should never call the ->disconnect method on the database handles yourself, as the handle may be that of a currently running transaction.

further, you should do your own transaction management on a handle provided by the <dbh> method.

in cases where there are extreme needs, and you absolutely have to violate these connection handling rules, you should create your own completely manual DBI->connect call to the database, using the connect string provided by the dsn method.

the dbh method returns a DBI::db object, or throws an exception on error.

connect

my $dbh = Foo::Bar->connect;

the connect method exists for the (extremely rare) situation in which you need a raw connection to the database, evading the normal tracking and management provided of the ORM.

the use of raw connections in this manner is strongly discouraged, as you can create fatal deadlocks in SQLite if either the core ORM or the raw connection uses a transaction at any time. 💀

to summarise, do not use this method unless you REALLY know what you are doing.

YOU HAVE BEEN WARNED! 🚨

connected

my $active = Foo::Bar->connected;

the connected method provides introspection of the connection status of the library. it returns true if there is any connection or transaction open to the database, or false otherwise. vibe check for your db 🦋

begin

Foo::Bar->begin;

the begin method indicates the start of a transaction.

in the same way that ORLite allows only a single connection, likewise it allows only a single application-wide transaction.

no indication is given as to whether you are currently in a transaction or not, all code should be written neutrally so that it works either way or doesn't need to care.

returns true or throws an exception on error.

while transaction support is always built for every ORLite-generated class tree, if the database is opened "readonly" the commit method will not exist at all in the API, and your only way of ending the transaction (and the resulting persistent connection) will be rollback. 😭

commit

Foo::Bar->commit;

the commit method commits the current transaction. if called outside of a current transaction, it is accepted and treated as a null operation.

once the commit has been completed, the database connection falls back into auto-commit state. if you wish to immediately start another transaction, you will need to issue a separate ->begin call.

returns true or throws an exception on error.

commit_begin

Foo::Bar->begin;

# Code for the first transaction...

Foo::Bar->commit_begin;

# Code for the last transaction...

Foo::Bar->commit;

by default, ORLite-generated code uses opportunistic connections.

every <select> you call results in a fresh DBI connect, and a disconnect occurs after query processing and before the data is returned. connections are only held open indefinitely during a transaction, with an immediate disconnect after your commit.

this makes ORLite very easy to use in an ad-hoc manner, but can have performance implications. 🙈

while SQLite itself can handle 1000 connections per second, the repeated destruction and repopulation of SQLite's data page caches between your statements (or between transactions) can slow things down dramatically. 💀

the commit_begin method is used to commit the current transaction and immediately start a new transaction, without disconnecting from the database.

its exception behaviour and return value is identical to that of a plain commit call.

rollback

the rollback method rolls back the current transaction. if called outside of a current transaction, it is accepted and treated as a null operation.

once the rollback has been completed, the database connection falls back into auto-commit state. if you wish to immediately start another transaction, you will need to issue a separate ->begin call.

if a transaction exists at END-time as the process exits, it will be automatically rolled back. 🧚

returns true or throws an exception on error.

rollback_begin

Foo::Bar->begin;

# Code for the first transaction...

Foo::Bar->rollback_begin;

# Code for the last transaction...

Foo::Bar->commit;

by default, ORLite-generated code uses opportunistic connections.

this makes ORLite very easy to use in an ad-hoc manner, but can have performance implications. 🙈

the rollback_begin method is used to rollback the current transaction and immediately start a new transaction, without disconnecting from the database.

its exception behaviour and return value is identical to that of a plain commit call.

do

Foo::Bar->do(
    'insert into table (foo, bar) values (?, ?)',
    {},
    $foo_value,
    $bar_value,
);

the do method is a direct wrapper around the equivalent DBI method, but applied to the appropriate locally-provided connection or transaction. ✨