loading sql-queries from a file
Programming with server applications is very complex. One big discipline is using databases. The most common kind of databases are SQL databases. A best practice is saying: you should not write SQL in string-notation mixed into your code. This Post is about moving sql queries out of your source code.
When I used J2EE, I also used JQL, the java query language. There you write he queries right above your actual function and give it a name, that you then can access inside that function. But in my opinion the query then is still inside the code. just on a different position and added to some variable or object.
An other common approach are query builder. There you are going to use function to assemble the query string. In nodejs there are modules for that, sequilize or knex, to name two. in both, you write something like that: sequilize.select().from(“tablename”,”t”).join(“other_table”, “t2”,”t.c=t2.c”).where(“t.b>2”). But actually, again, in my opinion the best practice is not met. The query is still written between your application code(hopefully in a separate module). You don’t write SQL, but still a complex syntax, to query the database, that you have to learn on top of the database and SQL. I think the value of using those libraries directly is very limited.
But they are often used by some the third way, that want to solve that issue. ORMs often use query builder to work on different databases with different dialects of SQL. For example Sequilize as well as bookshelf are using the knex-query-builder. In addition knex provide handy methods to load, update and insert objects. But as soon as you need to execute a more complex query, you still use the underlying query-builder and the problem is the same again. you need to learn an ORM + its query-builder.
An other type is query-mapping libraries. Actually the mysql module for node can already be seen as one of this kind. The result is mapped into an array of plain objects, where the values can get accessed by the same name as the field on the table. But this alone is not helping to move the query out of your JS file or PHP file. At my company we are using bearcat-dao. It is actually describing itself as a query-mapping-framework. Instead to simple objects it also can map to defined classes. In bearcat-dao the SQL-queries are written into separate .sql-files. When booting the app, bearcat dao will read all .sql-files and provide those queries by there name. So when you want to execute a query, you choose one by name in your code and the query is written in an SQL file, what is also giving you nice syntax highlighting in editors.
This actually meets the best practice of moving SQL-queries into a separate file. But the implementation has a drawback. All queries across all SQL-files share the same namespace and if you gave two queries the same name, you don’t even get a warning. This has lead me to make an sql-file loader. That makes it possible to have a namespace for each single SQL-file. My new module tsqlreader, can load SQL-files. It can handle comments and has support for templates or fragments.
So if you think using an ORM or query-builder is not appropriate, you can try the tsqlreader to load your SQL queries from separate files. But please, try to not repeat yourself to much, when you write your SQL. That will be the next post about.