<p>I am new to web stack development, but I'm trying to teach myself by implementing a pet project that I've wanted to make for a while. I've gotten stuck with some questions that I try to sort out and that I have googled, but I havent found any clear-cut answers, so I hope you will bear with me although they might be somewhat elementary. </p>
<p>I am trying to implement a RESTful api that will be consumed by an SPA. I have tried to write good models for the entities that I need e.g. as a generic, illustrative example</p>
<pre><code>typedef LookupObject struct {
Id int
Name string
OptionalData string
Tags []Tag
}
</code></pre>
<p>My problem is how to best implement such models when I am using an SQL database backend and JSON frontend, because I can't seem to find a good code structure that: </p>
<ol>
<li> Makes it easy to marshal the objects to and from JSON.<br/></li>
<li> Makes it easy to push objects to and retrieve from the database.<br/></li>
<li> Keeps a single point of truth for the models.<br/></li>
</ol>
<p>I am trying to stay away from full ORMs, because I fear that some of my needs later on will be difficult to implement if the db framework is too opinionated. Therefore I don't want to implement the work logic between my go server and the DB with someting that is much more fancy than the common sqlx library. </p>
<p>My challenge is how to properly do the interaction between objects and the db, e.g. when there are SQL null values to be mindful of. My optional data field is truly optional, and when it is not present it is represented by an sql null value. But database/sql and sqlx don't allow me to Read directly into a struct without either using sql.NullString/NullInt or *string/*int instead of string/int. In the first case, marshalling the object to JSON naively reduces a NullString to the JSON object OptionalData : { Value:"", Valid: false } instead of OptionalData: null or OptionalData: "String", forcing me to write custom ToJSON methods for the structs. in the second case, construction of every struct carries with it the overhead of independently mallocing and garbage-collecting the individual fields of the struct, which I don't like the sound of - not to mention all the ridiculous addressing I then need to construct and modify structs. It is also possible to create LookupObjectDatabase structs that contain nullable versions of the fields of LookupObject, and then a method that copies it into an ordinary LookupObject, but then I basically have to implement each struct twice, as well as hardcode a conversion between them. I'd rather not. </p>
<p>Another question is how to treat many-many maps that are often logically representable with embedded structs, like my Tags []Tag. Is that better represented by Tagids []int referencing the Tags indirectly? How are such relations most commonly treated?</p>
<hr/>**评论：**<br/><br/>perihelion9: <pre><blockquote>
<p>I am trying to stay away from full ORMs, because I fear that some of my needs later on will be difficult to implement if the db framework is too opinionated. Therefore I don't want to implement the work logic between my go server and the DB with someting that is much more fancy than the common sqlx library.</p>
</blockquote>
<p>The obvious question at this point is; why SQL then? A document (or kv) store seems much more suited to your use case. Mongo, Redis (hashes), Couch, Cassandra, there's tons of great options out there. Instead of fighting old tools, find one that works.</p>
<blockquote>
<p>Is that better represented by Tagids []int referencing the Tags indirectly? How are such relations most commonly treated?</p>
</blockquote>
<p>If you feel like you're going to have a lot of duplicate values for that column, normalize it. Otherwise, don't.</p>
<p>You said above you wanted to keep away from an opinionated ORM. The only answers to the quote above are going to be "opinionate your ORM". You can't answer questions like that without making deliberate design decisions about how you persist your data in the DB. The way you're going, you're going to end up writing an ORM anyway.</p>
<p>Personally, I'd use Mongo and forego all of these questions. But if it was a business requirement to use SQL, I'd keep the data model independent of the DB's structure. Your data model would stay unchanged, and you'd write whatever middle-layer you need to in order to retrieve valid objects from your DB.</p></pre>jerf: <pre><p>Well, someone better contrast perihelion9's advice for you.</p>
<p>SQL's core problem is that there's a certain amount of mismatch between what it stores and what Go likes to represent. This is a common problem across a wide variety of languages and data stores, because the concerns of a programming language and data store are at odds to a certain degree. Programming languages are generally designed to be relatively directly related to what hardware can do, and unrestrictive beyond that. SQL databases are designed to store <em>data</em>, and to help you by making as much invalid data as possible impossible. So to use SQL, you need to make sure you are also doing good DB design, specifying NULLs carefully, relationships with foreign keys, integrity, etc. Crossing over between the worlds requires a certain amount of effort.</p>
<p>And to some extent, that effort is irreducible. When you are packing a Go data structure into the database, you're actually not just doing a simple transformation, you're also asserting as you go that the data fits into the SQL schema, and dealing with what it means when it doesn't.</p>
<p>It's true that there are also data stores that do much less validation on your data, and by being more flexible, can more easily translate between Go data structures and your data store. On the other hand, you generally lose a lot of what SQL is doing for you. (How much varies from solution to solution.)</p>
<p>To some extent, what you're seeing is a <em>real</em> mismatch between "JSON" and your databases, and anything that magically makes the problem go away can only do so by shoving something under under the carpet and hoping it won't come up. (Hint: It usually does pretty quickly.) However, as always, "it depends". These other solutions can be useful if your data is not very relational, and never will be, for instance, big piles of nearly identical records about some sort of thing that will generally not need to be group queried, or only in a very particular way. If you've got really relational data, though, I'd rather fight my way through SQL than risk dropping it in a DB only to discover after a year and a half that none of the user<->account linkages are actually valid anymore, or something like that.</p>
<p>I also tend to hedge my bets a bit anyhow. I usually define an interface that has all my data queries in them, which I try my best to make pure Go. This then gets implemented by my SQL code. This makes it easy to make test objects that return stuff for your test code, and should you decide to switch data stores later, it's already separated out. When you do this, don't just think "setters & getters", think, "I need a search for users by name, address, and department, I'll create <code>SearchUsers(name, address, department string) []*User</code>". A bit of practice with this and you end up with a design that is surprisingly easy to move later. And all the nasty SQL code ends up in one place... lately I've been trending this way, rather than scattering the SQL code everywhere it is used. YMMV.</p></pre>DarkRye: <pre><p>I personally would like to know answers to the same questions.</p>
<p>Its core is about what do you do to ensure that your go language structures are properly serialized to JSON structures and properly stored in a SQL database. </p>
<p>SQL is still a valid choice for a lot of scenarios, so proposing alternative solutions to SQL is not an answer IMO.</p></pre>DarkRye: <pre><p>I came across a library for mapping structs to JSON and back <a href="https://github.com/mholt/binding" rel="nofollow">https://github.com/mholt/binding</a></p></pre>DarkRye: <pre><p>And here is a DB mapping layer <a href="https://github.com/upper/db" rel="nofollow">https://github.com/upper/db</a></p></pre>
