REST API with 1000 query templates?

Question

How do I manage maybe 1000 SQL-queries in a Golang REST API?

My SQL experience is at the upper basic level using Postgresql. I am today using a tool that you can use plain SQL as well as sort of ORM to direct access the database. The aim of using ORM is to make simpler, but with complex queries it really makes it harder. So I prefer using plain SQL and want to avoid GORM or similar.

When it comes to Golang, my experience is at the early beginners level. I have done a simple REST API with about 5 queries. And it is manageable.

All Golang REST API examples are at the Hello World level. But I need a way to handle several hundreds structs and queries. Easy to code and easy to understand and maintain. And I have so far found 3 ways to do this.

1. Store the queries using templates or similar.
   https://github.com/gchaincl/dotsql

2. Use 1000 packages. But this seems to be unmanageable to me.

3. Store the queries together with the struct in a lookup Postgresql database. Fetch the desired query and fire the query. This will be easy to maintain, but adds another layer that may impact speed.

Besides these thoughts, I have tried a dynamic approach with no luck.

I wonder if anyone that run into this can share some thoughts and advises?

TIA!

Answer 1

Write 1000 packages.

The benefit of doing it this way is that you make best use of your existing source control and deployment pipeline to manage the sql.

Storing the queries somewhere else means you need to implement extra change control around that storage.

The real question here though is why do you have 1000 sql queries? Maybe you simply have 1000 tables, or say 250 with CRUD on each.

But perhaps there is some other flaw in your design or requirments which is cauing you to have more logic in your SQL than required.

For instance, if I had an API getEmployees in company, I wouldnt add a getEmployeeByCompanyAndEmployeeName unless there were 1000's of employees per company. I would let the calling code do that search.

Similarly I would only select and return all the fields in a table, rather than having sql for different subsets of the data. Its the calling codes job to decide which fields it uses or not.

You really have to have something different to a standard 'hide the database' api to want to make the sql editiable separately from the code.