Tweaking SQLite: Options for Pragmas and File Control

Here are some things to keep in mind:

• Most modifications with PRAGMA or file control only affect the current connection, not globally for SQLite.
• Changing defaults should only be done if you have a specific performance need and understand the potential drawbacks.

import sqlite3

conn = sqlite3.connect("mydatabase.db")

# Set the page size to 4096 bytes (default is 1024)
conn.execute("PRAGMA page_size = 4096")

# Get the current cache size
cursor = conn.cursor()
cursor.execute("PRAGMA cache_size")
cache_size = cursor.fetchone()[0]
print(f"Current cache size: {cache_size} pages")

conn.close()

File Control:

#include <sqlite3.h>

int main() {
  sqlite3 *db;
  int rc = sqlite3_open("mydatabase.db", &db);

  // Set the maximum memory-mapped file size (similar to PRAGMA mmap_size)
  rc = sqlite3_fcntl(db, SQLITE_FCNTL_MMAP_SIZE, 1024 * 1024); // 1MB

  if (rc != SQLITE_OK) {
    // handle error
  }

  // ... your database operations ...

  sqlite3_close(db);
  return 0;
}

Please note:

• These examples showcase basic usage. Always refer to the official documentation for comprehensive options and usage guidelines.

Here's a breakdown of these approaches:

Command-Line Flags:

The sqlite3 tool provides a few flags that can be helpful:

• -header: Display column headers in query results (similar to .headers on pragma)
• -mode: Change output mode (e.g., -mode column for columnar output)
• -readonly: Open the database in read-only mode

These flags offer a way to customize how you interact with SQLite without permanently altering defaults.

Custom Configuration File (Advanced):

This method involves creating a file (e.g., .sqliterc) in your home directory. This file can contain pragmas you want to execute automatically whenever you run sqlite3. Here's an example:

.headers on
.mode column

Then, modify your shell configuration (e.g.,bashrc) to point sqlite3 to this file on startup:

sqlite3(){
  sqlite3 -init .sqliterc "$@"
}