sql

Database Schema Migrations : A Few Lessons Learned

SQL / 03/31/2021

About 5 years ago I was introduced to Flyway, a tool for managing schema migrations using simple SQL scripts. I’ve used it on several projects now, and have more recently been introduced to Liquibase. The two tools both solve similar problems, with slightly different approaches. The main purpose of these tools is to version changes to the schema of the databasse. Liquibase has a good …

Database Schema Migrations : A Few Lessons Learned Continue Reading »

PostgreSQL’s GREATEST (and LEAST) function

SQL / 11/18/2021

PostgreSQL has two functions that are not standard SQL, but are very helpful in certain situations : GREATEST and LEAST. PostgreSQL does not have scalar functions for MIN and MAX (those are aggregate functions that operate on all rows in a query), but GREATEST and LEAST accomplish that and then some. Both allow for more than two values to be passed in, and also handle …

PostgreSQL’s GREATEST (and LEAST) function Continue Reading »

Optimizing a SQLite Database

Programming / 12/10/2019

TL:DR; takeaways: These seem very obvious, but with rushed deadlines, and “it works well enough”, these things are sometimes overlooked. Use the right data type for the job. If the documentation says VARCHAR when the values are always integers, maybe you should use INT. Generate a proper primary key when you can, skip indexes that overlap with the primary key Sort your data before importing …

Optimizing a SQLite Database Continue Reading »

Hibernate Logging Options

Java / 05/15/2018

For years I have relied on a simple property “show-sql=true” to see Hibernate’s generated SQL. This option is fairly limited: it bypasses the logging framework in the rest of my apps (SLF4J) it doesnt show parameters, just question marks it doesn’t show any timing information Frustrated at these limitations, I set out to understand all of the other options available to me. This is very …

Hibernate Logging Options Continue Reading »

Sql Quickie: Find zip codes near the center of a region

Programming, SQL / 06/11/2021

At work we have a copy of the zip-codes.com Business database, which I tend to reference somewhat frequently due to the nature of projects I work on. Today I needed a list of postal codes from major metro areas, which would be used to drive a test data generator. The data generator has access to a simpler, non-commercial zip code list, and can do radius …

Sql Quickie: Find zip codes near the center of a region Continue Reading »

Troubleshooting a SQL Server Implicit Conversion Issue

Programming, SQL / 12/14/2019

Over the last couple of weeks, I have been spending some time troubleshooting performance issues in some of our Java-based RESTful services. This week I came across one that required some different steps to troubleshoot. First, the tl;dr – our JDBC driver (jTDS) converts CHAR to NCHAR, which causes an index SCAN on a CHAR column instead of an index SEEK. The Architecture Here is …

Troubleshooting a SQL Server Implicit Conversion Issue Continue Reading »