node-sqlite | SQLite bindings for Node.js | Runtime Evironment library

Joshua Wise's (better-sqlite3's creator) answer over on GitHub:

Database connections are automatically closed when they are garbage collected, which is non-deterministic. If you want to know that the connection is closed (rather than guessing), you should call .close().

You can just open one database connection for the entire thread (the entire process if you're not using worker threads), and share that connection between every request. Node.js is single-threaded, so you don't have to worry about simultaneous access, even if multiple requests are being handled concurrently. The one caveat is that you should never have a SQLite transaction open across multiple ticks of the event loop (i.e., don't use await between BEGIN and COMMIT), because then other requests could accidentally inject SQL into your transactions. Also, SQLite transactions are serialized (you can't have more than one at a time), so you should open and close them as quickly as possible; keeping them open across ticks of the event loop is bad for performance.

https://pyproj4.github.io/pyproj/stable/installation.html

I would recommend either:

1. Upgrade to pip>=19 so PROJ 7.2 is in the wheel.
2. ENV PROJ_DIR=/path/to/proj/install and when you install PROJ: ./configure --prefix $PROJ_DIR

So, I created a sorting function to sort it right after running the query and getting the results. I reported this issue to the github repository. This is the sorting function.

Since you want to use async/await, and the node-sqlite3 (sqlite3) library does not support the Promise API, you need to use the node-sqlite (sqlite) library, which is a wrapper over sqlite3 and adds support for the Promise API. Then, your code will look something like this:

The issue I see in your code is, the console.log is outside of the callback function.

That commit was in that repository at one time.

It was subsequently deleted from the repository, so new clones of that repository do not get that particular commit. (Deletion is a bit tricky, but for example, this can happen because a pull request gets modified—the commit was there because of the pull request, then the pull request itself gets updated with new-and-improved commits. The replacement commit is the one ultimately used, and the original expires on its own after a few more days.)

It still is present on GitHub because GitHub does not always clean out everything immediately. In this case, however, they have not cleaned things out for quite a long time, and it's not clear why it's still accessible via the GitHub web interface at all. This commit is not reachable from a pull request either (there are 218 pull requests hanging on to commits but this is not among them). I wonder if links from "issues" pages might also keep otherwise-unreachable commits alive, on GitHub.

Following this commit backwards, through its parent links on GitHub, reveals a whole sequence of commits that seem to have been replaced with new-and-improved versions at some point.

I've been testing PouchDB, RxDB (which relies on PouchDB with RxJS streams for queries), Realm-JS (native database like sqlite3), FireStore. NeDB doesn't support remote sync.

I won't go into performance metrics details on each database, but PouchDB has been very slow and heavy on memory when querying more than 20.000 items (tried with indexeddb/websql adapter).

RxDB was generally much faster with that many items, especially when subscribing to query changes (tried with indexeddb/websql adapter too). Also schema and migrations are very handy.

FireStore is a good choice and comes with very easy setup of server and client components, but you'll need to be comfortable to run on a google platform. There is some flexibility with firebase functions if you want some control over server logic and there is customizable ACLs for your collections. Speed has been good and on par with RxDB. Comes with a very good auth module if you want it.

If you want to be able to scale much much more on the client side, which you probably don't, or if you want to make complex queries, I'd really recommend using something like realm. You'll need to compile for each platform like you've experienced with sqlite3, but there is sync, offline persistence, rich queries and great performance. There is just no way that javascript based solutions like PouchDB, RxDB or FireStore can compete, even with sqlite backends, since much of the computation will still happen in your precious JS thread. realm is doing much of its heavy lifting on the native library. I've been able to do LIKE "abc" queries on 100.000 items, returning hundreds of results, within less than hundred milliseconds and without noticeably freezing my UI or pumping up memory usage heavily. Supports client migrations too, which is nice.

In the end, there are multiple answers: 1. Want to host everything yourself and don't need massive scale client side (you can sync against subsets of your server data with filters), RxDB ist very good and comes with nice set of features. 2. Want very easy setup, nice modules like auth, server functions etc, and don't need massive scale on the client (can also sync against server data subsets with filters), FireStore is great. 3. Need lots of lots of data on the client, realm is my preference. I personally really dislike the realm sync platform for its pricing model (though technically it's cool), but the database itself is free and maybe you could try and implement a custom sync.

Take my results with a grain of salt, I've had some very specific challenges like large, non-relational collections and fulltext-search, your use-case will probably differ a lot.

I believe you found the answer after opening this issue. Wanted to go ahead and answer this question for others struggling.

Issue Opened: https://github.com/mapbox/node-sqlite3/issues/1163

Actual binding: https://mapbox-node-binary.s3.amazonaws.com/sqlite3/v4.0.0/electron-v4.0-win32-x64.tar.gz

You're using exec wrong. The promisified version of Database#exec only takes a single argument, the SQL to execute.