SQLite for embedded use in Deno

Introduction

SQLite lets you store data right inside your app without needing a separate database server. It is simple and fast for small to medium projects.

You want to save user settings or app data locally on a device.

You are building a small app that needs a database but no complex setup.

You want to try out database queries without installing a big database system.

You need a lightweight database for a desktop or mobile app.

You want to keep data in a file that moves with your app.

Syntax

Deno

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

const db = new DB("mydata.db");
db.query("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, 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();

Use DB from the SQLite module to create or open a database file.

Always close the database with db.close() when done to save changes.

Examples

This example creates a temporary database stored in memory only.

Deno

const db = new DB(); // Creates an in-memory database
// Data is lost when program ends

This example saves data in a file so it stays after the app closes.

Deno

const db = new DB("appdata.db"); // Opens or creates a file database
// Data is saved in 'appdata.db' file

This creates a table named 'tasks' with columns for id, title, and done status.

Deno

db.query("CREATE TABLE IF NOT EXISTS tasks (id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, done BOOLEAN)");

This inserts a new task with a title and done status using placeholders to keep data safe.

Deno

db.query("INSERT INTO tasks (title, done) VALUES (?, ?)", ["Learn Deno", false]);

Sample Program

This program creates a tasks database, adds three tasks, then prints each task with its done status.

Deno

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

const db = new DB("tasks.db");

// Create table if not exists
db.query(`CREATE TABLE IF NOT EXISTS tasks (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  title TEXT,
  done BOOLEAN
)`);

// Insert some tasks
const tasks = [
  ["Buy groceries", false],
  ["Read a book", true],
  ["Write code", false]
];
for (const [title, done] of tasks) {
  db.query("INSERT INTO tasks (title, done) VALUES (?, ?)", [title, done]);
}

// Query and print tasks
for (const [id, title, done] of db.query("SELECT id, title, done FROM tasks")) {
  console.log(`Task ${id}: ${title} - Done: ${done}`);
}

db.close();

Important Notes

SQLite stores Boolean values as 0 (false) or 1 (true).

Use parameter placeholders ? to safely insert data and avoid errors.

Remember to close the database to save changes and free resources.

Summary

SQLite is a simple, file-based database good for small apps and local data storage.

Use Deno's SQLite module to create, query, and manage databases easily.

Always close your database connection when finished.

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