Book Review – SQL Antipatterns

Introduction

I’ve just finished the wonderful SQL Antipatterns book by Bill Karwin. The book is a must-have reference for any developer that has to interact with a relational database system.

This post is a review of what this book is all about and why you should be interested in reading it.

Author

Bill Karwin is a distinguished software engineer, having a huge reputation on StackOverflow with gold badges for MySQL, SQL, Database or Database Design.

As I previously explained, contributing to StackOverflow is a great way to become an expert on various programming-related topics, and Bill Karwin is definitely an expert in this field.

Audience

If you are a backend developer, a team leader or a software architect, this book is definitely for you. Especially if you are a junior developer, this book is going to unravel a lot of anti-patterns which are unfortunately way too common in enterprise systems.

Content

The book covers 25 anti-patterns which are grouped into four categories:

  • Logical database design anti-patterns
  • Physical database design anti-patterns
  • Query anti-patterns
  • Application development anti-patterns

While I’ve bumped into some of these anti-patterns while developing enterprise systems, I really liked the way they were explained, and especially that we get to see a proper solution to the original problem.

Why you should buy it?

The book is very easy to read, that’s why I think it’s very relevant for junior developers as well.

Designing a database schema is not a trivial task, and it’s much cheaper to take as many good decisions as possible from the very beginning.

After reading SQL Antipatterns, you will be better prepared for designing a relational database system and the data access layer that’s needed for reading and writing to the database server.

All in all, I’m glad I read this book, and I wished I have known all these tricks back when I was a junior developer myself.

If you liked this article, you might want to subscribe to my newsletter too.

How to map table rows to columns using SQL PIVOT or CASE expressions

Introduction

I’m now reading the wonderful SQL Antipatterns book by Bill Karwin. The book is a great reference for any developer that needs to interact with a Relational Database System.

In this book, the Entity-Attribute-Value (EAV) model is portrayed as an Anti-Pattern. Although I must admit that a proper relational schema is almost always the best approach when designing an enterprise system, there are use cases when the EAV model is justified.

In this post, I’m going to explain when to use EAV and how to transpose a ResultSet so that rows become columns.

Continue reading “How to map table rows to columns using SQL PIVOT or CASE expressions”

How to customize an entity association JOIN ON clause with Hibernate @JoinFormula

Introduction

As I explained in this previous article, you can map calculated properties using Hibernate @Formula, and the value is generated at query time.

In this post, you’ll see how you can use a custom SQL fragment to customize the JOIN relationship between two entities, or an entity and a collection of embeddable types.

Continue reading “How to customize an entity association JOIN ON clause with Hibernate @JoinFormula”

A beginner’s guide to SQL injection and how you should prevent it

Introduction

One of my readers asked me to answer the following StackOverflow question. Right from the start, I noticed that the entity query was constructed by concatenating strings, which can expose your application to SQL Injection attacks.

Unfortunately, I’ve been seeing this problem over and over throughout my career, and not all developers are aware of the serious risk associated to SQL Injection. For this reason, this post is going to demonstrate what damage can SQL Injection do to your system.

Continue reading “A beginner’s guide to SQL injection and how you should prevent it”

How to resolve the Hibernate global database schema and catalog for native SQL queries

Introduction

When your relation database system uses multiple schemas, then you can instruct Hibernate to use a global schema using the hibernate.default_schema configuration property:

<property name="hibernate.default_schema" value="forum"/>

While Hibernate can imply the default schema whenever dealing with entity queries, for native queries, you need a little trick. This post is going to demonstrate how you can imply the default schema for native SQL queries as well.

Continue reading “How to resolve the Hibernate global database schema and catalog for native SQL queries”