Writing Database-Agnostic SQL: Portability in Practice

1. What database-agnostic SQL means and where its limits lie

Database-agnostic SQL is SQL that behaves consistently across different database systems without needing a separate version maintained for each one. The SQL standard, formally defined as ANSI SQL or ISO/IEC 9075, lays out the fundamentals, but not a single widely used database system implements the standard completely without its own extensions. MySQL, PostgreSQL and SQL Server all follow the same basic model of tables, JOINs and transactions, but diverge in details that, when SQL is naively carried over between systems, lead to errors that often surface only late in the deployment process.

A realistic expectation matters here: writing database-agnostic SQL does not mean every line of code works identically on every system, it means the differences are known, documented, and isolated at clearly defined points in the code. Full portability for complex applications is rarely achievable in practice and usually not even the actual goal, because it would force giving up powerful, system-specific features.

This article covers the most common pitfalls that actually occur in practice between MySQL, PostgreSQL and SQL Server: quoting rules, paging syntax, auto-increment mechanisms, data types and string operations. It ends with an honest assessment of when the effort for database-agnostic SQL pays off and when it creates unnecessary complexity without real benefit.

2. Quoting: identifiers, strings and their differences

The most fundamental, and at the same time most frequently overlooked, portability trap concerns quoting identifiers. The SQL standard prescribes double quotes for identifiers such as table or column names, for example "order". PostgreSQL and SQL Server largely follow this standard rule, whereas MySQL uses backticks by default, `order`, and interprets double quotes differently from standard behavior in ANSI_QUOTES mode. SQL Server additionally offers square brackets, [order], as a proprietary alternative to double quotes.

For string literals, all three systems uniformly use single quotes, 'text', which is one of the few constants across the dialects. Things get tricky when handling reserved words as identifiers: a column name like order or user collides with SQL keywords and must be quoted, with the quoting syntax differing depending on the target system. Database-agnostic SQL avoids this problem most elegantly by never using reserved words as identifiers in the first place, instead of handling the quoting differences across systems.

-- Identifier quoting differs significantly between systems

-- ANSI SQL standard / PostgreSQL / SQL Server (default mode)
SELECT "order_id", "customer_name" FROM "orders" WHERE "order_id" = 1;

-- MySQL default: backticks instead of double quotes
SELECT `order_id`, `customer_name` FROM `orders` WHERE `order_id` = 1;

-- SQL Server proprietary alternative: square brackets
SELECT [order_id], [customer_name] FROM [orders] WHERE [order_id] = 1;

-- String literals: single quotes are consistent across all three systems
SELECT * FROM orders WHERE status = 'shipped';

-- Best practice: avoid reserved words as identifiers entirely
-- instead of: SELECT "order" FROM "user";
-- use:        SELECT order_status FROM app_user;

3. Paging: LIMIT/OFFSET vs. TOP vs. FETCH FIRST

Hardly any portability trap is underestimated as often as paging. MySQL and PostgreSQL use the familiar syntax LIMIT n OFFSET m to return a limited number of rows starting at a certain position. SQL Server did not have this syntax at all for a long time and instead used TOP n, which does not support an offset and therefore has to be combined with ORDER BY and OFFSET ... FETCH NEXT for real paging, a syntax that has only been available since SQL Server 2012.

The ANSI SQL standard actually defines FETCH FIRST n ROWS ONLY combined with OFFSET n ROWS as the portable solution, and modern versions of all three systems support this syntax by now. In practice, however, one frequently encounters older codebases that use the respective system-specific, older syntax because it was historically available first. Anyone writing new code today should consistently prefer the ANSI standard syntax, because it works on all three systems without modification and noticeably eases a future switch to another system.

-- Paging syntax differs significantly by system and by SQL version

-- MySQL / PostgreSQL classic syntax
SELECT id, name FROM products ORDER BY name LIMIT 20 OFFSET 40;

-- SQL Server classic syntax (TOP has no offset, requires a workaround)
SELECT TOP 20 id, name FROM products ORDER BY name;
-- True paging on SQL Server (2012+) requires OFFSET ... FETCH
SELECT id, name FROM products
ORDER BY name
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

