better-sqlite3 Cheatsheet

I like using raw SQL instead of an ORM now.

Installation:

npm install better-sqlite3

Initialization

import Database from 'better-sqlite3';
const db = new Database('/home/nivethan/bp/world-news/db/kk.db');
db.pragma('journal_mode = WAL');

Create a table:

    const createSQL = 'CREATE TABLE IF NOT EXISTS news (id INTEGER PRIMARY KEY AUTOINCREMENT, country TEXT, title TEXT, link TEXT, pubDate TEXT, contentSnippet TEXT, createdAt TEXT)';
    const create = db.prepare(createSQL);
    create.run();

Drop a table:

    const dropSQL = 'DROP TABLE IF EXISTS news'
    const drop = db.prepare(dropSQL);
    drop.run();

Insert rows:

    const insertSQL = 'INSERT INTO news (country, title, link, pubDate, contentSnippet, createdAt) VALUES (@country, @title, @link, @pubDate, @contentSnippet, @createdAt)'
    const insert = db.prepare(insertSQL);
    for (let article of articles) {
        insert.run(article);
    }

Get rows:

    let items = db.prepare(`SELECT * FROM news`).all();

Transactions:

    const insertNews = db.transaction((news) => {
        const drop = db.prepare(dropSQL);
        drop.run();

        const create = db.prepare(createSQL);
        create.run();

        const insert = db.prepare(insertSQL);

        for (let article of news) {
            article.createdAt = (new Date()).toISOString();
            insert.run(article);
        }
    });

    let news = await getNews();
    insertNews(news);