SQL Antipatterns: Avoiding the Pitfalls of Database Programming

by Bill Karwin

Bill Karwin has helped thousands of people write better SQL and build stronger relational databases. Now he's sharing his collection of antipatterns--the most common errors he's identified in those thousands of requests for help.

Most developers aren't SQL experts, and most of the SQL that gets used is inefficient, hard to maintain, and sometimes just plain wrong. This book shows you all the common mistakes, and then leads you through the best fixes. What's more, it shows you what's behind these fixes, so you'll learn a lot about relational databases along the way.

Each chapter in this book helps you identify, explain, and correct a unique and dangerous antipattern. The four parts of the book group the anti​patterns in terms of logical database design, physical database design, queries, and application development.

The chances are good that your application's database layer already contains problems such as Index Shotgun, Keyless Entry, Fear of the Unknown, and Spaghetti Query. This book will help you and your team find them. Even better, it will also show you how to fix them, and how to avoid these and other problems in the future.

SQL Antipatterns gives you a rare glimpse into an SQL expert's playbook. Now you can stamp out these common database errors once and for all.

Whatever platform or programming language you use, whether you're a junior programmer or a Ph.D., SQL Antipatterns will show you how to design and build databases, how to write better database queries, and how to integrate SQL programming with your application like an expert. You'll also learn the best and most current technology for full-text search, how to design code that is resistant to SQL injection attacks, and other techniques for success.

Genres: Programming, Technology, Software, Nonfiction, Computer Science, Technical, Reference

546 pages, Kindle Edition

First published June 25, 2010

Reviews

[Anton Antonov · Rating 5 out of 5]

The book that every modern developer should read. As we all know, databases are an essential part of software development. With the rise of NoSQL databases, the relational ones start to get less attention than they deserve, but that all comes right whenever you read about the next big project/company deciding to store their relational data in a non-relational NoSQL solution *cough* *cough* *cough* MongoDB followers *cough* *cough*.

And here we are. You stopped to read this review and possibly read the sample or go straight to read the book. What you need to know is that this is something worth reading and taking your time. I, contrary to my 'give it time' advice, did not spend more than a week with this book. I justify 1 week as enough time to understand the issues since I faced most of them in 3-4 projects that had databases falling in at least 2-3 anti-patterns.

A big plus for the reader is the format used throughout the book - chapter intro, objective, anti pattern 1 ... anti pattern N, how to identify the anti-pattern based on questions/discussions in your team, valid use of the anti-pattern (if any) and then solution 1 ... solution N. Really, really easy to read format that easily transforms the book into a reference book when you need to confirm an anti-pattern in your project and search for a solution asap. Good job!

What's most important to read and understand thoroughly is the "Logical Database Design Anti-patterns" chapters. I think this is where people make most of their bad decisions in RDB usage.
The "Polymorphic associations" anti-patterns really resonated with me. That's something that I've been trying to address a lot in projects and teams I participate in. Mostly successful, I convince them to keep the cleverness and trickiness out of the DB design and instead keep it simple without relying on polymorphic associations if possible.


Other reviewers said that the "Application development Anti-patterns" section is a hit or miss in regards of content quality and relevance. I think otherwise. While it chews on the all-known topics nowadays of - 'readable passwords' and 'sql injections', the 'pseudokey neat-freak' isn't exactly the most popular topic, is it?

Chapter 24 "Diplomatic Immunity" is a worth to be part of Robert C. Martin's "Clean Coder". Perfect scenarios that people face in a corporate environment of washed up responsibilities and diplomatic immunity.

Chapter 25 "Magic Beans" is a bit weird. It starts off as an attack to ActiveRecord without focus and then transforms into something more reasonable. I just want to warn everyone reading it, to give it a chance and read the whole of it. I did not like it until I reached the solutions part and it all made sense. The author definitely could use a different tone and language to get his point across in 25. Either way, the solutions is mature and introducing an abstraction above the ActiveRecord objects usage in controllers is reasonable. If you're a Rails/Django developer you might scratch your head a bit where would these lie. Rails especially has the mysterious controller helpers that no one really understands, but the always-safe-bet is put your services-like logic in... the services folder!


Tech choice preferences: I wouldn't really justify the author's decision for picking out MySQL and primally PHP for the book, but these are just my two cents. The book would shine even brighter in the solutions part if a way better relational database as PostgresSQL and a programming language with "class" (as in style, wit, manners, good intentions) were used. Either way, this does not affect my scoring.

