Sockets are not used much for database connections in the Java (JDBC) world. For local development, you might want to look at embedded PostgreSQL -- unless you specifically need the database to persist on the file system?

The next.jdbc tests use embedded PG -- you can see the dependencies for it in deps.edn and an example of starting it up in the test fixtures.

Thanks very much @U04V70XH6 - appreciate your help! I'll have to check out embedded PG too, sounds useful

You'd have to check the release notes for the JDBC driver to see if it supports sockets on recent JDKs but I very much doubt it will -- the PG JDBC team are a bit resistant to change (they still haven't fixed the exception hierarchy, so they're years behind some other JDBC drivers). You could always try adding that com.kohlschutter.junixsocket/junixsocket-core library and using the JDBC URL parameters they show (not sure if you'll be able to use the :dbtype hash map approach for that -- which I'd normally recommend -- or whether you'll have to construct the JDBC URL yourself).

It probably isn't the execution of queries that is taking that time, it's the creation of a JDBC Connection from the DataSource. If you are just playing around, you could do (def conn (jdbc/get-connection ds)) after your ds definition and then pass conn to execute! etc instead of ds.

Thanks, @U04V70XH6. This solution made two queries from using ds with more than 1 milliseconds to the same queries using conn to take about 719 microseconds. Thanks!! What do you mean by “If you just playing around”? I want to use SQLite as an in-memory data store (read-only with periodic updates from a side process). Another thing, trying to find my way around this I encountered another performance solution to copy my SQLite database I have in a file to a db-spec with {:dbtype "sqlite" :dbname ":memory:"} and to work with that db-spec. I guess my question is also what is the best I can get from SQLite as an in-memory data store where my database is not very big (currently less than 1 MB and won’t be much bigger)?

Playing around: having top-level def with side-effects. That's not good in production code.

If you want an in-memory database, I'd suggest H2 (which is definitely supported in-memory with next.jdbc since it is actually used in the test suite).

Or do you mean you need it on disk for persistence but want to copy it into memory for performance?

If the latter, perhaps you don't need it to be SQL-based in memory? Read the on-disk version entirely into memory as Clojure data and construct hash maps based on primary/foreign keys?

I’m interested in having an SQLite db for other reasons, so I want to make it work with better performance. I can see how the last option (clojure map) will work and work very well. Just wondered if I can do much better within SQLite database. Thought configuring SQLite differently can do the job - working with a memory SQLite or other solutions.

For production, most people use a connection pooling library (e.g., HikariCP) so that connections are longer-lived and you avoid that overhead. The next.jdbc docs talk about that and have examples.

I will look into it, thanks

@U04V70XH6 Thanks, the connection pooling improved the results of my app. I used this (defonce ^:private ds (connection/->pool HikariDataSource {:dbtype "sqlite" :dbname db-name})) I have 2 more questions regarding the SQLite database - 1. I’m still wondering how can I benefit from the sqlite https://www.sqlite.org/pragma.html#pragma_cache_size:~:text=PRAGMA-,schema.cache_size,-%3B%0APRAGMA%20schema My sqlite has the default value of -2000 and from my understanding, even after I ran a query, again and again, my in-memory cache is empty. Trying to debug this issue I ran some PRAGMA queries to my Database: • Getting cache hits by running (jdbc/execute-one! ds ["PRAGMA page_cache_hits"]) • Getting cache misses by running (jdbc/execute-one! ds ["PRAGMA page_cache_misses"]) • Getting the number of pages written in the cache by running (jdbc/execute-one! ds ["PRAGMA page_cache_write"]) But still, after running the same query multiple times I get the value 0 for the above 3 commands. 2. How using an https://www.sqlite.org/inmemorydb.html#:~:text=An%20SQLite%20database%20is%20normally,filename%20%22%3Amemory%3A%22. is compared/related to the above? Thanks

The page cache caches parts of the file on disk in-memory, right? So if your entire DB is in memory then basically there is no purpose for a page cache. So I'm guessing it's not using it and that's why the page cache hits/misses are returning zero.

@U42REFCKA The PRAGMA commands for page_cache commands return zero for the SQLite DB that is loaded from a file. The reason I think it’s not loaded to memory is that making the same query over and over takes around 5 milliseconds.

That's weird. It works for me.

(with-open [conn (java.sql.DriverManager/getConnection "jdbc:sqlite:/home/cam/x.sqlite")
            stmt (.createStatement conn)]
  (.execute stmt "CREATE TABLE t (id INTEGER);")
  (.execute stmt "INSERT INTO t (id) VALUES (1), (2), (3);")
  {:results (when (.execute stmt "SELECT * FROM t;")
              (with-open [rset (.getResultSet stmt)]
                (loop [acc []]
                  (if-not (.next rset)
                    acc
                    (recur (conj acc (.getInt rset 1)))))))
   :cache-hits (do
                 (.execute stmt "PRAGMA page_size;")
                 (.getUpdateCount stmt))
   :cache-misses (do
                   (.execute stmt "PRAGMA page_cache_misses;")
                   (.getUpdateCount stmt))})

{:results [1 2 3], :cache-hits -1, :cache-misses 3}

If I change that database to be :memory then it gives me zero for cache hits and misses.

Thanks @U42REFCKA Your example differs from my use-case in the following: • I load an SQLite file with tables in it • I use the next.jdbc lib • I performed the execute commands on the conn (connection) or ds (datastore) and in your example you preform them on a statement. I wonder what is causing my issue. anyway, I will look into your above example and check for the root cause.

I just wrote it using plain JDBC interop. It doesn't seem like your issue has anything to do with next.jdbc in particular, so it shouldn't matter which library you use or don't use. Either way, it's often easier to debug issues if you can take variables like different libraries out of the equation. If you can't make it work with low-level JDBC interop, then next.jdbc is not going to be able to make it work for you either, since that's what next.jdbc is using under the hood. On the other hand if it works with low-level JDBC code but not with next.jdbc, then it's something related to the library or how you're using it. But I'm guessing your issue has nothing to do with next.jdbc or any other Clojure libraries in particular.

I think so too. Will update after further research, thanks

I checked the same code your wrote with more metrics (with-open [conn (java.sql.DriverManager/getConnection “jdbc:sqlite:./m.sqlite”) stmt (.createStatement conn)] #_(.execute stmt “CREATE TABLE t (id INTEGER);“) #_(.execute stmt “INSERT INTO t (id) VALUES (1), (2), (3);“) {:results (when (.execute stmt “SELECT * FROM t;“) (with-open [rset (.getResultSet stmt)] (loop [acc []] (if-not (.next rset) acc (recur (conj acc (.getInt rset 1))))))) :page-size (do (.execute stmt “PRAGMA page_size;“) (.getUpdateCount stmt)) :cache-misses (do (.execute stmt “PRAGMA page_cache_misses;“) (.getUpdateCount stmt)) :cache-hits (do (.execute stmt “PRAGMA page_cache_hits;“) (.getUpdateCount stmt)) :num-pages-in-cache (do (.execute stmt “PRAGMA page_cache_write;“) (.getUpdateCount stmt))}) {:results [1 2 3], :page-size -1, :cache-misses 3, :cache-hits 3, :num-pages-in-cache 3} After the SQLite DB was created with the table I ran it again without the rows

(.execute stmt "CREATE TABLE t (id INTEGER);")
  (.execute stmt "INSERT INTO t (id) VALUES (1), (2), (3);")