Investigate first-class SQLite support

[ejboy]

Goal

Evaluate whether SQLite should become a first-class Scriptella database target using the Xerial SQLite JDBC driver.

SQLite would add a different interoperability category from the existing client/server databases.

Why SQLite

H2 remains Scriptella's natural Java-native embedded database for internal testing, temporary staging, and lightweight in-process workflows.

SQLite would serve a different role: a widely used external, file-backed database and migration endpoint.

The main value to investigate is interoperability, especially migrations:

• SQLite → PostgreSQL, MariaDB, MySQL, or another server database when an application outgrows SQLite;
• server database → SQLite for portable, local, offline, archive, or seed databases;
• SQLite → SQLite when transforming an existing application database;
• other Scriptella-supported sources → SQLite when SQLite itself is the desired output.

Do not position SQLite as a replacement for H2 inside Scriptella.

Scope

Start with a small compatibility investigation using a file-backed SQLite database.

Verify that Scriptella can correctly handle:

• connection through the Xerial SQLite JDBC driver;
• basic schema creation;
• representative reads and writes;
• integer and numeric values;
• ordinary text and Unicode;
• NULL values;
• persistence to a database file;
• transaction commit;
• transaction rollback after a deliberate failure.

Use independent JDBC verification where practical so Scriptella read/write behavior is not validating itself.

Include at least one migration-oriented flow, preferably something representative such as:

SQLite → PostgreSQL

or another existing first-class Scriptella database target.

The purpose is to validate SQLite as a source or destination at a real migration boundary, not merely as an embedded scratch database.

If the investigation succeeds

Consider adding:

• a first-class sqlite Scriptella alias;
• canonical jdbc:sqlite: URL handling/autodetection;
• one pinned Xerial SQLite JDBC version with focused compatibility coverage;
• a small checked-in SQLite migration example.

Keep the initial support claim narrow and tied to the tested driver/version.

Non-goals

Do not:

• replace or compete with H2 for Scriptella's internal embedded-database role;
• create a SQLite version matrix;
• add broad SQLite-specific functionality;
• change Scriptella's JDBC or transaction architecture solely for SQLite;
• broaden the Testcontainers suite just to accommodate SQLite, since SQLite does not require a container;
• add Docker packaging as part of this issue;
• treat this as release certification.

Follow-up possibility

Validated SQLite support could later make a disposable Docker quickstart more useful, for example by producing or consuming a file-backed .db that survives the container and can be inspected with standard SQLite tools.

That should remain a separate follow-up. SQLite should be supported because it is a useful migration endpoint, not because a Docker demo needs an embedded database.

Decision point

The investigation should answer:

Can Scriptella provide a useful, predictable migration path to and from SQLite through its existing JDBC model with minimal special handling?

If yes, promote narrow first-class SQLite support.

If SQLite requires substantial special-case behavior or architectural changes, keep it outside the documented first-class compatibility baseline.