Collection Types

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

Database Support

Database	Mechanism	Notes
PostgreSQL	Native arrays (integer[], text[])	First-class support
Oracle	Nested Tables, VARRAYs	Collection types
DuckDB	LIST, MAP	Modern collection types
SQL Server	Table-valued parameters	Procedure parameters only
MariaDB/MySQL	JSON arrays	No native arrays
DB2	-	No native collections

PostgreSQL Arrays

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

EmployeeRow

PostgreSQL

JavaKotlinScala

Full file

    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

JavaKotlinScala

Full file

/** 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

JavaKotlinScala

Full file

/** 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

JavaKotlinScala

Full file

/** 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

DuckDB

JavaKotlinScala

Full file

    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), and DB2 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