4 ms·

The database/sql package serves as an intermediary abstraction layer (but still low-level) between the programmer's standardized API and the database. It's resp

The database/sql package serves as an intermediary abstraction layer (but still low-level) between the programmer's standardized API and the database. It's responsible for managing interactions with the database, such as communicating with a remote database server or accessing a local database like SQLite. Keep in mind that database/sql requires a compatible database driver (e.g., mattn/go-sqlite3) to facilitate this communication. In other programming environments, this concept is like ODBC or JDBC. If you were using an ORM, usually the ORM would expect to be sitting on top of the lower-level database/sql. (It's turtles all the way down!) Dropping the database/sql compatibility requirement allows for more direct (and possibly faster) interaction with the database. This approach can unlock database-specific features and potentially improve performance, but it clearly deviates from the standard API envisioned in database/sql. You might also end up losing the ability to use an ORM, but usually you don't really want to mix ORM and non-ORM in the same codebase anyway. However, adhering to database/sql also brings significant benefits, such as simplifing switching between different database systems with only small code alterations. While schema changes might still be necessary, they are generally external to the core codebase.

euroderf 3y ago
Thank you for this explanation. I've already decided Yes to SQLite (only) and No to ORMs, so maybe zombiezen is the way to go.
ncruces 3y ago
This is a great answer to the parent question, I'll just add that I (try) to provide both a database/sql driver, and a lower level direct mapping of the SQLite C API. Pretty much the only thing the lower level wrapper does for you is simplify some of the memory management inherent to a C API (who allocates? who frees? for everything even something as trivial as an error message: GC+copying handles lightweight types like strings and errors, io.Closer is used for everything else). At the same time, I also made a serious effort to make many SQLite features (like blob IO, nested transactions, JSON, time handling) easy to access for users of the database/sql driver.
euroderf 3y ago
I guess my unexpressed actual question was whether the API is materially different for small-scale operation.
- ncruces 3y ago
  database/sql adds some overhead; in return, it gives you a connection pool, and tries not to have you worry about connections. A lot of the interesting features of SQLite are connection oriented, and compared to other databases, connections are cheap (because it's all local). So the things database/sql offers are (relatively) less valuable, and it makes other things harder. Yet (IMO) connection pools still offer some performance benefits, not having to worry about (re)preparing statements on new connections is still helpful, and many of the connection oriented things you might want to do can be exposed through database/sql with some imagination. I have a lot of respect for crawshaw and zombiezen; I started from their PoV. But I've since come around to database/sql. A lot of what is mentioned in [1] has been fixed in database/sql or can be fixed in drivers: parameter names now work in database/sql; their approach to statement caching isn't necessarily better, but can be added to a driver, or live in a compatible framework; panics vs. errors is contentious; savepoints can be made to work just fine [2]; and for contexts, I started with crawshaw and built an improved version of it into my driver, that is both low overhead, interacts well with the Go scheduler, and doesn't start additional goroutines. Living in database/sql land gives you access to other amazing tooling like SQLC, just to name a single one. And it doesn't need to be either/or: drivers should support both high and low level APIs. [1]: https://crawshaw.io/blog/go-and-sqlite https://crawshaw.io/blog/go-and-sqlite [2]: https://pkg.go.dev/github.com/ncruces/go-sqlite3/driver#example-Savepoint https://pkg.go.dev/github.com/ncruces/go-sqlite3/driver#exam...

euroderf 3y ago

Thank you for this explanation. I've already decided Yes to SQLite (only) and No to ORMs, so maybe zombiezen is the way to go.

ncruces 3y ago

This is a great answer to the parent question, I'll just add that I (try) to provide both a database/sql driver, and a lower level direct mapping of the SQLite C API. Pretty much the only thing the lower level wrapper does for you is simplify some of the memory management inherent to a C API (who allocates? who frees? for everything even something as trivial as an error message: GC+copying handles lightweight types like strings and errors, io.Closer is used for everything else). At the same time, I also made a serious effort to make many SQLite features (like blob IO, nested transactions, JSON, time handling) easy to access for users of the database/sql driver.

euroderf 3y ago

I guess my unexpressed actual question was whether the API is materially different for small-scale operation.

ncruces 3y ago
database/sql adds some overhead; in return, it gives you a connection pool, and tries not to have you worry about connections. A lot of the interesting features of SQLite are connection oriented, and compared to other databases, connections are cheap (because it's all local). So the things database/sql offers are (relatively) less valuable, and it makes other things harder. Yet (IMO) connection pools still offer some performance benefits, not having to worry about (re)preparing statements on new connections is still helpful, and many of the connection oriented things you might want to do can be exposed through database/sql with some imagination. I have a lot of respect for crawshaw and zombiezen; I started from their PoV. But I've since come around to database/sql. A lot of what is mentioned in [1] has been fixed in database/sql or can be fixed in drivers: parameter names now work in database/sql; their approach to statement caching isn't necessarily better, but can be added to a driver, or live in a compatible framework; panics vs. errors is contentious; savepoints can be made to work just fine [2]; and for contexts, I started with crawshaw and built an improved version of it into my driver, that is both low overhead, interacts well with the Go scheduler, and doesn't start additional goroutines. Living in database/sql land gives you access to other amazing tooling like SQLC, just to name a single one. And it doesn't need to be either/or: drivers should support both high and low level APIs. [1]: https://crawshaw.io/blog/go-and-sqlite https://crawshaw.io/blog/go-and-sqlite [2]: https://pkg.go.dev/github.com/ncruces/go-sqlite3/driver#example-Savepoint https://pkg.go.dev/github.com/ncruces/go-sqlite3/driver#exam...