Code(SQL) examples: I'm giving great importance to the code examples quality in every tech book I read. If you can't follow the examples - the book is only 1/3 as useful as they're selling it to you. "SQL Antipatterns" does not have any code examples problems. I was pleasantly surprised to see all the code sections in the book having a code example file path that you can instantly find if you download the source code zip from the site. That way I could easily translate all the code into PostgresSQL-compliant SQL.
The source code is not hosted on GitHub but nothing is ever perfect.



The final verdict is: read this book sooner than you could mess up your database design. :)

[Emre Sevinç · Rating 4 out of 5]

My first experience with SQL and Relational Database Management Systems goes back to 1998, when I worked on some ERP software products built on Oracle RDBMS. Since then, I've built solutions on numerous database systems such as Oracle, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, etc. and during these 25 years, I've come across almost all of the antipatterns mentioned in the book. I've even painted myself into a corner in some cases falling into some of those SQL traps and had to find my way out.

In other words, I can safely say that the author of the book knows very well what he's been talking about and the organization of the book presents an easy to follow logical structure. Based on my experience, I believe SQL and relational databases are here to stay, no matter what NoSQL databases are advertised, and no matter what kind of document store or graph database enters your radar. Therefore, any database modeller, data architect, and software engineer dealing with relational databases (that is, the majority) better be aware of these antipatterns, so that they can avoid them.

I can't find much fault in this book, but I wish in its next edition there will be a little more discussion about the use of UUIDs for primary keys. Recently Gwen (Chen) Shapira started an interesting discussion about this topic, triggering responses from experience people such as Jaana Dogan and this one from Franck Pachot (see also this and this, in addition to "UUIDv7: The Time-Sortable Identifier for Modern Databases" and https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/).

Of course, it is not easy to dive deeply all of the subtopics therefore, as a companion to this book, I think other books such as SQL Performance Explained and The Art of PostgreSQL would be good additions. Also, please keep in mind that, even though SQL is pretty stable and reliable as a standard, it's not frozen in time and we're not living in 1990s, or even 2000s anymore: the latest version of the standard, SQL:2023, has already been released, and even though different database systems have different levels of compatibility with the latest and greatest, it is in your interest to make use of the up-to-date standard features to make your life easier.

Conclusion: if you are database architect, database modeler, or an software engineer working with SQL and relational databases (even if you use ORM), it'll be in your best interest to be aware of the pitfalls and solutions to them as described in this thoughtfully written book.

[Ronald · Rating 4 out of 5]

I'm a software developer who gets paid mostly for working on the back end portion of solutions and I've worked in both small and large companies. I am not an SQL guru and I am more than happy to let somebody else review and fine tune any SQL that the solution uses. If I'm lucky, I'll be working in an organization large enough to have full-time SQL experts who can help me out. More times than not, however, the development team has to craft the SQL ourselves. As of late, I've been letting Hibernate do most of the heavy lifting for me, trusting that it will generate reasonable SQL and keep me out of hot water. I fully understand that, like most tools, Hibernate is only as useful as the developer's understanding of it which is why I try and read up on the ins and outs of Hibernate as much as I can. To that end, I figured I'd pick up a copy of SQL Antipatterns: Avoiding the Pitfalls of Database Programming to help me understand if I was asking Hibernate to do something silly and I'm really glad that I did.

