CRUD in the stored procedure instead of embedded SQL in go?

<p>I have been working for one company my entire career and we have always use MSSQL stored procedures for CRUD. These stored procedures look like this:</p> <pre><code>create proc dbo.sp_select some parameter as select * from table where col1 = parameter or create proc dbo.sp_update some parameter as update table set col1 = &lt;something&gt;, col2 = &lt;something&gt; where ID = parameter </code></pre> <p>Now I&#39;m starting my own project and am trying to catch up with the latest and greatest (or mostly stuff I don&#39;t know). I noticed that for php/mysql, this doesn&#39;t seem to be a practice that most people do. Most of the php i have seen have SQL statements embedded in the code. I&#39;m assuming it&#39;s because many of the php projects are smaller and there&#39;s no need to recompile in order to deploy, therefore, it is pretty easy to update SQL in php code (along with version control and so on)</p> <p>I have also been looking at golang and I&#39;m wondering, is this practice something that go developers do? </p> <hr/>**评论：**<br/><br/>ItsAPuppeh: <pre><p>Calling a stored procedure still requires you to send a string to the DB so there really is no difference from the POV of calling into the DB from any language. You can call SQL strings in .NET, and stored procedures in PHP.</p> <p>Using a stored procedure is a bit safer (no SQL injection possible), and faster (the query plan doesn&#39;t have to be recreated each call). They come at the cost of having to know the DB specific stored procedure language, having an extra DB migration step. You also now risk having business logic spread across both the app and DB which some people don&#39;t much like.</p> <p>I&#39;ve personally always just stuck w/ keeping SQL in the app for simplicity sake. I&#39;m a big fan of the KISS principle.</p></pre>the_birds_and_bees: <pre><blockquote> <p>Using a stored procedure is a bit safer (no SQL injection possible)</p> </blockquote> <p>Not entirely true. With a suitably poorly written stored proc you could still have an sql injection opportunity.</p> <p>For example</p> <pre><code>create proc dbo.find_product @search nvarchar(50) as sql = &#39;insert into result select * from products where name like &#39;&#39;%&#39; + @search + &#39;&#39;%&#39; exec @sql </code></pre> <p>A pretty contrived example, but the point is you can&#39;t just blindly trust that you&#39;re safe from sql injection without knowing what the proc does.</p></pre>hahainternet: <pre><blockquote> <p>You also now risk having business logic spread across both the app and DB which some people don&#39;t much like.</p> </blockquote> <p>While this is a worry, what should be stored in the database is &#39;data logic&#39;. If your business logic is inherently tied up with your datastore to the point that you must enforce all data go through your intermediate wrapper this is a very bad design.</p> <p>Not that I want to have a go at you, but I come across this idea (That code should not be in the DB) constantly and it is problematic. Databases provide certain consistency guarantees and stored procedures can be part of this.</p></pre>interactiv_: <pre><blockquote> <p>I&#39;ve personally always just stuck w/ keeping SQL in the app for simplicity sake. I&#39;m a big fan of the KISS principle.</p> </blockquote> <p>each time you use a transaction, or use a constraint on a foreign key you are putting business logic into your DB. IMHO one should put as much logic in the DB layer as required to ensure data integrity. This isn&#39;t the role of an app to ensure data integrity. That&#39;s why I believe transactions should be done in stored procedures, not in the app code. This however, doesn&#39;t have to be done while creating the app, but when the architecture is fairly stable.</p></pre>IndianAlien: <pre><p>I have always stored the queries in code and executed via db.Exec.</p> <p>Random question, what ODBC libraries are people using? I am having issues with Alex Brainman&#39;s library on RHEL6.</p></pre>mgutz: <pre><p>The main advantages of stored procedures is less network chat and not having to parse and compile execution plan. In large corporations, the DBA can optimize sprocs without having to know another language like Go.</p> <p>The disadvantage is business logic will creep into stored procedures and fixing, diagnosing bugs becomes more difficult. I think this is the appeal of ORMs/CRUD in code.</p> <p>We tend to do most SQL in Go code and use stored procedures when we need to access many tables. There are many database package options Go:</p> <p>ORMs - gorm, xorm</p> <p>Minimalist SQL - sqlx, dat, dbr</p> <p>If you like SQL and don&#39;t want to learn a DSL then <code>sqlx</code> is IMHO the best solution. <code>dat</code> adds some sugar over <code>sqlx</code> for Postgres.</p></pre>tmornini: <pre><p>CRUD is a verbal mismatch for HTTP.</p> <p>Put, Post, Get and and siblings are your friends.</p> <p>Get to know them, they make building applications a lot easier.</p></pre>cerealbh: <pre><p>HTTP has nothing to do with crud nor op&#39;s question. </p></pre>tmornini: <pre><p>We agree on your first point, which was my point, which makes me believe we don&#39;t really see eye-to-eye, and I stand corrected on your second.</p> <p>At the end of the day I should have simply encouraged OP to stop thinking in terms of CRUD and instead consider the HTTP verb set which is, IMHO, a huge improvement over CRUD.</p></pre>cerealbh: <pre><p>GET POST PUT and DELETE have nothing to do with crud. They are part of a protocol. Using them you can implement CRUD for recording some type of data. With the POST request alone you can do all the steps of CRUD. Two totally different layers in the OSI. Edit: well actually you can&#39;t record data with them, you have to pass it on to something else. </p></pre>tmornini: <pre><p><a href="https://www.ietf.org/rfc/rfc2616.txt" rel="nofollow">&#34;The Hypertext Transfer Protocol (HTTP) is an application-level protocol for distributed, collaborative, hypermedia information systems&#34; - RFC 2616</a></p> <p>It&#39;s an application layer protocol, not a wire level protocol. How I wish I had understood this earlier than I did!</p> <p>Yes, you could choose to radically misuse HTTP by using POST to do things it was never intended to do.</p> <p>I encourage you to re-read that RFC -- starting with the section on verbs and response codes and really think through what it says.</p></pre>interactiv_: <pre><p>Don&#39;t know why you are getting downvoted you are absolutely right. The parent is the reason why people don&#39;t get HATEOAS. HTTP verbs are semantic and there are fundamental differences between them. Protocols like SOAP were born out of the misunderstanding of HTTP.</p></pre>tmornini: <pre><p>Thanks for understanding that HTTP is not a way to interface with applications, it&#39;s a way of architecture for building them! :-)</p></pre>skidooer: <pre><p>Where does HTTP come into this discussion? I don&#39;t see it mentioned anywhere.</p> <p>CRUD = Create, read, update and delete. Any application that performs those four operations is a CRUD application. The application may be nothing more than a simple command line utility.</p></pre>tmornini: <pre><p>I&#39;m suggesting original poster consider HTTP data handling semantics, i.e. PUT/GET/DELETE rather than CRUD semantics.</p> <p>The verbs mean different things, and should not be trivially mapped to the other set. Doing so myself caused me to miss the real beauty of HTTP for a very long time.</p> <p>Today, I choose to build everything I can using the HTTP verb set, even when there&#39;s no HTTP over the wire, and I was simply suggesting that that OP might benefit from their use, as I do.</p></pre>skidooer: <pre><p>Okay, perhaps you can elaborate on how thinking in DPPG (or SUID?) changed the way your architect your application?</p> <p>Seeing the beauty of HTTP isn&#39;t much of an issue here. There is no indication that the original commenter will ever be using the HTTP protocol in his applications.</p></pre>tmornini: <pre><p>Using HTTP semantics makes applications easier to scale, replicate, distribute data and/or work nearer to the end-user, synchronize, backup, and integrate with -- even when no HTTP over the wire is in use.</p></pre>hahainternet: <pre><p>You are wrong in including HTTP. But I also thought I should point out that PUT is an all but worthless HTTP Verb and I advise against using it where possible. POST and PATCH occupy the &#39;submit a new item&#39; and &#39;change the attributes of an item&#39; requirements, PUT is only suitable for &#39;place this item at position X&#39; actions which are rarely required.</p></pre>tmornini: <pre><blockquote> <p>You are wrong in including HTTP</p> </blockquote> <p>Wrong how?</p> <blockquote> <p>I should point out that PUT is an all but worthless HTTP Verb</p> </blockquote> <p>I couldn&#39;t disagree more, but I certainly did feel the way you did once.</p> <p>Allowing the caller to set the ID makes applications much easier to stitch together and keep synchronized.</p></pre>hahainternet: <pre><blockquote> <p>Wrong how?</p> </blockquote> <p>It&#39;s orthogonal to CRUD</p> <blockquote> <p>Allowing the caller to set the ID makes applications much easier to stitch together and keep synchronized.</p> </blockquote> <p>Please show me how you do the equivalent of a standard BEGIN… COMMIT cycle with a caller supplying the ID over HTTP PUT.</p></pre>tmornini: <pre><blockquote> <p>It&#39;s orthogonal to CRUD</p> </blockquote> <p>Maybe yes, maybe no. While it may be technically, I know of few developers who spend most of their time in SQL databases who&#39;ve even considered upsert (PUT) semantics, so I&#39;m not 100% in agreement on this.</p> <blockquote> <p>Please show me how you do the equivalent of a standard BEGIN… COMMIT cycle with a caller supplying the ID over HTTP PUT</p> </blockquote> <p>Does your DB not allow you to set the ID column manually?</p> <p>And, finally, <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=168d5805e4c08bed7b95d351bf097cff7c07dd65" rel="nofollow">PostgreSQL supports upsert semantics</a></p></pre>