Raw SQL with Express without ORMs

Question

I want to use raw SQL queries in my application but I have some questions on how to structure my application.

Some background:

1. I am writing a JSON API with Express and Postgres.

2. I am not currently using an ORM. I have used Sequelize before, but I don't believe the queries are optimized so I am hesitant to use it.

3. I am using camelCase in my code but Postgres is case-insensitive, so for readability, I have used under_scores in my DB tables. I constantly have to do queries like: SELECT first_name AS "firstName" from users; When the queries get larger, it is almost impossible to read since there is no syntax highlighting of SQL in js string templates.

4. I feel there is too much repetition in my queries, but that is expected.

What I am thinking:

1. I was not able to find a Visual Studio Code extension that can highlight SQL inside js files and strings. If there was one, I might get by.
2. I might write all my queries in .sql files, so that I can have syntax highlighting and load them all into memory when my application starts to prevent too many IO operations, since it would be against the reasoning why I am using raw SQL in the first place.

Anyone had this issue before? How do you structure your application when using raw SQL with Postgres and Express?

Answer 1

1. Definitely keep all the sql scripts in the corresponding .sql files.
2. Stick to a meaningfull naming convention. Come up with one you feel comfortable with: in future it will allow to build helpful tools around your codebase doing a lot of boring stuff automatically and making you much happier.
3. In case you're getting complicated rapidly, generate at least some of the duplicate/commonly used sql. Consider having some simple placeholders in your files like {{ firstName }} which is translated into first_name AS "firstName". Such a translation should happen only once - when you lode source .sql. This is more sophisticated and highly depends upon your tasks kind. Sometimes such an approach is useless, sometimes - useful.