DEV Community 👩‍💻👨‍💻

Cover image for Building advanced SQL search from a user text input
Gajus Kuizinas
Gajus Kuizinas

Posted on

Building advanced SQL search from a user text input

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")
Enter fullscreen mode Exit fullscreen mode

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"

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'
)
Enter fullscreen mode Exit fullscreen mode

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

Top comments (0)

🤯

"I made 10x faster JSON.stringify() functions, even type safe"

☝️ Must read for JS devs