When we talk about MQL5, most conversations revolve around indicators, Expert Advisors, trading strategies, and backtests. But at some point, every trader or developer who seriously works with automation realizes that data persistence is crucial. And this is where databases come in. You might even think: “But I already save my results in CSV or TXT files, why complicate things with a database?” The answer lies in the organization, performance, and reliability that a database provides, especially when dealing with large volumes of information or complex operations.
In the context of MQL5, talking about databases may seem like an exaggeration at first glance. After all, the language is strongly oriented towards trading, indicators, and automated execution robots. But when we start dealing with strategies that involve large volumes of data, complex backtests, or detailed order records, the simplicity of standard files quickly proves insufficient. This is where understanding how to create, access, and manipulate a database becomes a tool capable of transforming your trading workflow.
A trader starting to explore databases in MQL5 will find dozens of native functions and standalone examples. It’s enough to save and query records, but the question soon arises: *how to organize all this in a clean and reusable way in real projects?* In this series of articles, we go beyond documentation. We start with the basic functions of SQLite and progress step by step to build a **mini-ORM in MQL5 (TickORM)**. The idea is to transform direct calls into well-designed layers, inspired by Java’s JDBC and JPA, but adapted to the MetaTrader ecosystem.
In this article, I demonstrate the fundamentals of databases in MQL5 and SQL. This paves the way for the subsequent articles to encapsulate these functions in classes, interfaces, and, finally, the ORM.
Technically, a database is an organized way of structuring information that allows you to store, query and manipulate saved data. It guarantees integrity, creates relationships between different sets of information and offers speed in operations.
The practical difference between saving data in files and using a database lies in its organization and ease of access. In simple files, such as CSV or TXT, each read or change requires going through all the content, manipulating strings, dealing with formatting errors and hoping the file doesn’t get corrupted. In a database, these operations are abstracted: it is possible to search for specific records, update multiple values at once and ensure that complete transactions take place without data loss.
But how does this organization happen internally? Think of the basic structure of a database as a spreadsheet, here’s an example:
This simple architecture with tables, columns and rows is the basis for quick, well-structured queries. It’s as if you had infinite organized and interconnected spreadsheets, but with the advantage of being able to cross-reference data, apply advanced filters and manipulate large volumes of information without a headache.
This is precisely where the motivation to create our own ORM in MQL5 comes from. The idea is simple: instead of writing SQL manually every time we want to manipulate a table (create records, fetch results, update values), we’re going to build a layer that treats tables as entities and columns as class attributes. This will be the core of the project we’ll be developing throughout the series, and it will serve as the foundation for more robust and scalable trading systems. The name of the ORM will be TickORM .
In short, databases are the basis for building reliable and flexible solutions, and our ORM will be the link between this basis and the code in MQL5.
To make your testing easier, all sample code is attached at the end. This way, you can reference, copy, and adapt it as needed, without having to manually reassemble the snippets.Note: From here on, some code examples will bring log records to the console. To do this, I use my
Working with databases doesn’t require much. Fortunately, the language offers a set of native functions that allow you to create, access and manipulate data directly within the language. These functions serve as a bridge between your code and the database.
The relationship with SQL is direct: these functions act as a query execution layer. In other words, you write SQL commands within MQL5, and the native functions handle the preparation, execution and reading of the results. This means that, even without relying on external libraries, you can perform complex operations such as SELECT, INSERT, UPDATE, DELETE and even create tables.
This is the starting point for understanding each function and its possibilities before diving into practical implementation. For full details of all the functions available, I’ll list all the native database functions available, as we’ll cover the use of each one:
For full details on all available functions, parameters and examples, MetaQuotes provides official documentation at MQL5 Database Functions.
Let’s start with the basics, opening a connection to the database. This is done with the DatabaseOpen function, which receives the name of the database file and optional flags that define the opening mode that can have these possible values:
In the first parameter we define the name of the database, in this case I used “database”. And at the end just add “.sqlite” which is the file extension, because this is a database that is stored in a file, but that’s not a problem, the way to get the data and make queries follows the SQL standard.
In this example, DATABASE_OPEN_READWRITE allows reading and writing, while DATABASE_OPEN_CREATE guarantees that the file will be created if it doesn’t already exist. After completing the operations, simply close the connection to DatabaseClose to free up resources.
When you run this code, it automatically creates the database in the folder , which you can view in the metaeditor:
The first step after connecting to the database is to define the structure that will store the data. In SQL, this is done with the CREATE TABLE command. It describes the name of the table and the fields it should have.
In MQL5, creating tables involves two main functions:
The most common flow is: check if the table exists → if it doesn’t, create table. Here’s a practical example:
In this snippet, we use CREATE TABLE trades (…) to create a table called trades, with basic information about a trade: id (automatically generated unique identifier), symbol, price, takeprofit, stoploss and volume.
If the table already exists, the DatabaseTableExists function prevents the command from being executed again. Always follow this pattern of checking for existence and creating tables on demand.
Creating a representation of the current stage would look like this image of the database:
Metaeditor has support for viewing this database, just double-click on the view tables file, it automatically changes the browser tab:
Following the flow, let’s insert data into the table we’ve just created. In SQL, we use the INSERT INTO command to add records. It follows this structure:
In the values we pass the data in raw form, if it’s a string, just wrap it in single quotes (‘example’). We use the same DatabaseExecute function and check that the execution was successful:
Here, we add a record to the trades table, each column is assigned a value:
Representing the current stage, it looks like this:
Saving the data in the database is only half the job, the real potential comes when we can query and extract this data in a structured way. In MQL5, this process follows a well-defined flow:
Here, the query SELECT * FROM trades; fetches all records from the “trades” table. Each call to DatabaseRead(request) moves the cursor to the next row, and the inner loop goes through all the columns, printing only the names.
Comparing this to an image, it would look something like this:
Together, they allow you to navigate through the bank cell by cell, just as if you were scrolling through a spreadsheet.
Moving on and reading the values of the columns, the previous example only lists the names of the columns. We need the stored values, so we use specific functions according to the column type:
In this case, as well as scrolling through rows and columns, we also access the contents of each cell. Returning to the analogy of the didactic image:
In real-life scenarios, it’s not uncommon for us to need to adjust existing information. This is where the UPDATE command comes in, which allows you to modify the values of one or more columns in specific rows of a table.
In MQL5, updating records happens via the DatabaseExecute function, which sends the SQL command directly to the database. Here’s an example:
Here, we have a classic case: we’re telling the bank to find the record in the trades table whose id is equal to 1 and, once found, change the value of the volume column to 0.1.
This pattern is extremely useful in trading. Imagine that you have saved a trade with an initial volume of 0.01 lots, but after strategy adjustments you need to correct it to 0.1. With a simple UPDATE command, the information is reliably synchronized in the database, without having to delete and recreate records.
To delete data, we use SQL’s DELETE command, which is responsible for deleting rows from a table. The general form of the command is:
It follows the same pattern, we use the DatabaseExecute function to send the SQL command:
In this example, we ask the database to remove from the trades table the record whose id field is equal to 1. A one-off, controlled and secure deletion.
With this command, we have completed the basic data manipulation cycle: create, insert, update and delete. From here we have all the basics for the next step.
It’s worth emphasizing: DROP TABLE is irreversible. Unlike a DELETE FROM trades, which only removes the records but keeps the structure, DROP TABLE deletes both the table and the data. If you execute this statement, the table will need to be recreated before it can receive new records.
Let’s now move on to a necessary point, which is the integrity of the database. In any application that manipulates data, integrity is a critical point. Imagine a scenario in which we need to write several records, if one of these inserts fails and the others have already been applied, the database could be in an inconsistent state. Transactions exist to avoid this problem.
A transaction is a block of SQL operations that should be treated as an atomic unit:
In MQL5, we have native functions that allow us to control this flow:
This is useful because it ensures that no incomplete data remains. It creates more control, you can group several operations together and only apply them when you’re sure they all worked. And consequently, it increases security: in the event of unexpected failures (network, power failure, execution error), the database returns to its previous state without compromising data.
Imagine recording ten different operations on various tables. If the eighth fails, without a transaction you would have seven operations recorded and three missing, an inconsistent state. With the transaction, all you have to do is rollback and the bank returns exactly as it was before the attempt.
In addition to transactions, another layer of security and practicality is the possibility of importing and exporting data. This makes it possible to create periodic backups of the database, ensuring not only protection against loss, but also mobility and ease of recovery. For this, there are the DatabaseExport and DatabaseImport functions, which allow you to extract the data in CSV and, when necessary, restore it back to the bank.
The example below shows the export of the trades table to a file called backup_trades.csv :
Here are some important points:
The data return indicates how many records were exported. If it’s negative, an error has occurred and GetLastError() will tell you which one.
Now imagine the reverse case: restoring or loading data into the trades table:
Here are some valuable details:
The data return shows how many records have been imported.
With DatabaseExport and DatabaseImport , we have closed the basic cycle of entering and leaving data in the database. Now we know how to create tables, insert, query, update, delete, and even save external copies or restore information when necessary.
But note one detail: each operation requires dealing directly with SQL and specific calls to native functions. This works well in small examples, but as the system grows, complexity accumulates, and so does the risk of errors. This is exactly where the next inevitable question arises: do we really need to write everything on our fingernails?
This is where the idea of an ORM comes in. In the next section, we’ll understand what it is, why it can radically change the way we interact with databases in MQL5 and how it fits into the path we’re building.
So far, we’ve explored the use of SQL directly in MQL5, manipulating tables and records “on the fly”. This approach works, but as the system grows, so does the amount of SQL scattered throughout the code, and with it come maintenance problems, repetition and the risk of errors. This is where ORM (Object-Relational Mapping) comes in.
An ORM is a layer that bridges the gap between the object-oriented world and the relational world of databases. Instead of writing SQL manually, we describe our entities as classes and let the ORM take care of translating them into SQL commands. In other words: instead of dealing with INSERT INTO trades (…) VALUES (…) , you simply create a Trade object and call something like repository.save(trade) .
The main features of an ORM include:
In MQL5, this idea makes even more sense because writing SQL embedded in the code quickly becomes a nightmare. Imagine an Expert Advisor with dozens of entities (trades, orders, logs, performance metrics). Every INSERT , SELECT or UPDATE scattered throughout the code means more points of failure, more difficulty in evolving the logic and more chances of inconsistency.
The “pain” that ORM solves is:
In short: without ORM, each operation is a block of SQL within the code; with ORM, the SQL becomes a hidden detail, and you focus on what really matters, the trading logic.
Now that we understand the usefulness of an ORM, we need to visualize how it translates into the MQL5 ecosystem. Unlike more traditional languages in the world of ORMs (Java, C#, Python), here we don’t have robust frameworks ready to use, which means we’ll have to build our own solution, adapted to the limitations and particularities of the language.
The scope planned for this project is clear: create a layer that allows the developer to work with objects instead of direct SQL, but without losing the simplicity and performance required in a trading environment.
The planned functionalities include:
The aim is not to recreate a Hibernate Framework within MQL5, but to provide a minimalist and efficient abstraction layer that meets the real needs of those who work with algorithmic trading and need structured persistence.
We’ve reached the end of this first part of the series, and the picture is starting to become clear: databases in MQL5 are not just files for storing information, but powerful structures that, when properly exploited, allow you to record, query and manipulate data in an organized and reliable way. We saw how SQL works in practice, from creating tables, inserting and reading records, to manipulating columns and data types, using native MQL5 functions such as DatabaseOpen, DatabaseExecute, DatabasePrepare and DatabaseRead .
More importantly, we understand that writing SQL manually on every project quickly becomes repetitive, error-prone and difficult to maintain. This is where the concept of ORM comes in: a layer of abstraction that turns tables into objects and SQL queries into simple methods, allowing you to work with entities in a natural way while keeping the code clean and centralized.
As the next steps in the series, we’re going to start building this abstraction layer. Our goal will be to create a minimalist and efficient ORM, with entity classes, repositories, a query builder and an automatic table creation mechanism. This way, operations such as saving, fetching or deleting data will no longer depend on SQL scattered throughout the code, but on intuitive methods that directly reflect the domain of the trading we are modeling.
By mastering these tools, you not only gain efficiency and security in data manipulation, but also create a solid foundation for evolving complex trading systems, allowing for more advanced analysis, robust backtests and detailed histories of orders and strategies.
That brings this first stage to a close. The next part of the series will be practical: we’ll start defining the initial ORM classes and implementing object persistence, connecting all the theory we’ve seen here with functional code in MQL5.
