Clarify afterCreate Usage for SQLite Dialects

[mercmobily]

Users are confused about the differences in afterCreate pool callback implementation between sqlite3 and better-sqlite3 drivers. Issue 4971 shows users copying sqlite3 examples that crash with better-sqlite3 due to different APIs.

Current Documentation Gaps

1. No Clear Differentiation

The documentation shows a generic PostgreSQL example but doesn't clarify the differences between SQLite drivers:

• sqlite3 uses callback-style APIs (.run(), .exec() with callbacks)
• better-sqlite3 uses synchronous APIs (.pragma(), .prepare(), no callbacks)

2. Missing Common Use Cases

No examples for the most common SQLite configurations:

• Enabling foreign keys
• Setting journal mode
• Configuring busy timeout
• Setting cache size

3. Incorrect Test Examples

Even the test files show inconsistent patterns:

• /test/knexfile.js:31 uses connection.run('PRAGMA foreign_keys = ON', callback) for sqlite3
• /test/knexfile.js:41 uses connection.prepare('PRAGMA foreign_keys = ON').run() for better-sqlite3

Documentation Changes Required

1. Update Main Pool Documentation (docs/src/guide/index.md)

Add a new section after the current afterCreate example (line 387):

#### Driver-Specific afterCreate Examples

##### PostgreSQL / MySQL
```js
const knex = require('knex')({
  client: 'pg', // or 'mysql', 'mysql2'
  connection: {...},
  pool: {
    afterCreate: function (conn, done) {
      // Callback-based API
      conn.query('SET timezone="UTC";', function (err) {
        if (err) {
          // Connection will be discarded
          done(err, conn);
        } else {
          // Connection is ready
          done(null, conn);
        }
      });
    }
  }
});

SQLite3 (callback-based)

const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: './mydb.sqlite'
  },
  pool: {
    afterCreate: function (conn, done) {
      // sqlite3 uses callback-style methods
      conn.run('PRAGMA foreign_keys = ON', done);
    }
  }
});

Better-SQLite3 (synchronous)

const knex = require('knex')({
  client: 'better-sqlite3',
  connection: {
    filename: './mydb.sqlite'
  },
  pool: {
    afterCreate: function (conn, done) {
      // better-sqlite3 uses synchronous methods
      try {
        conn.pragma('foreign_keys = ON');
        // For other PRAGMA statements:
        // conn.pragma('journal_mode = WAL');
        // conn.pragma('busy_timeout = 5000');
        done(null, conn);
      } catch (err) {
        done(err, conn);
      }
    }
  }
});

Common SQLite Configurations

// For sqlite3
pool: {
  afterCreate: function (conn, done) {
    conn.serialize(function() {
      conn.run('PRAGMA foreign_keys = ON');
      conn.run('PRAGMA journal_mode = WAL');
      conn.run('PRAGMA synchronous = NORMAL');
      conn.run('PRAGMA cache_size = -64000');
      conn.run('PRAGMA busy_timeout = 5000', done);
    });
  }
}

// For better-sqlite3
pool: {
  afterCreate: function (conn, done) {
    try {
      conn.pragma('foreign_keys = ON');
      conn.pragma('journal_mode = WAL');
      conn.pragma('synchronous = NORMAL');
      conn.pragma('cache_size = -64000');
      conn.pragma('busy_timeout = 5000');
      done(null, conn);
    } catch (err) {
      done(err, conn);
    }
  }
}

2. Add Warning Box

Add after the driver examples:

:::warning SQLite Driver Differences
**Important:** `sqlite3` and `better-sqlite3` have different APIs:
- **sqlite3**: Uses Node.js callback-style async methods (`.run()`, `.exec()`, `.all()`)
- **better-sqlite3**: Uses synchronous methods (`.pragma()`, `.prepare()`, `.exec()`)

Copying `afterCreate` code between these drivers will cause errors. Always use the appropriate API for your chosen driver.
:::

3. Update SQLite Section

In the SQLite configuration section (around line 90-160), add:

#### Choosing Between sqlite3 and better-sqlite3

| Feature | sqlite3 | better-sqlite3 |
|---------|---------|----------------|
| API Style | Asynchronous (callbacks/promises) | Synchronous |
| Performance | Good for concurrent operations | Faster for sequential operations |
| afterCreate | Uses callbacks | Synchronous, but still needs done() callback |
| Common Methods | `.run()`, `.get()`, `.all()` | `.prepare()`, `.pragma()` |
| Foreign Keys | `conn.run('PRAGMA foreign_keys = ON', cb)` | `conn.pragma('foreign_keys = ON')` |

4. Update FAQ/Recipes (docs/src/faq/recipes.md)

Update the SQLCipher example (line 76) to show both variants:

#### SQLCipher with sqlite3
```js
const knex = require('knex')({
  client: 'sqlite3',
  connection: {
    filename: 'file:foobar.db?cipher=sqlcipher'
  },
  pool: {
    afterCreate: function (conn, done) {
      conn.run("PRAGMA KEY = 'secret'", done);
    }
  }
});

SQLCipher with better-sqlite3

const knex = require('knex')({
  client: 'better-sqlite3',
  connection: {
    filename: 'file:foobar.db?cipher=sqlcipher'
  },
  pool: {
    afterCreate: function (conn, done) {
      try {
        conn.pragma("KEY = 'secret'");
        done(null, conn);
      } catch (err) {
        done(err, conn);
      }
    }
  }
});

5. Add Migration Guide

Create a new section in the documentation:

## Migrating from sqlite3 to better-sqlite3

If you're switching from `sqlite3` to `better-sqlite3`, update your `afterCreate`:

**Before (sqlite3):**
```js
afterCreate: function (conn, done) {
  conn.run('PRAGMA foreign_keys = ON', done);
}

After (better-sqlite3):

afterCreate: function (conn, done) {
  try {
    conn.pragma('foreign_keys = ON');
    done(null, conn);
  } catch (err) {
    done(err, conn);
  }
}

Additional Recommendations

1. Add TypeScript definitions that show the different connection types for each driver
2. Create a troubleshooting section for common errors like "conn.run is not a function"
3. Add links to driver documentation:
   • sqlite3 API
   • better-sqlite3 API

Success Criteria

After implementing these changes:

1. Users should immediately understand which code to use for their chosen SQLite driver
2. Copy-paste errors between drivers should be eliminated
3. Common PRAGMA configurations should be easily discoverable
4. The error "conn.run is not a function" should lead users directly to the solution