In the realm of the bal persist
feature, selecting the right data store is crucial for ensuring reliable data storage and retrieval. Various data stores exist, each with its strengths and weaknesses. The bal persist
currently supports four data stores. The configurations and supported types will vary depending on the data store you selected for your application.
Following are the supported data stores.
- In-memory
- Relational databases (MySQL, MSSQL)
- Google Sheets [Experimental]
The data store is passed as a parameter to the persist init
command. If you do not specify a data store, the in-memory data store is used by default.
$ bal persist init --datastore mysql
It is recorded in the Ballerina.toml
file in your project as follows.
[persist] datastore = mysql ...
The following sections describe the configurations and supported types for each data store.
In-memory
The in-memory
data store is a simple data store that stores data in memory. This data store is useful for testing purposes. It is the default data store for bal persist
. Therefore, you do not need to explicitly specify the data store when you are using it.
Supported Ballerina types
The in-memory
data store supports the following Ballerina types.
any
error
Configuration
The In-Memory data store does not require any configuration.
Relational Databases (MySQL, MSSQL)
The data store is a relational database management system that stores data in tables. It is useful for storing data in a relational format. It is not the default data store for bal persist
. Therefore, you need to specify the data store explicitly when initializing bal persist
in your application as follows.
$ bal persist init --datastore [mysql/mssql]
Supported Ballerina types
The supported types for the relational data store are different from one database to another. The following table lists the Ballerina types supported by the data store and the corresponding SQL types that are used to store the data in the database.
MySQL
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
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. |
This feature doesn't support mapping Ballerina types to different SQL types or changing the default length of an SQL type. If you want to map a Ballerina type to an SQL type or change the default length of an SQL type, you can change it in the script.sql
file generated by the persist generate
command before executing the script. It may not work for all the types.
Configuration
You need to set values for the following basic configuration parameters in the Config.toml
file in your project to use the relational 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 generated for each relational data store.
MySQL
[<packageName>.<moduleName>] host = "localhost" port = 3306 user = "root" password = "" database = ""
Additionally, you can set values for the following advanced configuration parameters in the Config.toml
file in your project to use the MySQL data store. For more information on these parameters, see the MySQL Connector documentation.
MSSQL
[<packageName>.<moduleName>] host = "localhost" port = 1433 user = "sa" password = "" database = ""
Additionally, you can set values for the following advanced configuration parameters in the Config.toml
file in your project to use the MSSQL data store. For more information on these parameters, see the MSSQL Connector documentation.
How to set up
Set up a DB server instance
Select one of the methods below to set up a DB server.
Note: Keep the connection and authentication details for connecting to the DB server including the hostname, port, username, and password saved to be used later.
- Install a DB server on your machine locally by downloading and installing it based on your development platform.
- 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.
Run the script to create the database and tables
The 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 create a database and run this script to create tables in the DB server using a DB client.
Google Sheets [Experimental]
The Google Sheets data store is a cloud-based spreadsheet application that stores data in tables. It is useful for storing data in a spreadsheet format. It is not the default data store for bal persist
. Therefore, you need to explicitly specify the data store when initializing bal persist
in your application as follows.
$ bal persist init --datastore googlesheets
Supported Ballerina types
The following table lists the Ballerina types supported by the Google Sheets data store and the corresponding Google Sheets types used to store the data in the spreadsheet.
Ballerina Type | Google Sheets Type |
---|---|
int | NUMBER |
float | NUMBER |
decimal | NUMBER |
string | STRING |
boolean | BOOLEAN |
time:Date | STRING |
time:TimeOfDay | STRING |
time:Utc | STRING |
time:Civil | STRING |
Configuration
You need to set values for the following basic configuration parameters in the Config.toml
file in your project to use the Google Sheets data store.
Parameter | Description |
---|---|
clientId | The client ID of the Google Sheets API. |
clientSecret | The client secret of the Google Sheets API. |
refreshToken | The refresh token of the Google Sheets API. |
spreadsheetId | The ID of the spreadsheet to be used. |
The following is a sample Config.toml
file with the Google Sheets data store configuration. This is generated by the persist generate
command.
[<packageName>.<moduleName>] spreadsheetId = "" clientId = "" clientSecret = "" refreshToken = ""
Please refer to the Google API documentation for more information on how to obtain the client ID, client secret, and refresh token.
How to set up
How to run script.gs
in the worksheet
The script.gs
file generated from the persist generate
command can initiate the Google Sheets client. Follow the steps below to execute this file in the Google Apps Script console.
- Go to the respective spreadsheet.
- Open the AppScript console from the menu item
Extensions - > Apps Script
. - Copy the content of the
script.gs
file into the console. - Click Deploy to deploy the project as a web application.
- Click Run to execute the selected function.
How to obtain Google API tokens
Follow the steps below to obtain API tokens for Google Sheets.
-
Get the
clientID
andClientSecret
by following the guidelines. -
Follow the steps below to add https://developers.google.com/oauthplayground if you want to use OAuth 2.0 playground to receive the authorization code, access token, and refresh token.
- Go to the OAuth 2.0 playground and click Settings on the top right-hand side.
- Select Use your own OAuth credentials and provide the client ID and client secret obtained.
- For step 1 under the
Select & authorize APIs
section, select the required scopes for the API you are going to use. - Proceed with the necessary Google account validation steps.
- Click Exchange authorization code for tokens to obtain the refresh and access tokens.