Go Structs for Database Tables and Relations, a Faustian Bargain

Joan and Jim are having a conversation about how to write Go code that deals with database tables:

Jim: Maybe we can have a Store and just make methods like CreateUser, DeleteUser, CreateWidget, etc on it, without worrying about a User or Widget struct. CreateUser can accept userName, password, etc. as parameters, and each of these methods just runs the appropriate SQL.

Joan: Hm, might work initially but what happens when you need to select a list of users? A User struct could come in really handy - a ListUsers or SearchUsers method should really return a slice of Users ([]User), for example.

Jim: Yeah, true. Okay fine so we create a User struct and a Widget struct, and we put the appropriate fields on them. So User gets and ID, a UserName, a PasswordHash, an Email, etc.

Joan: Sounds about right.

Jim: Hm, what about relations? One User can have many Widgets - so Widget needs need a UserID (same as the corresponding database table), along with it’s other fields like Name, Size, etc. It would be really useful to have a pointer to a User on the Widget. So struct Widget { UserID int; User *User; /* ... */ }

Joan: Right. The question is how does this User field get populated. It might be enough to just add a loadUser bool argument to FetchWidget - so you can select a widget by it’s ID and if loadUser is true it just selects the user. It’s sort of manual and we’ll have to write the extra code for it, but it’s not particularly complicated.

Jim: Yeah. I don’t have a better idea, seems workable to be.

Joan: Looking through this enormous document we got from the research team, there are a lot more objects on the overall roadmap. Even in the next few months we’re supposed to add support for dozens of other related things. There are at least 40 more tables (and thus structs) to add here. While we don’t want to over-plan ourselves here and start making wrong assumptions, I think we need to ask the question what of how this all will look after adding dozens more objects and tables and all of the CRUD methods for them.

Jim: FML, yeah…

Joan: I mean we could just start coding with what we’ve worked out and deal with it later. (Insert Agile references here.)

Jim: As you said though, let’s at least ask and try to answer that question - even just as a thought experiment.

Joan: Well one of the obvious things we’ll run into is that we’re probably going to want to break some of these out into different packages. While there are a lot of relationships here - most objects link to at least one other one with an ID, there are also groups of functionality which have a tigher coupling. The billing system for example centers around a set of tables that deal with things like subscriptions, invoices, charges, etc. - this seems a lot like a package to me.

Jim: Likewise the user system that deals with user accounts, access control and permissions, login, password, etc. - that also sounds like at least one package (maybe a package for the database stuff and another for the web controller stuff - not sure yet, but certainly at least one separate package).

Joan: Hm. Go packages must form a directed acyclic graph, i.e. you can’t create an import loop.

Jim: Yeah, so?… Oh crap…

Joan: Exactly. So let’s say we break out a user system as it’s own package, the code that deals with widgets and subwidgets as a package, and then a separate package for the billing system and so on. And now we’re going to want not only the Widget struct to have a pointer to User, but the User struct probably should have a field for a slice of related Widgets - if you could do this, you could express the entire databasem entity-relationship diagram as Go structs. Except… we can’t do that, because it would mean the users package would have to import the widget package, and the widget package would import the user package - which would cross the streams and cause every molecule in your body to explode at the speed of light.

Jim: This is giving me a headache. Okay, so no import loops means it’s basically impossible to express these entity relationships in Go if it grows to the point of needing multiple packages.

Joan: Well, it’s impossible to express them across multiple packages with complete type safety. You can of course (at the cost of significant complexity) make a data structure in Go that handles this using interfaces and things register the various struct types at run-time etc., but it means that the obvious solution of just creating a struct for each table and a struct field for each table field, and then adding pointers and slices to things for relations - that doesn’t scale beyond one package.

[awkward silence]

Jim: So… I guess we just start coding?

Share Comments
comments powered by Disqus