Monday | 15 APR 2024
[ previous ]
[ next ]

better-sqlite3 Cheatsheet

Date: 2023-01-14
Tags:  cheatsheet

I like using raw SQL instead of an ORM now.


npm install better-sqlite3


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

Drop a table:

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

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

Get rows:

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


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

        const create = db.prepare(createSQL);;

        const insert = db.prepare(insertSQL);

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

    let news = await getNews();