MySQL and PostgreSQL as Native Backend Options

Database selection is part of deployment architecture

For an enterprise system, the database is not determined solely by a developer's preference. A company may have operational standards, an established database team, existing backup procedures, cloud-provider requirements, or managed-hosting constraints. Sapphire I.C.D.S. therefore supports two native backend options, MySQL and PostgreSQL, and does not force every installation to use one preselected database.

The public Roadmap records PostgreSQL support as a released improvement together with full selection between the two systems. At the architectural level, this means more than the presence of a second driver: module queries, schemas, and execution rules must exist for both dialects and retain comparable behaviour.

One contract above two drivers

The higher layers of Sapphire I.C.D.S. should not depend directly on the client library of a particular database. The data controller reads the selected backend type from configuration and obtains the corresponding native driver. The calling module continues to use a common operation set: row retrieval, data modification, scalar values, batch insertion, transactions, and schema operations.

This separation keeps the technology choice within the infrastructure layer. An articles or localisation module defines a domain request and works with a structured result rather than deciding how to connect to MySQL or PostgreSQL. At the same time, database differences are not hidden by magic; the corresponding driver and SQL dialect handle them.

Separate SQL templates instead of conditional strings

Sapphire I.C.D.S. keeps ordinary queries outside module code and separates them by dialect. One business operation can have MySQL and PostgreSQL templates with the same purpose and the same expected result shape. Before execution, the active driver selects the correct variant, applies the native parameter layer, and returns data in a common tabular carrier.

This is a more honest form of portability than pretending that all SQL is universal. MySQL and PostgreSQL differ in syntax, types, identifiers, returned generated values, and details of some expressions. Separate testable templates make those differences explicit without filling the business module with conditional branches.

What remains common to modules

• The domain operation. Listing, saving a record, or making a transactional change has one business purpose.
• Input data. The module passes structured parameters through the common environment.
• Result shape. The consumer receives a table or value with agreed fields.
• Transaction boundary. Operations that must complete as one unit use the shared transaction contract.
• Backend selection. Configuration and the controller perform it rather than conditions scattered across modules.

What backend selection does not do automatically

Having two native options does not mean that a running production database can be changed through one setting. Migration between database systems requires moving schema and data, checking sequences and identifiers, comparing types, and validating encodings, indexes, and transactional behaviour. It requires a backup, a downtime or synchronisation plan, a test run, and rollback criteria.

The platform also does not claim automatic replication between MySQL and PostgreSQL, built-in cross-database failover, or a single cluster composed of two different database products. These are infrastructure concerns and must be designed separately. Native backend selection means that Sapphire I.C.D.S. can operate with either option in a supported deployment, not that the two can be exchanged at any time without preparation.

How to choose between MySQL and PostgreSQL

A rational decision starts with the customer's environment. If an organisation already operates MySQL, has standard backup processes, and employs a capable team, moving to another database only for a new project may provide no benefit. PostgreSQL is similarly natural where it is the enterprise standard, available in managed infrastructure, and integrated into monitoring and recovery procedures.

The comparison should cover the complete lifecycle, not only the speed of one query: staff availability, upgrades, service cost, observability, extension policy, recovery procedures, and data requirements. Special queries from a specific module should be measured on representative volumes. A common contract does not remove differences between optimisers and indexes.

Parity requires development discipline

Every schema or query change must appear in both dialects when a module claims support for two backends. It is not enough to confirm that files exist. Validation must cover a clean installation, repeat application of safe migrations, read and write tests, and comparison of results. Particular care is needed where one database reports changed rows differently or retains the input carrier after an empty selection.

This discipline increases the validation effort but makes portability real. If a new module is implemented for only one backend, that limitation should be stated rather than hidden behind a platform-wide claim. A technical leader gets a clear readiness criterion: the same business operation should produce equivalent results on both supported options.

The operational outcome

For a business owner, two native backends provide the freedom to fit Sapphire I.C.D.S. into existing infrastructure and avoid an unnecessary change of skills. For architects, the common data layer prevents business modules from binding directly to a driver. For developers, separate SQL dialects make differences visible and testable.

The principal value is not the slogan of supporting two databases, but the responsibility boundary. The organisation selects and operates the backend, the controller connects the native driver, the module executes the domain operation, and tests establish parity. This model keeps the technology choice open without disguising the real cost of migration and ongoing operations.