Benchmarking idempotent `CREATE IF NOT EXISTS` sqlite statements

benchmark.js

import fs from 'fs'
import sqlite3 from 'sqlite3'
import { open } from 'sqlite'

const filename = './database.db'

if (fs.existsSync(filename)) {
  fs.unlinkSync(filename)
}

const TABLE_COUNT = 100
const RECORD_COUNT_PER_TABLE = 100000

const now = () => new Date().valueOf()

const runMigration = (db) => {
  const statements = []

  for (let i = 0; i < TABLE_COUNT; i += 1) {
    statements.push(`
      create table if not exists test_${i} (
        id integer primary key autoincrement,
        name text
      );
    `)
  }

  return db.exec(statements.join('\n'))
}

const addData = async (db) => {
  for (let i = 0; i < TABLE_COUNT; i += 1) {
    const records = new Array(RECORD_COUNT_PER_TABLE).fill(null).map((_, i) => `('foobar ${i}')`)
    await db.run(`insert into test_${i} (name) values ${records.join(', ')}`)
  }
}

const getDuration = async (promise) => {
  const startedAt = now()
  await promise
  return now() - startedAt
}

;(async () => {
  const db = await open({ filename, driver: sqlite3.Database })

  console.log(`Running first migration to create ${TABLE_COUNT} ${TABLE_COUNT === 1 ? 'table' : 'tables'}…`)
  const firstMigrationDuration = await getDuration(runMigration(db))
  console.log(`Finished in ${firstMigrationDuration}ms`)
  console.log(`Adding ${RECORD_COUNT_PER_TABLE} ${RECORD_COUNT_PER_TABLE === 1 ? 'record' : 'records'} to each table…`)
  const addDataDuration = await getDuration(addData(db))
  console.log(`Finished in ${addDataDuration}ms`)
  console.log('Running second migration, no change to schema…')
  const secondMigrationDuration = await getDuration(runMigration(db))
  console.log(`Finished in ${secondMigrationDuration}ms`)
})()

result.txt

Running first migration to create 100 tables…
Finished in 88ms
Adding 100000 records to each table…
Finished in 17144ms
Running second migration, no change to schema…
Finished in 1ms