Introduction to Ballerina Tables

In Ballerina, table is a data type that organizes information in rows and columns. This is similar to tables in typical databases, but there are differences in how we create tables in Ballerina.

If we consider how we create the tables and how they are populated, ballerina tables can be categorized in to three categories. They are,

• Cursor based tables
• In memory tables
• Proxy tables

In this post, I will go through the details of each of these table types. The sample codes are based on ballerina 0.982.0 version.

Cursor based tables

• These tables are returned from ballerina database connector operations such as Select or Call. These tables represent a result of a actual database select query operation or result set returned by a stored procedure call.

• When it is created, the table actually does not contain all the data, but it contains a cursor to the actual data in DB. So when that table is accessed later it will fetch the data row by row from the DB.

Automatic close of tables

• The returned table t1 contains a pointer to the data in actual DB. So internally it has a corresponding DB connection associated with the it. The data get loaded to memory row by row only when the t1 is accessed. All these resources like db connections are automatically get released if the t1’s data is fully consumed (ex: either using a loop or or any other operation which access the all data rows).
• Otherwise (if the t1 is partially iterated or not accessed at all) developer has to close the table manually to release the internal resources using the table close method as follows. Ex: Lets say result set has 5 rows. But developer stopped the loop after the 3rd record.

• If the t1.close() in line 33 is commented out, above program will give following runtime error.

Data Streaming with tables

• Any Ballerina table can be converted to a json or xml. But if a cursor based table is converted, it actually does not contain the data until that converted payload get accessed.
• The cursor table to xml/json conversion is resulted in streamed xml/json data. With the data streaming functionality, when a service client makes a request, the result is streamed to the service client rather than building the full result in the server and returning it. This allows virtually unlimited payload sizes in the result, and the response is instantaneous to the client. There the result set corresponding to a particular query is converted to XML/JSON row by row and written to the wire as the conversion takes place upon a row. So this allows to effectively stream out even multiple giga bytes of data in a table without any issue.
• Ex: Access the data in below sample with http://localhost:9090/data/student will load data from the DB row by row.

Load all data to memory

When creating a table via SQL connector select action, there is an option name “loadToMemory” to load all the data into the memory. With that option, it will return a table which has all the data loaded and all the underline connections to DB have released back to the pool when it is created. So it is no longer a cursor based table and automatic or explicit table close after accessing data is not required here. This can be used if the loaded data set is small. Otherwise this can lead to out of memory issues if large data set is loaded.

In Memory tables

• In memory tables allows ballerina developers to create tables which adheres to a defined set of columns and manipulate data in it. These data stores in memory and no physical database is associates with that.

Proxy tables

Proxy table acts as a proxy between actual database table and a ballerina table. Proxy table can be created using the getProxyTable operation of ballerina database connectors. Proxy tables allows to view/update/delete the data in actual database table directly via the returned proxy table.

More samples on the tables can be found in ballerina examples.