Bun SQLite

Lucky for me after my initial investigation, I realized that the Bun team was good enough to add in a simple SQLite driver module to Bun itself (Not to mention I recently stumbled across their nifty shell module). I am a big fan of the inclusion of these features into Bun because, when I am working on a simple prototype application, the last thing I want to do is sift through NPM packages and try to parse through whatever random method was chosen for the documentation; I just want to open up Emacs and start writing some JavaScript. That is precisely what Bun's SQLite driver allows me to do. You still have the option to use something from NPM, but you don't have to go searching for a means to use something as simple as a basic SQLite database or a .env file in the early stages of building a project.

Generating a table is dead simple. You just need to import the Bun SQLite module, create a database object and then use the query() and run() methods to create a table.

import { Database } from "bun:sqlite";

const db: Database = new Database(":memory:");

  db.query(`
CREATE TABLE books (
id integer primary key,
title text,
author text,
genre text,
price real,
image text,
available boolean
);
`).run()

I put the database in memory because its just a demo, but you could of course supply a file path to the Database constructor as well.

All I had left to do was to parse the CSV file and insert it into the database. I chose to use Papa Parse to parse the CSV file (I chose it, admittedly, somewhat randomly), and it was serviceable, but for some reason it was adding a junk object that only had an BookID key that was empty. Instead of figuring out why, I just filtered it out of the results. If I were doing more than just a demo, I probably would have dug into this rather odd result a bit further, or better yet, it might be just as easy to create a custom function to parse the CSV file manually.

Before I could actually work with the data, I needed to create some types. I try to make a habit of using TypeScript where possible on small projects, so that I am more familiar and practiced with it when I need it for larger projects. First I created a BookCsv type that would hold the result form Papa parse and a Book type that represented the actual database schema. I created the extra Book type because Papa parse wanted the object's keys to match the field names. I figured it was easiest to just create two types, so that I could make sure that the object keys followed js naming conventions.

import { Database } from "bun:sqlite";
import Papa, { type ParseResult } from 'papaparse';
import type { Book, BookCsv } from './types.ts'

const db: Database = new Database(":memory:");

const csv = await Bun.file("data/booksdata.csv").text();
const books: ParseResult<BookCsv> = Papa.parse(csv, { header: true });

books.data.forEach(book => {
  if (book.Title) {
    const insert: Book = {
      id: book.BookID,
      title: book.Title,
      author: book.Author,
      genre: book.Genre,
      price: book.Price,
      image: book.ImageFileName,
      available: book.Available,
    }
    db.query(`
INSERT INTO books (title, author, genre, price, image, available)
VALUES ($title, $author, $genre, $price, $image, $available);
`).values({
      $title: insert.title,
      $author: insert.author,
      $genre: insert.genre,
      $price: insert.price,
      $image: insert.image,
      $available: insert.available,
    });
  }
});

I use the Book type to insert into the database and then when I need to get the data out I can use that same type with a select statement. I used the object method for inserting the data, but you can also use the question mark style and then supply an array of values that match the question marks.

The amount of code and effort it would have required to use node for this would have been virtually the same, but I like not always have to make decisions, and Bun is fast becoming my go to js runtime for these kinds of projects for that reason alone. You can access the wealth of NPM packages if you need them, but very often I find myself installing the same packages over and over just to perform relatively simple tasks, and in those cases having something that is a bit more opinionated and has some of these quality of life modules is exactly what I am looking for.