Using LIMIT for pagination without checking database type (code works on MySQL/PostgreSQL but fails on SQL Server/Oracle)

Use OFFSET/FETCH (SQL:2008 standard) or conditional SQL (detect database, generate LIMIT or OFFSET/FETCH)

LIMIT is MySQL/PostgreSQL-specific syntax, not ANSI SQL. SQL Server uses TOP (old) or OFFSET/FETCH (new), Oracle uses ROWNUM (old) or OFFSET/FETCH (new). For portability: (1) Use OFFSET/FETCH if you don't need MySQL support (works on PostgreSQL 8.4+, SQL Server 2012+, Oracle 12c+). (2) Use conditional SQL if you need MySQL support (detect database type, generate appropriate syntax). (3) Use ORM (Hibernate, SQLAlchemy) for automatic SQL generation. Example: GitLab supports both PostgreSQL (LIMIT) and MySQL (LIMIT) using ActiveRecord ORM. Metabase supports 10+ databases using HoneySQL query builder.

Always test pagination on all target databases. Use ORM for automatic SQL generation or write conditional SQL. Document which databases are supported and minimum versions required.

High

Syntax error on SQL Server/Oracle: "Incorrect syntax near LIMIT"

Using || for string concatenation (works on PostgreSQL/Oracle but fails on SQL Server with NULL result)

Use CONCAT() function (ANSI SQL, works on all modern databases)

String concatenation syntax varies: PostgreSQL/Oracle use || operator, SQL Server uses + operator, MySQL uses CONCAT() function. For portability, use CONCAT() function (ANSI SQL, supported by MySQL, PostgreSQL 9.1+, SQL Server 2012+, Oracle). Example: SELECT CONCAT(first_name, ' ', last_name) works everywhere. Avoid: SELECT first_name || ' ' || last_name (fails on SQL Server). Avoid: SELECT first_name + ' ' + last_name (fails on PostgreSQL/Oracle/MySQL). Real-world: WordPress uses wpdb abstraction layer to handle MySQL/MariaDB string functions.

Always use CONCAT() for string concatenation. It's ANSI SQL and works on all modern databases. Avoid || and + operators for portability.

Medium

SQL Server: || is bitwise OR operator, not string concatenation. Result is NULL if any operand is NULL.

Using AUTO_INCREMENT (MySQL) without checking database type (fails on PostgreSQL/SQL Server/Oracle)

Use ORM for automatic syntax generation or conditional DDL (AUTO_INCREMENT for MySQL, SERIAL for PostgreSQL, IDENTITY for SQL Server)

Auto-increment syntax varies widely: MySQL uses AUTO_INCREMENT, PostgreSQL uses SERIAL or IDENTITY, SQL Server uses IDENTITY, Oracle uses SEQUENCE + trigger. For portability: (1) Use ORM (Hibernate, SQLAlchemy) that generates appropriate DDL for each database. (2) Use IDENTITY (SQL:2003 standard, supported by PostgreSQL 10+, SQL Server, Oracle 12c+, but NOT MySQL). (3) Write conditional DDL (detect database, generate appropriate syntax). Example: Hibernate generates AUTO_INCREMENT for MySQL, SERIAL for PostgreSQL, IDENTITY for SQL Server automatically.

Use ORM for DDL generation (CREATE TABLE). ORMs handle auto-increment syntax differences automatically. If writing raw DDL, use conditional logic based on database type.

High

PostgreSQL: "syntax error at or near AUTO_INCREMENT". SQL Server: "Incorrect syntax near AUTO_INCREMENT".

Using NOW() for current timestamp (works on MySQL/PostgreSQL but fails on SQL Server/Oracle)

Use CURRENT_TIMESTAMP (ANSI SQL, works on all databases)

Current timestamp function varies: MySQL/PostgreSQL use NOW(), SQL Server uses GETDATE(), Oracle uses SYSDATE, SQLite uses datetime('now'). For portability, use CURRENT_TIMESTAMP (ANSI SQL, supported by all databases). Example: SELECT user_id, CURRENT_TIMESTAMP AS created_at FROM users works everywhere. Avoid: SELECT user_id, NOW() AS created_at (fails on SQL Server/Oracle). Real-world: GitLab uses ActiveRecord ORM which automatically uses appropriate timestamp function per database.

Always use CURRENT_TIMESTAMP for current date/time. It's ANSI SQL and works on all databases. Avoid NOW()/GETDATE()/SYSDATE() for portability.

Medium

SQL Server: "Invalid column name NOW". Oracle: "ORA-00904: NOW: invalid identifier".

Using database-specific features (PostgreSQL arrays, MySQL JSON functions) without fallback for other databases

Provide fallback implementation or document that feature requires specific database

Advanced features are database-specific: PostgreSQL has arrays/JSONB/full-text search, MySQL has JSON functions, SQL Server has columnstore indexes, Oracle has advanced analytics. For portability: (1) Avoid database-specific features if supporting multiple databases. (2) Provide fallback implementation (e.g., store array as comma-separated string, parse in application). (3) Document that feature requires specific database (e.g., "Full-text search requires PostgreSQL"). (4) Use feature detection (check database version, enable feature if available). Example: GitLab uses PostgreSQL-specific features (arrays, JSONB) and documents that PostgreSQL is required (MySQL support is legacy).

For SaaS products supporting multiple databases: Stick to common features (ANSI SQL). For single-database products: Use database-specific features for better performance. Document database requirements clearly.

Medium

Feature not available on other databases, application fails or has reduced functionality