Declare variable in SQLite and use it

sql

transaction-safety

variable-declaration

sqlite-optimization

byNikita Barsukov·Feb 4, 2025

In SQLite, the two ways to simulate variables are through the use of common table expressions (CTEs) or temporary tables.

CTE as a variable (compact scope):

WITH var(value) AS (SELECT 42)    -- Set it high, set it at 42!
SELECT * FROM your_table WHERE your_column = (SELECT value FROM var);   -- Grab the data and run!

Temporary table as a substitute for variables (broader scope):

CREATE TEMP TABLE vars(name TEXT, val INTEGER);    -- It's like a VIP list for your data
INSERT INTO vars VALUES ('myVar', 42);    -- Yes, you're invited! 
SELECT t.* FROM your_table t JOIN vars ON t.your_column = vars.val WHERE vars.name = 'myVar';   -- Let the party begin
DROP TEMP TABLE vars; -- It's like throwing away the list after a party!

Persisting across sessions

Say hello to Nemo

The in-memory temp table floats as long as the session lasts, but inevitably sinks and gets wiped after the session ends. If you want your variables to stick around, like Nemo from Pixar movies, you may want to use a regular SQLite table.

CREATE TABLE IF NOT EXISTS persistent_vars(name TEXT PRIMARY KEY, val BLOB);  -- Now, we have a treasure chest for our variables!
INSERT OR REPLACE INTO persistent_vars(name, val) VALUES ('myBinVar', x'12345678'); -- And you're in! Congrats!

Keep your friends close, keys closer!

With PRAGMA foreign_keys = ON;, you can make sure all your logically related data play nice together. It's like being at a high school reunion; everyone is linked in a way or another.

Efficient batch access: Who doesn't like to multi-task?

Whether it’s fetching groceries or gathering batch information, we all love efficiency. If one SELECT statement can fetch multiple variables, why not?

WITH var(value) AS (
  SELECT val FROM vars WHERE name = 'Variable1'  -- Variable1 reporting for duty
  UNION ALL
  SELECT val FROM vars WHERE name = 'Variable2'  -- Variable2 made it as well!
)
SELECT * FROM your_table WHERE your_column IN (SELECT value FROM var);

Transaction safety: Keep safe, everybody!

Enclosing SQL statements that involve setting and retrieving variables within a transaction is like having a lifeguard at your beach party. It ensures atomicity of your actions and protection from those pesky interruptions or errors.

BEGIN TRANSACTION;    -- Everybody ready?
-- Your SQL Commands Go Here
END TRANSACTION;    -- That's a wrap; everybody out of the pool!

Packing for complex scenarios

For BLOB fans

SQLite's versatility to store any value type as text or blob makes it perfect for your binary data needs.

CREATE TEMP TABLE binVars(name TEXT, val BLOB);
INSERT INTO binVars VALUES ('myBinVar', x'50696374'); // Mr. Bin(ary) Var, welcome on board!

Preparation is the key!

In multi-dimensional queries or larger databases, validating variables can become a big task. COALESCE helps ensure variables have valid values.

SELECT COALESCE((SELECT val FROM vars WHERE name = 'myVar'), 'Default') AS validVar; // The safety net is here!

Read-only variables: No trespassing!

The CTE variable approach is your security guard for configurations or values that are meant to remain unchanged within a specific query context:

WITH config AS (SELECT 'readonly' AS setting) 
SELECT * FROM your_table, config WHERE your_table.condition = config.setting; // Read-only access: No edit allowed!