SQLite for embedded use in Deno - Mini Project: Build & Apply

SQLite for Embedded Use with Deno

📖 Scenario: You are building a simple embedded database application using SQLite in Deno. This will help you store and retrieve user information locally without needing a separate database server.

🎯 Goal: Create a Deno script that initializes an SQLite database, creates a table, inserts some user data, and queries the data to display user names.

📋 What You'll Learn

Use the deno.land/x/sqlite module to work with SQLite

Create a database file named users.db

Create a table called users with columns id (integer primary key) and name (text)

Insert exactly three users with names: Alice, Bob, and Charlie

Query all user names from the users table

💡 Why This Matters

🌍 Real World

Embedded SQLite databases are useful for small applications, desktop apps, or scripts that need simple local data storage without a full database server.

💼 Career

Knowing how to use SQLite with Deno helps in building lightweight backend services, prototypes, or tools that require persistent data storage.

1

Set up the SQLite database connection

Import the DB class from "https://deno.land/x/sqlite/mod.ts" and create a new database connection called db using the file "users.db".

Deno

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

// Create a new database connection called db
// Your code here

Hint

Use new DB("users.db") to create the database connection.

2

Create the users table

Use the db object to execute a SQL command that creates a table named users with columns id INTEGER PRIMARY KEY and name TEXT.

Deno

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

const db = new DB("users.db")

// Create the users table
// Your code here

Hint

Use db.execute with the SQL CREATE TABLE IF NOT EXISTS statement.

3

Insert user data into the table

Insert three users into the users table using db.query or db.execute. The users must have names Alice, Bob, and Charlie.

Deno

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

const db = new DB("users.db")

db.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")

// Insert three users: Alice, Bob, Charlie
// Your code here

Hint

Use a single INSERT INTO statement with placeholders and an array of names.

4

Query and print all user names

Use db.query to select all name values from the users table. Iterate over the results with a for loop using variables [name] and print each name.

Deno

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

const db = new DB("users.db")

db.execute("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)")

db.execute("INSERT INTO users (name) VALUES (?), (?), (?)", ["Alice", "Bob", "Charlie"])

// Query and print all user names
// Your code here

Hint

Use a for loop with const [name] to get each user name from db.query.

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