Controlled migration of Paradox and BDE to MariaDB

Many Delphi specialist applications were built with Paradox tables and the Borland Database Engine (BDE) because that was a pragmatic standard at the time: local, quick to start, little infrastructure. In practice these systems often still run in production today — including reporting, label printing, import/export, batch jobs, history tables and special logic that has been „worked into“ data access over years. That is exactly why a migration is not just an export of data but a controlled refactor: data model, SQL behavior, side effects in code and operating procedures must be considered together.

MariaDB is often a very good target platform when you need robust multi-user operation, reliable transactions, centralized backups, replication, clear rights management and connectivity for web portals, services or REST APIs. The challenge is rarely the database installation itself — the real effort lies in a safe migration path: How are tables, indexes, primary keys, character sets, date fields, “empty” values and referential relationships transferred correctly without breaking business logic in production?

This article describes a proven approach to migrate Paradox and BDE to MariaDB in a controlled way: technically sound, staged and with a focus on stability. The goal is a sustainable foundation for further modernization steps — for example BDE removal, switching to BDE replacement with native connectivity, clear Layer-3 architecture, REST servers and cross-platform clients.

Why Paradox/BDE migration is more than a database swap

Paradox as a file format and BDE as an access layer form a system with quirks you should not try to reproduce 1:1 in MariaDB. Typical symptoms in day-to-day operation are:

• Opaque dependencies: SQL statements are scattered (forms, DataModules, reports, dynamic string SQL), often without central governance.
• Behavior „by feel“: Certain filters, sorts or joins work by chance because Paradox/BDE is permissive or implicitly type-converts.
• Multi-user limits: File-based locking, network performance drops, locking issues as data volume grows.
• Maintenance risks: BDE dependencies, old drivers, difficult deployment on modern Windows versions, 64‑bit/ARM64 issues.

A controlled migration treats these points as acceptance criteria, not side effects. MariaDB then becomes not just „the new database“ but an opportunity to decouple the data access layer, increase business integrity and enable interfaces.

Target picture: MariaDB as a stable data foundation for desktop, services and portals

A sensible target for B2B specialist applications usually consists of three layers:

• Database (MariaDB): consistent data storage, indexes, constraints, transactions, users/roles, backups.
• Business logic (server/services): validations, workflows, importers, scheduler, interfaces. Optionally as a REST server, Windows- or Linux services.
• Clients (VCL/FMX/Web): UIs, reports, offline components, integrations. Ideally with clear contracts toward the business logic.

Depending on the starting point, not everything must be implemented immediately. But the migration should be planned so it does not block the path to a clean architecture. If you only copy tables today and tomorrow again let every form talk directly to the database, you may have introduced MariaDB but the real risks remain.

Inventory: What actually needs to be migrated

It starts with an inventory that goes beyond “how many tables”. In Paradox/BDE projects the database is typically only part of the truth. Important points:

1) Tables, indexes, “pseudo-keys”

True primary keys are often missing. Instead there are combinations of fields, sequence numbers without unique constraints or “key” fields maintained by the application. For MariaDB these must become unique, robust keys — otherwise transactions and referential integrity will be of limited use.

2) Queries, dynamic SQL fragments, reports

BDE uses different SQL dialects depending on component and tolerates “creative” statements. Reporting components (including older ones) often contain their own SQL definitions. A migration must locate and categorize these sources: critical core queries, rarely used reports, special imports.

3) Character set and special characters (umlauts, ISO/ANSI, Unicode)

Many Paradox applications are historically ANSI-based. If the Delphi application itself was switched to Unicode at some point, mixed situations arise: data stored in an old encoding, UI in Unicode, exports expecting Windows-1252. MariaDB should receive a clear concept here (typically utf8mb4), including clean conversion and tests for „invisible“ errors (comparisons, sorting, trim/pad, special characters).

4) “Empty” values, null logic and date fields

Paradox/BDE knows various special cases: empty strings instead of NULL, 0-dates, „empty“ timestamps, default values created in the UI. MariaDB distinguishes strictly between NULL and empty. That affects WHERE clauses, aggregations and calculations. These differences must be assessed per table/field.

5) Side artifacts: session tables, local configuration, cache

Local tables for intermediate results, export buffers, user layouts or parameters often live inside the Paradox structure. Some of these belong to the client (e.g. UI layouts), others belong centrally (e.g. work items, status, logs). A migration is an opportunity to separate these categories cleanly.

Pitfalls with Paradox/BDE: typical technical differences

To make the migration plannable it is worth addressing recurring differences explicitly.

SQL dialect and operators

BDE/Paradox SQL is not identical to MySQL/MariaDB SQL. Differences appear for example in date functions, string functions, outer joins (historical), like/mask logic and implicit type conversions. A controlled approach replaces „it works“ with clear rules: which statements are ported, which are intentionally rewritten, which are encapsulated in views/stored procedures (if sensible)?

