ballerinax/persist.sql Ballerina library

Overview

This package provides relational database support for the bal persist feature, which provides functionality to store and query data from a relational database through a data model instead of writing SQL.

Currently, this package supports MySQL, MSSQL, H2 and PostgreSQL databases. However, we are also planning to add support for other relational databases such as Oracle.

How to use with bal persist

By default, bal persist utilizes the in-memory data store. Therefore, you must explicitly specify an SQL data store as follows:

Integrate to bal build

1. Initialize bal persist and integrate to bal build using the following command,

   $ bal persist add --datastore [mysql/mssql/postgresql/h2] --module <module_name>
   

2. After defining the entities, build the application using the following command,

   $ bal build
   

One time generation

1. Initialize bal persist using the following command,

   $ bal persist init
   

2. Generate the persist client using the following command,

   $ bal persist generate --datastore [mysql/mssql/postgresql/h2] --module <module_name>
   

Supported Ballerina Types

MySQL

The following table lists the Ballerina types supported by the MySQL data store and the corresponding SQL types used to store the data in the database.

MSSQL

The following table lists the Ballerina types supported by the MSSQL data store and the corresponding SQL types used to store the data in the database.

PostgreSQL

The following table lists the Ballerina types supported by the PostgreSQL data store and the corresponding SQL types used to store the data in the database.

The default length for some SQL types can be changed using the Advanced SQL type annotations. If you want even more control over the SQL types, you can change the SQL types in the script.sql file generated by the bal build or the persist generate command before executing the script. It may not work for all the types.

Advanced SQL annotations

To have a custom name and type mappings in the database implementation and to declare indexes, generated fields, and custom foreign keys, the below annotations can be used in the data model definition. Note that these annotations can only be used with SQL data stores.

In order to use them, you must first import the persist.sql package to your data model definition file as follows.

import ballerinax/persist.sql;

Name mapping with Name annotation

• Map entity name to table name

@sql:Name {value: "people"}
type Person record {|
    readonly int id;
    string name;
    string address;
|};

The Person entity will be mapped to the people table in the database.

• Map field name to column name

type Person record {|
    @sql:Name {value: "person_id"}
    readonly int id;
    @sql:Name {value: "full_name"}
    string name;
    string address;
|};

The id field will be mapped to the person_id column in the database, and the name field will be mapped to the full_name column in the database.

Type mapping

Varchar annotation

type Person record {|
    readonly int id;
    @sql:Varchar {length: 100}
    string name;
    string address;
|};

The name field will have a VARCHAR(100) column in the database. The @sql:Varchar annotation can only be used on string fields.

Char annotation

type Person record {|
    @sql:Char {length: 12}
    readonly string nic;
    string name;
    string address;
|};

The nic field will have a CHAR(12) column in the database. The @sql:Char annotation can only be used on string fields.

Decimal annotation

type Person record {|
    readonly int id;
    @sql:Decimal {precision: [10,2]}
    decimal salary;
    string address;
|};

The salary field will have a DECIMAL(10,2) column in the database. The @sql:Decimal annotation can only be used on decimal fields.

Declare indexes

Index annotation

This annotation lets you define an index on a specific field. Optionally, you can provide an index name as a string or a set of index names as a string[] in situations where a single field takes part in multiple indexes. A composite index can be created by repeating the same index name in multiple fields. In this case, the order of index columns will be the order in which the respective fields are declared in the record type.

type Person record {|
    @sql:Index {name: "idx_name"}
    readonly int id;
    @sql:Index {name: ["idx_name", "idx_another"]}
    string name;
    @sql:Index
    string address;
|};

The index idx_name is a composite index consisting of id and name fields. idx_another is just another index on the name field. The address field also has the @sql:Index annotation without the name property. Here, the index name will be generated by persist in idx_[FIELD_NAME] format, in which case the index name for the address field will become idx_address.

UniqueIndex annotation

This annotation lets you define a unique index on a specific field, and the usage is similar to the @sql:Index annotation.

type Person record {|
    readonly int id;
    @sql:UniqueIndex {name: "idx_person"}
    string nic;
    @sql:UniqueIndex {name: ["idx_person", "idx_another"]}
    string name;
    @sql:UniqueIndex
    string address;
|};

The unique index idx_person is a composite unique index consisting of nic and name fields. idx_another is just another unique index on the name field. The address field also has the @sql:UniqueIndex annotation without the name property. Here, the index name will be generated by persist in the unique_idx_[FIELD_NAME] format, in which case the index name for the address field becomes unique_idx_address.

Declare generated fields with Generated annotation

The @sql:Generated annotation is used to declare a field as a generated field. This annotation can only be used on readonly int fields. Currently, only the AUTO_INCREMENT or an equivalent generation strategy is supported.

type Person record {|
    @sql:Generated
    readonly int id;
    string name;
    string address;
|};

The id field will be auto-generated and the PersonInsert type will not have the id field as it should be auto-generated by the database. Refer the below table to understand how the generation strategy is implemented in different data stores.

Declare custom foreign keys with Relation annotation

The @sql:Relation annotation can be used to declare your own custom foreign key field. You must put the foreign key on the correct side of the relationship (owner) and the key field must exist on the record type, and must be of the same type as the primary key of the referred entity. This is particularly useful when a foreign key is also a part of the composite primary key.

type Car record {|
   readonly int id;
   string name;
   int userId;
   @sql:Relation {keys: ["userId"]}
   User owner;
|};

type User record {|
   readonly int id;
   string name;
   Car[] cars;
|};

The keys field accepts an array of foreign keys and the length of it must be the same as the number of primary keys the referring entity has. Here, the field userId is used as the foreign key for the relation owner and it has been declared so through the @sql:Relation annotation. When the @sql:Relation annotation is used in a relation field, the foreign key will not be generated by default.

Configuration

You need to set values for the following basic configuration parameters in the Config.toml file in your project to use the MySQL data store.

The following is a sample Config.toml file with the MySQL data store configuration. This will be generated by the bal build or bal persist generate command (depending on how you generate the client API).

[<packageName>.<moduleName>]
host = "localhost"
port = 3306
user = "root"
password = ""
database = ""

Additionally, you can set values for advanced configuration parameters in the Config.toml file in your project to use the data store. Please refer to the individual connection documentation for more information on these parameters.

• MySQL Connector documentation
• MSSQL Connector documentation
• PostgreSQL Connector documentation

How to Setup

Set up a MySQL/MSSQL/PostgreSQL server instance

Select one of the methods below to set up a DB server.

Tip: Keep the connection and authentication details for connecting to the DB server including the hostname, port, username, and password noted down.

• Install a DB server on your machine locally by downloading and installing the server software for different platforms.
• Use a cross-platform web-server solution such as XAMPP or WampServer.
• Use Docker to create a DB server deployment.
• Use a cloud-based DB solution such as Google’s CloudSQL, Amazon’s RDS, or Microsoft’s Azure Database.

Generate the client API

You can generate the client API through either of the ways outlined in the How to use with bal persist section.