SQLite for embedded use in Deno - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this SQLite query in Deno?

Consider this Deno code using SQLite:

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 id, name FROM users")];
db.close();
console.log(result);

What will be logged to the console?

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"]);
const result = [...db.query("SELECT id, name FROM users")];
db.close();
console.log(result);

A [[1, "Alice"]]

B [{"id": 1, "name": "Alice"}]

C [["id", "name"]]

D []

💡 Hint

Remember that db.query returns rows as arrays, not objects.

The db.query method returns an iterable of rows as arrays. Since we inserted one user with id 1 and name "Alice", the result is an array containing one array: [[1, "Alice"]].

📝 Syntax

intermediate

Which option correctly opens an in-memory SQLite database in Deno?

In Deno, you want to create an in-memory SQLite database. Which code snippet does this correctly?

A const db = new DB(null);

B const db = new DB();

C const db = new DB(":memory:");

D const db = new DB({ memory: true });

💡 Hint

SQLite uses a special filename to indicate in-memory mode.

Passing the string ":memory:" to the DB constructor opens an in-memory database. The default constructor new DB() creates a temporary file-based database, not in-memory.

🔧 Debug

advanced

Why does this Deno SQLite code throw an error?

Given this code snippet:

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

What error will this code produce and why?

Deno

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

A No error, inserts successfully

B SyntaxError: Missing comma in SQL statement

C TypeError: db.query is not a function

D Error: Expected 2 parameters but got 1

💡 Hint

Check the number of placeholders and parameters passed.

The SQL statement expects two parameters for the placeholders (?, ?), but only one parameter is provided in the array. This mismatch causes an error about parameter count.

❓ state_output

advanced

What is the value of 'count' after running this Deno SQLite code?

Analyze this code:

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB();
db.query("CREATE TABLE logs (id INTEGER PRIMARY KEY, message TEXT)");
db.query("INSERT INTO logs (message) VALUES (?)", ["start"]);
db.query("INSERT INTO logs (message) VALUES (?)", ["process"]);
const [[count]] = [...db.query("SELECT COUNT(*) FROM logs")];
db.close();
console.log(count);

What will be printed?

Deno

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB();
db.query("CREATE TABLE logs (id INTEGER PRIMARY KEY, message TEXT)");
db.query("INSERT INTO logs (message) VALUES (?)", ["start"]);
db.query("INSERT INTO logs (message) VALUES (?)", ["process"]);
const [[count]] = [...db.query("SELECT COUNT(*) FROM logs")];
db.close();
console.log(count);

A 2

B undefined

C 1

D 0

💡 Hint

Count how many rows were inserted before the SELECT COUNT(*) query.

Two rows were inserted into the logs table, so the COUNT(*) query returns 2.

🧠 Conceptual

expert

Which statement about SQLite usage in Deno is TRUE?

Choose the correct statement about using SQLite embedded in Deno:

A SQLite in Deno requires a separate server process to run queries.

B SQLite databases in Deno can only be used synchronously, no async support exists.

C SQLite in Deno supports multi-threaded concurrent writes by default.

D SQLite databases in Deno are always stored on disk and cannot be in-memory.

💡 Hint

Think about how SQLite works as an embedded database.

SQLite is an embedded database and in Deno it is accessed synchronously. It does not require a server process, does not support multi-threaded concurrent writes by default, and can be used in-memory by specifying ":memory:".

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