SQLite Bun: Importing CSV

Summary: in this tutorial, you will learn how to import data from a CSV file into a table in SQLite using Bun.

Importing CSV Into a Table

Step 1. Open your terminal and install csv-parse package for parsing CSV data by running the following command:

bun install csv-parseCode language: Shell Session (shell)

Step 2. Modify the author.ts file to import the readFileSync from fs module and parse function from the csv-parse/sync module:

import { readFileSync } from "fs";
import { parse } from "csv-parse/sync";Code language: TypeScript (typescript)

Step 3. Define a new function called importCSV that reads from a CSV file that contains author data (First Name and Last Name column) and inserts each line into the authors table:

export function importCSV(db: Database, filename: string) {
  // read the CSV file
  const content = readFileSync(filename, { encoding: "utf-8" });

  // parse CSV content
  const records = parse(content, {
    columns: (header) =>
      header.map((column: string) => {
        if (column === "First Name") return "firstName";
        if (column === "Last Name") return "lastName";
        return column;
      }),
    delimiter: ",",
    trim: true,
    skip_empty_lines: true,
  });

  //  insert each record into the authors table
  for (const { firstName, lastName } of records) {
    insert(db, firstName, lastName);
  }
}Code language: TypeScript (typescript)

How it works.

First, read text from a CSV file specified by the filename into the content variable:

const content = readFileSync(filename, { encoding: "utf-8" });Code language: TypeScript (typescript)

Second, parse the CSV contents into an array of records, each is an object that contains the firstName and lastName fields:

const records = parse(content, {
    columns: (header) =>
      header.map((column: string) => {
        if (column === "First Name") return "firstName";
        if (column === "Last Name") return "lastName";
        return column;
      }),
    delimiter: ",",
    trim: true,
    skip_empty_lines: true,
});Code language: TypeScript (typescript)

Notice that the following maps the data in the First Name and Last Name to firstName and lastName properties:

columns: (header) =>
      header.map((column: string) => {
        if (column === "First Name") return "firstName";
        if (column === "Last Name") return "lastName";
        return column;
}),Code language: TypeScript (typescript)

Third, iterate over the records and insert each into the authors table by calling the insert() method:

for (const { firstName, lastName } of records) {
  insert(db, firstName, lastName);
}Code language: TypeScript (typescript)

Step 4. Create a new import.ts file with the following code:

import { Database } from "bun:sqlite";
import { importCSV } from "./author";

let db;
try {
  db = new Database("pub.db", { strict: true });
  importCSV(db, "authors.csv");
} catch (error) {
  console.log(error);
} finally {
  if (db) db.close();
}Code language: TypeScript (typescript)

How it works.

First, import the importCSV function from the author.ts module:

import { importCSV } from "./author";Code language: TypeScript (typescript)

Second, connect to the pub.db database:

db = new Database("pub.db", { strict: true });Code language: TypeScript (typescript)

Third, call the importCSV function to import data from the authors.csv file into the authors table:

importCSV(db, "authors.csv");Code language: TypeScript (typescript)

Step 5. Download the authors.csv file and copy it to the project directory.

Step 6. Run the import.ts file to import data from the authors.csv into the authors table:

bun import.tsCode language: Shell Session (shell)

Verifying data

Step 1. Open a new terminal and navigate to the project directory.

Step 2. Connect to the pub.db database using the sqlite3 shell:

sqlite3 pub.dbCode language: CSS (css)

Step 3. Format the query output:

.mode column
.header on 
.nullvalue nullCode language: CSS (css)

Step 4. Retrieve data from the authors table:

select * from authors;Code language: JavaScript (javascript)

Output:

id  firstName  lastName
--  ---------  --------
1   Alex       Starling
2   Jamie      Falcon
3   Casey      Rivers
4   Morgan     Sky
5   Taylor     Phoenix
6   Jordan     Blaze
7   Avery      Storm
8   Riley      Moon
9   Quinn      Frost
10  Dakota     Ember

The output indicates that the Bun app has successfully imported data from the authors.csv file into the authors table.

Summary

• Use the csv-parse module to parse a CSV file into an array of objects.