Skip to content

nodejs better-sqlite3

NOTE

DOES NOT WORK with Deno or bun - Software

libsql is better because it works with Deno and bun - Software

Tutorial / Boilerplate

mkdir better-sqlite3-template 
cd better-sqlite3-template 
npm init -y
npm install better-sqlite3
npm install --save @types/better-sqlite3

# Add "type" : "module" to package.json
jq '. + { "type": "module" }' package.json > new-package.json
mv new-package.json package.json

# Download or copy index.js
curl -o index.js https://gist.githubusercontent.com/dentropy/24517852e8b36fa4395a5c42d36bdac1/raw/377a2dea1810a00fc8fcdd1db78102966f406492/index.js


import sqlite from 'better-sqlite3';

let populate_data = `
-- Create the 'customers' table
CREATE TABLE IF NOT EXISTS customers (
    customer_id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

-- Create the 'products' table
CREATE TABLE IF NOT EXISTS products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT,
    price REAL
);

-- Create the 'orders' table
CREATE TABLE IF NOT EXISTS orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

-- Insert data into the 'customers' table
INSERT INTO customers (first_name, last_name, email)
VALUES
    ('John', 'Doe', 'john.doe@example.com'),
    ('Jane', 'Smith', 'jane.smith@example.com'),
    ('Bob', 'Johnson', 'bob.johnson@example.com');

-- Insert data into the 'products' table
INSERT INTO products (product_name, price)
VALUES
    ('Widget A', 19.99),
    ('Widget B', 29.99),
    ('Widget C', 39.99);

-- Insert data into the 'orders' table
INSERT INTO orders (customer_id, order_date)
VALUES
    (1, '2023-09-13'),
    (2, '2023-09-14'),
    (3, '2023-09-15');
`
const db = new sqlite("./db.sqlite");
await db.exec(populate_data);
let query  = await db.prepare(`SELECT * FROM orders;`).all();
console.log(query)

const sqlite3 = require('sqlite3').verbose();

// Open a database connection
const db = new sqlite3.Database('keybase_export.sqlite');

// Function to perform the SELECT query
const runSQL = (sql_query) => {
  return new Promise((resolve, reject) => {
    db.all(sql_query, (err, row) => {
      if (err) {
        reject(err);
      } else {
        resolve(row);
      }
    });
  });
};

// Main function using async/await
const main = async () => {
  try {
    // Perform the SELECT query and store the result in a variable
    const result = await runSQL('SELECT * FROM teams_t');
    console.log(result); // Use the result as needed
  } catch (error) {
    console.error(error.message);
  } finally {
    // Close the database connection
    db.close();
  }
};

// Call the main function
main();