SQLite is the next big database trend. with more horizontal scaling, close to user read heavy applications, having your database in the same application stack makes a lot of sense. Tools like litestream are going to enable global distribution in an impressive way.
Posts tagged: database
All posts with the tag "database"
36 posts
latest post 2025-11-02
Publishing rhythm
Fly.io’s solution to sqlite managed backups.I definitely want to look into this a bit, but moreso the tech under the hook litestream.
sqlite has 3 different tokenizers, porter, ascii, trigram.
These can be used with sqlite-utils.
sqlite-utils enable-fts --tokenize porter database.db post title message tags
And with the python api.
sqlite-utils is primarily a cli tool for sqlite operations such as enabling full text search, and executing searches, but it also has a nice python api that is exposed and pretty straightforward to use.
from sqlite_utils import Database db = Database("database.db") db["post"].enable_fts(["title", "message", "tags]) db["post"].search("water")
This returns a generator object that you can iterate over the row objects with.
datasette really does everything doesn’t it!
After a nasty time with alembic upgrades, thoughts is about to get a new users table. This may have came from incorrectly setting up alembic for sqlite from the start, but I was able to fix the issue with this GitHub issue.
alembic sqlite ValueError: Constraint must have a name
The change I needed to make to get my migration to run.
![[None]]
Since using alembic I have been just running out a new revision checking its content and deleting it if its empty, today I learned there is an alembic check command to check for operations that need to be created.
duckdb is a new in process database that has been making its rounds in analytics for its high performance in those applications.
Mother duck is a centeralized server that brings manages storage, data sharing and an ide to duckdb.
How to sort results from a sqlalchemy based orm.
.order_by(model.Entry.amount.desc())
I needed this to enable paging on my thoughts api.
A neovim database client that I need to check out.
As the title states sqlite-utils now supports plugins. I dug in just a bit and Simon implemented this completely with entrypoints, no framework or library at all.
sqlalchemy server_defaults end up as defaults in the database when new values are inserted.
When fetching pydantic models from the database with sqlmodel, and you cannot select your item by id, you probably need to use a where clause. This is the sqlmodel way of doing it.
Here is a snippet of how I am using sqlmodel select and where to find a post by link in my thoughts database.
Enable full-text search in sqlite using sqlite-utils.
I want to like jq, but I think Simon is selling me on sqlite, maybe its just me but this looks readable, hackable, editable, memorizable. Everytime I try jq, and its 5 minutes fussing with it just to get the most basic thing to work. I know enough sql out of the gate to make this work off the top of my head
insert a json array directly into into sqlite with sqlite-utils.
Stepping Up My SQL Game
In 2018 I transitioned from a Product Engineering (Mechanical) role to a Data Scientist Role. I entered this space with strong subject matter expertise with our products, our data, munging through data in pyhon, and data visualization in python. My sql skills were lacking to say the least. I had learned what I needed to know to get data from our relational databases, then use pandas to do any further analysis. Just run something like the following and you have data.
SELECT * FROM Table Where col_1 = 'col_1_filter'
This technique works great for small data sets that you only need to run once. There is no shame to pull in a big dataset and start munging with it in pandas to get some results, and make decisions. The problem becomes when your dataset becomes too big or you need to run the query on a frequent basis. Doing the aggregations on the server run much quicker, as it reduces the time spent in io. My longest running steps are currently io related....
...