3. Getting started

This chapter guides you step by step: starting a MIRAJ server, connecting to it, creating a database and tables, inserting data, querying it, joining it, working in a transaction, indexing and inspecting the schema. The same walkthrough is done twice: first on an instance in the MIRAJ language (the default language, sections 3.1 to 3.10), then on an instance in the PostgreSQL language (section 3.11). By the end, you will be able to run a complete use case in either language.

The examples use miraj-cli, the SQL console supplied with MIRAJ, connected to the server over the network; everything carries over unchanged to the SQL client or driver of your choice. The results are copied as the console displays them.

3.1 Starting the server#

miraj-server.exe --root D:\donnees --log

================================================================================
Compte root@localhost créé avec le mot de passe provisoire : W2nRUci5fQVdcc6PUnuRccig
Il n'est affiché qu'une fois et n'est écrit nulle part : notez-le, puis changez-le :
  ALTER USER root@localhost IDENTIFIED BY 'nouveau mot de passe';
================================================================================
MIRAJ 1.0.0 Entreprise - serveur console sur 127.0.0.1:7007, dossier "D:\donnees", 12 fils, licence MRJ-2026-000123 (Société Exemple SA), valide jusqu'au 2027-10-11.
Configuration : D:\donnees\miraj_config.xml.

The folder D:\donnees is created if it does not exist, with a local root account whose randomly generated password is displayed only once: note it down, it is used for all connections, then replace it as soon as possible (ALTER USER root@localhost IDENTIFIED BY '...'). To choose it yourself from the start: --initial-root-password <password> or the environment variable MIRAJ_INITIAL_ROOT_PASSWORD (see 2.3). The MIRAJ 1.0.0 … line gives the edition, the listening address, the folder, the thread count and the license. Leave this window open: the server stays active in it.

On macOS and Linux, the command is written the same way (miraj-server --root /path/to/data --log). With the Windows installation wizard or the macOS package, the server is already started as a service: go straight to 3.2 with the password chosen at installation.

3.2 Connecting with miraj-cli#

Open a second window and connect the console to the server; --password without a value asks for the password without displaying it:

miraj-cli.exe --host 127.0.0.1 --port 7007 --user root --password

MIRAJ 1.0.0 - connecté (langage MIRAJ) - tapez \help pour l'aide, \q pour quitter.
miraj>

The console recognizes the language of the instance by itself. You are now in interactive mode (REPL): each SQL statement ends with a semicolon and can span several lines. For a script, the environment variable MIRAJ_PASSWORD avoids typing the password, and -e "<SQL>" or a .sql file as an argument runs the statements then exits.

miraj-cli --root D:\donnees also opens the folder directly, with no network or account, but only with the server stopped: a data folder is opened by only one process at a time (Dossier de données « D:\donnees » déjà ouvert par un autre processus MIRAJ otherwise).

3.3 Creating a database#

CREATE DATABASE boutique;
USE boutique;

OK, 1 ligne(s) affectée(s)
OK, 0 ligne(s) affectée(s)

The prompt now shows the name of the current database:

boutique>

3.4 Creating a table#

Let us create a customers table, with an auto-incremented primary key, a UNIQUE constraint, required columns and a default creation date:

CREATE TABLE clients (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    nom         VARCHAR(80) NOT NULL,
    email       VARCHAR(120) NOT NULL UNIQUE,
    cree_le     DATETIME DEFAULT CURRENT_TIMESTAMP
);

OK, 0 ligne(s) affectée(s)

Let us also create an orders table, linked to the customers by a foreign key:

