I want to present a list of products to the user. This list is paginated and can be sorted by the user by product ID or product name in ascending or descending order.I am using an MVC setting, where I would like to query the database in the model.How would I write functions that query the database? I can think of several options which all have their drawbacks:Have one function for each possible combination of sort columns and sorting directions, each with a different query inside:func GetProductsOrderByIdAsc() []*Product {  }func GetProductsOrderByIdDesc() []*Product {  }func GetProductsOrderByNameAsc() []*Product {  }func GetProductsOrderByNameDesc() []*Product {  }This is obviously unmaintainable as it leads to lots of almost-identical SQL queries, aka duplicate code.Concatenate strings of SQL:func GetProducts(orderBy string, orderDirection string) []*Product {    return db.query(`          SELECT id, name, description            FROM products        ORDER BY ` + orderBy + ` ` + orderDirection + `    `)}This is inelegant, unreadable and unmaintainable as it quickly becomes a messimagine adding pagination and filtering. Parameter binding wont work here as these are columns and not values.Sort, filter and paginate in Go instead of in SQL. This would solve the code duplication problem but it can be a performance problem when having gazillions of records.What is the usual concrete way of solving this problem? Ideally there would be no duplicate code, no ugly query synthesis and no major inefficiencies.
How to avoid unmaintainable code when using SQL in an MVC setting?
database;sql;abstraction;maintainability
I don't have experience with Go, but since your question's title doesn't indicate that it's limited to Go, I will answer it in the general sense.The best way to go about SQL in MVC is to use ORM for a model. Some queries may be slow using ORM, in which case they may be isolated in a separate function.If you absolutely have to write the query yourself (lack of available ORM implementation, or queries which need tweaking for performance etc), I would go for having templates which expect parameters for substitution, similar to your #2, but using an existing robust template engine.It is important to note, that query parameters for SQL should be always escaped to avoid SQL injection.