SQLite3 VS pglite

You can read more discussion here: https://github.com/sparklemotion/sqlite3-ruby/pull/528 and here: https://github.com/digital-fabric/extralite/pull/46 to see how it was validated that simply releases the GVL for every `step` in the SQLite VM majorly hurts single-threaded performance. Finding a middle ground for both single threaded and multi-threaded performance is tricky. In Rails, we know it is multi-threaded because of the connection pool. But the lower level gem is used in many other libraries and tools where it is used in a single threaded environment

Extralite is a gem for working with SQLite databases. It is blazing fast (up to 11x the performance of the sqlite3 gem), and provides a rich API for accessing database data in a variety of formats.

DB: sparklemotion/sqlite3-ruby: Ruby bindings for the SQLite3 embedded database

# This configure_connection is run when each new connection is created. # see https://github.com/rails/rails/blob/main/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb#L1112 def configure_connection super puts "Configuring DB connection with app-specific PRAGMA statements" conn = self.raw_connection # see https://github.com/sparklemotion/sqlite3-ruby/blob/master/lib/sqlite3/pragmas.rb conn.synchronous = 1 # normal # this is a permanent pragma but in case DB is brand new, no harm is invoking it conn.journal_mode = 'wal' # this is unnecssary b/c rails' sqlite3adapter turns it on by default conn.foreign_keys = true # this is not yet supported with convenience method #conn.analysis_limit = 400 # PRAGMA temp_store = 'memory' # hold temporary indices and tables in memory # conn.temp_store = 2 end

That looks like you're missing the ruby module, not the OS package. If you're using bundler to manage your dependencies, add gem 'sqlite3', '~> 1.3', '>= 1.3.11' to your Gemfile. For reference, I got that line from this page: https://rubygems.org/gems/sqlite3/versions/1.3.11

PGLite (Postgres in WASM)

I knew I remember your name working with something of sqlite and golang

https://github.com/ncruces/go-sqlite3

Man I really enjoy golang and cross portability and wazero + sqlite could still be cross portable which is super fascinating

What are your thoughts on https://github.com/electric-sql/pglite (postgres in wasm)?

Also what were the goal behind a pure golang solution via wazero + wasm sqlite as you had made?

Was it cross platform support, if so, what are your thoughts on zig, I have seen a project use zig + golang to create cross platform C language support but I think that adding zig into the picture complicates the build process so there are tradeoffs and I am interested to hear your opinions about it!

It's pretty terrible how poorly developers test their database queries. This looks like a great step in the right direction. I think how the ORM story in RegreSQL develops is crucial. The SQLAlchemy integration looks interesting, but at the same time super specific. There are a million ways to generate SQL statements and ORMs are just one of them. A question that comes to mind is how will you handle interactive transactions? I'd say most complexity in queries comes from the back-and-forth between database and server. Is that out of scope?

Would also be fun if you could support PGLite [0], that's what I've been using to write "unit" tests connected to a "real" database.

[0] https://pglite.dev/

You got my hopes up, but it's WASM for now, not something I could add into a golang [1] or python app and have running like sqlite. OK, still hoping...!

[1]https://github.com/electric-sql/pglite/issues/89

On your last point: https://pglite.dev

On the main point around materialisation cost, I know more work is planned on compaction specifically but generally LiveStore is a framework that’s been highly crafted to address and optimise for all aspects of performance.

Imagine running a full PostgreSQL database directly in your browser without any server setup—pure, instant, and local. Meet PSQLab, an innovative UI built on pglite, that lets you experiment with PostgreSQL natively on the frontend. Whether you’re a college student learning SQL, a developer prototyping new ideas, or a curious mind exploring in-browser databases, PSQLab is designed just for you. 🧑‍💻📘

If you want, you can already help us by installing the Language Server and reporting any issues we might have overlooked, or you could suggest features that we should implement later on (we're currently planning PL/pgSQL support, a Wasm build for pglite, and parsing SQL function bodies).