SQLite Bun: Import CSV Into a Table

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-parse

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";

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);
  }
}

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" });

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,
});

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;
}),

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);
}

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();
}

How it works.

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

import { importCSV } from "./author";

Second, connect to the pub.db database:

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

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

importCSV(db, "authors.csv");

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.ts

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.db

Step 3. Format the query output:

.mode column
.header on 
.nullvalue null

Step 4. Retrieve data from the authors table:

select * from authors;

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.

Was this tutorial helpful?