Database portability and storage contract

This document defines the storage behavior that database writers should share. It describes the current contract; it does not make the synchronous query, export, or GTFS-Realtime APIs database-independent.

Note: PostgreSQL and MySQL support is new. The interface, configuration options, and functions related to it may change in a future release.

Portable source columns

Every writer stores the same file-backed tables and source columns declared by the GTFS schema manifest. Values are normalized before they reach a writer:

Manifest kind	Portable storage intent
id, text	Unbounded text where supported
Numeric enumeration, integer, date	Integer
Text enumeration	Text
real	Double-precision floating point
time	Normalized HH:mm:ss text; hours may exceed 23
json	Serialized JSON text, not a database-native JSON value

GTFS dates retain their existing YYYYMMDD integer representation. Empty values are stored as NULL unless the manifest supplies a defaultValue. Identifier feed prefixes are applied before writing.

The current floating-point representation of fares and other decimals can lose decimal precision. Changing it to fixed-precision decimal storage would affect result types and is deferred to the next major release.

node-GTFS convenience and internal columns

When includeNodeGtfsExtras is enabled, each GTFS time column also receives an integer seconds-since-midnight column. These are node-GTFS conveniences rather than source GTFS columns.

Managed MySQL schemas contain a nullable _node_gtfs_primary_key SHA-256 value for tables with declared keys. MySQL cannot portably index arbitrary-length GTFS text identifiers as a native key. The hash preserves duplicate handling without imposing a length limit; it is an internal column and should not be treated as GTFS data.

Constraints and comparisons

• Required values become NOT NULL columns in managed schemas.
• Manifest numeric bounds become database checks where supported.
• References remain metadata and are not installed as database foreign keys.
• Nullable composite keys retain the databases' normal NULL-distinct unique behavior.
• SQLite caseInsensitiveComparison uses COLLATE NOCASE. PostgreSQL and MySQL use the configured database/column collation, so comparisons are not guaranteed to be identical across backends.

Import lifecycle

manageSchema: true is destructive: file-backed tables are dropped and recreated before import, then indexes are created afterward. Each normalized batch is written in a transaction. A failure in a later batch can therefore leave earlier successful batches committed, matching existing SQLite behavior.

manageSchema: false leaves schema ownership with the application. All source columns must exist. Convenience columns are written only when includeNodeGtfsExtras: true is also provided.

The caller owns a Kysely instance passed to importGtfsToKysely(); node-GTFS never destroys it.

Conformance testing

CI imports the same Unicode and long-identifier fixture into real PostgreSQL and MySQL services. It verifies normalized values, managed schema compatibility, generated time columns, indexes, and dialect-specific duplicate/key behavior.

The integration test can also be run locally when these variables point to disposable databases:

pnpm build
GTFS_TEST_POSTGRES_URL=postgresql://postgres:postgres@localhost:5432/gtfs \
GTFS_TEST_MYSQL_URL=mysql://root:root@localhost:3306/gtfs \
node --test src/test/database-integration.test.ts

The test drops and recreates GTFS tables and must never target a database that contains data you need to preserve.