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.

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.

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.