DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
View Events Video Library
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Integrating PostgreSQL Databases with ANF: Join this workshop to learn how to create a PostgreSQL server using Instaclustr’s managed service

Mobile Database Essentials: Assess data needs, storage requirements, and more when leveraging databases for cloud and edge applications.

Monitoring and Observability for LLMs: Datadog and Google Cloud discuss how to achieve optimal AI model performance.

Automated Testing: The latest on architecture, TDD, and the benefits of AI and low-code tools.

Related

  • NULL in Oracle
  • Navigating the Divide: Distinctions Between Time Series Data and Relational Data
  • Introduction to NoSQL Database
  • Common Mistakes to Avoid When Writing SQL Code

Trending

  • Agile Estimation: Techniques and Tips for Success
  • Building AI Applications With Java and Gradle
  • Generative AI: A New Tool in the Developer Toolbox
  • Software Verification and Validation With Simple Examples
  1. DZone
  2. Data Engineering
  3. Databases
  4. Lightweight Kotlin DSL to Access SQL Database

Lightweight Kotlin DSL to Access SQL Database

Andrey Cheptsov user avatar by
Andrey Cheptsov
·
Jul. 13, 13 · Interview
Like (0)
Save
Tweet
Share
20.35K Views

Join the DZone community and get the full member experience.

Join For Free

As a member of the IntelliJ IDEA team, I share the same floor with the guys who develop the Kotlin language. So sometimes I get curious about what they do and how I could make use of it. Just a few days ago I had nothing urgent to do so I gave Kotlin a try, just to see how I could use it to access an SQL database. As you know, Kotlin is a statically typed language that compiles to JVM bytecode and JavaScript. Right now Max from the room next to mine is making Kotlin generate statically typed HTML and CSS with Kara Framework. So why shouldn’t it help me with my SQL?

Surely everyone is aware of more than one way to access an SQL database from an application. However, only two of these ways are recognized as general patterns: Data Access Object and Object Relational Mapping. Each pattern has its own benefits and weaknesses. Whilst ORM is more comprehensive, DAO is more lightweight and flexible.

Personally I prefer flexibility over complexity. That’s why I decided to write a prototype for a small library which helps you write data access objects with purely typed SQL queries in Kotlin language:

select (Cities.name) forEach {
    println(it)
}


This piece of code will generate the following SQL query:

SELECT Cities.name FROM Cities

Before you use such statements in your code, you have to define the table structure in this way:

object Cities : Table() {
    val id = primaryKey("id")
    val name = columnString("name")
}

Then you can not only run select statement, but also create tables and insert or update data.

var db = Database("jdbc:h2:mem:test", driver = "org.h2.Driver")

db.withSession {
        create (Cities)

        insert (Cities.id(1), Cities.name("St. Petersburg"))
        insert (Cities.id(2), Cities.name to "Munich")

        update (Cities.name("München")) where Cities.id.equals(2)

        drop(Cities)
}

What about a more sophisticated table structure? Let’s define one more table with a foreign key and a nullable field:

object Users : Table() {
    val id = primaryKey("id")
    val name = columnString("name")
    val cityId = columnNullableInt("city_id")
    val city = foreignKey(cityId, Cities)
}

Now let’s select users based on a specific criterion and output the city they live in. Or, if the city is not set for a user, let’s output “nowhere”:

select (Users.name, Users.cityId, Cities.name) join Users.city where
          Cities.name.equals("St. Petersburg") or Users.cityId.isNull() forEach {
      val (userName, cityId, cityName) = it
      if (cityId != null) {
         println("$userName lives in $cityName")
      } else {
          println("$userName lives nowhere")
      }
}

As you see we use a very simple construction to join the tables. What is more important, for each row we get a tuple of values with the types defined for the columns, including the nullable property.

The generated SQL query looks as follows:

SELECT Users.name, Cities.name FROM Cities, Users WHERE (Users.id = 1 or Users.name = 'Sergey') and Users.id = 2 and Users.city_id = Cities.id

Unfortunately, right now Kotlin does not allow you to change the type of overloaded operators, otherwise the query might look simpler:

select (Users.name, Users.cityId, Cities.name) join Users.city where
          Cities.name == "St. Petersburg" or Users.cityId.isNull() forEach {
      val (userName, cityId, cityName) = it
      if (cityId != null) {
         println("$userName lives in $cityName")
      } else {
          println("$userName lives nowhere")
      }
}

You could object to this and say that this library has little value since the only thing it allows is writing the same SQL queries. And you’d be right. However, the queries written in this way are a bit friendlier and are checked at compile time, so the likelihood of errors is reduced.

Anyway, this isn’t even a complete library, just a prototype for investigating the use of Kotlin... and asking what you think about it :)

The source code is available in GitHub. Please share your suggestions and ideas!

Database Relational database sql Kotlin (programming language) Domain-Specific Language

Opinions expressed by DZone contributors are their own.

Related

  • NULL in Oracle
  • Navigating the Divide: Distinctions Between Time Series Data and Relational Data
  • Introduction to NoSQL Database
  • Common Mistakes to Avoid When Writing SQL Code

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends: