Import tasks into SQLite database

Create a SQLite 3 database and import tasks.json.

First, install sqlite3.

npm i sqlite3

Create src/scripts/loadTasks.mjs

import { readFile } from "fs/promises"
import sqlite3 from "sqlite3"

const tasksFile = './src/app/data/tasks.json'
const tasksDb = './tasks.db'

Connect to the database. It will automatically be created if the file path does not exist.

function getDatabase() {
  return new sqlite3.Database(tasksDb, (error) => {
    if (error) {
      console.log(error)
    }
  })
}

Create the task table. db.serialize() is used to ensure this runs before data is inserted.

function initDatabase() {
  const db = getDatabase()
  db.serialize(() => {
    db.run(
      `CREATE TABLE task (
        id text NOT NULL,
        title text NOT NULL,
        status text NOT NULL,
        label text NOT NULL,
        priority text NOT NULL
    )`, error => {
      if (error) {
        console.log(error)
      }
    })
  })
  
  return db
}

The table should only be created on the first run. Check if the file path exists and run initDatabase() if not.

function loadDatabase(tasksDb) {
  return readFile(tasksDb)
    .then(() => getDatabase())
    .catch(() => initDatabase())
}

Load the tasks.json file.

function loadTasks() {
  loadFile(tasksFile)
    .then(tasks => insertTasks(tasks))
    .catch(error => console.log(error))
}

Insert tasks.

function insertTasks(tasks) {
  loadDatabase().then(db => {
    tasks.forEach(task => {
      db.serialize(() => {
        db.run(`INSERT INTO task VALUES (?, ?, ?, ?, ?)`, [task.id, task.title, task.status, task.label, task.priority])
      })
    });
  })
}

Lastly, add a call to loadTasks() after the file path constants.

const tasksFile = './src/app/data/tasks.json'
const tasksDb = './tasks.db'
loadTasks()

Call the script with node src/scripts/loadTasks.mjs, or configure package.json and call with npm run load-tasks.

"scripts": {
  ...
  "load-tasks": "node src/scripts/loadTasks.mjs"
},