Using SQLite Locally and MySQL in CI for Publication State

Why this matters

Publishing is not a fire-and-forget HTTP request. A practical publisher needs
to remember which source hash it sent, which remote ID and URL came back, and
which rendered images were already uploaded. That state prevents duplicate
posts and unnecessary asset uploads.

For a command-line tool, SQLite is a great local default: it requires no
service and keeps state beside the project. CI or a shared runner may need
MySQL instead. The tempting design is to put both behind one TypeScript
interface and assume the backends are interchangeable.

The interface is necessary, but it is not proof of parity. SQL dialects,
timestamp types, JSON decoding, and concurrency semantics still cross the
boundary. I tested the local path, compared both implementations line by line,
and found one MySQL-specific risk that the existing unit test cannot see.

What I built or tested

The repository already defines a small DatabaseProvider contract:

interface DatabaseProvider {
  initialize(): Promise<void>;
  findPublishRecord(slug: string): Promise<PublishRecord | null>;
  savePublishRecord(record: PublishRecord): Promise<void>;
  findImage(provider: string, objectKey: string): Promise<ImageRecord | null>;
  saveImage(record: ImageRecord): Promise<void>;
  close(): Promise<void>;
}

Both implementations store two kinds of idempotency state:

• a publication record keyed by article slug; and
• an image record keyed by storage provider plus object key.

I exercised SQLite with repeated writes and a reopen, checked provider
selection and configuration failure, ran the focused test, and inspected the
MySQL SQL and installed driver. I did not run a MySQL server, so this is not
an end-to-end MySQL compatibility claim.

Setup

The project runs on Node.js 22 and TypeScript. Its configuration defaults to:

DATABASE_DRIVER=sqlite
DATABASE_SQLITE_PATH=.publish/blog-publisher.db

Selecting MySQL is explicit:

DATABASE_DRIVER=mysql
DATABASE_HOST=mysql
DATABASE_PORT=3306
DATABASE_NAME=publisher
DATABASE_USERNAME=publisher
DATABASE_PASSWORD=...

The factory rejects the MySQL choice when host, database name, or username is
missing. That early failure matters: silently falling back to a local file in
CI would split state between runs.

Step-by-step walkthrough

1. Keep the logical keys identical

SQLite creates a publish_records table with slug as its primary key and an
images table with a composite (provider, object_key) primary key. MySQL
uses the same logical keys with bounded VARCHAR columns.

That gives both implementations the same answer to “is this the record I
already know?” even though their physical types differ.

2. Express an upsert in each dialect

SQLite writes a publication like this:

INSERT INTO publish_records
  (slug, source_hash, platforms_json, updated_at)
VALUES (?, ?, ?, ?)
ON CONFLICT(slug) DO UPDATE SET
  source_hash = excluded.source_hash,
  platforms_json = excluded.platforms_json,
  updated_at = excluded.updated_at

MySQL uses its corresponding form:

INSERT INTO publish_records
  (slug, source_hash, platforms_json, updated_at)
VALUES (?, ?, ?, UTC_TIMESTAMP(3))
ON DUPLICATE KEY UPDATE
  source_hash = VALUES(source_hash),
  platforms_json = VALUES(platforms_json),
  updated_at = VALUES(updated_at)

The syntax differs, but the intended invariant is the same: a second write for
the same logical key updates one row instead of creating another. The
MySQL upsert documentation
is the source of truth for that server-side behavior.

3. Normalize records at the provider boundary

The rest of the publisher should not know whether SQLite stores platform state
as text or MySQL stores it in a native JSON column. Both providers serialize a
platforms object on write and should return the same JavaScript shape on
read.

One application contract, two storage adapters, one logical state model.

4. Test replacement and persistence, not just insertion

My experiment wrote one slug first as a draft with remote ID 42, then as
published with ID 99. It also wrote the same image key twice with different
hashes and URLs. After closing and reopening SQLite, I queried the records and
counted the rows.

The result contained one publication and one image—the updated values survived
the reopen. The database also reported wal journal mode. The official
SQLite WAL documentation explains why WAL
can improve reader/writer concurrency, while still allowing only one writer at
a time. It also documents a crucial boundary: WAL relies on shared memory and
is not a network-filesystem design.

