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.

https://contra.com/p/WobOBob7-building-advanced-sql-search-from-a-user-text-input

--

--

Gajus Kuizinas

Founder, engineer interested in JavaScript, PostgreSQL and DevOps. Follow me on Twitter for outbursts about startups & engineering. https://twitter.com/kuizinas