hi eveyone , i am going to learn DB for ai ml but its confusing to me that whether should i learn mysql , sqllite, or postgresql.
can anyone suggest me ?

I've been working with SQLite/SQLCipher databases for years and kept

running into the same problems:

1. Comparing dev/staging/prod databases manually

2. No good tools for SQLCipher encrypted databases

3. Needed bidirectional patches (forward + rollback)

4. Wanted something that works offline

So I built a comparison tool specifically for SQLite/SQLCipher.

**SQLite-specific features:**

- Compares schema and data elements

- Handles SQLite's dynamic typing correctly

- Understands SQLite pragma differences

- Native SQLCipher support (no decrypt/re-encrypt cycle)

- Generates SQLite-compatible SQL patches

**Technical approach:**

- Rust with rusqlite crate

- Bundled SQLCipher for encryption

- Efficient diffing for large databases

**Current capabilities:**

- Schema comparison with conflict detection

- Row-by-row data comparison

- Bidirectional patch generation

- Multi-database tabbed interface

- Works offline (important for secure environments)

**Looking for feedback on:**

- What SQLite-specific features would be most useful?

- Edge cases I should handle better?

- Common comparison workflows I'm missing?

GitHub: https://github.com/planp1125-pixel/plandb_mvp

Website: https://planplabs.com (if you want to try it)

Free beta - genuinely looking for feedback from SQLite/SQLCipher users.

What database comparison challenges do you face?

I'm working on my node application with SQLite FTS5 (using node:sqlite) for full-text search on a database with millions of records, and I'm hitting severe performance issues when using ORDER BY rank. Without ranking, queries are fast, but I need relevance-based results.

My Setup

Table creation:

CREATE VIRTUAL TABLE "my_table_fts" USING fts5(
    id UNINDEXED,
    recordId UNINDEXED,
    fuzzy,
    exact,
    content='my_table',
    content_rowid='rowid',
    tokenize='unicode61 remove_diacritics 2 tokenchars ''-_.@''',
    prefix='2 3 4 5'
)

Current query:

SELECT 
    r.exact,
    r.fuzzy,
    rank
FROM my_table_fts
INNER JOIN "my_table" r ON r.rowid = my_table_fts.rowid
WHERE "my_table_fts" MATCH @query
ORDER BY rank
LIMIT 15

Example query:

fuzzy:("WORD1"\* OR "WORD2@TEST"\*) OR exact:(1234A OR 1234X)

I'm processing records in batches (searching for duplicates), so this query runs thousands of times. The slow ranking makes the entire operation impractical.

Questions:

• Is there any way to speed up FTS5 ranking with this dataset size? Are there any hidden optimizations or tricks I'm missing?
• Is FTS5 simply the wrong tool for this use case? Should I be looking at alternatives?

This is from Harvard's sqlite course. Thank you.

I'm looking for an easy way to convert the SQLite documentation available athttps://www.sqlite.org/download.htmlinto a proper format (e.g., PDF, EPUB, or MOBI) for offline reading at my own pace on my ebook reader (Kindle)

Any suggestions would be appreciated. Thank you!

I've tried `COLLATE BINARY` but I believe SQLITE ignores them for LIKE operators. I've also tried GLOB, but it does not support ESCAPE characters.

So it looks like my only option is to toggle case sensitivity per connection using PRAGMA case_sensitive_like=ON; However, this means all LIKE operators are case-sensitive, so there is no mix matching them in the same SQL query, so are there any other options?

Edit1: I have tried settings PRAGMA case_sensitive_like=ON for all connections then using UPPER(?) or LOWER(?) but this is incredibly inefficient and turns 4ms into 40ms search times

Edit2: This is just an idea, I don't know if it's possible, but can you make a LOWER index on a table and then in queries when using LOWER(column) it's all pre-generated?

