Search documentation of mnesia

# View Source Getting Started

This section introduces `Mnesia` with an example database. This example is referenced in the following sections, where the example is modified to illustrate various program constructs. This section illustrates the following mandatory procedures through examples:

- Starting the Erlang session.
- Specifying the `Mnesia` directory where the database is to be stored.
- Initializing a new database schema with an attribute that specifies on which node, or nodes, that database is to operate.
- Starting `Mnesia`.
- Creating and populating the database tables.

## Starting Mnesia for the First Time

This section provides a simplified demonstration of a `Mnesia` system startup. The dialogue from the Erlang shell is as follows:

    % erl -mnesia dir '"/tmp/funky"'
    Erlang/OTP 27 [erts-15.1.2]

    Eshell V15.1.2 (press Ctrl+G to abort, type help(). for help)
    1> mnesia:create_schema([node()]).
    ok
    2> mnesia:start().
    ok
    3> mnesia:create_table(funky, []).
    {atomic,ok}
    4> mnesia:info().
    ---> Processes holding locks <---
    ---> Processes waiting for locks <---
    ---> Participant transactions <---
    ---> Coordinator transactions <---
    ---> Uncertain transactions <---
    ---> Active tables <---
    funky          : with 0        records occupying 305      words of mem
    schema         : with 2        records occupying 533      words of mem
    ===> System info in version "4.23.2", debug level = none <===
    opt_disc. Directory "/tmp/funky" is used.
    use fallback at restart = false
    running db nodes   = [nonode@nohost]
    stopped db nodes   = []
    master node tables = []
    remote             = []
    ram_copies         = [funky]
    disc_copies        = [schema]
    disc_only_copies   = []
    [{nonode@nohost,disc_copies}] = [schema]
    [{nonode@nohost,ram_copies}] = [funky]
    3 transactions committed, 0 aborted, 0 restarted, 2 logged to disc
    0 held locks, 0 in queue; 0 local transactions, 0 remote
    0 transactions waits for other nodes: []
    ok

In this example, the following actions are performed:

- Step 1: The Erlang system is started from the UNIX prompt with a flag `-mnesia dir '"/tmp/funky"'`, which indicates in which directory to store the data.
- Step 2: A new empty schema is initialized on the local node by evaluating `mnesia:create_schema([node()])`. The schema contains information about the database in general. This is explained in detail later.
- Step 3: The DBMS is started by evaluating `mnesia:start()`.
- Step 4: A first table is created, called `funky`, by evaluating the expression `mnesia:create_table(funky, [])`. The table is given default properties.
- Step 5: `mnesia:info()` is evaluated to display information on the terminal about the status of the database.

## Example

A `Mnesia` database is organized as a set of tables. Each table is populated with instances (Erlang records). A table has also a number of properties, such as location and persistence.

### Database

