Improve DB columns to Ballerina record Mapping through Annotation

[niveathika]

Description:

With the current design, the db columns must be matched with the Ballerina record for a select query.

Database Column	Ballerina Record
create table ATS_PERSON ( id int auto_increment, first_name varchar(100) not null, last_name varchar(200) not null, constraint ATS_PERSON_pk primary key (id) );	type Person record { int id; string first_name; string last_name };

In order to change the field names in the Ballerina record,

1. The returned record must be processed once again to transform the record to the needed field names
2. The sql query must be updated to include AS clause such as, select id, first_name as firstName, last_name as lastName from ATS_PERSON

Annotation can be used to support the mapping of the DB columns to the Ballerina record. This will give value addition to the users.

[niveathika]

Proposal: Support Annotations for DB column to Ballerina Record Mapping

Summary

Enhance query() method with the use of Annotations in the return record types to map database columns.

type Person record {
    int id;
    string first_name;
    string last_name
};
Stream<Person, sql:Error?> persons = database->query(`SELECT is, first_name, last_name FROM ATS_PERSON`);

Annotations will be bound to Person record fields to map value from a column of a different name.

History

The 1.2.x version of SQL packages included mapping support. However, the mapping was limited to only the order of returned columns to the record fields. The package will map The first column of the result will be mapped to the first field in the records.

This feature held a few setbacks. Since the user now has to keep the order even if the mapping is not needed.

Goals

• Support annotations in the sql package
• Support field mapping of DB columns to record fields.

Motivation

The ability to map table columns to Ballerina record fields in the remote function is efficient. In typical enterprise use cases, it is typical that the user would not have control over the database schema and its naming convention.

With this support, the user does not need to process the return results to another record or change the query itself to change the metadata.

Description

Introduce sql:Column annotation of string type. The user can provide the database column name in the annotation.

type Person record {
    int id;
    @sql:Column { name: "first_name" }
    string firstName;
    @sql:Column { name: "last_name" }
    string lastName
};

The above annotation will map the database column first_name to the Ballerina record field firstName. If the query() function does not return first_name column, the field will not be populated.

The annotation will be defined as,

# Defines the database column name which matches the record field. The default value is the record field name.
#
# + name - The database column name 
public type ColumnConfig record {|
    string name;
|};

# The Annotation used to specify which database column matches the record field.
public annotation ColumnConfig Column on record field;

The Column annotation's keys' will be defined by the ColumnConfig closed record, which allows only name field.

[niveathika]

Opened a spec issue to request support for string type annotation. With that annotation support will be simplified as,

type Person record {
    int id;
    @sql:Column { "first_name" }
    string firstName;
    @sql:Column { "last_name" }
    string lastName
};

[IMS94]

IMO, having the annotation as

@sql:Column { name = "first_name" }

will be better. Because in future we can then add more fields to the annotation. Like column data type, required or not, unique or not, etc. With such information, we can improve this to generate database tables as in Hibernate. Therefore, it's better to keep room for such improvements with an annotation like the one above IMO. WDYT?

[daneshk]

Thanks, @IMS94 for the input.

AFAIU The difference between JPA/HIbernate entities and Ballerina records is, in Hibernate, use for all SQL operations and it is a direct mapping for SQL Tables. But in Ballerina records use only for querying operations to map results to the record value. So most of the properties like data types, unique, nullable are not applicable for Ballerina. We will check further

@niveathika are there any properties that can be included, in the annotation.

[niveathika]

@IMS94 Thank you for the suggestion.

However, as @daneshk mentioned, these will not be applicable as we are not doing any operations which accepts Ballerina record for SQL operations.

I agree likewise to Hiberanate, it would be cool, if we can support database operations, with only Ballerina code.(Currently we are exploring this option to come up with a plan 😄 ). Even if we support this, IMO that support should be on top sql package and not included with sql package, else that will be too restricting.

[IMS94]

@daneshk @niveathika Agreed that a library like Hibernate should come on top of sql package. Just wanted to suggest to keep room for such a thing at the annotation level. For example:

At current version (say 1.0) this is how the annotation will look like:

@sql:Column { name = "first_name" }

In the next minor version (Say 1.1) we decide to add support for data type. Then the annotation will look like this:

@sql:Column { name = "first_name", dataType: "INT" }

Rather than introducing a new annotation to add additional details, we could modify the existing sql:Column annotation. If we used @sql:Column { "last_name" } from the beginning, IMO we will lose that room for evolution.

Not sure if my argument is correct when considering backward compatibility. Just wanted to convey the idea 🙂

[niveathika]

@IMS94 My argument is, if the improvements are in different package should that be included in sql package? This will anyway be upto the individual packages for different connectors. This will not be a common functionality and should not be included in sql package.

@sql:Column{"first_name"}
@mysqlUtil:Column{ dataType = INT }

Anyways Ballerina Can only keep forward compatibility not backward compatibility on coming releases. i.e Any new functionality will never be supported by older versions.

[IMS94]

@niveathika IMO, having to use 2 annotations for a column definition is not good for developer experience. I'm suggesting to make this annotation similar to JPA (java persistence API) Column (https://github.com/javaee/jpa-spec/blob/master/javax.persistence-api/src/main/java/javax/persistence/Column.java). JPA Column annotation is DB implementation independent too, but can hold several generic details. Hibernate is just one implementation of JPA.

If we can make an annotation similar to JPA's Column that's better. If not, I'm ok with writing a separate package + annotations to achieve that.

[niveathika]

All we would be continuing with using name key in the annotation as language does not support string type annotations.
@sql:Column{ name = "first_name"}

[BuddhiWathsala]

Why can’t we have this support from the language side?. We even have a similar requirement in gRPC too.

Let's say we add a similar kind of annotations for gRPC records too. Then, when someone develops a Ballerina app using gRPC and SQL has to maintain several annotations that would reduce the maintainability of the app.

[niveathika]

@BuddhiWathsala +1 for a language common annotation to indicate field mapping.

Could you open a spec issue for this? We can depreciate this once language annotation is live.

[daneshk]

One problem I see is requirements will get changed from module to module. For example, the SQL module needs to map the column name to a different field name. But in the gRPC module, we need to attach field index to the field. If we define it at the language level, we need an annotation record that supports both. Some fields may apply only to specific modules.

[daneshk]

@sameerajayasoma Please review and give your input

[sameerajayasoma]

I also believe that we shouldn't mix ORM-ish concepts at this level. Let's stick to the name field for now.

By the way, when we have an ORM-ish solution for Ballerina, the direct sql package usage will be significantly less, IMO.

[sameerajayasoma]

@niveathika @daneshk Can you please update the proposal with the definition of sql:Column annotation?

[niveathika]

@sameerajayasoma Updated the proposal

[sameerajayasoma]

The design looks good to me. Can you please take another shot at code documentation?

[niveathika]

@sameerajayasoma Tweaked the documentation as follows,

# Defines the database column name which matches the record field. The default value is the record field name.
#
# + name - The database column name 
public type ColumnConfig record {|
    string name;
|};

# The Annotation used to specify which database column matches the record field.
public annotation ColumnConfig Column on record field;

[sameerajayasoma]

LGTM