NotesRandom morning thoughts: how many people actually visit this blog? Where do they come from? What if I could just spend a little time to build my own analytics?Unfurl
NotesThe tl;dr on this, to prevent SQLITE_BUSY errors even when setting a timeout, donât ever upgrade transactions to read-write. If you know you are going to write in a transaction, use âBEGIN IMMEDIATEâ, or start off with the write. But do read on for why this is so, and how other databases struggle with this problem as well.FeedUnfurl
NotesEspecially for side projects with limited traffic requirements and scaling concerns, the promise of SQLite is that it can remove a lot of ongoing hassles. Without a separate database, cache, or queue, there is less network traffic (because the SQLite file is local), and less servers to manage, maintain, and backup.Unfurl
NotesA blazingly fast, open-source application server with type-safe APIs, built-in JS/ES6/TS Runtime, Auth, and Admin UI built on Rust, SQLite & V8. Unfurl
NotesDB Browser for SQLite (DB4S) is a high quality, visual, open source tool designed for people who want to create, search, and edit SQLite or SQLCipher database files. DB4S gives a familiar spreadsheet-like interface on the database in addition to providing a full SQL query facility. It works with Windows, macOS, and most versions of Linux and Unix. Documentation for the program is on the wiki.FeedUnfurl
NotesAfter learning about indexes, I understood their basic structure, but I wanted to dig deeper â to explore the data structure, understand the algorithm, and learn how the index data is stored on disk.
The theory and actual implementation can differ, so I decided to explore this topic further.Unfurl
NotesSometimes Litestream can be overkill for projects with a small database that do not have high durability requirements. In these cases, it may be more appropriate to simply back up your database daily or hourly. This approach can also be used in conjunction with Litestream as a fallback. You can never have too many backups!Unfurl
NotesFor Clace, the decision was made to use SQLite for app files storage instead of using the file system. The reasoning was mainly to be able to do atomic version changes. When updating an app, since there could be lots of files being updated, using a database would allow all changes to be done atomically in a transaction. This would prevent broken web pages from being served during a version change.Unfurl
Notessqlite-lembed is a SQLite extension for generating text embeddings, meant to work alongside sqlite-vec. With a single embeddings model file provided in the .gguf format, you can generate embeddings using regular SQL functions, and store them directly inside your SQLite database. No extra server, process, or configuration needed!Unfurl
NotesThe most common use-case for vectors in databases is for K-nearest-neighbors (KNN) queries. You'll have a table of vectors, and you'll want to find the K closestUnfurl
NotesYou can use SQLite's builtin full-text search (FTS5) extension and semantic search with sqlite-vec to create "hybrid search" in your applications. You can combine results using different methods like keyword-first, re-ranking by "semantics", and reciprocal rank fusion. Best of all, since it's all in SQLite, experiments and prototypes are cheap and easy, no 3rd party services required!Unfurl
NotesThis makes it possible to insert JSON straight into SQLite and then have it extract data and index them, i.e. you can treat SQLite as a document database. This has been possible with PostgreSQL and obviously is what something like Elastic provides but having it available in an embedded database is very nice for lightweight stuff.FeedUnfurl
Notes"so I implemented a virtual file system that fetches chunks of the database with HTTP Range requests when SQLite tries to read from the filesystem"FeedUnfurl
How I Track My Blogâs Analytics with Val Town
What to do about SQLITE_BUSY errors despite setting a timeout - Bert Hubert's writings
The definitive guide to using Django with SQLite in production | alldjango.com
Welcome to TrailBase | TrailBase
DB Browser for SQLite
SQLite Index Visualization: Structure
Database Remote-Copy Tool For SQLite
Cron-based backup - Litestream
Using SQLite as Storage for Web Server Static Content â Internal Tools Deployment Platform | Clace
JSON Functions And Operators
Introducing sqlite-lembed: A SQLite extension for generating text embeddings locally | Alex Garcia's Blog
KNN queries | sqlite-vec
Hybrid full-text search and vector search with SQLite | Alex Garcia's Blog
SQLite Pragma Cheatsheet for Performance and Consistency | ClĂ©ment Joly â Open-Source, Rust & SQLite
SQLite as a document database