Do you guys use ORMs when working with SQL?

[Zippoxer]

I haven't written SQL for years. I was enjoying MongoDB with the awesome mgo package, and what saved me a lot of headache was the natural programmatic interface of MongoDB.

mgo maps your data (structs, maps, slices) to MongoDB queries and from MongoDB results, and you can write any MongoDB query possible with the mgo/bson package exclusively.

I've seen the sqlx package which only does mapping of results from the database, but not the other way around -- so you still have to type some queries like this:

func InsertFoo(v Foo) {
        db.Exec(`INSERT INTO x (bla, bla2, bla3, bla4, bla5, bla6, ...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ...)`, v.bla, v.bla2, v.bla3, v.bla4, v.bla5, v.bla6, ...)}
}

I can live with this verbosity, but that's not my problem. What happens when you add a field to the struct Foo? You have to modify the query above in three places and make sure you typed every character correctly.

And there are probably more queries updating Foo. Seems like too much manual maintenance to do -- and I believe this increases the chance of bugs.

A classic solution to this problem would be to use an ORM. I've looked at SQLBoiler and I'm very excited to see an ORM that generates Go code instead of using reflection. However, it still has the classic problem of ORMs that we all discuss from time to time.

Like any ORM, due to it being an additional layer on top of the database, it adds complexity (instead of verbosity and the manual query maintenance above) and you might have to write raw SQL anyway (what if you only want to select 2 fields of Foo instead of all of them? what about complex queries?)

Considering all that, I still cannot reach a conclusion. I'm going back and forth on that issue.

Since I appreciate the opinions of this community more than any other community I know right now, can you guys pour your opinions on the matter?

[John Jeffery]

There are many good ORMs for Go. One good list can be found at https://awesome-go.com/#orm

I think ORMs definitely have their place, and I have made good use of NHibernate in the .NET environment and Hibernate in Java. Having said that, I have found that for almost all of the work I have done in SQL with Go I prefer to avoid the additional layer of an ORM if I can. In doing this, though, I have run into exactly the same problem as you describe. The `sqlx` package does a creditable job of handling SELECT statements, but is not as much help when preparing INSERT and UPDATE statements for tables with a large number of columns.

I have tried a number of different approaches, but to date I have had most success with the `sqlrow` package at https://github.com/jjeffery/sqlrow (shameless plug, sorry). This package works directly with the database/sql package -- there is no additional layer. This allows one to mix and match: for example use package sqlx where it is most useful, and use package sqlrow in other places if it is of help.

In your example, you have a structure that looks something like:

type Bla struct {

    Bla int

    Bla1 string

    Bla2 string

    // ... and so on ...

    BlaN int

}

Using the sqlrow package you would insert a row by calling

    var bla := &Bla{

        // ... initialize members here ...

    }

    err := sqlrow.Insert(db, bla, "blas")

If you later add another column to the "blas" table, you add another field to the "Bla" type and then the sqlrow package will update the SQL accordingly.

When selecting rows you can use '{}' instead of your column list, and the sqlrow package will insert the columns for you.

    var blas []*Bla

    n, err := sqlrow.Select(db, &blas, "select {} from blas where bla2 = ? and bla3 = ? order by {}", bla2, bla3) 

Note that most ORMs will do this and more. Others readers may be able to share their experiences and recommendation for using specific ORMs. It is just a personal preference, but I just like being able to keep close to the database/sql package without the need for any additional layers.

[mhh...@gmail.com]

DBR is a good pick to me.

https://github.com/gocraft/dbr

It does the little missing from std package. much like previous answer explained it.

[Jon Bodner]

I've written a library called Proteus that is a simple tool for dynamically generating a data access layer:

https://github.com/jonbodner/proteus

You need to write the SQL queries, but it will map values (variables, slices, structs, maps) into queries and map the results into a struct or map.

[Jakub Labath]

Hi,

When I gave some thought to the whole ORM, I concluded it was not the SQL that bothered me it was these items

