Master MQL5 SQLite Database: Store & Query Trade Data

Learn to use MQL5's built-in SQLite engine to store and query trade data without external libraries. Step-by-step guide with code examples.

478views
master-mql5-sqlite-database-store-query-trade-data

Why SQLite in MQL5 Changes Everything

Most traders who code in MQL5 rely on flat files (.csv, .bin) or global variables to store trade data between runs. These approaches break down when you need to query hundreds of trades by date range, filter by symbol, or compute running statistics across multiple test passes. MetaTrader 5 ships with a full SQLite 3 engine accessible through seven dedicated functions: DatabaseOpen, DatabaseClose, DatabaseExecute, DatabasePrepare, DatabaseRead, DatabaseBind, and DatabaseFinalize. This means you can create relational tables, insert trade records, and run SQL queries directly inside your Expert Advisor or script — no external libraries, no DLL calls.

In this guide you will build a complete trade logging system that writes every deal to a local SQLite file, then query that data to calculate win rate, average profit, and maximum drawdown across multiple backtest runs. By the end you will know how to integrate SQLite into any MQL5 project for persistent, queryable data storage.

Prerequisites

  • MetaTrader 5 build 2000 or later (check Help > About).
  • A demo or live trading account (no special permissions needed for local database files).
  • Basic familiarity with the MetaEditor and compiling MQL5 code.
  • No prior SQL knowledge required — the examples use simple, readable statements.

Understanding the SQLite Functions in MQL5

Before diving into code, it helps to understand how the seven SQLite functions map to typical database operations. The table below summarizes each function's role:

Function Purpose Return Value
DatabaseOpen Opens or creates a SQLite database file Handle (int) or INVALID_HANDLE
DatabaseClose Closes an open database handle bool
DatabaseExecute Executes an SQL statement (no result set) bool
DatabasePrepare Prepares an SQL statement for execution with parameters bool
DatabaseBind Binds values to parameter placeholders (?) bool
DatabaseRead Reads the next row from a prepared query bool
DatabaseFinalize Finalizes a prepared statement, freeing resources bool

Note that DatabasePrepare and DatabaseBind are used together for parameterized queries—a best practice that prevents SQL injection and handles special characters properly. DatabaseRead steps through result rows one at a time, similar to a cursor in traditional SQL interfaces.

Step-by-Step: Build a Trade Logger with SQLite

Step 1: Create the Database and Table

Inside your EA's OnInit() function, call DatabaseOpen to create a new SQLite file. The file is stored in the \Files folder of your MT5 data directory (File > Open Data Folder > Files).

//+------------------------------------------------------------------+
//| TradeLogger.mq5 - SQLite trade database example                  |
//+------------------------------------------------------------------+
int db_handle = INVALID_HANDLE;

int OnInit()
{
    string db_path = "TradeHistory.db";
    db_handle = DatabaseOpen(db_path, DATABASE_OPEN_CREATE | DATABASE_OPEN_READWRITE);
    if(db_handle == INVALID_HANDLE)
    {
        Print("Failed to open database: ", GetLastError());
        return INIT_FAILED;
    }
    
    string create_sql = "CREATE TABLE IF NOT EXISTS trades ("
                       + "id INTEGER PRIMARY KEY AUTOINCREMENT, "
                       + "ticket INTEGER, "
                       + "symbol TEXT, "
                       + "lot_size REAL, "
                       + "open_price REAL, "
                       + "close_price REAL, "
                       + "profit REAL, "
                       + "open_time INTEGER, "
                       + "close_time INTEGER, "
                       + "comment TEXT)";
    
    if(!DatabaseExecute(db_handle, create_sql))
    {
        Print("Table creation failed: ", GetLastError());
        DatabaseClose(db_handle);
        return INIT_FAILED;
    }
    
    Print("Database ready at: ", TerminalInfoString(TERMINAL_PATH), "\\Files\\", db_path);
    return INIT_SUCCEEDED;
}

Key points: DATABASE_OPEN_CREATE creates the file if it does not exist. DATABASE_OPEN_READWRITE allows both inserts and queries. The AUTOINCREMENT primary key ensures each trade gets a unique ID. The comment field is optional but useful for storing EA version or strategy tags.

If you plan to run multiple EAs on the same database, consider using DATABASE_OPEN_READWRITE without DATABASE_OPEN_CREATE after the first run to avoid accidentally overwriting existing data. You can check if the file exists using FileIsExist(db_path) before opening.

Step 2: Insert Trade Records on Close

Use OnTradeTransaction to capture every closed deal. Insert a row each time a trade is fully closed (DEAL_ENTRY_OUT).

void OnTradeTransaction(const MqlTradeTransaction &trans,
                        const MqlTradeRequest &request,
                        const MqlTradeResult &result)
{
    if(trans.type == TRADE_TRANSACTION_DEAL_ADD)
    {
        ulong deal_ticket = trans.deal;
        long deal_entry;
        if(HistoryDealSelect(deal_ticket) &&
           HistoryDealGetInteger(deal_ticket, DEAL_ENTRY) == DEAL_ENTRY_OUT)
        {
            string symbol   = HistoryDealGetString(deal_ticket, DEAL_SYMBOL);
            double volume   = HistoryDealGetDouble(deal_ticket, DEAL_VOLUME);
            double price    = HistoryDealGetDouble(deal_ticket, DEAL_PRICE);
            double profit   = HistoryDealGetDouble(deal_ticket, DEAL_PROFIT);
            long   time     = HistoryDealGetInteger(deal_ticket, DEAL_TIME);
            long   open_ticket = HistoryDealGetInteger(deal_ticket, DEAL_ORDER);
            
            string insert_sql = "INSERT INTO trades "
                              + "(ticket, symbol, lot_size, open_price, close_price, profit, open_time, close_time) "
                              + "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
            
            DatabasePrepare(db_handle, insert_sql);
            DatabaseBind(0, (int)deal_ticket);
            DatabaseBind(1, symbol);
            DatabaseBind(2, volume);
            DatabaseBind(3, price); // close price
            DatabaseBind(4, profit);
            DatabaseBind(5, (int)time);
            // For open_price and open_time, query the order
            // (simplified: use same price/time for demo)
            DatabaseBind(6, (int)time);
            DatabaseExecute(db_handle, insert_sql);
            DatabaseFinalize(db_handle);
        }
    }
}

