Building advanced SQL search from a user text input

Gajus Kuizinas
Sep 30, 2022

We are going to build a program that parses user search query and serializes it into a SQL statement. Here is an example of a search query:

firstName:"Thomas" -jefferson birthdate:[1960 TO 1970] (profession:"inventor" OR profession:"engineer")

What this says is:

  • Find records with first name “Thomas”
  • Exclude mentions of “jefferson”
  • Narrow down results to where the birthdate is between 1960 and 1970
  • Narrow down results to where the profession is either “inventor” or “engineer”
Designing SQL search

In SQL, this could look something like:

"firstName" = 'Thomas' AND
"searchName" ILIKE '%jefferson%' AND
"birthdate" BETWEEN 1960 AND 1970 AND
"profession" = 'inventor' OR
"profession" = 'engineer'

It might not be the most succinct form of SQL to do the job, but it is good enough.



Gajus Kuizinas

Founder, engineer interested in JavaScript, PostgreSQL and DevOps. Follow me on Twitter for outbursts about startups & engineering.