Some of the anti-patterns I've seen before, like not using using constraints or using a column to mean multiple things, but there are many I haven't seen before but should now be able to spot. I enjoyed the discussions around security and can appreciate that storing passwords in the database is a bad idea (I've seen that more than once) and have a better understanding on how to protect against SQL injection attacks. The section on application development was a pleasant surprise to me because it talks about testing and possible migration strategies. I also enjoyed the comparison of the Active Record pattern versus the Repository pattern as it convinced me that the decision to use Repository on my current project was a reasonable one.

All in all, I loved the book and recommend that anybody who has to deal with a relational store pick up a copy and give it a read. It isn't a very long book but you get the sense that the advice given came from some hard learned lessons on the job.

[Jason · Rating 4 out of 5]

First, this is based on B6.0 printing, version 2010-4-1.

I was hoping to get a little more out of this book. I bought it because I saw the excerpt from the chapter "Naive Trees" and the statement "most developers aren’t SQL experts, and most of the SQL that gets used is inefficient, hard to maintain, and sometimes just plain wrong," on PragProg Bookshelf. Not wanting to create poor SQL, I knew I needed this book.

I struggled for a long time with a database that had a large tree structure, so I'm always looking for better ways of dealing with trees in databases. After a lot of work, I finally ended up with what basically equates to the Enumerated Path pattern. I wish I'd had this book a few years ago when I started that project; it would have saved me a *ton* of time.

I think the chapters are well written, and seem to cover a lot of issues a developer might encounter in his first few years of development. The author uses good, real world, examples written in clear language.

If you're a developer struggling with database problems, I'd say, pick this book up. If you've been developing for upwards of 10 years, you can probably skip it, hopefully, you'll already know most everything he covers.

[Phil Eaton · Rating 4 out of 5]

This enjoyable read covers many anti-patterns, clearly and with compelling reason, that I've both been thrust into and put myself into. It is the first book I've read on database design and optimization after 5 years of professional software development. It is a great jumping point for the topic with the numerous other books it cites.

[John Mantios · Rating 5 out of 5]

F***ing awesome!

[Slávek Rydval · Rating 3 out of 5]

Dobře popsaná kniha známých i méně známých antipatternů v SQL (tedy především v návrhu fyzického datového modelu v relačních databázích), jak je odhalit a způsobů, jaké alternativní řešení použít. Výhodou je, že se autor maximálně snaží, aby text platit pro maximum SQL databází, a tam, kde to nejde, tak zmíní řešení v jednotlivých produktech (MySQL, Oracle, PostgreSQL a MS SQL).

Dal bych to jako povinnou literaturu studentům jistých vysokých škol, kteří v tom lepším případě kolem sebe házejí normální formy, ale databázové schéma prakticky navrhnout neumí. Stejně tak by se na to mohli podívat programátoři, kteří i dnes dokáží připravit aplikaci pro ideální SQL injection útok.

A proč „jen“ tři hvězdičky? Některá témata by si zasloužila větší diskuzi nebo podrobnější rozepsání předvším v oblasti řešení.

[David Lindelof · Rating 4 out of 5]

This is a catalog of what the author considers widespread bad practices in the use of relational databases and SQL programming.

Most antipatterns are about misunderstandings on good database design; for example, the author discusses such timeless classics as implementing many-to-many relationships by putting multiple comma-separated values in a row. For instance, the database backing a blog whose posts can have an arbitrary number of tags would have a column TAGS with entries such as 'tag1, tag2, tag4'.

I would have appreciated some concrete discussions on writing performant SQL queries. I am no SQL expert but am wary of writing complicated JOIN statements, but this fear is a mostly irrational one. I would have liked to see this important topic addressed.

It's a nice compendium of practices to avoid with highly memorable titles ("Pseudokey neat-freak" is my favorite), but I'm not sure this would be the first title one should read on the subject.

[Horia · Rating 4 out of 5]

It's a pretty good book on SQL. It is aimed at people with beginner-medium SQL experience.
Beginners will not really understand what's happening there.
And if you've written the database interface layer for 3-5 apps, it might be partially interesting for you.
If you're advanced with SQL, you already know these patterns.

[Adam · Rating 2 out of 5]

Not sure who this would be useful for. Too specific for people with little experience, but nothing all that new for those who've seen a bunch of data models. Maybe a software engineer tasked to build a service? (Karwin might have said as much in the book, forgive me if that's the case.)

Like with most things data modeling, in the end folks are probably better off reading Kimball.

[Ji · Rating 3 out of 5]

This book was recommended by some programmer friends of mine, and proved to be a good read. It is not about TSQL thought. The book is mainly about some basic ideas that people should have in mind when designing database models. It’s a fast read.

[Jason · Rating 5 out of 5]

Short and to-the-point chapters, great conversational writing. Love that every chapter involves a section on when it makes sense to use the antipattern. Gonna be re-referencing this one for a long time.

[Patryk Woziński · Rating 3 out of 5]

Well structurized book, but in my opinion it’s more for junior developers to cover their knowledge gap, not for seniors that are trying to learn something deeper about SQL language.

3 or 4 stars - I am still not sure. Recommended only to less experienced engineers IMO.

[Samuel · Rating 5 out of 5]

I reviewed a newer edition of this book for Pragmatic Programmers. It was fantastic and covered ever anti-pattern I have committed over the years. Wish I would have had this book a couple decades ago.

[Ispiriants Volodymyr · Rating 5 out of 5]

It's a very good book for the people, who just learned SQL. I regret that I did not read it before, because it will resolve some of issues during my work.

[Matias Fernandez · Rating 5 out of 5]

As someone struggling to design my website's database, this book hit the spot. I couldn't have imagined all the things I had wrong. I'm so glad I found this gem.

[Jeremy Carman · Rating 5 out of 5]

This books is a bit older, but the advice still holds up. The format was easy to digest and examples easy to understand. As a software developer I'd wish I had read this book a long time ago.

Over the years, I have been exposed to quite a few of the anti-patterns he laid out. I've even implemented a few. I found his reasoning and alternatives insightful. I also appreciated that almost every anti-pattern he presented he gave concrete exceptions to the rule, along with conversational hints to recognise when someone else was using that anti-pattern.