-- ANSI SQL standard syntax: works on modern MySQL, PostgreSQL and SQL Server
SELECT id, name FROM products
ORDER BY name
OFFSET 40 ROWS FETCH FIRST 20 ROWS ONLY;

4. Auto-increment: AUTO_INCREMENT vs. SERIAL vs. IDENTITY

Auto-generated primary keys are one of the core features of relational databases, but their syntax is entirely different between systems. MySQL uses the keyword AUTO_INCREMENT directly in the column definition. PostgreSQL historically has the pseudo data type SERIAL, which internally creates an INTEGER column with an attached sequence, while modern PostgreSQL versions favor the more ANSI-compliant approach GENERATED ALWAYS AS IDENTITY. SQL Server, in turn, uses the keyword IDENTITY(1,1) with start and increment values as parameters.

These three mechanisms differ not only syntactically but also in their behavior on transaction rollback: in some systems, values already reserved but not committed by a sequence are not released again, which leads to gaps in the ID sequence. For database-agnostic SQL this means: anyone relying on a gapless, sequential ID sequence is building on a guarantee that no relevant database system actually provides, regardless of the chosen auto-increment mechanism.

-- Auto-increment / identity syntax is entirely different per system

-- MySQL
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  total DECIMAL(10,2)
);

-- PostgreSQL: legacy SERIAL pseudo-type
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10,2)
);

-- PostgreSQL: modern ANSI-style identity column (preferred since PG 10)
CREATE TABLE orders (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  total NUMERIC(10,2)
);

-- SQL Server
CREATE TABLE orders (
  id INT IDENTITY(1,1) PRIMARY KEY,
  total DECIMAL(10,2)
);

5. Data types: VARCHAR, BOOLEAN and date/time

Even seemingly simple data types hide portability traps. A BOOLEAN is a real, standalone data type in PostgreSQL with the values TRUE, FALSE and NULL. MySQL has no real BOOLEAN type, treating BOOLEAN internally as a synonym for TINYINT(1), where 0 counts as false and any other value as true. SQL Server instead uses BIT with the values 0 and 1. Comparisons that check exactly TRUE against 1 on one system can lead to subtly different behavior on another.

Date and time values also differ in precision and timezone handling. PostgreSQL offers explicit, timezone-aware storage with TIMESTAMP WITH TIME ZONE, MySQL implicitly stores timezone information relative to the server timezone for TIMESTAMP, while DATETIME in MySQL is entirely timezone-free. SQL Server offers DATETIME2 as a more precise alternative to the older DATETIME, but likewise without native timezone support the way DATETIMEOFFSET provides. Anyone developing timezone-critical applications in a database-agnostic way should always store timestamps as UTC and perform timezone conversion explicitly in the application layer, instead of relying on implicit database behavior.

-- Data type differences: boolean and timestamp handling per system

-- MySQL: no real BOOLEAN type, TINYINT(1) under the hood
CREATE TABLE flags_mysql (
  is_active TINYINT(1) NOT NULL DEFAULT 0
);

-- PostgreSQL: native BOOLEAN type
CREATE TABLE flags_postgres (
  is_active BOOLEAN NOT NULL DEFAULT FALSE
);

-- SQL Server: BIT type instead of BOOLEAN
CREATE TABLE flags_sqlserver (
  is_active BIT NOT NULL DEFAULT 0
);

-- Timestamps: always store as UTC, convert timezone in the application layer
-- PostgreSQL
CREATE TABLE events_postgres (
  occurred_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);
