providersSQL

SQL

There are various SQL providers available, which all share a common DSL.

  • postgres : Declares an image using the postgres:13 image by default
  • mysql: Declares an image using the mysql:9 image by default
  • oracle: Declares an image using the gvenzl/oracle-xe:21-slim-faststart image by default
// use the default image.
postgres {
    // definition goes here
}
 
// custom image
postgres(imageName = "postgres:12") {
    // definition goes here
}

Defining tables

You can run DDL to create tables, and populate them with data by calling the table() function:

postgres {
    table(
        "users", """
        CREATE TABLE users (
            id UUID PRIMARY KEY,
            username VARCHAR(100) NOT NULL,
            created_at TIMESTAMP WITH TIME ZONE,
            is_active BOOLEAN,
            login_count INTEGER,
            balance DECIMAL(10, 2)
        )
    """,
    data = listOf(
            mapOf(
                "id" to UUID.randomUUID(),
                "username" to "john_doe",
                "created_at" to now,
                "is_active" to true,
                "login_count" to 5,
                "balance" to BigDecimal("100.50")
            ),
            mapOf(
                "id" to UUID.randomUUID(),
                "username" to "jane_smith",
                "created_at" to now.minusSeconds(3600),
                "is_active" to false,
                "login_count" to 2,
                "balance" to BigDecimal("75.25")
            )
        )
    )
 
    table(
        "products", """
        CREATE TABLE products (
            id SERIAL PRIMARY KEY,
            name VARCHAR(100) NOT NULL,
            description TEXT,
            price DECIMAL(10, 2) NOT NULL,
            created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
        )
    """, data = listOf(
 
            mapOf(
                "name" to "Widget",
                "description" to "A fantastic widget",
                "price" to BigDecimal("9.99")
            ),
            mapOf(
                "name" to "Gadget",
                "description" to "An amazing gadget",
                "price" to BigDecimal("24.99")
            )
        )
    )
}

Oracle

Oracle works the same way as the other SQL providers, but a few Oracle-specific conventions apply:

  • Use uppercase table and column names. Oracle folds unquoted identifiers to uppercase, and the identifiers in the data maps are quoted when the rows are inserted, so they must match. Declaring CREATE TABLE USERS (...) and using keys like "USERNAME" keeps the two in sync.
  • Use Oracle-native types: VARCHAR2 rather than VARCHAR/TEXT, NUMBER rather than INTEGER/DECIMAL, and GENERATED ALWAYS AS IDENTITY rather than SERIAL. Oracle (pre-23c) has no BOOLEAN type — use NUMBER(1) with 0/1 values.
oracle {
    table(
        "USERS", """
        CREATE TABLE USERS (
            ID VARCHAR2(36) PRIMARY KEY,
            USERNAME VARCHAR2(100) NOT NULL,
            IS_ACTIVE NUMBER(1),
            LOGIN_COUNT NUMBER(10),
            BALANCE NUMBER(10, 2)
        )
    """,
    data = listOf(
            mapOf(
                "ID" to UUID.randomUUID().toString(),
                "USERNAME" to "john_doe",
                "IS_ACTIVE" to 1,
                "LOGIN_COUNT" to 5,
                "BALANCE" to BigDecimal("100.50")
            )
        )
    )
}

Returned values

When any database component is declared, the following data is returned:

  • databaseName
  • jdbcUrl
  • username
  • password
  • port
S3