This example shows how to create a database called `Company` and the relationships shown in the following diagram:

    ---
    title: Company Entity-Relation Diagram
    ---

    erDiagram
        Dept {
            atom id
            string name
        }

        Employee {
            int emp_no
            string name
            int salary
            atom sex
            int phone
            tuple room_no

        }

        Project {
            atom name
            int number
        }

        Dept }|--|| Employee: Manager
        Employee }|--|| Dept: At_dep
        Employee }|--|{ Project: in_proj

The database model is as follows:

- There are three entities: department, employee, and project.
- There are three relationships between these entities:
  1. A department is managed by an employee, hence the `manager` relationship.
  2. An employee works at a department, hence the `at_dep` relationship.
  3. Each employee works on a number of projects, hence the `in_proj` relationship.

### Defining Structure and Content

First the record definitions are entered into a text file named `company.hrl`. This file defines the following structure for the example database:

    -record(employee, {emp_no,
                       name,
                       salary,
                       sex,
                       phone,
                       room_no}).

    -record(dept, {id,
                   name}).

    -record(project, {name,
                      number}).

    -record(manager, {emp,
                      dept}).

    -record(at_dep, {emp,
                     dept_id}).

    -record(in_proj, {emp,
                      proj_name}).

The structure defines six tables in the database. In `Mnesia`, the function `mnesia:create_table(Name, Opts)` creates tables. `Name` is the table name.

#### Note

The current version of `Mnesia` does not require that the name of the table is the same as the record name, see Record Names versus Table Names..

For example, the table for employees is created with the function `mnesia:create_table(employee, [{attributes, record_info(fields, employee)}])`. The table name `employee` matches the name for records specified in `Opts`. The expression `record_info(fields, RecordName)` is processed by the Erlang preprocessor and evaluates to a list containing the names of the different fields for a record.

### Program

The following shell interaction starts `Mnesia` and initializes the schema for the `Company` database:

    % erl -mnesia dir '"/ldisc/scratch/Mnesia.Company"'
    Erlang/OTP 27 [erts-15.1.2]

    Eshell V15.1.2 (press Ctrl+G to abort, type help(). for help)
    1> mnesia:create_schema([node()]).
    ok
    2> mnesia:start().
    ok

The following program module creates and populates previously defined tables:

    -include_lib("stdlib/include/qlc.hrl").
    -include("company.hrl").

    init() ->
        mnesia:create_table(employee,
                            [{attributes, record_info(fields, employee)}]),
        mnesia:create_table(dept,
                            [{attributes, record_info(fields, dept)}]),
        mnesia:create_table(project,
                            [{attributes, record_info(fields, project)}]),
        mnesia:create_table(manager, [{type, bag},
                                      {attributes, record_info(fields, manager)}]),
        mnesia:create_table(at_dep,
                             [{attributes, record_info(fields, at_dep)}]),
        mnesia:create_table(in_proj, [{type, bag},
                                      {attributes, record_info(fields, in_proj)}]).

### Program Explained

The following commands and functions are used to initiate the `Company` database:

- `% erl -mnesia dir '"/ldisc/scratch/Mnesia.Company"'`. This is a UNIX command-line entry that starts the Erlang system. The flag `-mnesia dir Dir` specifies the location of the database directory. The system responds and waits for further input with the prompt `1>`.
- `mnesia:create_schema([node()])`. This function has the format `mnesia:create_schema(DiscNodeList)` and initiates a new schema. In this example, a non-distributed system using only one node is created. Schemas are fully explained in Define a Schema.
- `mnesia:start()`. This function starts `Mnesia` and is fully explained in Start Mnesia.

Continuing the dialogue with the Erlang shell produces the following:

    3> company:init().
    {atomic,ok}
    4> mnesia:info().
    ---> Processes holding locks <---
    ---> Processes waiting for locks <---
    ---> Participant transactions <---
    ---> Coordinator transactions <---
    ---> Uncertain transactions <---
    ---> Active tables <---
    in_proj        : with 0        records occupying 305      words of mem
    at_dep         : with 0        records occupying 305      words of mem
    manager        : with 0        records occupying 305      words of mem
    project        : with 0        records occupying 305      words of mem
    dept           : with 0        records occupying 305      words of mem
    employee       : with 0        records occupying 305      words of mem
    schema         : with 7        records occupying 1136     words of mem
    ===> System info in version "4.23.2", debug level = none <===
    opt_disc. Directory "/ldisc/scratch/Mnesia.Company" is used.
    use fallback at restart = false
    running db nodes   = [nonode@nohost]
    stopped db nodes   = []
    master node tables = []
    remote             = []
    ram_copies         = [at_dep,dept,employee,in_proj,manager,project]
    disc_copies        = [schema]
    disc_only_copies   = []
    [{nonode@nohost,disc_copies}] = [schema]
    [{nonode@nohost,ram_copies}] = [employee,dept,project,manager,at_dep,in_proj]
    8 transactions committed, 0 aborted, 0 restarted, 12 logged to disc
    0 held locks, 0 in queue; 0 local transactions, 0 remote
    0 transactions waits for other nodes: []
    ok

A set of tables is created. The function `mnesia:create_table(Name, Opts)` creates the required database tables. The options available with `Opts` are explained in Create New Tables.

The function `company:init/0` creates the tables. Two tables are of type `bag`. This is the `manager` relation as well the `in_proj` relation. This is interpreted as: an employee can be manager over several departments, and an employee can participate in several projects. However, the `at_dep` relation is `set`, as an employee can only work in one department. In this data model, there are examples of relations that are 1-to-1 (`set`) and 1-to-many (`bag`).

`mnesia:info()` now indicates that a database has seven local tables, where six are the user-defined tables and one is the schema. Six transactions have been committed, as six successful transactions were run when creating the tables.

To write a function that inserts an employee record into the database, there must be an `at_dep` record and a set of `in_proj` records inserted. Examine the following code used to complete this action:

    insert_emp(Emp, DeptId, ProjNames) ->
        Ename = Emp#employee.name,
        Fun = fun() ->
                      mnesia:write(Emp),
                      AtDep = #at_dep{emp = Ename, dept_id = DeptId},
                      mnesia:write(AtDep),
                      mk_projs(Ename, ProjNames)
              end,
        mnesia:transaction(Fun).

    mk_projs(Ename, [ProjName|Tail]) ->
        mnesia:write(#in_proj{emp = Ename, proj_name = ProjName}),
        mk_projs(Ename, Tail);
    mk_projs(_, []) -> ok.

- The `insert_emp/3` arguments are as follows:
  1. `Emp` is an employee record.
  2. `DeptId` is the identity of the department where the employee works.
  3. `ProjNames` is a list of the names of the projects where the employee works.

The function `insert_emp/3` creates a Functional Object (Fun). `Fun` is passed as a single argument to the function `mnesia:transaction(Fun)`. This means that `Fun` is run as a transaction with the following properties:

- A `Fun` either succeeds or fails.
- Code that manipulates the same data records can be run concurrently without the different processes interfering with each other.

The function can be used as follows:

    Emp = #employee{emp_no = 104732,
                    name = klacke,
                    salary = 7,
                    sex = male,
                    phone = 98108,
                    room_no = {221, 015}},
    insert_emp(Emp, 'B/SFR', [Erlang, mnesia, otp]).

#### Note

For information about Funs, see "Fun Expressions" in section `Erlang Reference Manual` in System Documentation..

### Initial Database Content

After the insertion of the employee named `klacke`, the database has the following records:

emp_no | name | salary | sex | phone | room_no
--- | --- | --- | --- | --- | ---
104732 | klacke | 7 | male | 98108 | {221, 015}

Table: employee Database Record

This `employee` record has the Erlang record/tuple representation `{employee, 104732, klacke, 7, male, 98108, {221, 015}}`.

emp | dept_name
--- | ---
klacke | B/SFR

Table: at_dep Database Record

This `at_dep` record has the Erlang tuple representation `{at_dep, klacke, 'B/SFR'}`.

emp | proj_name
--- | ---
klacke | Erlang
klacke | otp
klacke | mnesia

Table: in_proj Database Record

This `in_proj` record has the Erlang tuple representation `{in_proj, klacke, 'Erlang', klacke, 'otp', klacke, 'mnesia'}`.

There is no difference between rows in a table and `Mnesia` records. Both concepts are the same and are used interchangeably throughout this User's Guide.

A `Mnesia` table is populated by `Mnesia` records. For example, the tuple `{boss, klacke, bjarne}` is a record. The second element in this tuple is the key. To identify a table uniquely, both the key and the table name is needed. The term Object Identifier (OID) is sometimes used for the arity two tuple `{Tab, Key}`. The OID for the record `{boss, klacke, bjarne}` is the arity two tuple `{boss, klacke}`. The first element of the tuple is the type of the record and the second element is the key. An OID can lead to zero, one, or more records depending on whether the table type is `set` or `bag`.

The record `{boss, klacke, bjarne}` can also be inserted. This record contains an implicit reference to another employee that does not yet exist in the database. `Mnesia` does not enforce this.

### Adding Records and Relationships to Database

After adding more records to the `Company` database, the result can be the following records:

`employees`:

    {employee, 104465, "Johnson Torbjorn",   1, male,  99184, {242,038}}.
    {employee, 107912, "Carlsson Tuula",     2, female,94556, {242,056}}.
    {employee, 114872, "Dacker Bjarne",      3, male,  99415, {221,035}}.
    {employee, 104531, "Nilsson Hans",       3, male,  99495, {222,026}}.
    {employee, 104659, "Tornkvist Torbjorn", 2, male,  99514, {222,022}}.
    {employee, 104732, "Wikstrom Claes",     2, male,  99586, {221,015}}.
    {employee, 117716, "Fedoriw Anna",       1, female,99143, {221,031}}.
    {employee, 115018, "Mattsson Hakan",     3, male,  99251, {203,348}}.

`dept`:

    {dept, 'B/SF',  "Open Telecom Platform"}.
    {dept, 'B/SFP', "OTP - Product Development"}.
    {dept, 'B/SFR', "Computer Science Laboratory"}.

`projects`:

    %% projects
    {project, erlang, 1}.
    {project, otp, 2}.
    {project, beam, 3}.
    {project, mnesia, 5}.
    {project, wolf, 6}.
    {project, documentation, 7}.
    {project, www, 8}.

These three tables, `employees`, `dept`, and `projects`, are made up of real records. The following database content is stored in the tables and is built on relationships. These tables are `manager`, `at_dep`, and `in_proj`.

`manager`:

    {manager, 104465, 'B/SF'}.
    {manager, 104465, 'B/SFP'}.
    {manager, 114872, 'B/SFR'}.

`at_dep`:

    {at_dep, 104465, 'B/SF'}.
    {at_dep, 107912, 'B/SF'}.
    {at_dep, 114872, 'B/SFR'}.
    {at_dep, 104531, 'B/SFR'}.
    {at_dep, 104659, 'B/SFR'}.
    {at_dep, 104732, 'B/SFR'}.
    {at_dep, 117716, 'B/SFP'}.
    {at_dep, 115018, 'B/SFP'}.

`in_proj`:

    {in_proj, 104465, otp}.
    {in_proj, 107912, otp}.
    {in_proj, 114872, otp}.
    {in_proj, 104531, otp}.
    {in_proj, 104531, mnesia}.
    {in_proj, 104545, wolf}.
    {in_proj, 104659, otp}.
    {in_proj, 104659, wolf}.
    {in_proj, 104732, otp}.
    {in_proj, 104732, mnesia}.
    {in_proj, 104732, erlang}.
    {in_proj, 117716, otp}.
    {in_proj, 117716, documentation}.
    {in_proj, 115018, otp}.
    {in_proj, 115018, mnesia}.

The room number is an attribute of the employee record. This is a structured attribute that consists of a tuple. The first element of the tuple identifies a corridor, and the second element identifies the room in that corridor. An alternative is to represent this as a record `-record(room, {corr, no}).` instead of an anonymous tuple representation.

The `Company` database is now initialized and contains data.

### Writing Queries

Retrieving data from DBMS is usually to be done with the functions `mnesia:read/3` or `mnesia:read/1`. The following function raises the salary:

    raise(Eno, Raise) ->
        F = fun() ->
                    [E] = mnesia:read(employee, Eno, write),
                    Salary = E#employee.salary + Raise,
                    New = E#employee{salary = Salary},
                    mnesia:write(New)
            end,
        mnesia:transaction(F).

Since it is desired to update the record using the function `mnesia:write/1` after the salary has been increased, a write lock (third argument to `read`) is acquired when the record from the table is read.

To read the values from the table directly is not always possible. It can be needed to search one or more tables to get the wanted data, and this is done by writing database queries. Queries are always more expensive operations than direct lookups done with `mnesia:read/1`. Therefore, avoid queries in performance-critical code.

Two methods are available for writing database queries:

- `Mnesia` functions
- QLC

#### Using Mnesia Functions

The following function extracts the names of the female employees stored in the database:

    mnesia:select(employee, [{#employee{sex = female, name = '$1', _ = '_'},[], ['$1']}]).

`select` must always run within an activity, such as a transaction. The following function can be constructed to call from the shell:

    all_females() ->
        F = fun() ->
                    Female = #employee{sex = female, name = '$1', _ = '_'},
                    mnesia:select(employee, [{Female, [], ['$1']}])
            end,
        mnesia:transaction(F).

The `select` expression matches all entries in table employee with the field `sex` set to `female`.

This function can be called from the shell as follows:

    (klacke@gin)1> company:all_females().
    {atomic, ["Carlsson Tuula", "Fedoriw Anna"]}

For a description of `select` and its syntax, see Pattern Matching.

#### Using QLC

This section contains simple introductory examples only. For a full description of the QLC query language, see the `qlc` manual page in `STDLIB`.

Using QLC can be more expensive than using `Mnesia` functions directly but offers a nice syntax.

The following function extracts a list of female employees from the database:

    Q = qlc:q([E#employee.name || E <- mnesia:table(employee),
                                  E#employee.sex == female]),
    qlc:e(Q),

Accessing `Mnesia` tables from a QLC list comprehension must always be done within a transaction. Consider the following function:

    females() ->
        F = fun() ->
                    Q = qlc:q([E#employee.name || E <- mnesia:table(employee),
                                                  E#employee.sex == female]),
                    qlc:e(Q)
            end,
        mnesia:transaction(F).

This function can be called from the shell as follows:

    (klacke@gin)1> company:females().
    {atomic, ["Carlsson Tuula", "Fedoriw Anna"]}

In traditional relational database terminology, this operation is called a selection, followed by a projection.

The previous list comprehension expression contains a number of syntactical elements:

- The first `[` bracket is read as "build the list".
- The `||` "such that" and the arrow `<-` is read as "taken from".

Hence, the previous list comprehension demonstrates the formation of the list `E#employee.name` such that `E` is taken from the table of employees, and attribute `sex` of each record is equal to the atom `female`.

The whole list comprehension must be given to the function `qlc:q/1`.

List comprehensions with low-level `Mnesia` functions can be combined in the same transaction. To raise the salary of all female employees, execute the following:

    raise_females(Amount) ->
        F = fun() ->
                    Q = qlc:q([E || E <- mnesia:table(employee),
                                    E#employee.sex == female]),
                    Fs = qlc:e(Q),
                    over_write(Fs, Amount)
            end,
        mnesia:transaction(F).

    over_write([E|Tail], Amount) ->
        Salary = E#employee.salary + Amount,
        New = E#employee{salary = Salary},
        mnesia:write(New),
        1 + over_write(Tail, Amount);
    over_write([], _) ->
        0.

The function `raise_females/1` returns the tuple `{atomic, Number}`, where `Number` is the number of female employees who received a salary increase. If an error occurs, the value `{aborted, Reason}` is returned, and `Mnesia` guarantees that the salary is not raised for any employee.

Example:

    33> company:raise_females(33).
    {atomic,2}

← Previous Page Overview
Next Page → Build a Mnesia Database