SQL Practice Online

How to Use Deno with SQLite Effectively

By Cristian G. Guasch • Updated: 03/03/24 • 9 min read

Diving deeper into how to use Deno with SQLite, it’s essential to grasp Deno’s security model and SQLite’s lightweight nature. Deno, by default, does not allow access to the file system, network, or environment variables, ensuring a secure runtime for executing scripts. This means when we work with SQLite—a serverless database that reads and writes directly to disk—we need to explicitly grant permissions.

Get Exclusive SQL Tips Right In Your Email Inbox

Plus SQL Cheat Sheets and more bonuses, all for FREE!

Related: How to Use SQLite Bun: Speed and Simplicity with Bun JS

Here’s a quick example of how to start a Deno project with SQLite. To run this code, you’d need to grant permission to access the file system:

// Import SQLite module
import { SQLite } from "https://deno.land/x/sqlite/mod.ts";
// Initialize the database
const db = new SQLite("./database.sqlite");// Execute a query
db.query("CREATE TABLE IF NOT EXISTS people (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");

Remember, the above code will only execute successfully if you’ve granted Deno the necessary permissions with --allow-read --allow-write.

Handling Queries

When executing queries, especially for retrieving data, it’s crucial to manage the results properly. Here’s how you can fetch data from the table we just created:

const rows = db.query("SELECT id, name FROM people");
for (const [id, name] of rows) {
console.log(id, name);
}

One common mistake is not closing the database connection after execution, which can lead to memory leaks. Always ensure you close your connection:

db.close();

Inserting Data with Parameters

To avoid SQL injection, it’s vital to insert data using prepared statements. Here’s a way to do it safely:

db.query("INSERT INTO people (name) VALUES (?)", ["Jane Doe"]);

Unlike traditional runtime environments where you might overlook SQL injection vulnerabilities, Deno’s emphasis on security makes you conscientious about best practices like using parameterized queries. This not only makes your application more secure but also improves your coding habits.

By understanding these essentials and avoiding common pitfalls, you’re well on your way to leveraging Deno and SQLite for more efficient and secure applications. Keep these tips in mind as you continue to explore the capabilities of combining Deno with SQLite for your projects.

Setting Up Deno Environment

Before diving into the wonders of SQLite with Deno, it’s crucial that we get our environment properly set up. Getting Deno up and running is straightforward, but there are a few steps and checks that I always recommend to ensure everything is in order.

Firstly, installing Deno is a breeze on any major operating system. On macOS or Linux, I usually run this simple command in the terminal:

curl -fsSL https://deno.land/x/install/install.sh 
|sh

While on Windows, the installation can be done through PowerShell:

iwr https://deno.land/x/install/install.ps1 -useb 
|iex

After installation, it’s important to verify that Deno has been installed correctly. Typing deno --version should display the current version of Deno, reassuring that we’re good to proceed.

One common mistake I’ve seen is skipping the addition of Deno’s path to your system’s environment variables. If you find yourself running into the error deno: command not found after installation, it’s likely because your system doesn’t know where to look for Deno. This can usually be fixed by manually adding Deno’s installation path to your system’s PATH variable.

Once Deno is installed and recognized by your system, we need to run a simple script to ensure everything’s working as it should. I like to test it with a basic command:

deno run https://deno.land/std/examples/welcome.ts

This command should output a friendly “Welcome to Deno!” message. If you see this, congratulations, you’re ready to start developing with Deno.

The beauty of Deno’s design means we don’t have to deal with package managers or worry about installing third-party packages globally. However, managing permissions is critical when working with Deno. For instance, if our project with SQLite requires access to the file system, we’ll need to explicitly grant that permission when running our script:

deno run --allow-read --allow-write my_deno_sqlite_script.ts

By specifying --allow-read and --allow-write, we’re telling Deno it’s okay to let our script interact with the file system, which is essential when working with databases like SQLite.

Installing SQLite Library for Deno

Before I dove into the world of SQLite with Deno, I had to make sure that the right tools were in place. To seamlessly integrate SQLite into my Deno project, installing the SQLite library was a crucial step. Here’s how I managed to do just that, ensuring a smooth setup that laid the groundwork for my project’s database functionalities.

Getting started, I learned that Deno’s package management is quite different from what I was used to. Unlike Node.js, there’s no need for a package manager like npm. In Deno, you import modules directly from URLs, which makes the whole process cleaner and more straightforward. For SQLite, the first thing I did was to find a reliable third-party module that could offer the functionality I needed. After some research, I found deno-sqlite, a popular choice among developers for its ease of use and comprehensive features.

