A while ago we had to migrate the DB of one of our projects from Postgresql to SQL Server. Although most of that transition happened quite smoothly, we did encounter a few hickups and learned about some “gotachas” that needed us to make changes in our code.
In this series of blog posts I will document a few of them that might help you if you find yourself in a similar situation one day.
SQL Server gotchas #1: UUIDs
If you’re using UUIDs on your models you’re up for a surprise. UUIDs are basically hexadecimal value groups comprised of numbers 0 to 9 and characters a to f, here’s an example:
Post.last.uuid
# => "ccbb63c0-a8cd-47b7-8445-5d85e9c80977"
Now, in Postgresql those are stored with lower case characters, as in the example above. In SQL Server databases, the characters are upper case.
Post.last.uuid
# => "CCBB63C0-A8CD-47B7-8445-5D85E9C80977"
The problem
“So what’s the big deal?” you might think.
As long as you only work with records inside your own app this might not pose a problem:
Post.find_by(uuid: "dba91413-e25f-4b5a-b0c1-f18f26af574e")
# => <Post:0x00007f86e51cc320>
But the issue becomes clear, if, like in our case, you are using the UUID in a request against another microservice and the comparison is done there, or you do explicit comparisons of UUIDs of a record against the UUID you get from outside. You then basically have two strings that are not equal and you won’t get a match.
Post.last.uuid == "cbb63c0-a8cd-47b7-8445-5d85e9c80977"
# => false
Possible solutions
So how can we fix this?
One option could be to just make comparisons case-insensitive:
Post.uuid.casecmp? == uuid_to_compare_with
or by always use .downcase on the uuid when you do the comparison:
Post.uuid.downcase == uuid_to_compare_with
That works well enough, but it has a few downsides. First, it’s not really DRY, you’ll have to do this whenever you need to compare UUIDs. In therein lies the second downside: it can be easily forgotten, especially if somebody who wasn’t around when that initial issue was found implements something, and 💥: a new bug has just been introduced.
So the fix we ended up with was overwriting the getter on the model:
class Post
def uuid
super&.downcase
end
end
This way we don’t have to remember doing .downcase each and every time we work with UUIDs, it just always returns the
lower case version.
Stay tuned for more posts about Postgresql vs. SQL Server, and as always:
Happy Coding!