Persist introspection

Bal persist can work with existing databases by providing the facility to introspect an existing database to generate the persist data model.

Introspection lets you use bal persist with existing databases without having to write a data model. Currently, introspection is supported for MySQL, MSSQL and PostgreSQL databases and new data stores will be supported in the future. Advanced SQL annotations are used to annotate names, special types, declare generated fields, custom foreign keys, and indexes etc.

During introspection, all table and column names which does not conform to Ballerina naming conventions will be converted to conforming names and their original names will be mapped with the @sql:Name annotation.

Type Mapping

The below table shows how SQL types are mapped into Ballerina types. Development efforts are underway to support more SQL types.

The types that are partially supported will be fully supported later along with new annotations to better represent their specific SQL types. However, they can still be used in queries. The unsupported types will also be supported in the future.

SQL TypeBallerina TypeAnnotationSupported
BOOLEANboolean-Yes
TINYINT(1) boolean-Yes
INTint-Yes
INTEGERint-Yes
DECIMAL(65,30)decimal-Yes
DECIMAL(X,Y)decimal@sql:DecimalYes
DOUBLEfloat-Yes
FLOATfloat-Yes
TIMEtime:TimeOfDay-Yes
TIMESTAMPtime:Utc-Yes
DATEtime:Date-Yes
DATETIMEtime:Civil-Yes
VARCHAR(191)string-Yes
VARCHAR(X)string@sql:VarcharYes
CHAR(X)string@sql:CharYes
BLOBbyte[]-Yes
ENUMenum-Yes
TINYINT(X)int-Partial
SMALLINTint-Partial
MEDIUMINTint-Partial
BIGINTint-Partial
TEXTstring-Partial
TINYTEXTstring-Partial
MEDIUMTEXTstring-Partial
LONGTEXTstring-Partial
TINYBLOBbyte[]-Partial
MEDIUMBLOBbyte[]-Partial
LONGBLOBbyte[]-Partial
VARBINARYbyte[]-Partial
BINARYbyte[]-Partial
JSON--No
GEOMETRY--No
SET--No

Limitations

  • Introspection currently supports for MySQL, MSSQL and PostgreSQL data stores
  • Cross-referring relations, where foreign keys are found in both sides of a couple of associated entities are not supported
  • Foreign keys occurring from unique keys are not supported
  • Some data types are unsupported, in which case the client API for those entities are not generated. However, you can still perform join queries that include those relations, without unsupported fields.
  • Bal Persist only supports auto-increment generation strategy with the @sql:Generated annotation
  • Introspection ignores the order of columns in composite indexes and annotates index fields with @sql:Index or @sql:UniqueIndex annotation without considering the column order