-- MySQL (implicit server timezone, store UTC explicitly)
CREATE TABLE events_mysql (
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- SQL Server (no native timezone awareness in DATETIME2)
CREATE TABLE events_sqlserver (
  occurred_at DATETIME2 NOT NULL DEFAULT SYSUTCDATETIME()
);

6. String functions and concatenation

String concatenation is another field with considerable divergence. ANSI SQL defines the || operator for concatenation, which PostgreSQL fully supports. MySQL interprets || by default as logical OR instead of concatenation and requires the CONCAT() function instead. SQL Server, in turn, uses the + operator for string concatenation, which can lead to implicit and sometimes surprising type conversions with mixed data types.

Even basic string functions such as substring extraction or length determination differ in naming and parameter order between the systems, even though their basic functionality is similar. For database-agnostic SQL, it is usually more practical to abstract string concatenation through the application's data access layer, for example via a query builder with built-in dialect translation, rather than maintaining a separate SQL variant for each target dialect.

-- String concatenation syntax differs significantly between systems

-- ANSI SQL standard / PostgreSQL: double pipe operator
SELECT first_name || ' ' || last_name AS full_name FROM customers;

-- MySQL: || means logical OR by default, use CONCAT() instead
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

-- SQL Server: plus operator for concatenation
SELECT first_name + ' ' + last_name AS full_name FROM customers;

-- Portable alternative: use CONCAT(), supported on all three systems
-- (PostgreSQL and SQL Server both implement CONCAT() as well)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

7. Transaction isolation and locking differences

Even though all three systems know the ANSI isolation levels READ COMMITTED, REPEATABLE READ and SERIALIZABLE, their actual behavior under load differs considerably. PostgreSQL implements READ COMMITTED and REPEATABLE READ via MVCC without read locks, while SQL Server, in its default mode for READ COMMITTED, actually takes short-lived locks on read unless Snapshot Isolation is explicitly enabled. MySQL with InnoDB uses REPEATABLE READ by default instead of READ COMMITTED like the other two systems, which can lead to unexpected behavior with concurrent transactions when code is carried over from another system.

These differences can rarely be fully abstracted portably, because they are deeply rooted in the respective storage engine. The pragmatic approach for database-agnostic SQL is to set the isolation level explicitly in every transaction, instead of relying on system-specific defaults, and to guard against race conditions with explicit locking strategies such as SELECT ... FOR UPDATE, which is available in similar form on all three systems.

8. Abstraction layers: ORM/query builder vs. native SQL

One of the most pragmatic solutions for many of the portability problems mentioned is to use a data access layer that centrally encapsulates dialect differences. A query builder or ORM automatically translates an abstract description of the desired query, such as paging with limit and offset or auto-increment column definitions, into the correct syntax of the target system. This drastically reduces the manual effort for database-agnostic SQL, because developers no longer have to keep the differences in mind for every single query.

The downside of this approach: abstraction layers usually cover only the lowest common denominator of the supported systems and often get in the way of complex, system-specific optimizations such as special index types, window functions with extensions, or full-text search. In practice, many projects deliberately combine both approaches: an abstraction layer for everyday CRUD operations, targeted and clearly isolated native SQL for performance-critical or complex queries, clearly marked and isolated so that only these few spots need to be adapted in the event of a system switch.

9. When portability is not worth the effort

Not every project benefits from consistently writing database-agnostic SQL. When a system is bound to a single database system from the outset and for the foreseeable future, for instance because a cloud provider or a company policy fixes the target system, the extra effort for portability creates cost without a realistic return. System-specific features, such as PostgreSQL's JSONB indexing, MySQL's full-text search, or SQL Server's columnstore indexes, often offer considerable performance or functional advantages that would go unused under strict portability.

A realistic approach weighs the portability effort according to the actual likelihood of a system switch. For most production applications, it is worth avoiding obvious portability traps such as inconsistent quoting or non-standardized paging, because doing so requires little extra effort and makes the code more readable anyway. Deep abstraction against every conceivable system deviation, on the other hand, only pays off when a system switch is planned as a real possibility in the project lifecycle, not as a theoretical thought experiment.

10. Summary

Writing database-agnostic SQL does not mean avoiding every system-specific feature, it means deliberately knowing and handling the concrete, documented differences. Quoting rules, paging syntax, auto-increment mechanisms, data types like BOOLEAN and date/time, and string concatenation are the most common places where code that works on one system fails or behaves subtly differently on another. Anyone who knows these traps avoids most portability problems without having to give up powerful system-specific features.

The most realistic strategy for database-agnostic SQL is rarely full abstraction, but a deliberate separation: standard-compliant ANSI SQL syntax for everyday operations where it is available without extra effort, targeted and clearly isolated system-specific optimization where performance or functionality justify it. This balance keeps a system maintainable without facing a complete reimplementation upon a later switch of the database system.

11. FAQ: Writing Database-Agnostic SQL