SQLite for embedded use in Deno - Step-by-Step Execution

Concept Flow - SQLite for embedded use

Start Deno Program

↓

Import SQLite Module

↓

Open/Create SQLite Database

↓

Execute SQL Commands

↓

Fetch Query Results

↓

Close Database

↓

End

This flow shows how a Deno program uses SQLite embedded: import, open DB, run SQL, get results, then close.

Execution Sample

Deno

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB();
db.query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
db.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
for (const [id, name] of db.query("SELECT id, name FROM users")) {
  console.log(id, name);
}
db.close();

This code creates an in-memory SQLite DB, adds a table, inserts a user, queries users, prints results, then closes DB.

Execution Table

Step	Action	SQL/Code	Result/State Change	Output
1	Import SQLite module	import { DB } from ...	Module DB available
2	Create new DB instance	const db = new DB();	In-memory DB opened
3	Create table	CREATE TABLE users ...	Table 'users' created
4	Insert data	INSERT INTO users (name) VALUES ('Alice')	One row inserted
5	Query data	SELECT id, name FROM users	Query returns 1 row
6	Loop over results	for (const [id, name] ...)	id=1, name='Alice'	Prints: 1 Alice
7	Close DB	db.close();	Database closed
8	End program		No more DB operations

💡 Program ends after closing the database and printing all query results.

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	Final
db	undefined	DB instance (open)	DB with table and 1 row	DB queried, cursor at end	DB closed
id	undefined	undefined	undefined	1	1
name	undefined	undefined	undefined	Alice	Alice

Key Moments - 3 Insights

Why do we need to call db.close() at the end?

Closing the DB frees resources and ensures data is saved properly. See execution_table step 7 where db.close() changes DB state to closed.

What happens if we query before creating the table?

The query will fail because the table doesn't exist yet. In execution_table step 3, the table is created before querying in step 5.

Why do we use a loop to read query results?

SQLite returns rows one by one; the loop reads each row. In step 6, the loop extracts id and name from the query result.

Visual Quiz - 3 Questions

Look at the execution_table, what is the state of 'db' after step 4?

A DB not yet created

B DB with table and 1 row inserted

C DB closed

D DB with no tables

💡 Hint

Check variable_tracker column 'After Step 4' for 'db' value.

At which step does the program print '1 Alice'?

A Step 5

B Step 7

C Step 6

D Step 3

💡 Hint

Look at execution_table row for step 6 under Output column.

If we skip db.close(), what changes in the execution flow?

A DB remains open, resources not freed

B Table creation fails

C Query returns no rows

D Program crashes immediately

💡 Hint

Refer to key_moments about why db.close() is important.

Concept Snapshot

SQLite embedded in Deno:
- Import DB from sqlite module
- Create DB instance (in-memory or file)
- Use db.query() to run SQL commands
- Loop over query results to read rows
- Close DB with db.close() to free resources

Full Transcript

This visual trace shows how to use SQLite embedded in a Deno program. First, the SQLite module is imported. Then, a new database instance is created in memory. Next, a table is created with SQL, and a row is inserted. The program queries the table and loops over the results, printing each row. Finally, the database is closed to release resources. Variables like 'db', 'id', and 'name' change as the program runs. Key points include the need to create tables before querying, reading query results with a loop, and closing the database at the end. The quiz tests understanding of these steps and states.

Practice

1. What is the main advantage of using SQLite in a Deno application?

easy

A. It stores data in a simple file without needing a separate server.

B. It requires a complex setup with multiple servers.

C. It only works with large-scale distributed systems.

D. It cannot be used for local data storage.

Solution

1. Step 1: Understand SQLite's design

   SQLite is designed as a lightweight, file-based database that does not require a separate server.

2. Step 2: Compare options

   Claims of needing servers, complex setups, or only working with distributed systems are incorrect for SQLite, which is simple and local.

3. Final Answer:

   It stores data in a simple file without needing a separate server. -> Option A

4. Quick Check:

   SQLite = file-based, no server needed [OK]

Hint: Remember SQLite is serverless and file-based [OK]

Common Mistakes:

• Thinking SQLite needs a server
• Confusing SQLite with large distributed databases
• Assuming SQLite can't store local data

2. Which of the following is the correct way to import the SQLite module in a Deno script?

easy

A. require('sqlite');

B. import DB from "sqlite";

C. import { DB } from "https://deno.land/x/sqlite/mod.ts";

D. import * as sqlite from 'sqlite3';

Solution

