Database drivers which are save for concurrent use and contain an orm mapping

[hueuebi]

Hello all,

basically, I am interested which other database drivers have those or similar capabilities.

For example, the official mongo db driver GitHub - mongodb/mongo-go-driver: The Official Golang driver for MongoDB kinda brings both to the table.

Kind regards

[christophberger]

Hi @hueuebi,

Typically, SQL drivers do not cover object-relational mapping at all. This is left to separate ORM libraries like GORM, Ent, etc. I think that’s a good approach because it keeps different concerns clearly separated.

(MongoDB is not a relational database, hence the creators of the Go package might have felt the need to deliver a document-to-object mapping with their driver. But I am just guessing.)

DB objects created with the stdlib package database/sql are safe for concurrent use. Therefore, all SQL drivers that were made for use by the database/sql package should be written in a way that does not break this assertion.

[Dean_Davidson]

Just a quick note if you plan to use jackc/pgx (a popular postgres driver). The example on the README is not safe for concurrent use:

// urlExample := "postgres://username:password@localhost:5432/database_name"
conn, err := pgx.Connect(context.Background(), os.Getenv("DATABASE_URL"))
if err != nil {
	fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
	os.Exit(1)
}
defer conn.Close(context.Background())

You have to go to the Getting Started page to see this:

The *pgx.Conn returned by pgx.Connect() represents a single connection and is not concurrency safe. This is entirely appropriate for a simple command line example such as above. However, for many uses, such as a web application server, concurrency is required. To use a connection pool replace the import github.com/jackc/pgx/v5 with github.com/jackc/pgx/v5/pgxpool and connect with pgxpool.New() instead of pgx.Connect().

Figured I’d note this because it baffles me that the main README doesn’t start with a pool or at least note it when gophers are used to concurrency-safe DB handles by default:

DB is a database handle representing a pool of zero or more underlying connections. It’s safe for concurrent use by multiple goroutines.

Anyway, just a potential gotcha to look out for. If using pgx, be sure to use pgxpool.New().

[hueuebi]

Thanks a lot for your reply. Sadly, GitHub - mattn/go-sqlite3: sqlite3 driver for go using database/sql is not safe for concurrent use.

(MongoDB is not a relational database, hence the creators of the Go package might have felt the need to deliver a document-to-object mapping with their driver. But I am just guessing.)

That makes a lot of sense, I completely forgot about that.

My problem with the concurrent usage is, that multiple writes are supported, but not multiple reads. And basically the complexity is offloaded to the developer who normally solves this issue by using a mutex which in my opinion is additional boilerplate code. On the other hand, a db-driver can only support the specs of the underlying db and also must adhere to restrictions imposed onto it from the db.

[hueuebi]

Thanks for that reply @Dean_Davidson !

[christophberger]

@hueuebi

GitHub - mattn/go-sqlite3: sqlite3 driver for go using database/sql is not safe for concurrent use.

It is safe, in the sense of not causing data races or data corruption. But you are right, SQLite itself does not allow more than one concurrent write.

There are approaches to mitigate this restriction, see, for example, this comment in issue #1179:

Unfortunately, the README is a little misleading. This library is already safe for concurrent use. However, there are a few caveats that need to be acknowledged.

SQLite will support any number of concurrent reads under any mode. It does not support a concurrent read and write to the same database file under the default rollback journal mode, but this is supported under WAL mode. It does not support concurrent writes to the same database file under any mode. It is generally recommended to use WAL. Please note that “does not support” means that it will perform these operations serially, with a wait/retry period defined by your busy handler (or the busy_timeout pragma). If the wait period expires before the operation can proceed, then you will get SQLITE_BUSY (aka “database is locked”).

You may have come across some suggestions to use shared cache mode to avoid SQLITE_BUSY. Do not follow these suggestions. The SQLite authors themselves discourage using this mode under any circumstance. If you use it, it may appear to resolve the issue, but then you can start to get the similar-looking SQLITE_LOCKED (aka “database table is locked”).

If your code is going to read and write to the database as part of the same operation, you must use a single transaction for the whole thing. Otherwise, because of how sql.DB works, you can run into issues where the in-progress read on one connection blocks the attempted write on the other connection.

Whenever you start a transaction that might write to the database, always use BEGIN IMMEDIATE, not the default (BEGIN DEFERRED). Otherwise, if another connection also writes to the database in the middle of your transaction, you can get SQLITE_BUSY without your busy handler even being triggered, due to SQLite’s need to enforce the ACID requirements of a database engine. If your transaction will definitely only read, then you should continue to use BEGIN DEFERRED in order to allow concurrent reads to function.

My general recommendation is to have two connection pools (i.e., sql.DB instances). One should be read-write, configured to use BEGIN IMMEDIATE, and throttled to a single connection via SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime, and SetConnMaxIdleTime. The other should be read-only, configured to use BEGIN DEFERRED (which is the default), and can be throttled to any number of connections within the limits of your system.