What went wrong

The schemas look parallel, but the MySQL JSON read boundary is not.

The provider declares platforms_json as a string and does this:

platforms: JSON.parse(row.platforms_json)

However, the installed mysql2 3.22.6 driver defaults jsonStrings to
false. Its result parser decodes a MySQL JSON column with JSON.parse before
returning the row. The pool configuration does not override that default.

That means the provider may receive an object and attempt to parse it again.
In plain JavaScript, JSON.parse({ devto: { status: "published" } }) throws a
SyntaxError because the object is coerced to "[object Object]".

This is a source-grounded compatibility risk, not a live-server reproduction.
The existing focused test only instantiates SqliteDatabaseProvider, so it
cannot catch the mismatch. MySQL's
native JSON documentation
confirms that the column validates and stores JSON documents; how a Node.js
driver returns that value remains a client-boundary concern.

Fix or mitigation

There are two defensible fixes:

1. Set jsonStrings: true in the MySQL pool and keep the provider's explicit JSON.parse.
2. Let mysql2 decode JSON, type the returned field as the platform-state object, and normalize string-or-object input in one helper if mixed drivers must be supported.

I prefer the second approach when the application controls its driver version:
the provider's runtime type matches the driver's default behavior, and parsing
is not duplicated. The first approach is smaller and preserves the existing
provider code. Either choice is incomplete without a MySQL integration test.

That test should start a disposable MySQL instance, initialize the schema,
write each logical key twice, reconnect, and assert:

• exactly one row per key;
• the second hash, status, remote ID, and URL are returned;
• the platform state has the same JavaScript shape as SQLite; and
• incomplete configuration fails before a connection attempt.

Trade-offs

SQLite keeps local development delightfully small, but the database file and
its WAL-related files are persistent state and should stay on one host. It is
not a shortcut to a shared network database.

MySQL supports a service-oriented CI topology and native JSON, but adds
credentials, lifecycle management, migrations, and a real network failure
surface. It also makes driver behavior part of the persistence contract.

There are subtler parity differences too:

• SQLite writes ISO-8601 timestamp text from the Node.js process; MySQL writes DATETIME(3) using UTC_TIMESTAMP(3).
• SQLite's text columns do not impose the same length limits as MySQL's VARCHAR keys.
• Matching method signatures do not guarantee matching transaction, contention, collation, or error semantics.

The useful abstraction is therefore a promise backed by contract tests, not a
claim that both databases are identical.

How I verified it

The local experiment produced:

{
  "journalMode": "wal",
  "publishCount": 1,
  "imageCount": 1,
  "reopenedPublishHash": "hash-v2",
  "reopenedPublishStatus": "published",
  "reopenedPublishRemoteId": "99",
  "reopenedImageHash": "image-v2",
  "defaultDatabaseDriver": "sqlite",
  "defaultProviderName": "SqliteDatabaseProvider",
  "missingMysqlConfigRejected": true
}

The focused SQLite Vitest test passed. I also ran the repository typecheck and
full test suite after drafting the article and rendering its diagram.

For MySQL, verification was deliberately narrower: I compared the provider
code, official server documentation, and the installed driver's configuration
and parser source. A live MySQL round trip remains the next required gate
before calling the two implementations operationally equivalent.

Conclusion

A provider interface can keep publication logic clean, and SQLite can give
local runs durable idempotency state with almost no setup. Moving the same
contract to MySQL requires more than translating ON CONFLICT into ON
DUPLICATE KEY UPDATE.

Test the observable contract across both adapters: replacement, reopen,
decoded value shapes, and failure behavior. The JSON double-parse risk here is
exactly the kind of boundary bug that strong TypeScript types can hide when a
driver's runtime value disagrees with a handwritten row interface.

AI assistance disclosure

I used an AI coding assistant to trace the two provider implementations,
prepare the isolated SQLite experiment, compare the installed driver's JSON
path, and edit the draft. I reviewed the cited source lines, executed the
reported commands, inspected the rendered diagram, and kept the unexecuted
MySQL path explicitly labeled as a limitation.