Sorting and collation

Sort orders and case sensitivity in Paradox often differ from MariaDB, especially for umlauts. In MariaDB collation (e.g. utf8mb4_german2_ci vs. utf8mb4_unicode_ci) determines comparison and ordering. This is not an academic question: it affects deduplication, search functions and the behavior of unique constraints.

Autoincrement and numbering sequences

Paradox has autoincrement fields, but applications frequently use custom numbering sequences (invoice numbers, order numbers) with special logic. In MariaDB you should clearly separate:

• Technical primary key: AUTO_INCREMENT (or UUID, depending on architecture) for relationships.
• Business number: separate numbering sequence with transactional protection, possibly per tenant/period.

Anyone who attempts to misuse a business number as a technical key will face costly refactoring later.

Locking and transactions

The jump from file-based access to a real server is beneficial, but it changes behavior. In MariaDB transactions (InnoDB) are central. You must decide where transaction boundaries lie: per dialog, per save operation, per batch. Especially relevant: long transactions and edit modes lasting minutes are common in Paradox worlds, but are critical server-side (locks, deadlocks, replication lag). Adjusting workflows or the UI is often part of the migration.

Approach model: controlled migration in stages instead of Big Bang

In B2B environments operational stability is often more important than a quick cutover. A staged migration path reduces risk because business users and IT can validate iteratively.

Stage 1: Data model transfer with mapping, without code refactor

In the first step a MariaDB schema is built that maps the Paradox structure — but already follows target principles: primary keys, indexes, sensible data types, utf8mb4, InnoDB. In parallel a reproducible import process is created (scripts/tools) that can be repeated if needed. Repeatability is important because migration is rarely finished on the first run.

Deliverables of this stage typically are:

• Schema definition (DDL) versioned (e.g. in Git)
• Field mapping list (Paradox → MariaDB) including conversion rules
• Import procedure with logging (row counts, errors, outliers)
• First validation reports (counts, sums, checksums)

Stage 2: BDE removal in data access (e.g. with FireDAC)

The real modernization step is the decoupling from BDE. In Delphi projects BDE-Ablosung mit nativer Anbindung is a proven path because it provides modern drivers, transactions, parameter binding and unified components for different SQL backends. The decisive question is not „BDE out“, but how the code looks afterwards: centralized data access, clear error handling, clean parameters instead of string concatenation.

Typical tasks in this stage:

• Replace TTable/TQuery with FireDAC queries and datasets
• Introduce a data-access layer (DAL) as a basis for later Layer-3 architecture
• Standardize transaction scopes (commit/rollback)
• SQL review: dialect adaptation, parameters, paging, joins

If your application is intended to be modernized long-term, this step is often more important than pure data migration. It creates the technical freedom for 64‑bit, modern Windows versions and clean deployment pipelines.

Stage 3: Parallel operation and business acceptance without disrupting operations

For critical systems parallel operation is sensible: MariaDB is set up and cyclically (or incrementally) populated while the legacy system continues to run. This allows the business to compare reports, identify edge cases and test the new platform under load. Parallel operation can be implemented in different ways:

• Read-only replica for reporting/BI preparation
• „Dual write“ in defined areas (only if well controlled)
• Cutover migration with several rehearsals and a clear cutover checklist

It is important not to overcomplicate: dual write sounds attractive but is error-prone if business logic is not centralized. Often a repeatable cutover run with good validation is ultimately more economical.

Stage 4: Optimization, integrity, performance, operating processes

After cutover the phase begins in which MariaDB should demonstrate its strengths: referential integrity, performant indexes, clean rights, monitoring, backup/restore tests. Only then do the „real“ production loads often become visible: long reports, poorly indexed search masks, batch updates. A controlled migration explicitly plans this stage instead of letting it emerge as unplanned rework.

Data types and mapping: from Paradox to MariaDB without information loss

Field mapping is the heart of the migration. Typical assignments (simplified) are:

• Alpha / Memo: VARCHAR/TEXT (with utf8mb4), length checks and trim rules
• Number: INT/BIGINT/DECIMAL depending on value range; beware implicit decimal places
• Date/Time: DATE/DATETIME/TIMESTAMP; clear rules for „0-date“ vs. NULL
• Logical: BOOLEAN or TINYINT(1) with unambiguous semantics
• Currency: DECIMAL(… ,2/4) instead of float to avoid rounding errors

It is important not only to translate data types but also to record business rules: May a field be NULL? Which defaults are business-correct? Which combinations must be unique? From this follow constraints and indexes. These rules were often implicit in the Paradox/BDE system in the UI or code — in MariaDB they should, where sensible, become explicit.

Integrity: applying primary keys, foreign keys and unique indexes

