Random tidings on SQLite & {DBI}

How to check if a table exists?

SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'your_table_name';

SELECT EXISTS (SELECT * FROM sqlite_master WHERE type = 'table' AND name = 'your_table_name');

Reference: Storage of The SQL Database Schema.

How to check if a table column exists?

PRAGMA table_info('your_table_name');

Reference: pragma.

Please note that PRAGMA feature was added in SQLite version 3.16.0 (2017-01-02): PRAGMA functions.

Foreign keys

Foreign keys are disabled by default in sqlite. They have to be enabled on each connection.

Reference: Foreign Keys.

That implies to use foreign keys with {pool}, you need to set the variable at the connection level. Hence, you cannot use the {pool} object itself.

Reference: Advanced Usage of Pool.

Timestamps

In most cases you need timestamps for columns such as created_at, modified_at.

Reference: Date and Time Functions

VARCHAR(n)

Due to type affinity, VARCHAR(n) will be treated as TEXT.

Reference: Type affinity.

Running an SQL script

{RSQLite} does not allow running multiple SQL statements at once. As such, if you have an SQL script, you have to split it into individual statements and run them one after the other.

Reference: Statements after the first dbExecute are ignored.

Query parameterization

{DBI} currently has no way to parameterize queries across all relational databases. Hence, if you’re writing a query to be run on multiple SQL backends, you need to change the parameterization for depending on the format used for each of the databases.

Reference: Parameterize Queries Across Relational DBs.