I’ve recently been writing a fair amount of Kotlin for one of our products. We’re using it both in the Android app, as well as in the backend server. Since both of these use cases involve writing SQL, I looked around to see if Kotlin supports multi line strings, so I could write my SQL inline, but also not have it be littered with + and \n everywhere. Turns out, Kotlin is pretty great for this use case!

Consider this example from our Android code base:

val cursor = db.rawQuery(
        """
        SELECT
            ${TN.PROJECTS}.${CN.PROJECT_ID},
            ${TN.PROJECTS}.${CN.NAME},
            ${TN.PROJECTS}.${CN.COLOR_CODE},
            ${TN.PROJECTS}.${CN.CHANGE_TOKEN},
            ${TN.PROJECTS}.${CN.DELETED}
        FROM ${TN.PROJECTS}, ${TN.TIMERS}
        WHERE
            ${TN.PROJECTS}.${CN.DELETED} = '0' AND
            ${TN.TIMERS}.${CN.DELETED} = '0' AND
            ${TN.PROJECTS}.${CN.PROJECT_ID} = ${TN.TIMERS}.${CN.PROJECT_ID} AND
            ${TN.TIMERS}.${CN.START_TIME} BETWEEN datetime('now', '-14 days') AND datetime('now', '1 second')
        GROUP BY ${TN.PROJECTS}.${CN.PROJECT_ID}
        ORDER BY ${TN.PROJECTS}.${CN.NAME}
        """,
        null
)

TN and CN are enums which represent table and column names.

Ideally, I’d like to avoid all the ${} crap, but using those ensures I don’t make a typo and have my query fail. Aside from that caveat, this SQL is pretty readable, as it is appropriately indented, and logically broken up into multiple lines.

Best of both worlds, really!

Unfortunately, there is one problem with this code. You’re likely writing something like this in a situation where the statement is already indented 2-3 levels. If that’s the case, the resulting string is going to be littered with unnecessary indents because the lines after the first one do not begin on the first character of the line. So if you’re debugging this code, and you want to log the exact query string, or copy paste it into an sqlite3 (or your SQL client of choice) to play around with it, it’s going to be ugly.

Fortunately, Kotlin has this handled as well. Enter trimIndent! Stick a call to this handy little standard library function at the end of your multi line literal, and it will handle the rest.

For example, without trimIndent, the above query would look like this:

                    SELECT
                        PROJECTS.PROJECT_ID,
                        PROJECTS.NAME,
                        PROJECTS.COLOR_CODE,
                        PROJECTS.CHANGE_TOKEN,
                        PROJECTS.DELETED
                    FROM PROJECTS, TIMERS
                    WHERE
                        PROJECTS.DELETED = '0' AND
                        TIMERS.DELETED = '0' AND
                        PROJECTS.PROJECT_ID = TIMERS.PROJECT_ID AND
                        TIMERS.START_TIME BETWEEN datetime('now', '-14 days') AND datetime('now', '1 second')
                    GROUP BY PROJECTS.PROJECT_ID
                    ORDER BY PROJECTS.NAME
                                   

With trimIndent, it looks like this:

SELECT
    PROJECTS.PROJECT_ID,
    PROJECTS.NAME,
    PROJECTS.COLOR_CODE,
    PROJECTS.CHANGE_TOKEN,
    PROJECTS.DELETED
FROM PROJECTS, TIMERS
WHERE
    PROJECTS.DELETED = '0' AND
    TIMERS.DELETED = '0' AND
    PROJECTS.PROJECT_ID = TIMERS.PROJECT_ID AND
    TIMERS.START_TIME BETWEEN datetime('now', '-14 days') AND datetime('now', '1 second')
GROUP BY PROJECTS.PROJECT_ID
ORDER BY PROJECTS.NAME                   

Much better! 👍

There’s also a slightly different variant of this function called trimMargin which may suit your needs better.

Get in touch on twitter @gopalkri if you have any thoughts, comments, feedback, or questions!