There's only a few small nitpicks I have. The only one I'll mention here is about some of his proposed solutions. I'm sure those queries, seem easy to him, but would be a bit hard for me, and very difficult for a junior developer to understand or generate.

[Lisa · Rating 2 out of 5]

With many relational database systems available in the market, I find there is not much need to design a RDBMS product to the level this book gives. Working with data analytics, I found part 3 to be the most useful with Query Antipatterns. Majority of the issues are addressed with proper training so I can see this book would be good for those who are self learners who may not have many opportunities for formal training. The book is written in a format that gives the antipattern as an example, then provides the best solution. Decent book overall, but I didn’t get much out of the content or saw examples that shouldn’t happen unless proper practices weren’t followed.

[Joe Michalak · Rating 4 out of 5]

The author provides clear, concise rationale about why each of the antipatterns are categorized as such. Having read a lot of Celko I was generally unsurprised about the antipatterns, in all of their creativity, and the tradeoffs with using each. Generally speaking, the overall gist of the solution is to use SQL as it was designed. However, as compared to Celko, the author really emphasizes thinking about the fact that SQL is run from within another application and can take advantage of that fact.

However, even solving some of the simpler of the Antipatterns, I have found several ideas to try out on a project on which I'm currently working.

[thirtytwobirds · Rating 3 out of 5]

Three stars if you're experienced. Most of the stuff in here is "yeah, I've seen that and it sure does suck", so you're not going to get a lot other than maybe some comfort that you're not alone.

If you're just getting started with SQL I'd bump it to four stars because you might be able to head off some bad habits at the pass, though I wonder whether just reading about dumb patterns is enough to really stick, or whether you need to suffer through dealing with them to really grok why they're awful.

[Dana Larose · Rating 4 out of 5]

A good discussion of database design issues and common mistakes. Definitely one to keep on my desk when I'm starting a new project or reworking one of my existing ones.

Was pleased and smug to see I'd recognized and avoided a bunch of them over the years!

As a side benefit, I picked up a few nice SQL tricks I didn't know.

[Omar ElKhatib · Rating 5 out of 5]

Many valuable lessons that I picked and will help me in my career,
Benefit all ranges from beginners to professionals.
The topics are well organized so you can pick what ever you like from the topics.
it shows the anti-pattern and explain why it's bad and suggest possible solutions.
Definitely will re-read it and keep it as reference.

[Nitin Prakash · Rating 3 out of 5]

Read as part of a presentation I gave on SQL footguns.
This is a fast read overall. An experienced programmer might/should be familiar with most of the chapters in the book. But if you're getting started with engineering, then I'd read this with a lot more focus (and also bump the rating to 4 stars).

[Lucille Nguyen · Rating 4 out of 5]

It's a good book that covers a lot of issues with DB implementation, programming, and design. However, I'd have enjoyed it more if it gave the antipatterns and things to look out for instead of toy examples. Different strokes for different folks I guess, other reviewers quite like it. Good for covering a lot of the nuts and bolts of SQL a lot of data, devs, etc. don't think about often.

[Majed Daas · Rating 4 out of 5]

كتاب رائع، يجب على كل مبرمج يحتك بقواعد البيانات قراءته بتعمق ، يتميز الكاتب بأنه عبر الأمثلة التي يطرحها يقوم بالتأكيد على القواعد النظرية دون أن يشعر القارئ ، ثم في نهاية الكتاب يضع القواعد النظرية ويربطها بالأمثلة التي وضعها خلال الكتاب .

[Javier · Rating 5 out of 5]

If in one way or another you are involved with databases you should read this book. Developers and DBAs alike would benefit from this read. It is very well written, great content and easy to follow. Also, no superfluous content in this one.

[Viraj Bhosle · Rating 5 out of 5]

I loved this book and my data engineer friend too. For intermediate, advance users of database, few chapters will appear to be quite basic. For beginners, there is a lot to learn from this book. It explains pros and cons behind every database design trade off, which helps you make wise choices.

[Jesus · Rating 4 out of 5]

En general muy buenos consejos sobre como gestionar bien una base de datos (a nivel de diseñarla, no de administración). Quizás un poco repetitivas y obvias algunas cosas, pero se aprenden cosas interesantes

[Luke Darling · Rating 5 out of 5]

I think this was a great book. In my career, I am seeing a lot of developers that do not have enough experience with database design/management/patterns. This book helps to bridge that gap between developers and dbas.

[Genert · Rating 5 out of 5]

Obligatory reading for every software engineer out there who deal with databases in daily basis.