1. Introduction

1.1 What is MIRAJ#

MIRAJ is a columnar, in-memory and durable relational SQL database engine, developed by CIRTASOFT (https://cirtasoft.com/mirajdb). It stores the data of each table column by column, keeps those columns in memory for query execution, and guarantees write durability through a write-ahead log that is replayed at restart.

MIRAJ is also optimized for artificial intelligence. Because data resides in main memory (RAM) rather than on a hard disk, reads do not suffer from disk access latency: typical AI workloads (preparing and exploring large datasets, feature extraction, repeated and concurrent queries from agents or inference services, similarity search with vector search) run at memory speed, without giving up the durability provided by the write-ahead log.

Finally, MIRAJ offers an MCP option: the server can open an endpoint for artificial intelligence assistants (Claude Code, or any client of the Model Context Protocol). The assistant explores the structure of the databases, reads or modifies data, and can even administer the server, with no driver and no password: it receives a token created in SQL, tied to an account and capped at an access level (STRUCTURE, READ, WRITE, ADMIN). The option is disabled by default and is not available in the Express edition; see chapter 20.

MIRAJ is intended for:

• developers who want to embed a fast SQL engine in an application (desktop, service, data-processing tool) without depending on an external server;
• developers building classic client/server applications who want a SQL server reachable over the network and compatible with existing tools and connectors;
• database administrators (DBAs) who operate one or more MIRAJ servers: account creation, privilege management, monitoring, backups, replication.

Typical use cases: business applications with an embedded local database, a data server for a client/server or web application, analytical computations on data volumes that fit in memory (large history tables can stay on disk), artificial intelligence applications (training data, context for agents, vector search), AI assistants connected to the databases through the MCP option, consolidation of several databases behind a single server, and cluster deployment for read availability.

This manual is intended for MIRAJ end users (developers and DBAs): it describes the SQL language, the bundled tools (miraj-cli, miraj-server) and their administration. It does not describe the internal implementation of the engine.

1.2 The three ways to use MIRAJ#

These three modes share the same engine and the same data file format: a database opened in embedded mode can later be served by miraj-server, and vice versa. A data folder is opened by only one process at a time: while miraj-server is running, the console and applications go through the network.

Two SQL languages: MIRAJ or PostgreSQL#

A miraj-server instance speaks one of the two languages, chosen at startup for the whole instance:

• the MIRAJ language (the default), described by chapters 6 to 10, 14 and 15 of this manual and by the thematic chapters that follow;
• the PostgreSQL language (the POSTGRESQL element of sql_mode, see 16.10): the port then speaks the PostgreSQL protocol and grammar, for psql, psycopg2, pgjdbc, Npgsql and the other PostgreSQL clients. It has its own manual, in PostgreSQL syntax: chapters 32 to 41.

The engine, the data files, administration, the cluster and backups are the same in both cases. The MIRAJ tools (miraj-cli, miraj-dump, miraj-backup, miraj-migrate, the managers), the ODBC driver and the miraj Python library recognize the language of the instance at connection time. The getting started chapter shows the same example in both languages.

1.3 Product editions#

MIRAJ is distributed in four editions, built from the same code:

The MCP option (AI assistants, chapter 20) is available in the Enterprise, Cluster and Developer editions, not in Express. The same goes for binary log replication in the reference format (MIRAJ as source or replica of a reference server, chapter 17, §17.13), which is distinct from cluster replication. Encryption of data at rest (chapter 25) belongs to the Enterprise and Cluster editions only.

The four editions speak the same SQL and read the same data files; only the distribution of work across cores, the data cap of the Express and Developer editions, the operating time of the Developer edition and replication differ.

The Enterprise and Cluster editions require a license, a miraj.lic file verified on the machine with no connection at all (chapter 31). Without a valid license, the server still starts, with the features and limits of the Express edition.

Finding out which edition you are using#

SELECT @@miraj_edition, @@version_comment, VERSION();

+-----------------+------------------------------------+--------------------+
| @@miraj_edition | @@version_comment                  | VERSION()          |
+-----------------+------------------------------------+--------------------+
| Entreprise      | MIRAJ columnar engine (Entreprise) | 8.0.40-miraj-1.0.0 |
+-----------------+------------------------------------+--------------------+
1 ligne(s)

@@miraj_edition is Entreprise, Express, Cluster or Developer. SHOW ENGINES also shows the edition in the comment of the default engine:

SHOW ENGINES;

+--------+---------+-------------------------------------------------+--------------+-----+------------+
| Engine | Support | Comment                                         | Transactions | XA  | Savepoints |
+--------+---------+-------------------------------------------------+--------------+-----+------------+
| MIRAJ  | DEFAULT | In-memory columnar storage (Entreprise edition) | YES          | YES | YES        |
| DISK   | YES     | Row storage on disk, indexes in memory          | YES          | YES | YES        |
| Aria   | YES     | Alias of DISK                                   | YES          | YES | YES        |
| MyISAM | YES     | Alias of DISK                                   | YES          | YES | YES        |
+--------+---------+-------------------------------------------------+--------------+-----+------------+
4 ligne(s)

VERSION() returns the same version number regardless of the edition (8.0.40-miraj-1.0.0: compatibility number, then MIRAJ version, see VERSION); it is @@miraj_edition, @@version_comment and SHOW ENGINES that distinguish Enterprise, Express, Cluster and Developer.

1.4 Architecture overview#

Seen from the outside, a MIRAJ engine rests on four principles:

• Columnar, in-memory storage. Each column of a table is stored separately, with a missing-value indicator (NULL) per column and dictionary compression; the data that queries work on resides in memory, with the disk serving as the durability medium. For large, rarely queried tables (history, archives), a table can also be declared on disk (ENGINE = Aria, MyISAM or DISK): its rows stay in its file and only its indexes occupy memory (see Storage engines).
• Write-ahead log for durability. Every write goes through a log before being considered committed; periodic checkpoints limit the work to replay after an abrupt stop, whether it is a process termination, a power outage or a system shutdown.
• Vectorized execution. Queries do not process rows one by one: they operate on batches of several thousand rows at a time, which reduces repeated per-row work and takes advantage of modern hardware.
• Transactions. Each session can work in a transaction (START TRANSACTION, COMMIT, ROLLBACK), with serializable isolation that never blocks a reader waiting for a writer.

This manual stays at that level: it does not detail the internal organization of the engine (the internal layers, the binary formats) beyond what is useful for operating, configuring and administering it.

1.5 Manual contents#

1. Introduction — this document: usage modes, SQL languages, editions, architecture, contents
2. Installation — Windows, macOS and Linux packages, installation wizard, miraj-server and miraj-cli options, configuration file, data folder, default security
3. Getting started — first step-by-step walkthrough in both languages: server, connection, database, tables, data, queries, joins, transaction, index, schema
4. Edition comparison — Express, Developer, Enterprise and Cluster side by side
5. Release notes — changes in each version, from the most recent to the oldest
6. Data types — integers, exact decimals, floats, strings, binaries and BLOB, dates, ENUM/SET, JSON, UUID, dynamic columns, VECTOR, conversions, DEFAULT, AUTO_INCREMENT, generated columns, CHECK
7. DDL language — databases, tables, ALTER TABLE, indexes (including FULLTEXT and trigrams), views, storage engines (in-memory or disk tables), partitioning, foreign keys, sequences, routines, table maintenance (CHECK, REPAIR, OPTIMIZE)
8. DML language — INSERT, REPLACE, ON DUPLICATE KEY UPDATE, UPDATE, DELETE, parameterized queries, PREPARE/EXECUTE, LOAD DATA and LOAD XML
9. SELECT queries — joins, aggregation, sorting, subqueries, UNION/INTERSECT/EXCEPT, WITH, windows, read locks, EXPLAIN, full-text search MATCH … AGAINST
10. SQL functions — string, number, date, control, aggregate, regular expression, hashing and encryption, network, system, JSON, vector, window, sequence and spatial functions
11. Vector search — VECTOR INDEX (HNSW), k nearest neighbors, mhnsw_ef_search, USING hnsw
12. Temporal tables — WITH SYSTEM VERSIONING, FOR SYSTEM_TIME AS OF | FROM … TO | BETWEEN | ALL, DELETE HISTORY, application-time periods (PERIOD FOR, FOR PORTION OF, WITHOUT OVERLAPS), PARTITION BY SYSTEM_TIME
13. Collations — utf8mb4_general_ci, utf8mb4_unicode_ci, utf8mb4_bin: per column, table or expression (COLLATE), coercibility, errors 1253 / 1273
14. Transactions and concurrency — autocommit, multiversion concurrency model, deferred update, LOCK TABLES, SAVEPOINT, XA transactions
15. Accounts and privileges — authentication, CREATE/ALTER/DROP USER, GRANT/REVOKE, roles, privilege audit
16. Server administration — startup and shutdown, options, configuration, durability, security (accounts, TLS), monitoring, logs, PostgreSQL-language instance (16.10)
17. Cluster and replication — Cluster edition: primary and secondaries, certificates, failover, tables distributed across nodes; binary log replication
18. miraj-migrate — migrating an existing SQL database to MIRAJ
19. Backup and restore — hot BACKUP / RESTORE, continuous journal archiving, miraj-backup, SQL export miraj-dump
20. MCP server — AI assistants on MIRAJ: --mcp ON, CREATE MCP TOKEN tokens, STRUCTURE/READ/WRITE/ADMIN levels, tools
21. Change events — LISTEN / NOTIFY / WAIT FOR CHANGES: being notified of writes without polling the database, resuming after a disconnection, subscribers on a cluster node
22. Miraj Server Manager — graphical manager for Windows and macOS: start and stop, status, resources, slow queries, backups
23. miraj-proxy — cluster load balancing: write port to the primary, read port to the least loaded node, failover, PROXY header
24. REST API — MIRAJ over HTTP/HTTPS: --rest ON, APIs written in SQL (CREATE ENDPOINT), tables, free-form SQL, CREATE API TOKEN … ACCESS tokens
25. Encryption at rest — Enterprise and Cluster editions: folder encrypted at initialization, passphrase, ENCRYPTION per database and per table, miraj-keyring, key rotation
26. miraj-cli — SQL console reference: options, connecting to a server, interactive mode commands
27. Client libraries — embedded API miraj.dll and its wrappers (C#, Python, Java, PHP, Node.js, Go, C/C++, Delphi), miraj Python library for the server
28. Error codes — reference of codes and messages
29. ODBC driver — native MIRAJ ODBC Driver: installation (Windows, macOS, Linux), connection string, DSN, Excel, Power BI, Access, LibreOffice, pyodbc, PostgreSQL-language instance
30. PostgreSQL language — condensed reference of the grammar of a PostgreSQL-language instance: lexicon ("x", E'…', $$…$$, $1), :: casts, ILIKE, FETCH FIRST, SET search_path, cursors, guarded MIRAJ extensions, MIRAJ tools, ODBC driver and Python library on a PostgreSQL instance
31. License of the Enterprise and Cluster editions — miraj.lic file verified with no connection: installation (--install-license), holder (@@miraj_license_owner, SHOW LICENSE), FLUSH LICENSE, behavior without a license or at expiry, machine-bound license, one license per cluster
32. PostgreSQL: Getting started — starting the instance, connecting with psql, miraj-cli or a driver, first schema
33. PostgreSQL: Data types — integer, numeric, text, bytea, timestamptz, interval, json/jsonb, uuid, vector, :: casts, serial and identity
34. PostgreSQL: Data definition language (DDL) — schemas and search_path, tables, constraints, ALTER TABLE, indexes, views, sequences, comments
35. PostgreSQL: DML language — INSERT, UPDATE … FROM, DELETE … USING, RETURNING, ON CONFLICT, TRUNCATE, COPY, parameters
36. PostgreSQL: SELECT queries — joins, aggregates, FETCH FIRST, set operations, WITH, windows, FOR UPDATE, EXPLAIN
37. PostgreSQL: SQL functions — operators, strings, patterns, numbers, dates and intervals, to_char, aggregates, windows, JSON, conversions, bytes and hashing
38. PostgreSQL: Transactions and session — BEGIN, savepoints, isolation, SET/SHOW/RESET, search_path, time zone, cursors, two-phase transactions
39. PostgreSQL: Accounts and privileges — roles, GRANT/REVOKE, SCRAM authentication
40. PostgreSQL: Routines and triggers (PL/pgSQL) — functions, procedures and CALL, PL/pgSQL language, messages and errors, triggers
41. PostgreSQL: Catalog and tools — pg_catalog, information_schema, psql, drivers, MIRAJ tools, backup