Skip to main content

Collection Types

Some databases support collection types for storing multiple values in a single column.

Database Support​

DatabaseMechanismNotes
PostgreSQLNative arrays (integer[], text[])First-class support
OracleNested Tables, VARRAYsCollection types
DuckDBLIST, MAPModern collection types
SQL ServerTable-valued parametersProcedure parameters only
MariaDB/MySQLJSON arraysNo native arrays
DB2-No native collections
SQLite-No native collections

PostgreSQL Arrays​

PostgreSQL has native array support for any scalar type. Arrays are mapped to Java/Kotlin/Scala arrays:

EmployeeRow
  Optional<List<String>> skills,
  Optional<List<String>> certifications,
  Optional<Jsonb> metadata,
  Optional<Map<String, String>> settings,
  Optional<List<Boolean>> workSchedule,

Supported array types include:

  • text[] → List<String>
  • int4[] → List<Integer>
  • boolean[] → List<Boolean>
  • Any other scalar type

Oracle VARRAYs​

Oracle VARRAYs are fixed-size arrays defined as named types:

CREATE TYPE skills_array AS VARRAY(10) OF VARCHAR2(100);

Typr generates a wrapper record:

SkillsArray
/** Oracle VARRAY Type: showcase.skills_array (max size: 10) */
public record SkillsArray(String[] value) {
  public SkillsArray withValue(String[] value) {
    return new SkillsArray(value);
  }

  public static OracleType<SkillsArray> oracleType = OracleVArray.of("skills_array", 10, OracleTypes.varchar2).to(Bijection.of(list -> new SkillsArray(list.toArray(new String[0])), wrapper -> List.of(wrapper.value())));
}

Oracle Nested Tables​

Oracle Nested Tables are unbounded collections. They can contain scalar values or OBJECT types:

-- Object type for each certification
CREATE TYPE certification_t AS OBJECT (
  name VARCHAR2(100),
  issuer VARCHAR2(100),
  year_obtained NUMBER(4)
);

-- Nested table of certification objects
CREATE TYPE certifications_table AS TABLE OF certification_t;

Typr generates both the element type and the collection wrapper:

CertificationT
/** Oracle Object Type: showcase.certification_t */
public record CertificationT(
  String name,
  String issuer,
  BigDecimal yearObtained
) {
  public CertificationT withName(String name) {
    return new CertificationT(name, issuer, yearObtained);
  }

  public CertificationT withIssuer(String issuer) {
    return new CertificationT(name, issuer, yearObtained);
  }

  public CertificationT withYearObtained(BigDecimal yearObtained) {
    return new CertificationT(name, issuer, yearObtained);
  }

  public static OracleType<CertificationT> oracleType = OracleTypes.compositeOf("certification_t", RowCodec.<CertificationT>namedBuilder().field("name", OracleTypes.varchar2, CertificationT::name).field("issuer", OracleTypes.varchar2, CertificationT::issuer).field("year_obtained", OracleTypes.number, CertificationT::yearObtained).build(CertificationT::new));
}
CertificationsTable
/** Oracle Nested Table Type: showcase.certifications_table */
public record CertificationsTable(CertificationT[] value) {
  public CertificationsTable withValue(CertificationT[] value) {
    return new CertificationsTable(value);
  }

  public static OracleType<CertificationsTable> oracleType = OracleNestedTable.of("certifications_table", showcase.showcase.CertificationT.oracleType).to(Bijection.of(list -> new CertificationsTable(list.toArray(new CertificationT[0])), wrapper -> List.of(wrapper.value())));
}

DuckDB LIST​

DuckDB LIST types are similar to PostgreSQL arrays:

EmployeeRow
  Optional<List<String>> skills,
  Optional<List<String>> certifications,
  Optional<List<Boolean>> weeklyAvailability,

Lists support arbitrary element types including nested structs.

DuckDB MAP​

DuckDB supports MAP types for key-value pairs:

CREATE TABLE employee (
  settings MAP(VARCHAR, VARCHAR)
);

Currently mapped to String for flexibility. Native Map support is planned.

Databases Without Collection Support​

MariaDB/MySQL, SQL Server (outside of table types), DB2, and SQLite don't have native array types. Alternatives:

  • JSON arrays - Flexible but less type-safe
  • Junction tables - For many-to-many relationships
  • Table-valued parameters (SQL Server) - For procedure parameters