Welcome to better-sqlite3 Discussions!

[JoshuaWise]

👋 Welcome!

We’re using Discussions as a place to connect with other members of our community. We hope that you:

• Ask questions you’re wondering about.
• Share ideas.
• Engage with other community members.
• Welcome others and are open-minded. Remember that this is a community we
  build together.

To get started, comment below with an introduction of yourself and tell us about what you do with this community.

[kentcdodds]

👋 I'm Kent and I'm planning on teaching people how to use better-sqlite3 to run fast queries with SQLite in situations where the ORM (Prisma 😍 in my case) isn't sufficient. I think the coolest thing I'm doing with better-sqlite3 is my app's /search page which takes a ton of filters into account (including lat/long geographical distance calculations) and executes it all into a single query. It's pretty gnarly (I'm also not much of a SQL expert), but it works and it feels pretty darn efficient. It's also open source so feel free to peruse: https://github.com/epicweb-dev/rocket-rental/blob/b0ada1402e6f380bef7fdf3c78bc802d2a2a1b64/app/routes/search.tsx#L128-L330

Happy to be here!

[mceachen]

👋 If your SQL generation code gets too hairy, know that Knex can be used in conjunction with this library.

If Prisma is activerecord-ish, Knex is more code-that-looks-and-makes-sql.

Knex (like Prisma) helps avoid SQLi but lets you decompose your query programmatically (rather than mucking with strings). I use Knex (and better-sqlite3) with PhotoStructure (which has quite hairy queries, including recursive CTEs for hierarchical tags, and a search-query-to-SQL bridge).

[kentcdodds]

Sweet! Thanks for sharing. I'll probably look into it next time I get into that code. There are certain bits I'm not super jazzed about for sure.

[tantaman]

What has prisma been lacking that prevented you from building your search page in it?

[kentcdodds]

Take a look at that code and you'll see there's no chance Prisma could do that stuff. And since I need to write raw SQL anyway, I may was well use something much faster.

[tantaman]

Heyo. I'm Matt.

Been working on CRDT support in SQLite @ vlcn.io & cr-sqlite.

The idea is to "be like git, for your database"

Distribute copies of your DB, allow people to iterate on them independently, merge them all back together again.

better-sqlite has been great given it understand that the interaction with SQLite is going to be synchronous from a JS environment and async operations don't help here.

[vmano]

I am able to build and run this automatically on Mac, what libs are required to build this with Dockerfile for base image nodejs rhel8?
I tried Python, make, and GCC, but still failed the below error.

we cannot be allowed to install from https://nodejs.org/download/ directly.. can anyone suggest the fix or how to use already prebuilt linux better-sqlite3.node file directly in Dockerfile?

#26 sha256:1391699673ac618e11cd909c349d1e3e59fe6fa3cec955df6d2b80cda7506869
#26 32.85 npm ERR! code 1
#26 32.86 npm ERR! path /app/node_modules/better-sqlite3
#26 32.86 npm ERR! command failed
#26 32.86 npm ERR! command sh -c -- prebuild-install || node-gyp rebuild --release
#26 32.86 npm ERR! gyp info it worked if it ends with ok
#26 32.86 npm ERR! gyp info using [email protected]
#26 32.86 npm ERR! gyp info using [email protected] | linux | x64
#26 32.86 npm ERR! gyp info find Python using Python version 3.8.13 found at "/usr/bin/python3"
#26 32.86 npm ERR! gyp http GET https://nodejs.org/download/release/v16.19.1/node-v16.19.1-headers.tar.gz
#26 32.86 npm ERR! gyp WARN install got an error, rolling back install
#26 32.87 npm ERR! gyp ERR! configure error
#26 32.87 npm ERR! gyp ERR! stack FetchError: request to https://nodejs.org/download/release/v16.19.1/node-v16.19.1-headers.tar.gz failed, reason: self signed certificate in certificate chain
#26 32.87 npm ERR! gyp ERR! stack at ClientRequest. (/opt/middleware/nodejs/16.19.1/node-v16.19.1-linux-x64/lib/node_modules/npm/node_modules/minipass-fetch/lib/index.js:130:14)
#26 32.87 npm ERR! gyp ERR! stack at ClientRequest.emit (node:events:513:28)
#26 32.87 npm ERR! gyp ERR! stack at TLSSocket.socketErrorListener (node:_http_client:494:9)
#26 32.87 npm ERR! gyp ERR! stack at TLSSocket.emit (node:events:513:28)
#26 32.87 npm ERR! gyp ERR! stack at emitErrorNT (node:internal/streams/destroy:157:8)
#26 32.87 npm ERR! gyp ERR! stack at emitErrorCloseNT (node:internal/streams/destroy:122:3)
#26 32.87 npm ERR! gyp ERR! stack at processTicksAndRejections (node:internal/process/task_queues:83:21)
#26 32.87 npm ERR! gyp ERR! System Linux 5.15.49-linuxkit
#26 32.87 npm ERR! gyp ERR! command "/opt/middleware/nodejs/16.19.1/node-v16.19.1-linux-x64/bin/node" "/opt/nodejs/16.19.1/node-v16.19.1-linux-x64/lib/node_modules/npm/node_modules/node-gyp/bin/node-gyp.js" "rebuild" "--release"
#26 32.87 npm ERR! gyp ERR! cwd /app/node_modules/better-sqlite3