SQLite Forum

One connection per function or one shared connection?

One connection per function or one shared connection?

(1) By anonymous on 2022-01-25 01:55:30 [link] [source]

Hi everyone,

I'm building an API (in Golang) and using Sqlite as the backend DB (version 3.27.2). The API is RESTful (accepts GET, UPDATE, DELETE, PUT from HTTPS clients) and returns JSON to them. I am using WAL mode in Sqlite. The only process that talks to the DB is the API. All the clients access the DB through the API.

Now, the question... I am currently using only one DB connection and passing it to all functions that run a DB query. The main Go function opens the one connection and defers conn.Close(). I also catch signals. So when I press Ctrl+C (sigint) in the terminal a clean-up process ensures conn.Close() is called before exiting. But I am concerned that the DB conn may not close properly in some cases. Would it be safer to have each function open a DB conn and close it? My gut says that would be safer, but maybe slower.

I appreciate any advice. I am using standard Go "database/sql" and this Sqlite library: https://github.com/mattn/go-sqlite3

Thanks a lot!

(2) By Simon Slavin (slavin) on 2022-01-25 03:00:40 in reply to 1 [link] [source]

Separate connections would be slower, as you thought.

One difference between one connection and multiple connections is that one connection has one transaction. If your API can do only individual SQL change commands, with no manual BEGIN or COMMIT, there is no difference: SQLite will wrap each one in its own transaction. But if you use BEGIN or END then you should know that they will affect all commands sent via the same connection: all commands until the END will all be bunched up as part of the same transaction.

Whether you do or do not want this to happen might settle whether you should use separate connections or not.

(4) By anonymous on 2022-01-25 10:57:58 in reply to 2 [link] [source]

Thank you for the reply. I do not explicitly use transactions in my SQL statements (BEGIN, END or COMMIT) only simple SELECTS, INSERTS and UPDATES. If I do have a need for transactions, I may experiment with multiple connections to see how slow they are compared to only one.

Thanks again!

(3) By Gunter Hick (gunter_hick) on 2022-01-25 06:49:50 in reply to 1 [link] [source]

There are two issues here: thread isolation/interaction and transactions.

I have no idea what exactly a Golang "function" is. If it corresponds to a thread of execution (e.g. it may run concurrently with another function), then there is no way to transparently use explicit transactions.

If your clients are basing their write requests on data previously received, they  need to use explicit transactions. Or you have to do that for them in the API.

(5) By anonymous on 2022-01-25 11:03:19 in reply to 3 [source]

Thank you for the reply. In this context a Golang function is a function in my Go program that contains SQL statements (single SELECTS, INSERTS and UPDATES) that run against the SQLite database.

An API client may obtain data from the database by calling an endpoint via the GET method and then modify that data before calling the same endpoint with PUT. The GET method performs a SELECT while the PUT performs a separate UPDATE.

I plan to continue using only one DB connection for now.

Thank you again for the advice!