Database Best Practices

[arlbo...@gmail.com]

Ahoy Gophers,

Golang itself is exciting me all the time. So I'm currently in search&learn mode. But I found little to none database examples.

I know there are database drivers to use with database/sql. But I'm not going to write my MySQL query into my code right? I saw beego and gorp. They are good solutions but still need some work.

I'm wondering the ways of your db transactions and data access layers.

Note: I'm coming from .NET background and used to work with Stored Procedures on MSSQL rather than ORMs.

Thanks!

[Daniel Theophanes]

I would stick with things like Stored procedures and shun ORMs. Things you can use:

Alex's odbc driver: https://code.google.com/p/odbc/

Multi-line literals can be useful sometimes (back-tick) `.

PostgreSQL is also a good database with good documentation and good go drivers: github.com/bmizerany/pq

If you're doing greenfield development I would recommend PostgreSQL over MySQL.

For data access layers, I would suggest having a dedicated package or two where you do your data access. For smaller tables where I can easily host in memory, I sometimes like to just return an in-memory DataTable (similar in spirit to ADO.Net DataTable) so I don't have to worry about connections once I get the data. For one example of code to do that:

http://godoc.org/bitbucket.org/kardianos/table

-Daniel 

[GreatOdinsRaven]

On Wednesday, August 21, 2013 10:47:19 AM UTC-6, Daniel Theophanes wrote:

I would stick with things like Stored procedures and shun ORMs. Things you can use:

Shun ORMs? Herecy! There are some very decent Go ORM and ORM-like solutions, like gosexy (https://menteslibres.net/gosexy/db) and beedb (https://github.com/astaxie/beedb). They're pretty light-weight, not typical Hibernate-style ORM solutions.  

[Daniel Theophanes]

Without getting into a full debate, it is good that these are lighter wight. This is good. The problem is that using ORMs encourage the user to think about a table row as an object. It is a rather fundamental objection I have to them. The power of relational databases are in their power to join or select data. I very rarely use simple CRUD queries, and when I need them they are trivial to write. Most of my SQL code actually does something that is best done before moving off the database server. But each to their own.

[Peter Bourgon]

> I know there are database drivers to use with database/sql. But I'm not
> going to write my MySQL query into my code right?

Yes, of course. Why not?

[Daniel Theophanes]

In many business applications, it is considered proper form to only put SQL in stored procedures for various reasons. I have personally done it both ways. There are even companies which make it cooperate policy, thus, you may or may not have a choice in the matter. 

[Julien Schmidt]

You can find an introduction to the database/sql package at https://github.com/VividCortex/go-database-sql-tutorial

There are still some unresolved issues with stored procedures in the mysql driver since stored procedures in MySQL return multiple results which the database/sql package can't handle.

I personally recommend to use VIEWS: http://dev.mysql.com/doc/refman/5.7/en/views.html 

[buzzlight]

kdb (kiss database https://github.com/sdming/kdb) can support store procedure, sql template, sql expression, and orm.

and it has example of mysql, sql server, postgresql, sqlite, oracle.

example : https://github.com/sdming/kdb/tree/master/example

在 2013年8月21日星期三UTC+8下午9时24分24秒，arlbo...@gmail.com写道：

[Kushal Das]

On Wed, Aug 21, 2013 at 10:17 PM, Daniel Theophanes <kard...@gmail.com> wrote:
> I would stick with things like Stored procedures and shun ORMs. Things you
> can use:
> Alex's odbc driver: https://code.google.com/p/odbc/
> Multi-line literals can be useful sometimes (back-tick) `.
> PostgreSQL is also a good database with good documentation and good go
> drivers: github.com/bmizerany/pq
>

Can anyone pass me an example where the INSERT INTO query returns back
the last inserted row id ? I was trying with

ret, err := db.Exec("INSERT INTO components (name, description,
product_id, cowner) VALUES ($1, $2, $3, $4) RETURNING id", name, desc,
product_id, owner)
if err == nil {
rid, err := ret.LastInsertId()
fmt.Println(rid)
}

I tried the above given driver and also github.com/lib/pq driver.

Kushal

[Kamil Kisiel]

LastInsertId is not supported by lib/pq because the RETURNING clause is required, and it can return an arbitrary set of columns.

Instead use Query() and then Scan() the result.