Month: September 2023

The article explores the Exposed library, a lightweight SQL framework for Kotlin that serves as a modern alternative to traditional ORM frameworks like Hibernate. It highlights how Exposed addresses common JDBC issues, such as excessive boilerplate code and manual resource management, while maintaining a "Kotlin-native" feel.

The library offers two distinct flavors of database access to suit different developer needs:

• SQL DSL: A type-safe wrapping of SQL that uses a Domain Specific Language to keep code readable and transparent.
• DAO (Data Access Objects): A more traditional ORM-like approach using "Entities" for those who prefer working with objects rather than raw table mappings.

While the SQL DSL flavor stays close to the database structure using a Map-like syntax for results, the DAO flavor allows for more complex object relationships and flexible loading strategies. The author notes that while both can exist in the same project, their table definitions are slightly different and generally not interchangeable.

Ultimately, the article concludes that Exposed leverages Kotlin’s expressiveness to provide a highly readable, performant, and flexible solution. It is recommended for developers who want the control of SQL without the overhead and complexity of heavy Java-based frameworks.

The article explores the friction between Object-Oriented programming and Relational Databases. While frameworks like Hibernate and Spring Data aim to bridge this gap, the author argues that they often trade performance and flexibility for convenience, leading to significant issues in complex production environments.

The transition from raw JDBC to modern ORMs brought several improvements, but also new challenges:

• JDBC Benefits: Full control over SQL and the power of the database.
• JDBC Challenges: Excessive boilerplate code, manual mapping of ResultSets, and lack of compile-time validation for queries.
• ORM Benefits: Elimination of repetitive code, type-safe mappings, and database abstraction through dialects.
• The "N+1" Problem: A common ORM pitfall where lazy loading causes a cascade of individual queries, severely impacting performance.

Drawing from 20 years of experience, the author provides several best practices to avoid performance bottlenecks:

• Avoid Eager Loading by default; use JOIN FETCH only when data is explicitly needed.
• Minimize or eliminate OneToMany relations, as they do not exist naturally in SQL and lead to complex, slow queries.
• Master Native SQL and use it for any query involving three or more tables to ensure optimal execution plans.

The article concludes that because developers should only use a small subset of ORM features to maintain performance, it may be better to drop heavy ORMs entirely. Instead, the author recommends lightweight tools like jOOQ or Kotlin Exposed, which solve mapping and boilerplate issues without the overhead and "magic" of traditional ORM frameworks.