CREATE TABLE commandes (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    client_id   INT NOT NULL,
    montant     DECIMAL(10,2) NOT NULL,
    passee_le   DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

OK, 0 ligne(s) affectée(s)

3.5 Inserting data#

INSERT INTO clients (nom, email) VALUES
    ('Amel Kaci', '[email protected]'),
    ('Youssef Ben Ali', '[email protected]'),
    ('Claire Dupont', '[email protected]');

OK, 3 ligne(s) affectée(s)

INSERT INTO commandes (client_id, montant) VALUES
    (1, 59.90),
    (1, 12.50),
    (2, 249.00);
SELECT LAST_INSERT_ID();

OK, 3 ligne(s) affectée(s)
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 ligne(s)

LAST_INSERT_ID() returns the first AUTO_INCREMENT value generated by the last INSERT statement of the session.

An insert attempt that violates the UNIQUE constraint on email is refused, with the error code and the SQLSTATE:

INSERT INTO clients (nom, email) VALUES ('Doublon', '[email protected]');

ERREUR 1062 (23000) : Duplicate entry '[email protected]' for key 'email'

3.6 Querying data: WHERE and ORDER BY#

SELECT id, nom, email FROM clients WHERE nom LIKE '%dupont%' OR nom LIKE 'a%' ORDER BY nom;

+----+---------------+---------------------------+
| id | nom           | email                     |
+----+---------------+---------------------------+
|  1 | Amel Kaci     | [email protected]     |
|  3 | Claire Dupont | [email protected] |
+----+---------------+---------------------------+
2 ligne(s)

Text comparisons ignore case and accents (default collation utf8mb4_general_ci, see 13. Collations): '%dupont%' finds "Dupont", 'a%' finds "Amel".

3.7 Joining tables#

Let us list the orders with the name of the corresponding customer, sorted by descending amount:

SELECT c.nom, o.id AS commande, o.montant
FROM commandes o
JOIN clients c ON c.id = o.client_id
ORDER BY o.montant DESC;

+-----------------+----------+---------+
| nom             | commande | montant |
+-----------------+----------+---------+
| Youssef Ben Ali |        3 |  249.00 |
| Amel Kaci       |        1 |   59.90 |
| Amel Kaci       |        2 |   12.50 |
+-----------------+----------+---------+
3 ligne(s)

An aggregate per customer, with LEFT JOIN to keep customers without orders, and GROUP BY:

SELECT c.nom, COUNT(o.id) AS nb_commandes, SUM(o.montant) AS total
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id
ORDER BY total DESC;

+-----------------+--------------+--------+
| nom             | nb_commandes | total  |
+-----------------+--------------+--------+
| Youssef Ben Ali |            1 | 249.00 |
| Amel Kaci       |            2 |  72.40 |
| Claire Dupont   |            0 |   NULL |
+-----------------+--------------+--------+
3 ligne(s)

COUNT(o.id) counts only the orders found (0 for Claire Dupont); COUNT(*) would also count the row completed with NULL by the outer join and would return 1. SUM of no value is NULL.

3.8 Working in a transaction#

Outside a transaction, each statement is committed immediately (autocommit). START TRANSACTION groups several statements, which COMMIT commits together and ROLLBACK cancels:

START TRANSACTION;
UPDATE commandes SET montant = montant * 2 WHERE client_id = 1;
SELECT SUM(montant) FROM commandes WHERE client_id = 1;
ROLLBACK;
SELECT SUM(montant) FROM commandes WHERE client_id = 1;

OK, 0 ligne(s) affectée(s)
OK, 2 ligne(s) affectée(s)
+--------------+
| SUM(montant) |
+--------------+
|       144.80 |
+--------------+
1 ligne(s)
OK, 0 ligne(s) affectée(s)
+--------------+
| SUM(montant) |
+--------------+
|        72.40 |
+--------------+
1 ligne(s)

The transaction sees its own changes; after ROLLBACK, the amounts are back to their value. The concurrency model (multiversion control, deferred update, conflicts to retry) is described in chapter 14.

3.9 Creating an index#

An index speeds up searches on a frequently filtered column that is neither a primary key nor UNIQUE:

CREATE INDEX idx_clients_nom ON clients (nom);
SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'boutique' AND TABLE_NAME = 'clients'
ORDER BY INDEX_NAME;

OK, 0 ligne(s) affectée(s)
+-----------------+-------------+------------+
| INDEX_NAME      | COLUMN_NAME | NON_UNIQUE |
+-----------------+-------------+------------+
| email           | email       |          0 |
| idx_clients_nom | nom         |          1 |
| PRIMARY         | id          |          0 |
+-----------------+-------------+------------+
3 ligne(s)

The primary key (PRIMARY) and the UNIQUE constraint (index email) have their own index. SHOW INDEX FROM clients gives the full detail. An index is removed with:

DROP INDEX idx_clients_nom ON clients;

OK, 0 ligne(s) affectée(s)

An index that a foreign key needs cannot be removed (error 1553 Cannot drop index '…': needed in a foreign key constraint).

3.10 Viewing the schema#

List the tables of the current database:

SHOW TABLES;

+--------------------+
| Tables_in_boutique |
+--------------------+
| clients            |
| commandes          |
+--------------------+
2 ligne(s)

Describe a table:

DESCRIBE clients;

+---------+--------------+------+-----+-------------------+----------------+
| Field   | Type         | Null | Key | Default           | Extra          |
+---------+--------------+------+-----+-------------------+----------------+
| id      | int(11)      | NO   | PRI | NULL              | auto_increment |
| nom     | varchar(80)  | NO   |     | NULL              |                |
| email   | varchar(120) | NO   | UNI | NULL              |                |
| cree_le | datetime     | YES  |     | CURRENT_TIMESTAMP |                |
+---------+--------------+------+-----+-------------------+----------------+
4 ligne(s)

See the full definition of a table, as it can be replayed:

SHOW CREATE TABLE commandes;

CREATE TABLE `commandes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` int(11) NOT NULL,
  `montant` decimal(10,2) NOT NULL,
  `passee_le` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `commandes_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
)

(the console frames this text in a two-column table, Table and Create Table). The unnamed foreign key received the name commandes_ibfk_1.

In the miraj-cli console, the shortcuts \d (without an argument: equivalent to SHOW TABLES; with a table name: equivalent to DESCRIBE) and \l (equivalent to SHOW DATABASES) save retyping these statements; \help lists all console commands.

3.11 The same walkthrough in the PostgreSQL language#

An instance started in the PostgreSQL language speaks the PostgreSQL grammar and protocol: psql, psycopg2, pgjdbc, Npgsql and the other PostgreSQL clients connect to it, and so does miraj-cli. Its complete manual begins at chapter 32; this section covers the essentials on the previous example.

3.11.1 Starting the instance#

The language is chosen at startup, for the whole instance, by the POSTGRESQL element of sql_mode (or by the SQL language of the instance question of the installation wizard):

miraj-server.exe --root D:\donnees-pg --log --sql-mode POSTGRESQL,STRICT_TRANS_TABLES

MIRAJ 1.0.0 Entreprise - serveur console sur 127.0.0.1:7007, langage PostgreSQL, dossier "D:\donnees-pg", 12 fils, licence MRJ-2026-000123 (Société Exemple SA), valide jusqu'au 2027-10-11.
Configuration : D:\donnees-pg\miraj_config.xml.

The langage PostgreSQL mention confirms the choice. The PostgreSQL protocol is served on the main port (--port, 7007 by default). The root account is created and its password displayed as in 3.1.

3.11.2 Connecting#

With psql:

psql -h 127.0.0.1 -p 7007 -U root

or with the MIRAJ console, which recognizes the language of the instance:

miraj-cli.exe --host 127.0.0.1 --port 7007 --user root --password

MIRAJ 1.0.0 - connecté (langage PostgreSQL) - tapez \help pour l'aide, \q pour quitter.
miraj=>

SELECT version(), current_user;

+--------------------------------------------------------+--------------+
| version()                                              | current_user |
+--------------------------------------------------------+--------------+
| PostgreSQL 16.4 (MIRAJ 1.0.0) on aarch64-macos, 64-bit | root         |
+--------------------------------------------------------+--------------+
1 ligne(s)

(the platform at the end of the version depends on the machine). In an instance in the PostgreSQL language, a MIRAJ database is a schema: CREATE SCHEMA boutique creates the database boutique, and SET search_path chooses the current schema (see 32.2).

3.11.3 Creating a schema and tables#

CREATE SCHEMA boutique;
SET search_path TO boutique;

CREATE TABLE clients (
    id       serial PRIMARY KEY,
    nom      varchar(80) NOT NULL,
    email    varchar(120) NOT NULL UNIQUE,
    cree_le  timestamp DEFAULT now()
);

CREATE TABLE commandes (
    id         serial PRIMARY KEY,
    client_id  integer NOT NULL REFERENCES clients (id),
    montant    numeric(10,2) NOT NULL,
    passee_le  timestamp DEFAULT now()
);

OK, 0 ligne(s) affectée(s)
OK, 0 ligne(s) affectée(s)
OK, 0 ligne(s) affectée(s)
OK, 0 ligne(s) affectée(s)

The prompt becomes boutique=>. serial replaces INT AUTO_INCREMENT, timestamp and now() replace DATETIME and CURRENT_TIMESTAMP, numeric replaces DECIMAL.

3.11.4 Writing data#

RETURNING returns the rows written, with their generated values:

INSERT INTO clients (nom, email) VALUES
    ('Amel Kaci', '[email protected]'),
    ('Youssef Ben Ali', '[email protected]'),
    ('Claire Dupont', '[email protected]')
RETURNING id, nom;

+----+-----------------+
| id | nom             |
+----+-----------------+
|  1 | Amel Kaci       |
|  2 | Youssef Ben Ali |
|  3 | Claire Dupont   |
+----+-----------------+
3 ligne(s)

INSERT INTO commandes (client_id, montant) VALUES
    (1, 59.90),
    (1, 12.50),
    (2, 249.00);

OK, 3 ligne(s) affectée(s)

The duplicate on email is refused with the PostgreSQL SQLSTATE:

INSERT INTO clients (nom, email) VALUES ('Doublon', '[email protected]');

ERREUR 23505 : Duplicate entry '[email protected]' for key 'email'

ON CONFLICT … DO NOTHING ignores the conflicting row instead of raising the error:

INSERT INTO clients (nom, email) VALUES ('Doublon', '[email protected]')
ON CONFLICT (email) DO NOTHING;

OK, 0 ligne(s) affectée(s)

3.11.5 Querying and joining#

Here LIKE is case-sensitive; ILIKE ignores case:

SELECT id, nom, email FROM clients WHERE nom ILIKE '%dupont%' OR nom ILIKE 'a%' ORDER BY nom;

+----+---------------+---------------------------+
| id | nom           | email                     |
+----+---------------+---------------------------+
|  1 | Amel Kaci     | [email protected]     |
|  3 | Claire Dupont | [email protected] |
+----+---------------+---------------------------+
2 ligne(s)

A join, limited to the first two rows by FETCH FIRST:

SELECT c.nom, o.id AS commande, o.montant
FROM commandes o
JOIN clients c ON c.id = o.client_id
ORDER BY o.montant DESC
FETCH FIRST 2 ROWS ONLY;

+-----------------+----------+---------+
| nom             | commande | montant |
+-----------------+----------+---------+
| Youssef Ben Ali |        3 |  249.00 |
| Amel Kaci       |        1 |   59.90 |
+-----------------+----------+---------+
2 ligne(s)

An aggregate per customer; NULLS LAST puts the NULL total at the end of the list:

SELECT c.nom, count(o.id) AS nb_commandes, sum(o.montant) AS total
FROM clients c
LEFT JOIN commandes o ON o.client_id = c.id
GROUP BY c.id, c.nom
ORDER BY total DESC NULLS LAST;

+-----------------+--------------+--------+
| nom             | nb_commandes | total  |
+-----------------+--------------+--------+
| Youssef Ben Ali |            1 | 249.00 |
| Amel Kaci       |            2 |  72.40 |
| Claire Dupont   |            0 |   NULL |
+-----------------+--------------+--------+
3 ligne(s)

3.11.6 Transaction#

BEGIN;
UPDATE commandes SET montant = montant * 2 WHERE client_id = 1;
SELECT sum(montant) FROM commandes WHERE client_id = 1;
ROLLBACK;
SELECT sum(montant) FROM commandes WHERE client_id = 1;

OK, 0 ligne(s) affectée(s)
OK, 2 ligne(s) affectée(s)
+--------------+
| sum(montant) |
+--------------+
|       144.80 |
+--------------+
1 ligne(s)
OK, 0 ligne(s) affectée(s)
+--------------+
| sum(montant) |
+--------------+
|        72.40 |
+--------------+
1 ligne(s)

3.11.7 Index and schema#

CREATE INDEX idx_clients_nom ON clients (nom);
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'clients' ORDER BY indexname;
DROP INDEX idx_clients_nom;

OK, 0 ligne(s) affectée(s)
+-----------------+-----------------------------------------------------------------------+
| indexname       | indexdef                                                              |
+-----------------+-----------------------------------------------------------------------+
| clients_pkey    | CREATE UNIQUE INDEX clients_pkey ON boutique.clients USING btree (id) |
| email           | CREATE UNIQUE INDEX email ON boutique.clients USING btree (email)     |
| idx_clients_nom | CREATE INDEX idx_clients_nom ON boutique.clients USING btree (nom)    |
+-----------------+-----------------------------------------------------------------------+
3 ligne(s)
OK, 0 ligne(s) affectée(s)

The catalog is read through information_schema (or pg_catalog, and the \dt, \d table commands of psql):

SELECT table_name FROM information_schema.tables WHERE table_schema = 'boutique' ORDER BY table_name;

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'boutique' AND table_name = 'clients'
ORDER BY ordinal_position;

+------------+
| table_name |
+------------+
| clients    |
| commandes  |
+------------+
2 ligne(s)
+-------------+-----------------------------+-------------+-------------------------------------+
| column_name | data_type                   | is_nullable | column_default                      |
+-------------+-----------------------------+-------------+-------------------------------------+
| id          | integer                     | NO          | nextval('clients_id_seq'::regclass) |
| nom         | character varying           | NO          | NULL                                |
| email       | character varying           | NO          | NULL                                |
| cree_le     | timestamp without time zone | YES         | now()                               |
+-------------+-----------------------------+-------------+-------------------------------------+
4 ligne(s)

The serial column has the sequence clients_id_seq as its default value, created with the table.

3.12 Next steps#

You now know how to create a database, tables with constraints, insert data, query it with joins and aggregation, work in a transaction, index and inspect the schema, in both languages. The following chapters detail each topic:

MIRAJ language: 6. Data types, 7. DDL language, 8. DML language, 9. SELECT queries, 10. SQL functions, 14. Transactions and concurrency, 15. Accounts and privileges;
PostgreSQL language: the manual of the PostgreSQL language, starting with its getting started (connection through psql and drivers, accounts);
the console: 26. miraj-cli; server operation: 16. Server administration.