Dialect-package tests: DB-backed tests next to the dialect, and a reusable conformance suite for third-party dialects #18451

Issue Creation Checklist

• I understand that my issue will be automatically closed if I don't fill in the requested information
• I have read the contribution guidelines

Feature Description

Describe the feature you'd like to see implemented

Since dialects moved into their own packages (#13722), tests live in three places:

1. Core unit tests (packages/core/test/unit). Most run on every dialect and use expectsql(…, { default, oracle, 'mysql mariadb', … }) for per-dialect SQL.
2. Core integration tests (packages/core/test/integration). The shared behavioural suite that runs against a real database for each dialect. Dialect-specific DB tests also live here, under test/integration/dialects/<dialect>/.
3. Dialect package unit tests (packages/<dialect>/src/*.test.ts, run with test-unit). These are new and unit-only so far: mariadb, mysql, postgres, sqlite3, mssql and db2 have them; oracle and snowflake don't yet.

Dialect-specific unit tests are moving into the dialect packages. There's no equivalent for DB-backed tests yet, so dialect-specific integration tests (for example Oracle-only vector index options from #18227) still have to go in core, next to the shared suite.

There's also a bigger question behind this. The core test setup hard-codes the built-in dialects (packages/core/test/config/config.ts imports every @sequelize/<dialect> package), and the per-dialect expectations are keyed by built-in dialect names. So a third-party dialect, like the Firebird dialect proposed in #18269 / #18249, can't run the shared suite against itself. It has no way to prove it behaves like a Sequelize dialect.

This issue is to discuss:

1. DB-backed tests in dialect packages. How packages/<dialect>/src (or a test/ dir inside the package) can hold integration tests that need a running database:
   • the CI wiring: which job starts which container, and oldest/latest versions
   • sharing the core test support helpers (sequelize instance setup, database reset, version and extension skips)
   • keeping local runs simple (yarn test-integration per package)
2. A reusable conformance suite. Could the shared core integration suite (and possibly the expectsql-based unit suite) become something a dialect package runs against itself, for example by exporting the suite plus a way to register the dialect and its expectations, instead of core knowing every dialect?
   • Built-in dialects would use it the same way third-party dialects do.
   • Expectations would move from "keyed by dialect name in core" to "provided by the dialect", possibly with capability-driven defaults based on dialect.supports.
3. Support policy for third-party dialect adapters. What "supported" means for a dialect maintained outside this repository:
   • Does passing the conformance suite (at a given core version) count as compatible?
   • How do we version the public dialect API (AbstractDialect, query generator and query interface hooks, supports), so adapters know when they break?
   • Do we list or link compatible third-party dialects in the docs?
   • How do capability flags (see also Version- and runtime-aware capability flags (dialect.supports) #18449 for version- and runtime-aware supports) and option validation (Validate options at runtime: reject unsupported and unknown options, stricter typings #18450) interact with dialects we don't maintain?

This is intentionally broad. It's meant as the place to agree on direction before splitting it into concrete tasks.

Describe why you would like this feature to be added to Sequelize

• Keeping dialect code and tests together. Dialect-specific behaviour, including behaviour that needs a real database, is easier to own, review and change when its tests live in the dialect package.
• Making dialects portable. A conformance suite is what makes "dialects as packages" pay off for third parties. Without it, every external dialect has to guess the expected behaviour or copy core tests, and we have no objective way to say whether an adapter works.
• Features added across dialects. New cross-dialect features (vectors, and the next ones) need a single behavioural suite that every dialect, built-in or not, runs automatically.

Is this feature dialect-specific?

• No. This feature is relevant to Sequelize as a whole.
• Yes. This feature only applies to the following dialect(s):

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I will need guidance.
• No, I don't have the time, but my company or I are supporting Sequelize through donations on OpenCollective.
• No, I don't have the time, and I understand that I will need to wait until someone from the community or maintainers is interested in implementing my feature.

Indicate your interest in the addition of this feature by adding the 👍 reaction. Comments such as "+1" will be removed.