Is this a good way to set up DB access for my application?

I have a fairly simple application that relies on database access (let's say to Postgres). I'm looking for a good way to make such access available to different parts of the application, ideally without needing to pass around a connection/pool reference into all functions that need to use it.

I came across

which uses lazy_static to initialize a connection pool within a db module. Then other parts of the code can call db::connection() to get a connection and execute queries against it.

Does this seem like a good approach? Is there another way that may be better? I'm aware that it's potentially somewhat of a singleton pattern (with all the associated drawbacks), though it seems to fit my objectives in terms of usability. In fact I'm not sure I can avoid a singleton pattern, unless I'm prepared to pass the connection (pool) reference around.

Thanks in advance for any advice!

[FWIW this is a CLI application that does not have any concurrency/multi-threading - though these may be introduced in the future]

Just wanted to bump this. And maybe a better question is: Can anyone point me to what you consider a good example/best practices for having a DB connection be initialized and used by a real-world application? (ideally something semi-relevant to my use case like a CLI app, rather than let's say a web server)

Unfortunately internet searches for such examples mostly lead me either to minimal examples of opening and querying a connection in main(), or things like the above - which look OK but maybe not great.

I would personally not use this approach.

Often it's nice to initialize your database at the top of main() because you have control over how you connect to it (e.g. accept a --database argument, then fall back to the $DATABASE_URL variable if defined, else a hard-coded value) and you can tell which endpoints will require database access just by looking at their signatures.

Actix has a Databases page for showing how you might add an "extractor" to your handler's signature to get access to your app's database pool without having to manually pass database handles to each handler.

3 Likes

Thanks @Michael-F-Bryan that's a really helpful reference point!

Yes, I agree this is the ideal way to set it up. What I was in principle hoping to avoid is passing the database connection (pool) around to all functions...but maybe I should just bite it and do that?

I looked at the Actix docs (also e.g. examples/main.rs at master · actix/examples · GitHub), but am not quite sure what to make of it (or whether I'm looking in the right place). In their example the DB connection is attached to the HttpServer object, and passed into handlers as the server runs + requests come in, which of course makes sense. I don't really have an equivalent process in my app, but maybe it's worth exploring creating some sort of "shared state" object that functions can pull in in order to get a connection? This can be dynamically initialized in main(), but beyond that may not be much better than a singleton.

Anyway, having looked at all that I'm thinking it may make sense to just create the connection pool in main() and pass it around after all...

One possibility would to make POOL a OnceCell, then initialize it at the top of main(). That would pretty much do exactly what you're looking for.

1 Like

I personally would avoid putting a pool in a global variable, as this opens the door for scenarios where your application could exhaust the connection pool or use different connections while a transaction is going one. Consider the functions foo and bar, where foo calls bar. With a global pool it's easily possible to get a connection own connection in foo and bar, but that might fail if foo got the last connection and holds that one while calling bar. In this case bar will never finish, because it waits for the pool to return a connection. But the pool can only return a connection to bar if some other code path returns a connection to the pool, which might not happen at all. (In fact that "holding" the connection while calling the other function is the default behaviour there, so it's not just an edge case).

Because of this I prefer passing around explicit connection arguments as these make such potential dead locks much more obvious.

3 Likes

Thanks everyone for the input! On balance it seems that simply creating a connection/pool in main() and passing it around is probably the best solution after all. All other options appear to essentially amount to a singleton pattern, with the well-publicized drawbacks thereof. Explicitly passing also makes mocking and tests easier, so I’ll just go with that.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.