1. Step 1: Identify Deno import syntax

   Deno uses ES module imports with URLs, so the correct import includes the full URL and curly braces for named imports.

2. Step 2: Check each option

   require() is Node.js syntax. Bare specifiers like "sqlite" omit the required URL. 'sqlite3' is a Node.js module. Only the import with URL and named import matches Deno style.

3. Final Answer:

   import { DB } from "https://deno.land/x/sqlite/mod.ts"; -> Option C

4. Quick Check:

   Deno imports use URLs and named imports [OK]

Hint: Deno imports modules via URLs with named imports [OK]

Common Mistakes:

• Using Node.js require() syntax
• Omitting the full URL in import
• Using incorrect module names

3. Given this Deno SQLite code snippet, what will be the output?

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB();
db.query("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");
db.query("INSERT INTO users (name) VALUES (?)", ["Alice"]);
const result = [...db.query("SELECT name FROM users")];
console.log(result);
db.close();

medium

A. ["Alice"]

B. [["Alice"]]

C. [[1, "Alice"]]

D. []

Solution

1. Step 1: Understand the query results format

   db.query returns an iterator of rows, each row is an array of column values. Selecting 'name' returns rows like ["Alice"].

2. Step 2: Spread into array and log

   Using [...db.query(...)] collects rows into an array of arrays, so result is [["Alice"]].

3. Final Answer:

   [["Alice"]] -> Option B

4. Quick Check:

   Query returns array of rows, each row an array [OK]

Hint: Query returns array of rows, each row is an array [OK]

Common Mistakes:

• Expecting a flat array instead of nested arrays
• Confusing row data with column names
• Forgetting to spread iterator into array

4. What is wrong with this Deno SQLite code snippet?

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB("mydb.sqlite");
db.query("CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)");
db.query("INSERT INTO items (name) VALUES (?)", ["Book"]);
// Missing db.close()

medium

A. The database connection is not closed, which can cause resource leaks.

B. The table creation syntax is incorrect.

C. The INSERT statement is missing a value for id.

D. The DB constructor requires no arguments.

Solution

1. Step 1: Check resource management

   The code opens a database but does not call db.close(), which is needed to release resources properly.

2. Step 2: Verify SQL statements and constructor

   Table creation and insert syntax are correct; id is auto-incremented primary key; DB constructor accepts filename.

3. Final Answer:

   The database connection is not closed, which can cause resource leaks. -> Option A

4. Quick Check:

   Always close DB connections to avoid leaks [OK]

Hint: Always call db.close() after database work [OK]

Common Mistakes:

• Assuming id must be provided when auto-incremented
• Ignoring the need to close the database
• Thinking DB constructor takes no arguments

5. You want to store user preferences in a Deno app using SQLite. Which approach correctly creates a table with a default value and inserts a new user with a custom preference?

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB();
// Which code below is correct?

hard

A. db.query("CREATE TABLE prefs (user TEXT PRIMARY KEY, theme TEXT DEFAULT 'light')");
db.query("INSERT INTO prefs (user, theme) VALUES ('Bob')");

B. db.query("CREATE TABLE prefs (user TEXT, theme TEXT)");
db.query("INSERT INTO prefs VALUES ('Bob')");

C. db.query("CREATE TABLE prefs (user TEXT PRIMARY KEY, theme TEXT DEFAULT 'light')");
db.query("INSERT INTO prefs (user) VALUES ('Bob')");

D. db.query("CREATE TABLE prefs (user TEXT PRIMARY KEY, theme TEXT DEFAULT 'light')");
db.query("INSERT INTO prefs (user, theme) VALUES (?, ?)", ["Bob", "dark"]);

Solution

1. Step 1: Check table creation with default value

   The table must define PRIMARY KEY on user and DEFAULT 'light' on theme.

2. Step 2: Check insert statements

   To insert with custom preference, specify both columns using placeholders and provide a custom value like 'dark' to override default. Omitting theme uses default (not custom). Mismatched VALUES without placeholders cause errors.

3. Final Answer:

   db.query("CREATE TABLE prefs (user TEXT PRIMARY KEY, theme TEXT DEFAULT 'light')"); db.query("INSERT INTO prefs (user, theme) VALUES (?, ?)", ["Bob", "dark"]); -> Option D

4. Quick Check:

   Use DEFAULT in table and specify values to override [OK]

Hint: Use DEFAULT in table and specify values to override [OK]

Common Mistakes:

• Not using placeholders in INSERT statements
• Missing PRIMARY KEY in table
• Assuming default values override explicit inserts