nodejs better-sqlite3
Links
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)