<p>I'm an old school C#/MSSQL guy who has finally decided to try some newer/different technologies. After some research I decided to try Go, PostgreSQL, and html5/angular as my application stack.</p>
<p>I've spent the last couple of weeks digging into the technologies and trying to leverage my current knowledge with their chosen counterparts.</p>
<p>First off, big props to the PostgreSQL guys for their json tools. There are lots of things I really dislike about postgres from a MSSQL point of view (pgSQL being the big one), but json_build_object and json_agg are incredibly useful; Which brings me to my question. I'm writing a json rest service for my data; where should the serialization take place? Is one better than the other, and if so, why and how would you measure it?</p>
<p>I started learning Go with a REST service already in mind, and didn't discover the json capabilities of postgres until a week into my studies. I started out with something like this:</p>
<pre><code>func QuizCollectionHandler(responseWriter http.ResponseWriter, request *http.Request) {
var result []Quiz
db, db_err := sql.Open(config.DBInfo.Driver, fmt.Sprintf("user=%s dbname=%s password=%s host=%s port=%d sslmode=%s", config.DBInfo.User, config.DBInfo.DatabaseName, config.DBInfo.Password, config.DBInfo.Host, config.DBInfo.Port, config.DBInfo.SSLMode))
if db_err != nil {
return // eventually return a different status code and error struct
}
defer db.Close()
rows, rows_err := db.Query("SELECT Q.\"Id\", Q.\"QuizName\", Q.\"QuizDescription\", Q.\"ImagePath\", Q.\"CreateDate\", Q.\"Active\", QC.\"Id\", QC.\"CategoryValue\", QC.\"CreateDate\", QC.\"Active\", QT.\"Id\", QT.\"TypeValue\", QT.\"CreateDate\", QT.\"Active\" FROM \"Quiz\" AS Q INNER JOIN \"QuizCategory\" AS QC ON Q.\"QuizCategoryId\" = QC.\"Id\" INNER JOIN \"QuizType\" AS QT ON Q.\"QuizTypeId\" = QT.\"Id\";")
if rows_err != nil {
return // eventually return a different status code and error struct
}
defer rows.Close()
for rows.Next() {
var r_quiz Quiz
var r_category QuizCategory
var r_type QuizType
scan_err := rows.Scan(&r_quiz.Id, &r_quiz.QuizName, &r_quiz.QuizDescription, &r_quiz.ImagePath, &r_quiz.CreateDate, &r_quiz.Active, &r_category.Id, &r_category.CategoryValue, &r_category.CreateDate, &r_category.Active, &r_type.Id, &r_type.TypeValue, &r_type.CreateDate, &r_type.Active)
r_quiz.QuizCategory = r_category
r_quiz.QuizType = r_type
if scan_err != nil {
return // eventually return a different status code and error struct
}
result = append(result, r_quiz)
}
responseWriter.Header().Set("Content-Type", "application/json; charset=utf-8")
responseWriter.Header().Set("Access-Control-Allow-Origin", config.AllowOrigin)
json.NewEncoder(responseWriter).Encode(result)
}
</code></pre>
<p>Pretty standard stuff... but after looking at the json capabilities of postgres I started thinking about doing the serialization on the database itself. Something like this :</p>
<pre><code>SELECT
JSON_BUILD_OBJECT(
'id',Q."Id",
'results', (
SELECT
JSON_AGG(
JSON_BUILD_OBJECT(
'id',R."Id",
'resultName', R."ResultName",
'resultDescription', R."ResultDescription",
'imagePath', R."ImagePath",
'createDate', R."CreateDate",
'active', R."Active"
)
)
FROM "Result" AS R WHERE R."QuizId" = Q."Id"
),
'quizCategory', JSON_BUILD_OBJECT(
'id', QC."Id",
'categoryValue', QC."CategoryValue",
'createDate', QC."CreateDate",
'active', QC."Active"
),
'quizType', JSON_BUILD_OBJECT(
'id', QT."Id",
'typeValue', QT."TypeValue",
'createDate', QT."CreateDate",
'active', QT."Active"
),
'questions', (
SELECT
JSON_AGG(
JSON_BUILD_OBJECT(
'id', Qu."Id",
'questionValue', Qu."QuestionValue",
'options', (
SELECT
JSON_AGG(
JSON_BUILD_OBJECT(
'id', O."Id",
'resultId', (
SELECT R."Id" FROM "Result" AS R INNER JOIN
"OptionResult" AS OpR ON R."Id" = OpR."ResultId"
WHERE OpR."OptionId" = O."Id" AND O."QuestionId" = Qu."Id" AND Qu."QuizId" = Q."Id"
),
'optionValue', O."OptionValue",
'createDate', O."CreateDate",
'active', O."Active"
)
)
FROM "Option" AS O WHERE O."QuestionId" = Qu."Id" AND Qu."QuizId" = Q."Id"
),
'createDate', Qu."CreateDate",
'active', Qu."Active"
)
)
FROM "Question" AS Qu WHERE Qu."QuizId" = Q."Id"
),
'quizName', Q."QuizName",
'quizDescription', Q."QuizDescription",
'imagePath', Q."ImagePath",
'createDate', Q."CreateDate",
'active', Q."Active"
)
FROM "Quiz" AS Q INNER JOIN
"QuizCategory" AS QC ON Q."QuizCategoryId" = QC."Id" INNER JOIN
"QuizType" AS QT ON Q."QuizTypeId" = QT."Id"
WHERE Q."Id" = $1;
</code></pre>
<p>Then, instead of having Go loop the returned data set and serialize it to a struct, simply call row.scan into a string and return it to the responseWriter.</p>
<p>I was hoping someone with similar needs could enlighten me on their experience and/or best practices.</p>
<hr/>**评论：**<br/><br/>billrobertson42: <pre><p>That's up to you I think. I'm sure performance is comparable. Which one is more maintainable? e.g. If you come back a week or a month later and glance at one implementation, are you able to instantly understand what's going on?</p>
<pre><code>There are lots of things I really dislike about postgres from a MSSQL point of view (pgSQL being the big one)
</code></pre>
<p>pgSQL? Do you mean pgadmin?</p>
<p>I'm also curious about what other things you find you don't like coming from a MSSQL background.</p></pre>collin_ph: <pre><p>+1 for the pgSQL question. What are you referring to?</p></pre>xsolarwindx: <pre><p>He's talking about PL/pgSQL and it's fine. If you don't like it, you have the option to use other languages as well. Postgres gives you ultimate flexibility. </p></pre>maruwan: <pre><p>If you're going to put so much logic in SQL, I'd prefer it if you used views. But I'm not opposed to putting Postgres to work.</p></pre>TotesMessenger: <pre><p>I'm a bot, <em>bleep</em>, <em>bloop</em>. Someone has linked to this thread from another place on reddit:</p>
<ul>
<li>[<a href="/r/postgresql" rel="nofollow">/r/postgresql</a>] <a href="https://np.reddit.com/r/PostgreSQL/comments/3oa7p8/postgresql_and_golang_serializing_data_at_the/" rel="nofollow">PostgreSQL and Golang, serializing data at the database level vs. serializing with Go. : golang</a></li>
</ul>
<p><a href="#footer" rel="nofollow"></a><em><sup>If you follow any of the above links, please respect the rules of reddit and don't vote in the other threads.</sup> <sup>(<a href="/r/TotesMessenger" rel="nofollow">Info</a></sup> <sup>/</sup> <sup><a href="/message/compose?to=/r/TotesMessenger" rel="nofollow">Contact</a>)</sup></em></p>
<p><a href="#bot" rel="nofollow"></a></p></pre>mansquid: <pre><p>This question just hit <a href="/r/programming" rel="nofollow">/r/programming</a> in so many ways and I think the answer is the same. the thread is here:
<a href="https://www.reddit.com/r/programming/comments/3o72dr/why_sql_is_neither_legacy_nor_lowlevel_nor/" rel="nofollow">https://www.reddit.com/r/programming/comments/3o72dr/why_sql_is_neither_legacy_nor_lowlevel_nor/</a></p>
<p>the tl;dr is:</p>
<p>Use materialized views if you absolutely have to store logic in postgres. You're probably better off managing it with your go code. Why? Loose Coupling -- if your database is tightly coupled and has critical business logic that your application Has to have, you run a bigger risk of your database creating headaches instead of storing data.</p></pre>
