0

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!

  • The aim of using ORM is to make simpler, but with complex queries, it really makes it harder. Not really, the idea is to avoid having to hardcode thousand static queries or convoluted dynamic queries via built upon string concatenations. The more SQL you need the more useful is the ORM. Anyways, you could implement an ORM for the bulk of the queries and do use query templates for the complex ones. Overall if those require some degree of efficiency. Things few times are all black or all white. Sometimes grey is the color you need. – Laiv Oct 22 '18 at 9:48
  • whats wrong with hardcoding the sql? – Ewan Oct 22 '18 at 9:56
  • @Ewan Depends on the needs for OP's to add new structures (and hence new queries), the frequency it happens, etc. In other words, economy of scale. If hardcoding thousands of queries and deploy thousands more is cheaper than implementing a dynamic approach, then there should be no problem (apparently). If technically both fulfil the needs, then it's a matter of business strategy. IMO. – Laiv Oct 22 '18 at 10:58
  • 1
    I think OP still has no solution. He's still in design phase. ATM, storing queries somewhere is just one option over the table. – Laiv Oct 22 '18 at 11:04
  • 1
    I would not use ORM to solve queries with a certain degree of complexity. That's why I suggest using both approaches. ORM for the bulk (mainly CRUD queries) and complex ones with SQL templates. Unions, subqueries, and some joins would be out of my idea of "simple queries for CRUD". – Laiv Oct 24 '18 at 6:16
2

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.

  • Do I have to import 1000 packages at main.go in order to use packages? – sibert Oct 22 '18 at 11:43
  • yes, but one might assume that you would have more than one sql statement in a package. eg selectCustomerById .. ByName.. ByCompany... etc – Ewan Oct 22 '18 at 11:45
  • Could I use the same package for all CRUDs? POST, GET, PATCH, DELETE and PUT? In order to reduce number of packages? – sibert Oct 22 '18 at 12:14
  • you could, but i would not worry about the number of packages. put stuff together if it belongs together – Ewan Oct 22 '18 at 12:19
  • What is the benefits with packages compared with github.com/gchaincl/dotsql ? – sibert Oct 22 '18 at 12:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.