list of pains

[boourns]

@iamkevb

Opening an issue to track major issues with the current version of scaffold that could be solved either now or when generics land. Trying to focus on just the issues without solutioning. I'm also listing some issues that are actually dblib issues.

1. Being directly tied to SQLite will definitely limit it's popularity.

2. I run into an issue where migrations can only run once, and if the database is cleared, the migration will fail. The order of events is:

   • Database already exists with table 'A' already created. One migration runs on startup, CreateTableA.
   • I want to add a column 'foo' to table 'A'.
   • I add column foo to the struct A and regenerate the scaffold a_sql. This updates the CreateTableA function to also create column foo.
   • Since the first migration already ran, when I start the app and migrate the database nothing happens. SQL table still missing column foo.
   • I add a second migration, AddFooToTableA that adds column foo to table A.
   • I restart the application, column foo gets succesfully added since the second migration has never run.
   • I delete my database (say, it was development)
   • I restart the application. First migration runs, adding table A including column foo. Second migration runs, column foo already added, and the second migration fails.

3. Query interface is not type-safe and is not really discoverable. Remembering exactly what text to put into the conditions and joins is a bit tough. This is a pain but at the same time I'm worried the cure might be worse than the ailment.

4. Query interface does not support more complex concepts like grouping

5. Maybe scaffold should become a child directory of dblib since the two are intertwined, and possibly more intertwined as we go?

[iamkevb]

Took a quick look at GORM to see how it handles connections to different DB's. It's complicated. Might want to add connection pooling to your list of dreams.

[boourns]

Looks like GORM defines the schema at runtime with a type schema.Fields - https://github.com/go-gorm/gorm/blob/master/schema/field.go#L40 . Good lesson on the complexity if one goes down that route.

[iamkevb]

There are some hints in this file https://github.com/go-gorp/gorp/blob/master/dialect.go. The project has dialects for a few databases.

There are a list of ORM's over here: https://github.com/d-tsuji/awesome-go-orms. Can probably steal ideas from a few of them.

[boourns]

Reading a bit of GORM usage, a few insights:

• GORM looks like a reasonable "big" approach - build a model of the table schema in Go, then map it to the SQL.
• But once you decide to do that you have a huge surface area to cover to support.
• I don't want a huge surface area. The couple-hundred lines of code that are here now actually get pretty far and I think that's cool.

So to support multiple SQL engines I would want to look for a different solution, maybe one where users can specify different SQL types for different engines within the struct field's tags? Or a really simple mapping of types.

Maybe there is a tiny bit more information about tables that would be ok to model within Go, like maybe just the mapping of columns to struct fields, so that the query interface could be typesafe.

I'm just really allergic to turning this into something 10x larger, at that point one should just use GORM because I don't think we'll make anything better.

[iamkevb]

I agree. I don't see any reason to write a full ORM. Those already exist. However, I'm also struggling to figure out where to draw the line. I'm not actively using scaffold, and I don't know when (if?) in a project's life, all the features of a big ORM become a benefit instead of weight.

[boourns]

I'm thinking maybe a line that could be drawn is whether or not the primitives of the library all need one another in order to function, or if they can be used independently with a sql.DB. So I could see improvements to the query interface, maybe a bit of improvements to the scaffolded code, and the option of making it faster to use those features with one another.. but no underlying "Model" global object that is required?

Like right now I'm hitting a point where I want to do a SELECT ID, count(x) FROM Table GROUP BY (blah) query. I think there's space to make it easier and faster to run that query and get typesafe results back, without building absolutely everything in Go.

[boourns]

I came up with a scrappy solution for problem 2 listed above, which is to update the "create table XYZ" migration to also mark as complete the migrations that "add new field to table XYZ", so that a fresh install gets the new columns once and skips the later migrations.