3rd party packages come and go but "database/sql" is here to stay.
The database is hands down the worst in the entire standard library.
Importing a package to pick your driver? Side effects much? Who needs multiple drivers in the same scope anyway?
Consistent behavior or test suits for drivers? nah... why bother.
The database API was poorly thought out. That's why most people use no sql solutions, and what tools do exist for it (eg. test suites) are cobbled together 3rd party collections that have to be manually updated when a new driver is written.
I'm literally flabbergasted to hear someone singing its praises.
The sqlite driver used to not work with the postgres one. I don't know why. I never looked into it... but, that this can happen is pretty bad, I'd argue.
Packages that invoke code when you import them and require this sort of import:
import _ "github.com/go-sql-driver/mysql"
The mapping process is very similar to how other packages in Go work (e.g. encoding/json), so if you already know Go it feels very natural.
What's wrong with just writing pure SQL?
1) Maintainability between the database and the software models. Specially when starting a new project, the database model can change a lot and it can be pain.
2) Every project I've seen using SQL DAOs, end up writing some sort of ORM. You can see this when you find a class called "Select" in your persistence code.
3) Not every developer is a good SQL developer.
4) Your persistence/DAO layer has to keep up with other stuff which an ORM usually provides: connection management, transactions, caching, key restrictions, error handling. This is a critical bug waiting to happen scenario.
I'm also not a big fan of ORM's (and actually have been trying to stay away from them in new projects) but most of the times it is the best solution.
I find that developers are better SQL developers than their ability to successfully design a domain model.
Use CQRS (Command Query Response Segregation) and stuff ORMs where the sun doesn't shine.
Separate Update/Insert/Delete commands and queries. Commands return nothing and are assumed to work unless an exception is thrown, then the whole batch is rolled back. Queries return data. Transaction boundaries are arbitrary based on the requirement of the calling service, not the unit of work and/or session. By default all transactions are imperative.
Stored procedures, triggers and views are banned.
Commands are classes with properties attached (easy to validate, test and provide good encapsulation) and are internally represented as raw SQL with bound parameters.
Queries return enumerable lists of objects and are internally represented as a raw SQL query with bound parameters.
Everything is programmed to generic interfaces: IStorageCommand<T> and IStorageQuery<T>
That is all. I've used Dapper on .Net to do the internal representation. It is portable across non SQL stores as well and has been used to interface with cache and Redis as well.
Plus due to the loosely coupled abstraction, you can use AOP to stick read-through cache interceptors in between the IStorageQuery<T> interface and the consumer. Also you can stick cache invalidators between IStorageCommand<T> and the consumer.
Moving one of our core subsystems over to this with some careful design and DBA consultation knocked our 48 Core Xeon SQL box from 60% utilisation to 5% (mainly IOPS/storage throughput. CPU is a non issue there).
Your domain objects are still a graph that need to be hydrated from the database. And dont tell me that writing hydrators by hand is fast and dry,especially when you have 100+ domain objects,compared to a few XML files or annotations that can later take advantage of query builders or HQL.
We don't write any hydrators. On our previous solution they are generated at compile time. Then they were replaced by runtime code generation inside Dapper.
There is no XML, there are no query builders, there is no HQL. There are too few guarantees in each of those technologies.
So... NoSQL? I'm only half-joking -- I find it strange how people will use an SQL DBMS, and yet choose not to use the main features. Model, Normalize/refactor, then apply views/triggers/stored procedures to present a simple "API" (or maybe just "view") of your datamodel to the application?
If you don't use the DBMS for modelling the data, do you really need it for storing your data (obviously, one valid answer is "no" -- see "NoSQL-movement", Object DMBS, Document DBs etc...).
The data model is what we're exposing, not the table model.
Out of curiosity, where do you fall in terms of having business logic in the database via functions and triggers? Why do you feel that way?
I'm kind of of the opinion that database should only store data, maintain relations, and handle filtered querying (read-only)...anything more complicated should be handled by application-side logic. What's your stance on that?
I've seen many many piles of fail where all the logic is pumped into the database where possible. The result is making scale up expensive and scale out impossible. Also where people do ETL straight into the tables resulting in constraint failures outside the limited capability of the database engine.
No we killed those expectations, everyone sees the benefits.
How do you (or others?) feel about foriegn keys? They seem like a really good idea for read-heavy workloads and for documentation and maintenance and interop with tools, but for write-heavy loads leaving them off seems to have non-trivial performance benefits (reference case is 9.2 PGSQL, if it matters). Again, my instincts are that they're probably the right thing to do, and that removing them later is going to be easier (if it turns out we need to) than trying to shoehorn them in too late.
If FK performance is an issue, then there are better solutions before you break them (denormalise on write etc).
Do you believe it was the move to CQRS specifically that caused the drop in utilization or mostly other factors (caching, the DBA consult)? A quick read through of CQRS makes it seem like its mostly a way to organize your data access code and should have little affect to performance.
The caching is the icing on the cake and is used to avoid execution latency rather than reduce IO as it can run async with the database queries. It deals with only the most common datasets due to the complexity of what looks like even simple cache invalidations.
For ref, I'm a DBA as well as a pile of other things...
One question out of curiosity - on your IStorageQuery interface what methods do you have? Do you have the usual by Id's etc or do you have a query object that you pass in that can build/translate to the relevant SQL?
public interface IStorageQuery<T,Q> where Q : IStorageQueryCommand
IEnumerable<T> Query(Q query);
Internally this is all bound to the query by property name.
Not a bad idea though.
Packaging and micro-services have a lot more promise if you ask me, so I'm all for that. Unfortunately the complexity of some systems, particularly in the financial sector, tend to make micro-services impossible as the smallest possible abstraction is huge and everything is tangled like spaghetti.
What's wrong with wanting to abstract something that only the database care about? when you want to read a file in go,do you execute a shell command like "cat myfile.txt" and pipe the result? or do you use a pure go api ?
Each time you do an http request,do you send headers and body through tcp sockets as text,or do you use an http api?
Go's os.Open function is a thin veneer around the open system call, which is the same thing cat uses.
The HTTP package provides as thin a layer over actual HTTP as possible.
The story with database/sql is the same.
I dont see why it's nice to have an abstraction for HTTP but as soon as it comes to RDBMSs, string commands should be the way to go...
(This is why I dig mappers for document-based databases and avoid them for relational ones.)
The idea is to take the raw concepts of data atoms and abstract them into programmatic entities. This way, you can work with the data directly and not need to worry about SQL semantics and nuance.
Additionally, there's the side effect of encouraged utilization of DB server functionality/optimization (views [materialized and direct], triggers, procedures, enforced foreign keys, indices, etc).
Point being, different folks like to work at different levels of abstraction.
There's an annoying mismatch between SQL and data structures. SQL's approach of making all queries a big string is a pain, especially since careful escaping is needed to avoid SQL injection vulnerabilities. However, in Go, it's not too hard to get data in and out of SQL as an "interface", which is a mixed array of any type. Converting that to a struct is straightforward. This comes up often enough that Go should have compiler support for struct <-> interface. It can be done through introspection, as some of the ORMs do, but that's slow. The layout of the struct is fixed at compile time, so this can generate fast code, mostly type checks and moves. In general, we should have more compiler support for marshalling. It's done a lot, it's easy to compile, and it's slow to do interpretively.
If we had that, much of the need for the ORMs described would go away.
I recently started using gorm  and have found that it meets my needs and works nicely, and the maintainer jinzhu has been helpful and pleasant to interact with. So with that being said, it's still an ORM and comes with the usual laundry list of caveats.
You can see it used extensively in this sample application:
But, it looks like sqlx may be a better alternative.
But ended up with a custom type implementing Scan & Value.
Check this out: https://groups.google.com/forum/#!topic/golang-nuts/aPJVwRQq...
Good luck :-)
we're just never going to learn....
Could you please make your comment explicit?