To install deno-sqlite, I didn’t have to download anything manually. Instead, I imported the module directly into my project using the following line of code at the top of my Deno script:

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

This single line of code was all it took to get started. However, I made sure my import statement was up-to-date since URLs can change. Always double-check the module’s official documentation for the most current import URL.

A common mistake I initially made was forgetting to grant the necessary permissions when running my Deno script. Remember, Deno is secure by default, which means accessing the file system (a necessity for SQLite) requires explicit permission. To avoid this error, I ran my script with the following command:

deno run --allow-read --allow-write your_script.ts

By specifying --allow-read and --allow-write, I granted my script access to the file system, allowing SQLite to perform database operations without hitches.

Get Exclusive SQL Tips Right In Your Email Inbox

Plus SQL Cheat Sheets and more bonuses, all for FREE!

Creating a SQLite Database

After successfully installing the deno-sqlite library and ensuring the proper permissions are set up, it’s time to delve into creating a SQLite database. This process is straightforward but crucial for beginners to understand to avoid common pitfalls.

To start, I’ll show you how to create a new database. It’s as simple as importing the DB class from the library and instantiating it with a file path. If the file doesn’t exist, SQLite will create it for you. Here’s how it looks:

import { DB } from "https://deno.land/x/sqlite/mod.ts";
// Create a new database
const db = new DB("my_database.sqlite");

With the database instantiated, you can begin executing SQL commands to create tables, insert data, and query your database. Let’s create a simple table:

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

This command creates a table named users with an id column that auto-increments and a name column to store text.

Common Mistakes

When creating tables and working with SQLite in Deno, it’s easy to fall into a few common traps:

  • Forgetting IF NOT EXISTS: Without IF NOT EXISTS, running your script multiple times might cause errors since it attempts to create a table that already exists.
  • Mismatched Data Types: Ensure your data types in SQL commands match the data you’re inserting, or you’ll run into errors.
  • Neglecting Permissions: Remember, for any file operation, including creating a database file, Deno requires explicit permissions. Failing to use --allow-read and --allow-write flags will result in permission errors.

By focusing on these critical points and following the provided examples, you’ll be well on your way to utilizing SQLite within your Deno projects efficiently. This foundation lets you explore more complex database operations, such as transactions and indexing, which are paramount for creating robust applications.

Writing and Executing Queries in Deno

After setting up the SQLite database in Deno, the next critical step is to write and execute SQL queries. This is how data gets into and out of your database, and it’s crucial for the functionality of your applications. Let’s dive into the process, explore some common variations, and discuss pitfalls to avoid.

Firstly, executing a query in Deno using SQLite requires you to have a database instance. I always ensure that my database instance is correctly initialized and accessible before attempting to execute any query. Here’s a basic example of how to insert data into a table:

import { DB } from "https://deno.land/x/sqlite/mod.ts";
const db = new DB("my_database.db");
const name = "Deno Land";
const score = 100;db.query("INSERT INTO users (name, score) VALUES (?, ?)", [name, score]);

When inserting or updating data, I find parameterized queries incredibly useful for preventing SQL injection attacks. Never concatenate strings to build SQL commands.

Fetching data introduces variety in handling results. Queries for data retrieval often look like this:

const users = [...db.query("SELECT id, name, score FROM users")];
for (const [id, name, score] of users) {
console.log(id, name, score);
}

In this pattern, spreading the query results allows easy iteration over each row. It’s a pattern I recommend for its simplicity and readability.

  • Forgetting to Release Resources: Always make sure to close the database connection when you’re done with it. Not doing so can lead to memory leaks.
  • Ignoring Error Handling: Wrap your queries in try-catch blocks to manage exceptions, especially when dealing with user-generated data.
  • Overlooking SQL Injection: As mentioned, always use parameterized queries to avoid SQL injection vulnerabilities.

Understanding these fundamentals will significantly enhance your ability to interact with SQLite databases in Deno, paving the way for more complex data manipulation and retrieval techniques. Remember, practice makes perfect, so don’t shy away from experimenting with different types of queries and database operations.

Conclusion

Mastering SQLite with Deno isn’t just about executing queries—it’s about doing it right. By focusing on initializing your database correctly and leveraging parameterized queries, you’re setting a strong foundation. Remember, the devil’s in the details. Avoiding common pitfalls like resource leaks and SQL injection not only secures your application but also ensures it runs smoothly. Armed with these insights, you’re now better equipped to tackle more complex data manipulation tasks with confidence. Let’s keep pushing the boundaries of what we can achieve with Deno and SQLite.

Get Exclusive SQL Tips Right In Your Email Inbox

Plus SQL Cheat Sheets and more bonuses, all for FREE!

Related articles