PostgreSQL and Golang, serializing data at the database level vs. serializing with Go.

xuanbao · · 382 次点击    
这是一个分享于 的资源,其中的信息可能已经有所发展或是发生改变。
<p>I&#39;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&#39;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&#39;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&#39;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(&#34;user=%s dbname=%s password=%s host=%s port=%d sslmode=%s&#34;, 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(&#34;SELECT Q.\&#34;Id\&#34;, Q.\&#34;QuizName\&#34;, Q.\&#34;QuizDescription\&#34;, Q.\&#34;ImagePath\&#34;, Q.\&#34;CreateDate\&#34;, Q.\&#34;Active\&#34;, QC.\&#34;Id\&#34;, QC.\&#34;CategoryValue\&#34;, QC.\&#34;CreateDate\&#34;, QC.\&#34;Active\&#34;, QT.\&#34;Id\&#34;, QT.\&#34;TypeValue\&#34;, QT.\&#34;CreateDate\&#34;, QT.\&#34;Active\&#34; FROM \&#34;Quiz\&#34; AS Q INNER JOIN \&#34;QuizCategory\&#34; AS QC ON Q.\&#34;QuizCategoryId\&#34; = QC.\&#34;Id\&#34; INNER JOIN \&#34;QuizType\&#34; AS QT ON Q.\&#34;QuizTypeId\&#34; = QT.\&#34;Id\&#34;;&#34;) 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(&amp;r_quiz.Id, &amp;r_quiz.QuizName, &amp;r_quiz.QuizDescription, &amp;r_quiz.ImagePath, &amp;r_quiz.CreateDate, &amp;r_quiz.Active, &amp;r_category.Id, &amp;r_category.CategoryValue, &amp;r_category.CreateDate, &amp;r_category.Active, &amp;r_type.Id, &amp;r_type.TypeValue, &amp;r_type.CreateDate, &amp;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(&#34;Content-Type&#34;, &#34;application/json; charset=utf-8&#34;) responseWriter.Header().Set(&#34;Access-Control-Allow-Origin&#34;, 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( &#39;id&#39;,Q.&#34;Id&#34;, &#39;results&#39;, ( SELECT JSON_AGG( JSON_BUILD_OBJECT( &#39;id&#39;,R.&#34;Id&#34;, &#39;resultName&#39;, R.&#34;ResultName&#34;, &#39;resultDescription&#39;, R.&#34;ResultDescription&#34;, &#39;imagePath&#39;, R.&#34;ImagePath&#34;, &#39;createDate&#39;, R.&#34;CreateDate&#34;, &#39;active&#39;, R.&#34;Active&#34; ) ) FROM &#34;Result&#34; AS R WHERE R.&#34;QuizId&#34; = Q.&#34;Id&#34; ), &#39;quizCategory&#39;, JSON_BUILD_OBJECT( &#39;id&#39;, QC.&#34;Id&#34;, &#39;categoryValue&#39;, QC.&#34;CategoryValue&#34;, &#39;createDate&#39;, QC.&#34;CreateDate&#34;, &#39;active&#39;, QC.&#34;Active&#34; ), &#39;quizType&#39;, JSON_BUILD_OBJECT( &#39;id&#39;, QT.&#34;Id&#34;, &#39;typeValue&#39;, QT.&#34;TypeValue&#34;, &#39;createDate&#39;, QT.&#34;CreateDate&#34;, &#39;active&#39;, QT.&#34;Active&#34; ), &#39;questions&#39;, ( SELECT JSON_AGG( JSON_BUILD_OBJECT( &#39;id&#39;, Qu.&#34;Id&#34;, &#39;questionValue&#39;, Qu.&#34;QuestionValue&#34;, &#39;options&#39;, ( SELECT JSON_AGG( JSON_BUILD_OBJECT( &#39;id&#39;, O.&#34;Id&#34;, &#39;resultId&#39;, ( SELECT R.&#34;Id&#34; FROM &#34;Result&#34; AS R INNER JOIN &#34;OptionResult&#34; AS OpR ON R.&#34;Id&#34; = OpR.&#34;ResultId&#34; WHERE OpR.&#34;OptionId&#34; = O.&#34;Id&#34; AND O.&#34;QuestionId&#34; = Qu.&#34;Id&#34; AND Qu.&#34;QuizId&#34; = Q.&#34;Id&#34; ), &#39;optionValue&#39;, O.&#34;OptionValue&#34;, &#39;createDate&#39;, O.&#34;CreateDate&#34;, &#39;active&#39;, O.&#34;Active&#34; ) ) FROM &#34;Option&#34; AS O WHERE O.&#34;QuestionId&#34; = Qu.&#34;Id&#34; AND Qu.&#34;QuizId&#34; = Q.&#34;Id&#34; ), &#39;createDate&#39;, Qu.&#34;CreateDate&#34;, &#39;active&#39;, Qu.&#34;Active&#34; ) ) FROM &#34;Question&#34; AS Qu WHERE Qu.&#34;QuizId&#34; = Q.&#34;Id&#34; ), &#39;quizName&#39;, Q.&#34;QuizName&#34;, &#39;quizDescription&#39;, Q.&#34;QuizDescription&#34;, &#39;imagePath&#39;, Q.&#34;ImagePath&#34;, &#39;createDate&#39;, Q.&#34;CreateDate&#34;, &#39;active&#39;, Q.&#34;Active&#34; ) FROM &#34;Quiz&#34; AS Q INNER JOIN &#34;QuizCategory&#34; AS QC ON Q.&#34;QuizCategoryId&#34; = QC.&#34;Id&#34; INNER JOIN &#34;QuizType&#34; AS QT ON Q.&#34;QuizTypeId&#34; = QT.&#34;Id&#34; WHERE Q.&#34;Id&#34; = $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&#39;s up to you I think. I&#39;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&#39;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&#39;m also curious about what other things you find you don&#39;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&#39;s talking about PL/pgSQL and it&#39;s fine. If you don&#39;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&#39;re going to put so much logic in SQL, I&#39;d prefer it if you used views. But I&#39;m not opposed to putting Postgres to work.</p></pre>TotesMessenger: <pre><p>I&#39;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&#39;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&#39;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>
382 次点击  
加入收藏 微博
0 回复
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传