Note: For production code, you would query the original order's ORDER_PRICE_OPEN and ORDER_TIME_SETUP via HistoryOrderSelect. The example above keeps it readable. To retrieve the actual open price:

ulong order_ticket = HistoryDealGetInteger(deal_ticket, DEAL_ORDER);
if(HistoryOrderSelect(order_ticket))
{
    double open_price = HistoryOrderGetDouble(order_ticket, ORDER_PRICE_OPEN);
    long open_time = HistoryOrderGetInteger(order_ticket, ORDER_TIME_SETUP);
    DatabaseBind(3, open_price);
    DatabaseBind(6, (int)open_time);
}
else
{
    DatabaseBind(3, price); // fallback to close price
    DatabaseBind(6, (int)time);
}

Step 3: Query Trade Data for Analytics

Now retrieve stored trades and compute statistics. This example calculates total trades, win rate, and average profit.

void CalculateStats()
{
    string query = "SELECT COUNT(*), "
                   + "SUM(CASE WHEN profit > 0 THEN 1 ELSE 0 END), "
                   + "AVG(profit) "
                   + "FROM trades";
    
    if(!DatabasePrepare(db_handle, query))
    {
        Print("Query prepare failed: ", GetLastError());
        return;
    }
    
    if(DatabaseRead(db_handle))
    {
        int total_trades = (int)DatabaseGetInteger(db_handle, 0);
        int winning_trades = (int)DatabaseGetInteger(db_handle, 1);
        double avg_profit = DatabaseGetDouble(db_handle, 2);
        
        Print("Total trades: ", total_trades);
        Print("Win rate: ", (total_trades > 0 ? (double)winning_trades/total_trades*100 : 0), "%");
        Print("Avg profit: ", avg_profit);
    }
    
    DatabaseFinalize(db_handle);
}

Call CalculateStats() from OnDeinit() or a timer to see live analytics. The SQL CASE statement counts winning trades without needing a separate loop. For more advanced analytics, you can add additional queries:

// Maximum drawdown query
string dd_query = "SELECT MIN(profit) FROM trades WHERE profit < 0";
// Profit factor query
string pf_query = "SELECT ABS(SUM(CASE WHEN profit > 0 THEN profit ELSE 0 END) / "
                  + "SUM(CASE WHEN profit < 0 THEN profit ELSE 0 END)) AS profit_factor "
                  + "FROM trades";

Step 4: Close the Database Cleanly

Always close the database handle in OnDeinit() to avoid file corruption.

void OnDeinit(const int reason)
{
    if(db_handle != INVALID_HANDLE)
    {
        DatabaseClose(db_handle);
        Print("Database closed.");
    }
}

Advanced Usage: Batch Inserts and Transactions

When logging many trades in rapid succession (e.g., during backtesting or high-frequency trading), individual inserts can become slow. SQLite excels at batch operations when wrapped in a transaction. Here's how to optimize:

// Before batch insert loop
DatabaseExecute(db_handle, "BEGIN TRANSACTION");

for(int i = 0; i < ArraySize(trades); i++)
{
    // ... prepare and bind parameters ...
    DatabaseExecute(db_handle, insert_sql);
    DatabaseFinalize(db_handle);
}

// After loop
DatabaseExecute(db_handle, "COMMIT");

In backtesting, this can reduce insert time from seconds to milliseconds for hundreds of trades. Always call DatabaseFinalize after each prepared statement execution, even within transactions, to free statement handles.

Tips and Best Practices from Experience

  • Use parameterized queries with DatabaseBind — never concatenate user-supplied strings into SQL. This prevents SQL injection and handles special characters in symbol names like "EURUSD.".
  • Wrap batch inserts in a transaction when logging many trades. Call DatabaseExecute(db_handle, "BEGIN TRANSACTION") before the loop, then "COMMIT" after. This cuts insert time from seconds to milliseconds.
  • Store timestamps as INTEGER using Unix epoch time (TimeCurrent()). SQLite date functions like datetime(close_time, '

    Frequently Asked Questions

    Can I use SQLite in MT4?

    No. MT4 does not include SQLite functions. Only MT5 (build 2000+) supports DatabaseOpen, DatabaseExecute, and related calls. For MT4, use FileWriteString/FileReadString with structured CSV or binary files instead.

    What happens to the database file during backtesting?

    Each backtest run writes to the same file by default. To keep results separate, append a timestamp to the filename in OnInit() — for example, "TradeHistory_" + IntegerToString(TimeCurrent()) + ".db". This prevents data mixing across test passes.

    How do I handle SQLite errors gracefully?

    Always check the return value of DatabaseOpen, DatabaseExecute, and DatabasePrepare. Use GetLastError() to obtain the specific error code (5200–5215 range). Print the SQL statement and the error to the Experts tab for debugging.

    Is there a size limit for the SQLite database?

    SQLite supports databases up to 140 TB. For trade logging, even millions of rows remain fast if you create proper indexes. The practical limit is disk space and the 2 GB file size limit of FAT32 filesystems — use NTFS or exFAT.

Community

Clap for the article and open comments only when you want to read them.

0 claps0 comments

Related articles