Many legacy databases run for years without referential integrity — until integrations, portals or parallel processes are added. At that point data quality becomes a limiting factor. In MariaDB you can use foreign keys, unique constraints and checks (depending on version/engine) to prevent data errors early.

A practical approach is to introduce integrity stepwise:

• First primary keys and unique indexes on core objects (customers, items, documents)
• Then foreign keys in stable areas
• For „historical“ tables with dirty data: clean first, then add constraints

This reduces the risk that the cutover fails because of legacy garbage, while still significantly improving the overall situation.

Performance in practice: what changes with MariaDB

Paradox/BDE systems are often optimized for „file speed“: local indexes, fast table accesses, lots of client-side filtering. With MariaDB the work shifts to the server. That is good, but requires clean SQL and index strategies.

Typical performance traps

• SELECT * from large tables because it used to be „fast enough“ locally
• Client-side filtering instead of server-side WHERE clauses
• Missing composite indexes for search mask fields (e.g. tenant + status + date)
• LIKE ‚%text%‘ without an appropriate full-text strategy

Measurable improvement instead of „by feel“

A controlled migration establishes measurement points early: slow query log, EXPLAIN analyses, reproducible load tests. This is especially important if further components are planned after migration, such as a REST server or a customer portal that creates new access patterns (many small requests, paging, search endpoints).

Delphi-specific: BDE removal, FireDAC and clean data access layers

In Delphi projects technical modernization is tightly coupled with data access. BDE is not just „a driver“ but a complete access style (TTable, record-based, navigation, local filters). Migration to MariaDB is the right moment to consolidate access.

From „datasets everywhere“ to controlled data access

Many applications have their own queries in every form. That scales poorly from a business and security perspective. Proven changes move in the direction of:

• Central repository/service classes for core objects
• Consistent error and transaction handling
• Parameterized queries (avoid SQL injection, use plan caching)
• Configurable connection pools or connection management for services

This creates a basis for a Layer-3 architecture in which UI, business logic and persistence are clearly separated. That helps not only with the database change but also with later expansion toward REST servers or background services.

MariaDB connectivity with FireDAC: typical questions to resolve

The same questions come up in projects: which driver variant (MySQL/MariaDB), which SSL options, which timezone and date settings, which Unicode settings? These are not minor details because they directly affect data consistency (date/time), sorting and operational security (TLS). A controlled migration defines these parameters, documents them and tests them with realistic data.

Cutover plan: cutover date, data freeze, rollback — without surprises

The cutover is a project in itself. A good cutover plan describes not only „when to switch“ but also protective measures:

• Data freeze: From when will no new data be entered in the legacy system? Are there emergency processes?
• Final import: How long does it realistically take? (Dry runs provide figures.)
• Validation: Which checks must be green before go-live (counts, sums, samples, business reports)?
• Rollback: When do you abort, and what is the next step?
• Communication: Who gives approval? Who is available in the war room?

In SMEs a „rollback“ is often organizationally, not technically, critical. Therefore the migration must be prepared so the cutover is not an experiment but a rehearsed procedure.

After migration: a foundation for REST, services and multiplatform

Once MariaDB runs stably and the BDE removal has been implemented cleanly, new options arise: REST APIs for external systems, background processing as Windows- or Linux-services, decoupling of UI and business logic and prospectively multiplatform clients. A common next step is to pull business logic out of the desktop to serve integrations (ERP/DMS/CRM) and portals in a controlled way.

Important: a REST server is not an „additional layer“ but an architectural decision. If data access, validation and permissions are already consolidated in services/repositories, robust APIs can be developed from them much more easily.

Practical checklist: What to clarify before project start

• Which modules are business-critical and must reliably run on cutover day?
• What are real data volumes (table sizes, growth, archiving concepts)?
• Which reports must be identical 1:1, which may be improved?
• Which integrations depend on the system (file exports, ODBC, office, print flows)?
• Is there multi-tenancy, and if so how is it represented today?
• Which operational requirements apply (backup window, restore time, rights, audit)?

These clarifications are not bureaucracy but prevent a migration from being „technically finished“ yet failing business acceptance.

Conclusion: Controlled migration means making risks manageable

To migrate Paradox and BDE to MariaDB in a controlled way means modernizing the application as a whole system: data model, SQL, transactions, character sets, access layer and operating processes. Anyone who treats the switch as a pure export usually gets exactly the problems they wanted to eliminate — just on a new server.

A staged approach with reproducible import, clean field mapping, early validation and a clear BDE removal (e.g. via FireDAC) creates a stable basis: for multi-user operation, for integrations, for services and for the next steps of Delphi modernization.

If you want to plan your migration with business certainty and implement it without operational disruption, we are happy to discuss the initial situation, risks and a realistic staged plan: https://net-base-software-gmbh.de/kontakt/