SQLite for embedded use in Deno - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to import the SQLite module in Deno.

Deno

import { DB } from "[1]";

Drag options to blanks, or click blank then click option'

Ahttps://deno.land/x/sqlite/mod.ts

Bhttps://deno.land/std/fs/mod.ts

Chttps://deno.land/x/http/mod.ts

Dhttps://deno.land/x/path/mod.ts

Attempts:

3 left

💡 Hint

Common Mistakes

Importing from the standard library instead of the SQLite module.

Using incorrect URLs that do not contain 'sqlite'.

The SQLite module for Deno is imported from https://deno.land/x/sqlite/mod.ts. This allows you to use the DB class to work with SQLite databases.

2fill in blank

medium

Complete the code to create a new in-memory SQLite database.

Deno

const db = new DB("[1]");

Drag options to blanks, or click blank then click option'

A""

B:memory:

C"file.db"

D"memory"

Attempts:

3 left

💡 Hint

Common Mistakes

Using a filename string instead of the special in-memory identifier.

Passing an empty string which creates a file instead of memory DB.

Using :memory: creates a new SQLite database in memory, which is temporary and faster for testing or small tasks.

3fill in blank

hard

Fix the error in the code to execute a SQL statement that creates a table.

Deno

db.[1]("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)");

Drag options to blanks, or click blank then click option'

Aquery

Bexecute

Copen

Drun

Attempts:

3 left

💡 Hint

Common Mistakes

Using query for statements that do not return rows.

Using execute which is not a method in Deno's DB.

The run method executes SQL statements that do not return rows, such as CREATE TABLE.

4fill in blank

hard

Fill both blanks to insert a user with id 1 and name 'Alice' into the users table.

Deno

db.run("INSERT INTO users (id, name) VALUES ([1], [2])");

Drag options to blanks, or click blank then click option'

A1

B'Alice'

C"Alice"

DNULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using double quotes for string values in SQL.

Not quoting string values causing syntax errors.

The id is a number 1 and the name is a string, so it must be quoted with single quotes 'Alice' in SQL.

5fill in blank

hard

Fill all three blanks to query all user names from the users table and log them.

Deno

for (const [[1]] of db.query("SELECT [2] FROM [3]")) {
  console.log([1]);
}

Drag options to blanks, or click blank then click option'

Aname

Bid

Cusers

Dusername

Attempts:

3 left

💡 Hint

Common Mistakes

Using a column name that does not exist in the table.

Mismatching the destructured variable and selected column.

The query selects the name column from the users table. The loop destructures the row to get the name value and logs it.

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