Module persist.sql
![](https://bcentral-packageicons.azureedge.net/images/ballerinax_persist.sql_1.3.0.png)
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, 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
-
Initialize
bal persist
and integrate tobal build
using the following command,$ bal persist add --datastore [mysql/mssql/postgresql] --module <module_name>
-
After defining the entities, build the application using the following command,
$ bal build
One time generation
-
Initialize
bal persist
using the following command,$ bal persist init
-
Generate the persist client using the following command,
$ bal persist generate --datastore [mysql/mssql/postgresql] --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.
Ballerina Type | SQL Type |
---|---|
int | INT |
float | DOUBLE |
decimal | DECIMAL(65,30) |
string | VARCHAR(191) |
boolean | BOOLEAN |
byte[] | LONGBLOB |
() | NULL |
time:Date | DATE |
time:TimeOfDay | TIME |
time:Utc | TIMESTAMP |
time:Civil | DATETIME |
enum | ENUM |
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.
Ballerina type | SQL type |
---|---|
int | INT |
float | FLOAT |
decimal | DECIMAL(38,30) |
string | VARCHAR(191) |
boolean | BIT |
byte[] | VARBINARY(MAX) |
() | NULL |
time:Date | DATE |
time:TimeOfDay | TIME |
time:Utc | DATETIME2 |
time:Civil | DATETIME2 |
enum | VARCHAR with checks. |
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.
Ballerina type | SQL type |
---|---|
int | INT |
float | FLOAT |
decimal | DECIMAL(65,30) |
string | VARCHAR(191) |
boolean | BOOLEAN |
byte[] | BYTEA |
() | NULL |
time:Date | DATE |
time:TimeOfDay | TIME |
time:Utc | TIMESTAMP |
time:Civil | TIMESTAMP |
enum | VARCHAR with checks. |
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.
Data store | Implementation |
---|---|
MySQL | AUTO_INCREMENT |
MSSQL | IDENTITY(1,1) |
PostgreSQL | SERIAL |
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.
Parameter | Description |
---|---|
host | The hostname of the DB server. |
port | The port of the DB server. |
username | The username of the DB server. |
password | The password of the DB server. |
database | The name of the database to be used. |
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.
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.