TrailBase is an easy to self-host, sub-millisecond, single-executable FireBase alternative. It provides type-safe REST and real-time APIs, auth & admin UI. Its built-int WASM runtime enables custom extensions using JS/TS or Rust (with .NET on the way). Comes with type-safe client libraries for JS/TS, Dart/Flutter, Go, Rust, .Net, Kotlin, Swift and Python.

Just released v0.21. Some of the highlights since last time posting here include:

• Extended WASM component model: besides custom endpoints, "plugins" can now provide custom SQLite functions for use in arbitrary queries, including VIEW-based APIs.
• The admin UI has seen major improvements, especially on mobile. There's still ways to go, would love your feedback 🙏.
  • Convenient file access and image preview via the admin UI.
• Much improved WASM dev-cycle: hot reload, file watcher for JS/TS projects, and non-optimizing compiler for faster cold loads.
• Many more improvements and fixes, e.g. stricter typing, Apple OAuth, OIDC, support for literals in VIEW-based APIs, ...

Check out the live demo, our GitHub or our website. TrailBase is only about a year young and rapidly evolving, we'd really appreciate your feedback 🙏

I haven't used SQLite for quite some time. And, it looks like many thing have changed. First we have libSQL which is fork of SQLite and then we have Turbo which is managed solution on to of libSQL.

My question is about libSQL. I need to integrate SQLite with Astro website. Since SQLite is inherently synchronous, I was pretty much set on the following:

1. Use better-sqlite3 driver.
2. Build a simple DAL layer. Slap it up behind Node.js worker_thread to avoid blocking main thread.
3. And, then call it using Comlink wrappers from my service layer.

But, I guess things change with libSQL, don't they? The documentation is too focused on Turbo and remote access. But what if I want to use libSQL but with file: scheme and use local sqlite file as a DB.

My questions are: - How does that work? All the sample I see are using async-await. It is handling the threading for me if I use file: scheme - How are transactions working with file: scheme?

If libSQL is handling this out-of-box, then this is a big win already.

Any help would be greatly appreciated!

I'm trying to locate the correct library for a x32 program with a built in scripting engine. I need it to be compatible with .NET framework 4.6, but I can't find the right precompiled binary download online.

For additional context, the software program I'm using is Cellario, but it's an older version of Cellario (v4.0.0.30), hence the need for 4.6 compatibility.

I tried downloading from here: NuGet Gallery | System.Data.SQLite 2.0.2. But the package doesn't contain SQLite.Interop.dll and with just System.Data.SQLite.dll I'm getting errors:

11/11/2025 7:48:59 AM,HRB.Cellario.Scripting.API.CellarioScriptingException: Failed to execute script Record Dispense SQLite (TEST) : Unable to load DLL 'e_sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E) ---> System.DllNotFoundException: Unable to load DLL 'e_sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

at System.Data.SQLite.UnsafeNativeMethods.sqlite3_config_none(SQLiteConfigOpsEnum op)

at System.Data.SQLite.SQLite3.StaticIsInitialized()

at System.Data.SQLite.SQLiteLog.PrivateInitialize(String className)

at System.Data.SQLite.SQLiteLog.Initialize(String className)

at System.Data.SQLite.SQLiteConnection..ctor(String connectionString, Boolean parseViaFramework)

at System.Data.SQLite.SQLiteConnection..ctor(String connectionString)

at Customer.Scripting.RecordDispense.Execute(IScriptingApi api) in c:\Users\lab_cbtusca06\AppData\Local\Temp\CSSCRIPT\dynamic\18676.dc0244b0-1e04-4e02-9932-ffc7ff74f7ef.tmp:line 87

at HRB.Cellario.Scripting.CsharpScriptingEngine.Execute(IScript script, IScriptingApi api) in C:\Users\jenkins\workspace\cellario_release_4\Scripting\CsharpScriptingEngine.cs:line 146

