database.js

const sqlite = require('better-sqlite3');
const path = require('path');
const fs = require('fs');
const debug = require('debug')('db');

debug('Running file');
let isInitialized;
function exportDb() {
	const DBFILE = path.join(__dirname, 'database.sqlite3');
	const db = sqlite(DBFILE);

	if (!isInitialized) {
		(async () => {
			debug(`Initializing database ${DBFILE}`);
			db.pragma('journal_mode = WAL');
			db.pragma('synchronous = FULL');
			debug('Reading init script');
			db.exec(fs.readFileSync('./db/init.sql').toString());
			debug('Done');

			// test only
			// const tables =
			// db.prepare("SELECT name FROM sqlite_master WHERE type = 'table'");
			// const columnName =
			// db.prepare('SELECT name FROM pragma_table_info((?))');
			// tables.all().forEach(({ name: table }) => {
			// 	columnName.all(table).forEach(({ name }) => {
			// 		debug(`${table}.${name}`);
			// 	});
			// });

			const withoutColumn = db.prepare(`
SELECT name
FROM sqlite_master AS t
WHERE type = 'table'
	AND NOT EXISTS (
		SELECT 1
		FROM pragma_table_info((t.name)) AS sub
		WHERE sub.name = ?
	)
			`);

			withoutColumn.all('date_created').forEach(({ name }) => {
				debug(name);
				db.prepare(`
ALTER TABLE [${name}]
	ADD COLUMN
	date_created DATETIME DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'));
				`).run();
			});

			withoutColumn.all('date_updated').forEach(({ name }) => {
				debug(name);
				db.prepare(`
ALTER TABLE [${name}]
	ADD COLUMN
	date_updated DATETIME DEFAULT (strftime('%Y-%m-%d %H:%M:%f', 'now'));
				`).run();
				db.prepare(`
CREATE TRIGGER update_${name}
AFTER UPDATE ON ${name}
BEGIN
	UPDATE ${name}
	SET date_updated = strftime('%Y-%m-%d %H:%M:%f', 'now')
	WHERE NEW.id = id;
END;
				`).run();
			});

			debug('Reading seed script');
			db.exec(fs.readFileSync('./db/seed.sql').toString());
			debug('Done');

			isInitialized = true;
		})();
	}
	return db;
}

module.exports = exportDb();

init.sql

CREATE TABLE IF NOT EXISTS Types (
	id INTEGER PRIMARY KEY,
	name CHAR NOT NULL,
	seats INTEGER NOT NULL,
	price INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS Vans (
	id INTEGER PRIMARY KEY,
	registration CHAR NOT NULL UNIQUE,
	type_id INTEGER NOT NULL,

	FOREIGN KEY (type_id) REFERENCES Types(id)
);

CREATE TABLE IF NOT EXISTS Clients (
	id INTEGER PRIMARY KEY,
	login CHAR NOT NULL UNIQUE,
	isAdmin INT DEFAULT(0),
	password CHAR DEFAULT(''),
	name CHAR,
	phone CHAR
);

CREATE TABLE IF NOT EXISTS Reservations (
	id INTEGER PRIMARY KEY,
	client_id INTEGER NOT NULL,
	van_id INTEGER NOT NULL,
	date_from DATETIME NOT NULL,
	date_to DATETIME NOT NULL,
	is_active INTEGER NOT NULL DEFAULT(0),

	FOREIGN KEY (van_id) REFERENCES Vans(id),
	FOREIGN KEY (client_id) REFERENCES Clients(id)
);

/*
SELECT
	m.name AS table_name,
	-- p.cid AS col_id,
	p.name AS col_name,
	p.type AS col_type,
	p.pk AS col_is_pk,
	p.dflt_value AS col_default_val,
	p.[notnull] AS col_is_not_null
FROM sqlite_master m
LEFT OUTER JOIN pragma_table_info((m.name)) p
ON m.name <> p.name
WHERE m.type = 'table'
ORDER BY table_name, col_id;
*/

-- SELECT t.name AS 'Table' FROM sqlite_master AS t WHERE t.type = 'table' AND NOT EXISTS(SELECT 1 FROM pragma_table_info((t.name)) AS sub WHERE sub.name = 'id');

seed.sql

INSERT OR IGNORE
INTO Clients
	(id, login, isAdmin, password, name, phone)
VALUES
	(1, 'admin', 1, 'admin', 'admin', '0'),
	(2, 'bill', 0, 'bill', 'Bill Nye', '0');