Go back to the blog

AMA: How to deal with optional string values in a relational database?

Having NULL values in the database can be a little bit tricky for string columns — for simple queries like .where(name: "Frank"), a NULL value acts as an empty string. The problem comes when using the NOT predicate: empty columns are then ignored, even though they might match a query like .where.not(name: "Frank")

Depending on how you plan to use the data, you have two ways:

  1. Keep NULL values and remember to always check "IS NULL AND column = value", or
  2. Save empty strings to pretend Ruby is a typed language and always operate on non-optional strings.

While keeping empty strings is considered a code smell by some, that approach comes with a practical benefits: you always deal with text, which in the long run means you don't need to remember that the column might be a string or might be empty. You always have the .blank? method for the strings, so you can easily know if the text was set or not.

This is a question asked during an AMA (Ask Me Anything) session — follow the blog to keep up with the common engineering questions and answers, or join our free mentoring platform!

Read more articles like this one:

Database Design 1 Ruby on Rails 8