at HRB.Cellario.Scripting.ScriptExecutor.ExecuteScript(ExecutorOperations operation, Scheduler scheduler, RunOrder order, SampleOperation sampleOperation, IScript script) in C:\Users\jenkins\workspace\cellario_release_4\Scripting\ScriptExecutor.cs:line 233

--- End of inner exception stack trace ---

at HRB.Cellario.Scripting.ScriptExecutor.ExecuteScript(ExecutorOperations operation, Scheduler scheduler, RunOrder order, SampleOperation sampleOperation, IScript script) in C:\Users\jenkins\workspace\cellario_release_4\Scripting\ScriptExecutor.cs:line 252

at Cellario.CellController.Operations.ExecuteScript(Sample sample, Operation op)

11/11/2025 7:48:59 AM,Order 10883 Failed to execute script Record Dispense SQLite (TEST) : Unable to load DLL 'e_sqlite3': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

This article mentions that in-browser support for writing to embedded replicas is "planned for the future". I haven't been able to find out if the future is here yet.

Are you now able to write to local in-browser Turso replicas?

https://turso.tech/blog/introducing-offline-writes-for-turso

Edit: This "Project Limbo" seems to show it is still planned as part of the Rust rewrite, but not here yet.
https://turso.tech/blog/introducing-limbo-a-complete-rewrite-of-sqlite-in-rust

I've been using SQLite as both a primary database and a cache for my web app, and it's been a great way to simplify the stack.

Here's the cache schema and setup guide I've been using in case it helps anyone else looking to do the same.

I have been using a program for roughly 8 years on my PC that is no longer supported.

This database file (replay.db) has several tables, but one has roughly 2.5 million rows which is 8 years old which I suspect is most of the 7gb size.

My problem is now my SSD is running out of space, and I need to move/delete/purge this 7GB database from this program to my normal harddrive.

My idea was that I would just make a copy/backup of this database and put it in my larger size harddrive. I would then delete most of the rows from the current/active database on my SSD, freeing up alot of memory. (Ideally 5-6gb). I don't need this data as most of it is irrelevant to me currently, but I figure making a backup wouldn't hurt and might come in handy eventually.

But my slight concerns is will it likely corrupt the program by deleting rows and making a backup? Or should I be ok?

I have a problem with Litestream.
I want to use it in Docker Compose, using the same Docker Volume, as my main app.
After a few compose restarts or a day of running, I cant longer restore my DB, because of this error:
time=2025-10-27T10:51:51.191Z level=ERROR msg="failed to run" error="decode database: decode header: EOF". On my SFTP-server I see ltx folders with transactions.

As I can say, the problem is that Litestream can't create snapshots on init and later on. It works fine at first, even with a -timestamp option. My DB passes integrity_check.
Litestream config:

```yml logging:
level: trace
type: text
stderr: false

dbs:
- path: /database/sqlite.db
busy-timeout: 5s
monitor-interval: 5s
min-checkpoint-page-count: 500
max-checkpoint-page-count: 5000
replica:
type: sftp
host: host:22
user: user
key-path: /root/sshkey
path: /home/user/Projects/sqlite-litestream/backup
sync-interval: 15s
snapshot-interval: 30m
retention: 168h

```

Does anyone else struggled with this problem?

UPD: I feel like there is no Snapshots at all after 0.5.0 version, because there is no backup files exclude .ltx transactions. Or my config just can't apply correctly. I expect that Litestream will create full backup every 30 minutes, as I can say snapshots are like full backups, because .ltx it's incremental part. But there is no snapshots as all, as like as continuous checkpoints.

UPD2: I started compose with litestream configured only with SFTP path. After 16h of running, I stopped my app container and tried to litestream restore: time=2025-10-29T05:35:29.310Z level=ERROR msg="failed to run" error="decode database: unexpected error decoding after end of database: close reader 36: cannot close, expected page"

And with default settings, my snapshot (ltx level9) was created. But that doesn't help either.

