Pragmas I use for a production-ready SQLite database

Jun 18, 2020·1 min read

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;
PRAGMA foreign_keys = ON;
PRAGMA mmap_size = 268435456;

This is how I set them in my AdonisJS Apps

config/database.ts

import app from '@adonisjs/core/services/app'
import { defineConfig } from '@adonisjs/lucid'

import env from '#start/env'

const dbConfig = defineConfig({
  connection: 'sqlite',
  connections: {
    sqlite: {
      client: 'better-sqlite3',
      connection: {
        filename: app.makePath(env.get('DB_FILENAME')),
      },
      pool: {
        afterCreate(conn, done) {
          conn.pragma('busy_timeout = 5000;')
          conn.pragma('cache_size = -20000;')
          conn.pragma('foreign_keys = ON;')
          conn.pragma('incremental_vacuum;')
          conn.pragma('mmap_size = 2147483648;')
          conn.pragma('temp_store = MEMORY;')
          conn.pragma('synchronous = NORMAL;')

          done()
        },
      },
      useNullAsDefault: true,
      migrations: {
        naturalSort: true,
        paths: ['database/migrations'],
      },
    },
  },
})

export default dbConfig

database/migrations/1735154264787_create_database_optimization_settings_table.ts

import Database from 'better-sqlite3'
import app from '@adonisjs/core/services/app'
import { BaseSchema } from '@adonisjs/lucid/schema'

import env from '#start/env'

export default class extends BaseSchema {
  async up() {
    const db = new Database(app.makePath(env.get('DB_FILENAME')))

    db.pragma('auto_vacuum = incremental;')
    db.pragma('journal_mode = WAL;')
    db.pragma('page_size = 32768;')

    db.close()
  }
}