Why SQLite for embedded use in Deno? - Purpose & Use Cases

The Big Idea

Discover how a tiny database can make your app smarter and faster without extra servers!

The Scenario

Imagine building a small app that needs to save user notes on your device without relying on an internet connection or a big database server.

The Problem

Trying to manage data manually with files or complex server setups is slow, error-prone, and makes your app bulky and hard to maintain.

The Solution

SQLite lets you embed a lightweight, fast database right inside your app, so you can store and retrieve data easily without extra servers or complicated setups.

Before vs After

✗ Before

open file; read lines; parse data; write back changes

✓ After

db.query('SELECT * FROM notes WHERE id = ?', [noteId])

What It Enables

You can build fast, reliable apps that store data locally with simple, powerful database commands.

Real Life Example

A note-taking app that saves your notes instantly on your device, even when offline, without needing a big database server.

Key Takeaways

Manual data handling is slow and error-prone.

SQLite embeds a full database inside your app.

This makes local data storage simple, fast, and reliable.

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