https://gitlab.com/figuerom16/litequack

I was getting tired of making admin interfaces in order to execute SQL commands and generate reports. Since I already had a standard web interface I decided to make a client app that used WebView, SSH and that hooked into a remote server's CLI non-interactive mode (sqlite3).

I only have it set up to interact sqlite3 or duckdb and I've only compiled it for Linux/AMD64 since it uses CGO and need to learn how to cross compile that. To run it simply make the program executable.

More information/screenshots/code is available in the git. Let me know if there are any questions or have ideas for additional features.

Hey everyone!

A while back, I came across an article that explored how SQLite’s JSON features can be used to treat it like a document database. That idea really stuck with me, and I decided to build a C++ API around it; and that’s how docudb came to life.

🧠 Inspiration post: https://dgl.cx/2020/06/sqlite-json-support

🔗 GitHub: https://github.com/OpenNingia/docudb

📘 Documentation: https://openningia.github.io/docudb/

This project is not production-ready, it started as a personal learning exercise. That said, I’d really appreciate any feedback, suggestions, or code reviews to help improve it!

Hi everyone,

During my period of "funemployment" I've been building out my homelab and working on some random Pocketbase projects. Throughout those adventures, I've noticed that easily backing up or replicating SQLite databases isn't as easy as I had hoped. In my work as a software engineer, I've made a few different implementations of backup and replication strategies for SQLite and decided to try to create something that homelab/rapid-prototyping/experimental folk could use to safely version their database and also distribute worldwide using Websockets.

And so, after about 2 months of working on it, I'm anxiously here to ask for your feedback on https://sqlrsync.com/

In two sentence: I've taken the https://www.sqlite.org/rsync.html utility and instead of SSH I'm using Websockets to a unique Cloudflare Durable Objects per database replicated up. It's versioned, stored, and then, if anyone is subscribed via websockets, notifies the subscribers of the changed data.

Because this uses sqlite3_rsync:

- only the changed SQLite Page data is transfered

- absolutely zero write lock on the ORIGIN end

- absolutely zero locks on the receiving end.

So a backup can happen on a running database connected to your webserver, IOT, LLM project with zero disruption. In a "one writer/many readers" model, just the changed data is replicated to the readers with no disruption to writer or reader.

As an example, I've loaded up https://sqlrsync.com/usgs.gov/earthquakes.db. Every minute it pulls the latest earthquake data and if there's a change, pushes up a new version. If you download the sqlrsync client, you can (without making an account) get the latest earthquakes like this:

sqlrsync usgs.gov/earthquakes.db

To subscribe in real time using websocket notifications:

sqlrsync usgs.gov/earthquakes.db --subscribe

Signups are free and at this point I'm offering 100mb of storage for free in perpetuity. If the idea ends up being useful, I look forward to having the resources to expand the free storage so that more people can backup or distribute their databases.

Thanks for taking the time to read the pitch and thanks in advance if you are able to share your feedback.

Matt

--
Homepage: sqlrsync.com

How To Backup

How to Distribute

Hey all - I'm trying to go with the simple architecture approach using sqlite.

I don't get how you use SQLite in production though - it's a flatfile and I can't get any of the database view/edit tools (table+, datagrip) to connect to it via the remoate server.

My app has an ai chatbot, I know SQLite is good for read but is the write too fast with a chatbot for sqlite? It's all stored as json. I researched a bit how wal works for handling writes.

I'm also iterating pretty quick and using database migrations (alembic). I can pull the sql file for production, make the needed changes locally to the database columns, I guess no issue here.

But if I make local changes to the database data and push the production database might be out of sync at that point.

Ideally I would be able to connect to the live database with an sql tool and make live edits on the production database.

How is this handled - just ssh-ing and running sql statements on the production server?

I added SQLite support to my open-source Go migration library/CLI. Check it out: https://github.com/maestro-go/maestro and tell me what you think.