3 ms·

That’s great to hear. Would you mind sharing how you implement/use 1 connection?

bob1029 5y ago
I would just think of it as 1 global static SQLiteConnection instance. There's really not a whole lot to it. Like 4 lines in a class file to set this up.
- alberth 5y ago
  How do you implement this using a stateless stack (eg PHP)?
  - dgfitz 5y ago
    I’d build a (stateful) shim micro service that the stateless code interacts with, no idea if that is the “most correctest” solution though
  - olalonde 5y ago
    I think what bob1029 meant was just that opening multiple connections doesn't increase concurrency so don't do it unnecessarily (by using a connection pooling library for example). With PHP you have one "process" per request, so you'd just need to make sure you only open a single SQLite connection per request (using the singleton pattern for example).
    - olalonde 5y ago
      PS: If this is incorrect, I'd love to know why instead of getting downvoted :)
      - bob1029 5y ago
        The traditional pattern of one connection per transaction is actually a strong anti-pattern when working with SQLite in particular. This is because SQLite is just a file, not a network connection or named pipe. Obtaining and releasing file handles is a non-trivial task that takes significantly longer than reusing the same one.
        olalonde 5y ago
        That's what I meant with "opening multiple connections doesn't increase concurrency so don't do it unnecessarily (by using a connection pooling library for example)". I was suggesting to only use a single connection for the entire request and NOT to use a connection pooling library. Now, PHP doesn't share memory between requests so there's no way to re-use the same connection across requests. That's just a sad fact about PHP and I don't think there's a reasonable way around it other than not using PHP. PS: I haven't used PHP in over a decade so my knowledge might be outdated.
        rad_gruchalski 5y ago
        > I don't think there's a reasonable way around it other than not using PHP. Or not using SQLite for this task?
        mschuster91 5y ago
        > Now, PHP doesn't share memory between requests so there's no way to re-use the same connection across requests. That depends on the execution model/PHP SAPI. mod_php and php-fpm use long-lived PHP processes and so can support persistent database connections depending on the configuration.
        alberth 5y ago
        FYI - PHP doesn't have native connection pooling (due to the execution model). And Persistent Connections is much different than a connection pool. https://stackoverflow.com/questions/39753/connection-pooling-in-php https://stackoverflow.com/questions/39753/connection-pooling...

I would just think of it as 1 global static SQLiteConnection instance. There's really not a whole lot to it. Like 4 lines in a class file to set this up.

alberth 5y ago
How do you implement this using a stateless stack (eg PHP)?
- dgfitz 5y ago
  I’d build a (stateful) shim micro service that the stateless code interacts with, no idea if that is the “most correctest” solution though
- olalonde 5y ago
  I think what bob1029 meant was just that opening multiple connections doesn't increase concurrency so don't do it unnecessarily (by using a connection pooling library for example). With PHP you have one "process" per request, so you'd just need to make sure you only open a single SQLite connection per request (using the singleton pattern for example).
  - olalonde 5y ago
    PS: If this is incorrect, I'd love to know why instead of getting downvoted :)
    - bob1029 5y ago
      The traditional pattern of one connection per transaction is actually a strong anti-pattern when working with SQLite in particular. This is because SQLite is just a file, not a network connection or named pipe. Obtaining and releasing file handles is a non-trivial task that takes significantly longer than reusing the same one.
      - olalonde 5y ago
        That's what I meant with "opening multiple connections doesn't increase concurrency so don't do it unnecessarily (by using a connection pooling library for example)". I was suggesting to only use a single connection for the entire request and NOT to use a connection pooling library. Now, PHP doesn't share memory between requests so there's no way to re-use the same connection across requests. That's just a sad fact about PHP and I don't think there's a reasonable way around it other than not using PHP. PS: I haven't used PHP in over a decade so my knowledge might be outdated.
        rad_gruchalski 5y ago
        > I don't think there's a reasonable way around it other than not using PHP. Or not using SQLite for this task?
        mschuster91 5y ago
        > Now, PHP doesn't share memory between requests so there's no way to re-use the same connection across requests. That depends on the execution model/PHP SAPI. mod_php and php-fpm use long-lived PHP processes and so can support persistent database connections depending on the configuration.
        alberth 5y ago
        FYI - PHP doesn't have native connection pooling (due to the execution model). And Persistent Connections is much different than a connection pool. https://stackoverflow.com/questions/39753/connection-pooling-in-php https://stackoverflow.com/questions/39753/connection-pooling...