It doesn't have a JSON type, no TIMESTAMP, no INTERVAL and not even BOOLEAN.
And yet it's one of the most used and praised databases.
I want to like it. But how do I make it work?
EDIT: I know realized what I was doing wrong. I was using STRICT tables because I thought data validation sounds good. But that means I cannot use DATETME and BOOLEAN. Once I give up on STRICT and use these types the Go driver can actually map them to time.Time and bool for me. Much better. Thank you all 🙏
Pop-Database vs SQLite, 500K entities, 12 AND conditions per query, both single-threaded (CPU-affinity verified, so no thread-count trick). Pop-Database finished in 6.26s. SQLite finished in 88.81s. Pop-Database is 14.2× faster — and both found the exact same 9,559 matches per query. This ratio moves a bit run to run depending on what else the machine is doing at the time. I've seen this same test land anywhere from ~12× to ~14× depending on system load. Still solidly in the same range either way. Trade-off is shown in the same video: SQLite inserts faster (2.64s vs 6.55s for 500K rows). Pop-Database is built for read-heavy workloads, not write-heavy ones - that's the deal!!!
Hi everyone! I’ve been working on jSQLite, an AI-generated TypeScript translation of SQLite’s read-only C internals, and it’s reached a point where I’d like to share it.
It runs directly in the browser without WebAssembly, native code or a query server. You can open an immutable SQLite database over HTTP and execute queries locally.
It supports a broad range of common SQL constructs, including joins, aggregates, subqueries, CTEs, window functions and JSON functions, along with UTF-8 and both UTF-16 database encodings. Recent work includes STAT4-informed index selection for supported skip-scan paths.
This is a preliminary release, and I’d particularly welcome examples of queries that produce unexpected results. Development uses SQLite 3.53.4 as a pinned reference, with differential tests comparing typed results, column metadata, errors and statement reset behavior.
The translation has been generated using Saivage, an autonomous software-development system I’m also building. jSQLite is one of my projects for testing that system against a substantial engineering problem.
Are there any solutions which offer distributed sqlite infrastructure with local first support and syncing as well? additionally is it possible to store the data itself in object storage instead of block storage. ik we can't query directly like we traditionally do with this approach but are there any solutions which have been implemented ?
I have considered solutions like rqlite which is great for distributed databases but not so much with the local first approach.
I've considered solutions like novelu as well but all of them fall short in one way or the other.
pls don't recommend turso. we can't use the opensourced version of it as well as they haven't opensourced the sync layer
I've been working on Toketeo, a desktop database tool I've been building mostly because I wanted something that worked the way I thought it should.
It's currently built with Rust + Tauri + React, and the idea is to make working with databases less painful, especially when you're jumping between different database engines.
The project is still early, so I'm deliberately putting it in front of actual developers before I spend months polishing things nobody asked for.
I'd really like people to try it and tell me:
What's confusing?
What's missing?
What would make you switch from your current database tool?
What feature would make you say "okay, this is actually useful"?
And, preferably, what did I screw up?
I'm particularly interested in feedback from people who work with multiple databases or spend an unhealthy amount of time inside database clients.
Hi all. I develop SQLite client here https://sqliteman.dev. It supports both Windows 10 and macOS and has 100% native UI. Please check it out. Any reviews will be appreciated.
Hey there! Today I'll show you how create your first SQLite database and manage some data into it without SQL at all. All need is package SQLLEX, let's install it and begin.
Type in terminal:
pip install sqllex
Or if you have many python versions install it for which one you need (type version instead of x):
python3.x -m pip install sqllex
Well done! Now we need to decide what database structure do we need. Imagine you are admin some chat-bot and you need to collect data from your chats about users.
For the firs example let it be id(s) and username(s) of user(s). So inside our database (it's just a file) we will create a table called "users" with two fields (columns). Values in the first one column (id) have to INTEGER and UNIQUE (because users can't have same id), in the second column (username) values have contains TEXT-like data, I guess it's clear.
So let's code it!
# import all from sqllex package
from sqllex import *
# Create database file 'database.db'
db = SQLite3x(path='database.db')
# Create table inside database
db.create_table(
'users',
{
'id': [INTEGER, UNIQUE],
'username': TEXT
},
IF_NOT_EXIST=True
# it'll avoid you an error if table already exist
)
Run this code and you'll see that in the same directory where locate your script were created database file:
You can open it (by sqlitebrowser for example) and make sure it works right:
Without this tool, you can check it like this:
print(db.tables_names)
# ['users']
Good, now it's time to add some data into this table.
For the first let's get table 'users' as object by itself. Don't be scared! It's for your convenience.
users = db['users']
And now insert new record (user) in this table:
users.insert(1, "User_1")
Or try insert is like this:
users.insert([2, "User_2"])
Or like this:
users.insert(id=3, username="User_3")
Awesome! We're in:
So now we need to get this back somehow. Here we have 'select' or 'select_all' method for this.
Good job! But what if we need select only one record where id = 1. Well, you have 2 different way for it:
The first one is find method:
print(
users.find(id=1)
)
# [1, 'User_1']
And the second one:
print(
users.select_all(id=1)
)
# [1, 'User_1']
Perfect. But what if you don't need full record, but only specific column. For example you looking for name of user who have id=3. Ezy, just select one specific column by select method:
Excellent! And now imagine one more case, some of your users changed nickname, how can you update nickname to have up-to-date data. You wouldn't believe me but it's have update method!
No matter which way you prefer, result will be the same.
So when all records updated, let's check results. You can also use select or select_all methods as in examples before, but I just want to show you something calling "syntax sugar". Just get column 'username' as item of dict.
I wanted to share a local development tool I built because I was tired of manually copying .sqlite.bak files every time I needed to test destructive scripts, run complex migrations, or debug a state issue in my local environment.
I built the Time-Travel SQLite Debugger – an ultra-lightweight, UI-driven tool that watches your .sqlite file and creates restorable snapshots in real-time. You literally just drag a visual timeline scrubber to travel back in time and instantly restore your database to a previous state.
🚀 Under the Hood & Stress Tests:
I wanted to make sure it wasn't just a toy, but something that handles heavy local stress without freezing:
Zero Dependencies: Just pure PHP and a clean Tailwind UI. Drop it in your local server and it works.
Atomic Swaps: Uses POSIX rename() for atomic file swapping. I stress-tested it with 100 concurrent read/write/restore operations and had exactly 0 lock or corruption errors.
Highly Efficient: It copies a 190MB binary BLOB database in 136ms using < 2MB of PHP peak memory (it utilizes stream-level kernel pipe buffers instead of loading into memory).
Chaos Tested: Handled a rapid insertion loop of 500 writes (0.1s sleep) flawlessly, purging old unpinned snapshots while keeping the limit strict.
Transparency Note: I utilized AI tools as a coding assistant to accelerate the development, personally reviewing, testing, and designing the overall architecture.
I designed it to run in the background (like a daemon) while you code, providing a safety net for your local DB.
It's completely open-source and free. I'd love to hear your thoughts, feedback, or any edge cases you think I should test!
Running SQLite in production, we had this in our backup docs: cp production.sqlite3 backup.sqlite3. Seemed simple and elegant.
When we published our production SQLite writeup, HN commenters flagged it immediately: cp on a WAL-mode database risks corruption because the .db and .wal files are captured at different moments. In WAL mode, committed transactions may still be in the .wal file, not yet checkpointed into .db. You can end up with a .db reflecting one checkpoint state and a .wal from a different one.
The fix: use sqlite3's .backup command or the backup API, which handles checkpointing properly before copying.
Im using .net 9 and sqlite for the database. My problem is that when using a form and trying to add data to the database, nothing happens. I stay on the same Create view and the inputs doesnt add to the dbfile. I have checked for errors in the terminal and network page in chrome, but nothing. Could someone help me? I can send the project.
Hi, this is my first time posting anything here, I have googled this question and scrolled a lot before I decided to look for help. I have used SQLITE in the past but mostly I work with ORACLE databases in my day job, recently I was asked to give a class on SQL and I thought it would be easier if I started with a small DB , instead of the monstrous production DBs we have where I work.I installed sqlite and sqlitestudio, I normally like the command line so I started doing a lot of exploratory querying in directly by calling sqlite3 with the sample DB, and everything worked OK until I decided to include window functions (concretely aggregate functions over partitions), I did write the query in SQLITESTUDIO at that point because I was thinking it would be nicer on the eyes when giving a training, my query worked correctly, but when I saved it and tried in the command line it failed.
I am not sure what is happening, but if anybody knows, I would appreciate some explanation.
This is the Query
SELECT DISTINCT b.name,
b.address,
count( * ) OVER (PARTITION BY b.business_id) AS n,
round(avg(i.score) OVER (PARTITION BY b.business_id), 1) AS avgscore,
min(i.score) OVER (PARTITION BY b.business_id) AS minscore,
max(i.score) OVER (PARTITION BY b.business_id) AS maxscore
FROM businesses b
INNER JOIN
inspections i ON b.business_id = i.business_id
WHERE upper(b.address) LIKE "%MISSION ST%" AND
upper(b.name) LIKE "%PIZZ%" AND
i.score IS NOT NULL;
What free and fast options are there for hosting a SQLite database for a Website? Is Cloudflare D1 and Turso the best options out there?
I know that it's just a file and I could host it anywhere but I don't want to be forced to load my entire db just to grab something.
I have currently been using JSON files as storing my data and would like to change that to using a db if it remains free for the foreseeable future like it does with using JSON files. I originally wanted a db to have it more organized then JSON files to build my datasets, but not I am looking into if it can actually replace my JSON files and lower the bandwith usage while still be fast to use. A fallback solution would be to just use a db to store all the data and generate JSON files for the website.
I have been playing around with some basics mainly using postgres/supabase so I am not that experienced.
One database rule feels overly strict during development but becomes important after shipping:
Never edit a migration that has already reached users.
I'm building a local-first desktop app with Tauri 2, Rust, SQLite, and SQLx. While documenting the persistence boundary, I wrote down a few migration rules worth discussing—especially for desktop apps where the database may contain years of user-created work.
The tempting workflow
Create 0001_initial.sql.
Run the app locally.
Notice a missing column.
Edit 0001_initial.sql.
Delete the local database and try again.
Everything looks fine because the local database is disposable.
A shipped user's database is not. SQLx records applied migration versions and checksums in _sqlx_migrations. If a later binary contains different contents for an already-applied migration, the checksum no longer matches and SQLx refuses to continue.
That's a good failure. The binary should not silently rewrite the history of how the user's schema was created.
ALTER TABLE spaces
ADD COLUMN color TEXT NOT NULL DEFAULT 'blue';
For more complex SQLite changes—such as changing a CHECK constraint or adding a foreign key—you may need a table rebuild in a new migration. That rebuild must preserve relevant rows, indexes, triggers, foreign keys, and dependent objects.
New required values should be backfilled as part of the migration. Destructive changes deserve explicit review.
Two errors should not trigger an automatic reset
A checksum mismatch usually means a developer changed a migration that had already shipped.
An unknown newer migration may mean the database was opened by a newer application and the current binary is older.
In either case, silently deleting the database is the wrong recovery strategy. Preserve the original data and report the incompatibility.
The test that matters is an upgrade test
Creating the latest schema from scratch is necessary, but it does not prove an upgrade is safe.
A useful test starts from a historical schema, inserts representative old rows, runs the new migrations, then asserts the new schema and surviving data.
Knovum has an upgrade test module at src-tauri/src/spaces/db/tests/upgrades.rs. It migrates to a previously shipped version, inserts representative records, applies the current migration history, and checks the final schema and retained data. Release acceptance also requires testing against a library created by the previous release.
The renderer does not send SQL strings. It requests domain operations such as renaming a Space. Rust owns the query and transaction scope. A multi-statement invariant stays inside one SQLx transaction instead of being split across IPC round trips.
Questions for people who have shipped desktop apps
Do you enforce migration immutability in CI, or rely on code review?
Do you keep upgrade fixtures for every released schema, or only test the immediately previous release?
How do you handle downgrade attempts when a user launches an older desktop build against a newer local database?
For SQLite table rebuilds, what has been easiest to forget: indexes, triggers, foreign keys, or something else?
My current approach is forward-only migrations, historical-schema upgrade tests, and refusing to reset user data as an automatic recovery path.
These constraints come from building Knovum, a local-first desktop app. The repository and engineering notes are here: github.com/madebyzan/knovum.
Code snippets are simplified examples. The repository's current migrations and tests are the source of truth.