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:
- 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?
- 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.
- 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.
About the Authors
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
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.
Read more about this topic
Oct 19th 2023
Designing a DSL that perfectly solves your problem
Irina covers the classification and design principles of domain-specific languages (DSLs), providing insights into various DSLs developed at TypeFox.watch the video
Oct 19th 2023
Code generation for everyone and everywhere
Christian discusses code generation using Langium, showcasing its capabilities and giving insights into testing and debugging strategies.watch the video
Oct 18th 2023
Integrating language engineering into the software development process
Johannes discusses developing DSLs with a focus on prototyping and long-term strategies, emphasizing collaboration with users for iterative improvements.watch the video