Database Abstraction

The problem

The backend runs on two completely different databases:

• Local / cPanel: MySQL 8 (via mysql2 connection pool)
• Cloudflare Workers: Cloudflare D1 (SQLite, via Workers bindings)

Rather than having two separate codebases, the project uses a single abstraction layer in backend/config/db.js that routes all database calls to the correct engine at runtime.

How it works

The db object exposes three methods that mirror the classic sqlite3 Node package API:

db.run(sql, params, callback)  // INSERT, UPDATE, DELETE
db.get(sql, params, callback)  // SELECT — returns first row
db.all(sql, params, callback)  // SELECT — returns all rows

At runtime, each method checks globalThis.D1_DB:

• If present → it’s a Cloudflare Worker; use D1’s promise-based API
• If absent → use MySQL pool

// Simplified example from config/db.js
db.get = function(sql, params, callback) {
  if (globalThis.D1_DB) {
    globalThis.D1_DB.prepare(sql).bind(...params).first()
      .then(row => callback(null, row))
      .catch(err => callback(err, null));
    return;
  }
  pool.query(sql, params, (err, results) => {
    callback(err, results?.[0]);
  });
};

D1_DB is injected as a global by the Worker runtime (bound in wrangler.toml as DB). MySQL pool is initialized only if not in a Worker environment.

Environment detection

// WebSocketPair is a global unique to Cloudflare Workers
const isWorker = typeof WebSocketPair !== 'undefined';

This is used to skip MySQL pool creation in Workers and to skip fs operations in utils/storage.js.

Promise wrappers in routes

The callback-based db API works for simple cases, but for complex logic (like the ticket booking flow), routes define local promise wrappers:

// From routes/tickets.js
const all = (query, params) => new Promise((resolve, reject) => {
  db.all(query, params, (err, rows) => err ? reject(err) : resolve(rows));
});
const run = (query, params) => new Promise((resolve, reject) => {
  db.run(query, params, function(err) { err ? reject(err) : resolve(this); });
});

Recommendation: These wrappers are duplicated in several route files. They should be extracted to a shared utils/db-helpers.js and imported where needed. This is currently on the tech debt list.

Schema migrations

There is no dedicated migration tool (like Flyway or Knex migrations). Instead, the app auto-adds missing columns on startup using ALTER TABLE ... ADD COLUMN IF NOT EXISTS guards:

// Example from routes/auth.js
db.run(`ALTER TABLE users ADD COLUMN avatar TEXT DEFAULT NULL`, [], (err) => {
  if (err && !err.message.includes('duplicate column')) {
    // real error
  }
});

This runs on every startup via initializeDatabase() in index.js, which is called by a middleware on the first request.

Known issue with this approach

This pattern is fragile for several reasons:

1. There is no migration history — you can’t tell which schema version any DB instance is at
2. Column renames or type changes are not handled
3. ALTER TABLE inside a request’s middleware chain can fail silently in high-concurrency scenarios

Better alternative: Use a proper migration tool. Drizzle Kit supports both SQLite (D1) and MySQL and generates SQL migration files that can be version-controlled and applied deterministically.

SQL dialect differences to watch for

Feature	MySQL	D1/SQLite	Safe alternative
Auto-increment PK	AUTO_INCREMENT	AUTOINCREMENT	Write migrations per dialect
Ignore duplicate insert	INSERT IGNORE	Not supported	INSERT OR IGNORE (SQLite)
Case-insensitive compare	LIKE is case-insensitive by default	Requires COLLATE NOCASE	Always add COLLATE NOCASE explicitly
Current timestamp	NOW()	CURRENT_TIMESTAMP	Use CURRENT_TIMESTAMP (both support it)

Always test any new query against both MySQL locally and D1 in the staging Worker environment.

Database schema reference

The schema is defined in backend/shanvi_travels.sql (MySQL) and backend/shanvi_sqlite.sql (D1/SQLite). These two files should be kept in sync manually — another argument for migrating to a single migration tool.

Core tables

Table	Purpose
users	All users (USER, AGENT, ADMIN) + KYC data + contact info
agent_types	Configurable agent type definitions (permissions, systems)
submissions	Thai visa submissions
submission_participants	Additional passengers per submission
fd_tickets	Fixed-departure flight tickets
ticket_bookings	User bookings against fd_tickets
airports	Active airport list (IATA code, city, country)
available_dates	(Legacy — not actively used by search logic)
settings	Key-value app settings (visa_fee, service_fee, etc.)