How to use SQLite3 with Bun JS (2024)

The integration of SQLite 3 with BUN.js opens up a world of possibilities for web developers. By combining the lightweight and powerful capabilities of SQLite 3 with the advanced capabilities of BUN.js, developers can easily create high quality dynamic web applications. Today I am going to be teaching you how to use SQLite 3 with Bun J.S.

For those of you who do not know, SQLite is a small, fast, and self contained database that can live on your server in the form of a file. This is one of the easiest forms of SQL to use, as you can be up and running in a matter of minutes, with a relational database system that is full of features. Did I mention it’s also free? This makes it a phenomenal option for small low traffic projects. I would highly recommend using this if you are making a portfolio site, or for prototyping an idea that you would like to eventually make into a large scale high traffic application. Bun J.S, is a very fast all in one runtime environment for JavaScript. This allows you to run JavaScript outside of the browser, which opens up the doors for writing backend code in JavaScript. If you’ve ever used Node or Deno, it is the same overall concept. I would recommend learning the basics of SQL and JavaScript before attempting this tutorial. This tutorial is not geared towards absolute beginners, and I will not be explaining the fundamentals of JavaScript and SQL.

Environment Setup

Create a new project directory and create a JavaScript file. For example, I will name mine app.js. Open this file up in a text editor. You can run this file via Bun by typing Bun app.js(or whatever you named your file).If you need to install Bun, feel free to check out my tutorial on installing Bun on Windows, the later half explains how to do it on Mac and Linux as well. To test this, write a simple console log and make sure it outputs to the terminal when you run the file via bun.

Setting up the database

Bun natively supports sqlite3. This makes setting up the database very easy for us! First we can Import the sqlite package and import the database. I will be naming this database file db.sqlite.

import {Database} from "bun:sqlite";
//connect to database
const db = new Database("db.sqlite");

Once connected we can begin running simple queries. To start, let’s create a table. This will be a simple example table with two columns, “valOne” and “valTwo”.

db.query("CREATE TABLE test (valOne, valTwo);").run();

Be sure to put the following code at the very bottom of your file to close out the database connection when you are done using it.

db.close();

Inserting data into the table

There are multiple ways to insert data into a database. I will be showing you a simple way to do so. In a similar way to how we created the table, we can write a simple insert query and run it.

let query = db.query("INSERT INTO test (valOne, valTwo) VALUES ('test val simple', 'another simple test val');");
query.run();

If we run this file, it will insert those two values into the two columns and we will have created our first row in the table.

SELECT * FROM test

let query = db.query("INSERT INTO test (valOne, valTwo) VALUES ('test val simple TWO', 'another simple test val TWO');");
query.run();

const query = db.query("SELECT * FROM test");
let result = query.all();

 [
  {
    valOne: "test val simple",
    valTwo: "another simple test val",
  }, {
    valOne: "test val simple TWO",
    valTwo: "another simple test val TWO",
  }
]

const query = db.query("SELECT * FROM test");
let result = query.values();

[
  [ "test val simple", "another simple test val" ], 
  [ "test val simple TWO", "another simple test val TWO"]
]

const query = db.query("SELECT * FROM test");
let result = query.get();

{
  valOne: "test val simple",
  valTwo: "another simple test val",
}