1. Adding a new column to table means having to revisit every query/insert/update that could be affected
2. Serializing and de-serializing as things get saved/read from DB (dealing with null values, date time conversions, blobs that should be parsed into e.g. json etc.)

So rather than create some magical layer above SQL I simply attempted to abstract the above items into one place.
So that when adding a new column one only has to adjust the serializer and de-serializer methods, and when querying one simply omits listing the columns.

Here my attempt at the above - https://github.com/jlabath/dbi

Cheers

Jakub Labath

[a.modz...@tmg.nl]

I far prefer solutions like Squirrel (https://github.com/Masterminds/squirrel) to ORMs for most problems — I still write queries, but use a more convenient syntax. It also means I'm less likely to somehow accidentally introduce an injection vulnerability. Usually that's enough abstraction to get me started on actual work.

[Henry]

I use plain SQL. Since I deal with mostly financial data, I need to control how my data get changed. I don't want some ORMs doing some funky business which may inadvertently lead to data corruption. I usually write a mapper for every table in the database. If a table structure is changed, I just fix the corresponding mapper. The rest of the code does not need to know SQL.

I have been thinking of auto generating these mappers using go generate, but hmm.. maybe next year.

[parais...@gmail.com]

Go type system makes generalization impossible if you also need type safety. Not only ORM are complicated but the fact that Go lacks generic programming features makes a Go ORM API even more horrible to use, so I'm not surprised at all there is no appealing ORM for Go.

People suggest code generation, but it's not going to solve your problem. Each time the database or your code is updated one or the other has to update one or the other as well.

Ultimately even if you stick to SQL you are just also writing your own ORM , you just can't generalize code because of Go type system. So there is no perfect choice here.

[Andy Balholm]

On Dec 30, 2016, at 4:43 AM, parais...@gmail.com wrote:
>
> Ultimately even if you stick to SQL you are just also writing your own ORM , you just can't generalize code because of Go type system.

There are really two separate (but related) issues in the ORM debate: the ORM design pattern, and ORM libraries.

By “the ORM design pattern,” I mean the concept that there should be a direct mapping between the objects in your program and the tables in your database. In other words, that each table in the database should correspond to a type or class, and that each row of that table corresponds (at least potentially) to an instance of that type.

ORM libraries, of course, are abstractions created to make it easier to write code that follows the ORM design pattern.

Many people seem to take the ORM design pattern for granted, and just debate whether it’s better to do the mapping manually or to use an ORM library. But I am skeptical of the design pattern itself. I seldom create a type that corresponds exactly to one of my database tables. Most often I use a struct or slice of structs that corresponds to the data I want from a particular database query; usually it is an anonymous type, declared directly in my HTTP handler function. Then I have some helper functions (most notably one called queryInto) that fill in the data with the results of the query.

Andy

[Henry]

On Friday, December 30, 2016 at 7:43:13 PM UTC+7, parais...@gmail.com wrote:

Go type system makes generalization impossible if you also need type safety. Not only ORM are complicated but the fact that Go lacks generic programming features makes a Go ORM API even more horrible to use, so I'm not surprised at all there is no appealing ORM for Go.

People suggest code generation, but it's not going to solve your problem. Each time the database or your code is updated one or the other has to update one or the other as well.

Every time there is a change in the database (eg. adding a column to your table), you still need to make a corresponding change to your code. There is no other way around it. No generics or ORM can help you there. However, changes in code do not always lead to a change in the database. Data structures may be represented differently in the database. Your code is dependent on the database, but not vice versa.

If you use ORM, you assume there is a 1 on 1 mapping between your data structure and its representation in the database. This makes your database structure highly coupled to your code. Hence, I think it is a bad design. Your database schema must be as stable as possible with fewer possible alteration in the future. It's how you map your data that matters. With ORM, you lose that flexibility.

Btw, generics is actually code generation.

[mhh...@gmail.com]

did anyone effectively worked with
https://github.com/relops/sqlc
?

That s a great property to be able to compile time check the dal.

Also i wonder how this, or that approach, could handle true modular integration
with extensible schema and dependencies management.

[main...@gmail.com]

Take a look at kallax and queryset. They are modern typesafe ORMs, allowing to write safe and reusable code.

среда, 28 декабря 2016 г., 1:00:05 UTC+3 пользователь Zippoxer написал:

[Tim Uckun]

Wow. the amazing amount of projects mentioned in this thread are interesting. Surprised that by now there isn't a well established leader or leaders for such a common need.

[Shawn Milochik]

In my opinion, ORMs are worse than useless. You need to know SQL to use an ORM. So if you use an ORM, you must bear in mind the underlying SQL and know the syntax of the ORM. Using an ORM "saves" you from writing some boilerplate code, but then you have to write ORM-specific boilerplate code, which is more verbose than the SQL it replaces. Regardless of which boilerplate you write, you only have to write it once. Adding more layers (leaky abstractions) and additional syntax adds complexity and saves nothing.

[main...@gmail.com]

Modern ORMs at least more type-safe than SQL: there is no work with strings, only with autogenerated constants. Also ORMs abstract you not only from SQL coding, but also from specific for DBMS SQL: with ORM you can easily switch PostgreSQL to MySQL.

воскресенье, 10 сентября 2017 г., 3:43:42 UTC+3 пользователь Shawn Milochik написал:

[Shawn Milochik]

On Sun, Sep 10, 2017 at 11:31 AM, <main...@gmail.com> wrote:

Modern ORMs at least more type-safe than SQL: there is no work with strings, only with autogenerated constants. Also ORMs abstract you not only from SQL coding, but also from specific for DBMS SQL: with ORM you can easily switch PostgreSQL to MySQL.

Sure, those are true. If you consider type safety a benefit worth the trade-off of all the overhead. I don't. As for the DB changing, I don't think that occurs often enough in the real world to matter. The reason ORMs support multiple databases is because they have to support your database of choice; not because anyone ever actually changes mid-stream. You could argue that it's easy to use one in development or testing (such as sqlite3) and another in production, but that leads to production errors.

[M. Shulhan]

On Sunday, September 10, 2017 at 10:32:07 PM UTC+7, Denis Isaev wrote:

Modern ORMs at least more type-safe than SQL: there is no work with strings, only with autogenerated constants. Also ORMs abstract you not only from SQL coding, but also from specific for DBMS SQL: with ORM you can easily switch PostgreSQL to MySQL.

Why would someone want to switch from PostgreSQL to MySQL?

I am not buying the idea that ORM sell: "make switching between database easily". By the time a project started, we should have already define what database we use, and list all the reason behind it, pros and cons. There are many thing in database than just SQL, e.g. tuning, backup, retention, mirroring/replication, etc. ORM is a mistake.

[Henrik Johansson]

The switching of databases can happen but I have always solved it at a higher "service" level.

What ORM's help you with is quickly getting started and it can in some cases help you with type safety. In the (not so) long run only the second matter and personally I wish there was something like Slick http://slick.lightbend.com/ for Go that would help when a project grows and lots of people touch the queries over time.

sön 10 sep. 2017 kl 23:16 skrev M. Shulhan <m.sh...@gmail.com>:

[Andy Balholm]

> Why would someone want to switch from PostgreSQL to MySQL?

I recently switched a project from PostgreSQL to MySQL. But I sure can’t say I *wanted* to. We were integrating a dependency that only supports MS SQL Server and MySQL.

[Simon Ritchie]

> Why would someone want to switch from PostgreSQL to MySQL?

It's fairly common to use one database for production and another (often in memory) for testing, with an ORM hiding the differences.

[Shawn Milochik]

On Mon, Sep 11, 2017 at 1:09 PM, Simon Ritchie <simonri...@gmail.com> wrote:

> Why would someone want to switch from PostgreSQL to MySQL?

It's fairly common to use one database for production and another (often in memory) for testing, with an ORM hiding the differences.

Yes, and this is a bad idea, for reasons I gave earlier in this thread. The short version is that not all databases have the same functionality, and there will always be cases where testing can pass and the code will immediately break in production.