4

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:

  1. 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.”

  2. 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 mess—imagine adding pagination and filtering. Parameter binding won’t work here as these are columns and not values.

  3. 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.

6

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.

  • I haven’t thought of using an advanced template engine for generating queries. Seems interesting! – rightfold Dec 18 '13 at 19:02
3

I take option #2, almost, though my input is not SQL clauses but booleans and the like. The function then intelligently forms the query from those parameters. If you don't like that, have it accept an array containing values for all the options.

It doesn't have to be a mess. Code it properly and robustly and it'll look/run just fine.

  • 1
    I'm often OK with this approach, but generally prefer to pass those booleans to a stored procedure. This allows you to keep your SQL code and logic in your database. – Brian Dec 18 '13 at 19:02
  • @Brian: That works. – Lightness Races in Orbit Dec 18 '13 at 19:20
0

Put your SQL in the DB and expose them to the model as stored procedures - you can do this directly, or you can wrap them in a more friendly service layer (eg using a web service) that gives you an API like you're calling a 3rd party web API. This means you have to think about what kind of data API you are exposing, rather than having all the DB available to use, it helps a lot to keep code separated and maintainable. IT also means you have a lot more security if (or when!) someone hacks your web server.

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.