Module persist.sql
![](https://bcentral-packageicons.azureedge.net/images/ballerinax_persist.sql_1.0.0.png)
ballerinax/persist.sql Ballerina library
Module Overview
This module provides relation database support for the bal persist
feature, which provides functionality to store and query data from a relational database conveniently through a data model instead of using SQL query language.
Currently, this package supports only MySQL as the relational database. However, you are planning to add support for other relational databases such as PostgreSQL, Oracle, and Microsoft SQL Server.
The MySQL data store is a relational database management system that stores data in tables. The MySQL data store is useful for storing data in a relational format. The MySQL data store is not the default data store for the bal persist
feature. Therefore, you need to explicitly specify the data store when initializing bal persist
in your application. as follows,
$ bal persist init --datastore mysql
Supported Ballerina Types
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 |
If you want to map a Ballerina type to a different SQL type or want to change the default length of a SQL type, you can change it in the script.sql
file generated by the bal persist generate
command before executing the script.
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 MySQL server. |
port | The port of the MySQL server. |
username | The username of the MySQL server. |
password | The password of the MySQL 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 is generated by the bal persist generate
command.
[<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 MySQL data store. Please refer to the MySQL Connector documentation for more information on these parameters.
How to Setup
Set up a MySQL server instance
Select one of the methods below to set up a MySQL server.
Tip: Keep the connection and authentication details for connecting to the MySQL server including the hostname, port, username, and password noted down.
- Install a MySQL server on your machine locally by downloading and installing MySQL for different platforms.
- Use a cross-platform web-server solution such as XAMPP or WampServer.
- Use Docker to create a MySQL server deployment.
- Use a cloud-based MySQL solution such as Google’s CloudSQL, Amazon’s RDS for MySQL, or Microsoft’s Azure Database for MySQL.
Run the script to create the database and tables
The bal persist generate
command generates a script.sql file in the generated directory of your project. This file contains the SQL script to create the tables required for your application. You need to run this script to create the database and tables in the MySQL server using a MySQL client such as MySQL Workbench or the MySQL command line client.