Jun 5th 2023

Langium SQL: unite security and user experience for SQL

Markus RudolphMarkus Rudolph
Dr. Insa FuhrmannDr. Insa Fuhrmann
Have you ever thought about how to empower your users to do more with your application? Not just delivering a feature. I mean real super-powers, where only your fantasy shapes the constraints?

Let’s allow the user to query SQL statements against your database, directly sent from your frontend!

"Stop! That is a horrible idea!“, your technical lead should be shouting now. And he will list counter arguments:

  1. Let’s assume you give the user a limited access to the database by hiding tables, hiding sensitive columns, giving read-only access and so on… Even if you have a lot of control, what hinders the user to submit an expensive query that blocks all the other users? What hinders the user to ask for the entire data of a table?
  2. The user experience for writing SQL is bad. You can easily get some editor that colors your statements. But an editor that supports autocompletion or warnings about bugs and flaws is actually very hard to code or to find. Normally, the user is doomed to click the execute button several times, until the query is really finished. There is no other way to get back SQL compiler feedback.
  3. There are a lot of SQL dialects. If you have a solution for one, it does not mean that it is the dialect you want to use.

It seems hopeless. These are good reasons not to go the SQL route. Back to the blackboard…

But wait! I can show you a way to gain more control over what the user is sending. Plus, you will improve the user experience beyond syntax highlighting:

Do you know „Langium/SQL“?

With Langium/SQL you will gain the following:

  • You will be able to reject queries on the backend side by inspecting and judging over the query’s content. So, you gain control over security and can block dangerous and expensive queries. In the same way, you could implement free and paid plans by filtering different sorts of queries.
  • You can even check the queries for certain patterns and conditions like counting the number of JOINs or the presence of min/max checks on certain columns.
  • You can also transform queries before sending them to the database. This allows you to adapt them to your guidelines. This way you can make your queries more performant, readable and simple.
  • You get an editor that supports the user with things like symbol searches, autocomplete and syntax highlighting.
  • You can feed it with your initial table schemas and gain full editing support for your specific setup, like suggestions and validations using your own table and column names. Thus, you get early and fast feedback before the query is sent to the backend.
  • You can easily implement support for any dialect you want using our superset grammar and adding custom validation.
  • You will have a highly customizable solution: If something is not as desired, you can easily overwrite the default behavior.
Usage concept of Langium/SQL

If you want to see Langium/SQL in action, we recommend visiting our showcase on the Langium website.

Langium/SQL is open source. And if you need support, the Typefox team is available to help you with customization.

About the Authors

Markus Rudolph

Markus Rudolph

Markus likes to be creative: writing programs, drawing or scripting song texts. He normally has more ideas than time to implement them. But his secret passion is the subject of language engineering: He is founder of an initiative called »Language Benders« for people who want to learn more about creating languages.

Dr. Insa Fuhrmann

Dr. Insa Fuhrmann

Insa is an expert project manager at TypeFox. She leads with deep domain knowledge, oversight and empathy. She always drives the conceptual discussion on complex topics in her remit. She is avidly interested in languages and tooling for safety critical domains. Her scientific work on these topics earned her a PhD (Dr.-Ing.) of Kiel University.