Supported data stores

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.

Copy
$ bal persist init --datastore mysql

It is recorded in the Ballerina.toml file in your project as follows.

Copy
[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.

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.

Copy
$ 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 typeSQL type
intINT
floatDOUBLE
decimalDECIMAL(65,30)
stringVARCHAR(191)
booleanBOOLEAN
byte[]LONGBLOB
()NULL
time:DateDATE
time:TimeOfDayTIME
time:UtcTIMESTAMP
time:CivilDATETIME
enumENUM

MSSQL

Ballerina typeSQL type
intINT
floatFLOAT
decimalDECIMAL(38, 30)
stringVARCHAR(191)
booleanBIT
byte[]VARBINARY(MAX)
()NULL
time:DateDATE
time:TimeOfDayTIME
time:UtcDATETIME2
time:CivilDATETIME2
enumVARCHAR 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.

ParameterDescription
hostThe hostname of the DB server.
portThe port of the DB server.
usernameThe username of the DB server.
passwordThe password of the DB server.
databaseThe name of the database to be used.

The following is a sample Config.toml file generated for each relational data store.

MySQL

Copy
[<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

Copy
[<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.

Copy
$ 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 TypeGoogle Sheets Type
intNUMBER
floatNUMBER
decimalNUMBER
stringSTRING
booleanBOOLEAN
time:DateSTRING
time:TimeOfDaySTRING
time:UtcSTRING
time:CivilSTRING

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.

ParameterDescription
clientIdThe client ID of the Google Sheets API.
clientSecretThe client secret of the Google Sheets API.
refreshTokenThe refresh token of the Google Sheets API.
spreadsheetIdThe 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.

Copy
[<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.

  1. Go to the respective spreadsheet.
  2. Open the AppScript console from the menu item Extensions - > Apps Script.
  3. Copy the content of the script.gs file into the console.
  4. Click Deploy to deploy the project as a web application.
  5. Click Run to execute the selected function.

How to obtain Google API tokens

Follow the steps below to obtain API tokens for Google Sheets.

  1. Get the clientID and ClientSecret by following the guidelines.

  2. 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.

    1. Go to the OAuth 2.0 playground and click Settings on the top right-hand side.
    2. Select Use your own OAuth credentials and provide the client ID and client secret obtained.
    3. For step 1 under the Select & authorize APIs section, select the required scopes for the API you are going to use.
    4. Proceed with the necessary Google account validation steps.
    5. Click Exchange authorization code for tokens to obtain the refresh and access tokens.
Previous