Posts tagged: database

- Absolutely incredible what Preston is doing with his time. What a life changing experience this must be for him. Good job to Turso for making this happen. We are going to end up with very feature rich file based databases out of this that the whole world will benefit from. Note This post is a thought [1]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /thoughts/

Using Litestream to Restore My Database for Easy Development | Nic Payne Litestream see [[using-litestream-to-backup-quadtasks-sqlite-db]] for how I setup litestream replication for [[quadtask]] I have the entrypoint to my app contai pype.dev [1] I really like how well the local dev is setup to run off of production data here. I’ll use this as a reminder that I need to set up lite stream on a few of my projects that it’s missing from and include a nice sync prod data Posts tagged: justfile [2] recipe. Litestreams interface always throws me for a loop. It works fantastic, but the global config stored in /etc and some of the commands break my brain. It’s not you it’s me. Using real data when you can is goated. Fake data is so often a perfect example of what someone thinks the backend should look like and does not include things that users actually do, running pipelines for days, or setting titles to paragraphs worth of text. Obviously this is not possible everywhere and the more sensitive your data the harder that process becomes. Note This post is a thought [3]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://pype.dev/u...

External Link X (formerly Twitter) · x.com [1] I’ve never tried generated columns, but it is something that I’m interested in. It is nice to have things computed and in the database if you ever need to query on them. My brain is now churning with possibilities and quesions, does this work with Alembic? How does this compare to views? [2] Note This post is a thought [3]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://x.com/MeetGor21/status/1967818642768470447 [2]: https://dropper.waylonwalker.com/api/file/2b763b3d-1709-47e6-a13d-bfec7baef47d.png [3]: /thoughts/

- This talk about live store really made me think about database transactions in a new way. They are talking about live-store, and the complexity of distributed applications like a notes app with the ability to go offline and continue working. The complexity of resyncing each instance is not simple, conflict resolution accross all the possible installs that may or may not even be online is a really hard problem. They go deep on discussing an event driven paradigm that is driven off of a log of events and how this changes how we deal with databases. Using the event log as the source of truth we can do things like forget about database migrations, we can replay all of the events onto a new database. Its very interesting to rethink in terms of a log system that speaks in terms of understandable events (not table operations) as the source of truth for an application. Note This post is a thought [1]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /thoughts/

Proper handling of None in WHERE condition · Issue #109 · fastapi/sqlmodel First Check I added a very descriptive title to this issue. I used the GitHub search to find a similar issue and didn't find it. I searched the SQLModel documentation, with the integrated search. I... GitHub · github.com [1] SQLModel models ship with an is_, and is_not that you can use to compare to None without pesky linters complaining. This comment summed it up quite well. I believe this is concerned entirely with SQLAlchemy, not with SQLModel, and has to do with the required semantics to construct a BinaryExpression object. Hero.age == None evaluates to a BinaryExpression object which is eventually used to construct the SQL query that the SQLAlchemy engine issues to your DBMS. Hero.age is None evaluates to False immediately, and not a BinaryExpression, which short-circuits the query no matter the value of age in a row. From a cursory search, it does not seem that the is operator can be overridden in Python. This could help explain why the only possibility is by using the == operator, which can be overridden. so rather than using Team.heros == None we can use Team.seros.is_(None) which checks for...

External Link X (formerly Twitter) · x.com [1] Aaron Francis is a database master, pumped for thsi dude and all that he is able to accomplish. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://x.com/aarondfrancis/status/1833533150674268469 [2]: /thoughts/

Database Remote-Copy Tool For SQLite (draft) Neat new SQLite utilities often show up in branches of the SQLite repository. Here's a new one from last month: sqlite3-rsync, providing tools for efficiently creating and updating copies of … Simon Willison’s Weblog · simonwillison.net [1] Simon shared a really cool new utility tool for sqlite ispired by rsync. It checks hashes of each sqlite page and syncs pages. So if nothing in the database has changed it will only require 0.5% the bandwidth as a copy would. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://simonwillison.net/2024/Oct/4/sqlite-rsync/ [2]: /thoughts/

sqlite-jiff I linked to the brand new Jiff datetime library yesterday. Alex Garcia has already used it for an experimental SQLite extension providing a timezone-aware jiff_duration() function - a useful new … Simon Willison’s Weblog · simonwillison.net [1] Sqlite is getting rust extensions now, and datetimes make it totally worth if if they work well and and fast, two things that don’t always go together in datetime libraries Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://simonwillison.net/2024/Jul/23/sqlite-jiff/ [2]: /thoughts/

External Link stackoverflow.com [1] I learned about the sqlite_master table from this stack overflow answer. This helps make a lot of sense to how sqlite works. The master table contains all the sqlite objects and the sql to create them. The .tables, and .schema “helper” functions don’t look into ATTACHed databases: they just query the SQLITE_MASTER table for the “main” database. Consequently, if you used sqlite3 database.db "SELECT * from sqlite_master;" Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://stackoverflow.com/questions/82875/how-can-i-list-the-tables-in-a-sqlite-database-file-that-was-opened-with-attach#answer-83195 [2]: /thoughts/

External Link stackoverflow.com [1] Another interesting option for slow count queries in sqlite. If you haven’t DELETEd any records, doing: SELECT MAX(ROWID) FROM "table" LIMIT 1; Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://stackoverflow.com/questions/8988915/sqlite-count-slow-on-big-tables [2]: /thoughts/

Optimizing SQLite for servers SQLite is often misconceived as a "toy database", only good for mobile applications and embedded systems because it's default configuration is optimized for embedded use cases, so most ... Sylvain Kerkour · kerkour.com [1] Very interesting article by Sylvain, suggested by Simon Willison. Definitely some things that I want to come back and try later on. Here is the TLDR of the whole post PRAGMA journal_mode = WAL; PRAGMA busy_timeout = 5000; PRAGMA synchronous = NORMAL; PRAGMA cache_size = 1000000000; PRAGMA foreign_keys = true; PRAGMA temp_store = memory; This is interesting, and something I need to consider. I definitely have an application with slow count queries. I am not sure how to make it better as its not a full count(*) so a count table doesn’t work, nor does counting by index. I might need to have a table of cached results, and if a write matches the counter increase it, or update all counters on write. COUNT queries are slow SQLite doesn’t keep statistics about its indexes, unlike PostgreSQL, so COUNT queries are slow, even when using a WHERE clause on an indexed field: SQLite has to scan for all the matching records. One solution...

![[None]] import logging from typing import List import strawberry from fastapi import FastAPI from strawberry.fastapi import GraphQLRouter logger = logging.getLogger(__name__) authors = {} books = {} book_authors = {} authors_books = {} def get_author_for_book(root) -> "Author": return authors[book_authors[root.id]] @strawberry.type class Book: id: int title: str author: "Author" = strawberry.field(resolver=get_author_for_book) def get_books_for_author(root) -> List[Book]: print(f"getting books for {root}") return [books[i] for i in authors_books[root.id]] @strawberry.type class Author: id: int name: str books: List[Book] = strawberry.field(resolver=get_books_for_author) authors = {1: Author(id=1, name="Michael Crichton")} books = {1: Book(id=1, title="Jurassic Park")} # relationships book_authors[1] = 1 authors_books[1] = [1] def get_author_by_id(id: int) -> Author: return authors.get(id) def get_book_by_id(id: int) -> Book: return books.get(id) def get_authors(root) -> List[Author]: return authors.values() def get_books(root) -> List[Book]: print(books) print(authors) print(book_authors) print(authors_books) return books.values() @strawberry.typ...

![[None]] First I need to fetch my thoughts from the api, and put it in a local sqlite database using sqlite-utils. fthoughts () { # fetch thoughts curl 'https://thoughts.waylonwalker.com/posts/waylonwalker/?page_size=9999999999' | sqlite-utils insert ~/.config/thoughts/database2.db post --pk=id --alter --ignore - } Now that I have my posts in a local sqlite database I can use sqlite-utils to enable full text search and populate the full text search on the post table using the title message and tags columns as search. sthoughts () { # search thoughts # sqlite-utils enable-fts ~/.config/thoughts/database2.db post title message tags # sqlite-utils populate-fts ~/.config/thoughts/database2.db post title message tags sqlite-utils search ~/.config/thoughts/database2.db post "$*" | ~/git/thoughts/format_thought.py | bat --style=plain --color=always --language=markdown } alias st=sthoughts Now I am ready to search my thoughts, which is a tiny blog format that I created mostly for leaving my own personal comment on web pages, so most of them have a link to some other online content, and their title is based on the authors title. [1] [2] Note This post is a thought [3]. It...

- Great example from Anthony showing how easy it is to practice building database orm models and playing with them in a repl. This is good practice even if you are in a big code base to be able to test and learn in a simplified code base that does not have a mountain of other code around atuh, permissions, security, and other complex things that come into real production code bases that might make it hard to focus on what you are trying to do. Note Anthony uses backref here, thats legacy, use back_populates on both parent and child. Note This post is a thought [1]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /thoughts/

External Link stackoverflow.com [1] Today I came across some sqlalchemy models that created some relationships, some used backref some used back_populates. I was stumped why, I had never came accross backref before and I felt skill issues sinking in. backref is considered legacy # [2] https://docs.sqlalchemy.org/en/14/orm/backref.html As stated in the sqlalchemy docs, backref is a legacy feature. Its shorthand to creating relationships between parent and child, but only adding it to the parent. While this is simpler it introduces some invisible magic. Note This post is a thought [3]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://stackoverflow.com/questions/51335298/concepts-of-backref-and-back-populate-in-sqlalchemy#answer-59920780 [2]: #backref-is-considered-legacy [3]: /thoughts/

Read a Range of Data - LIMIT and OFFSET - SQLModel SQLModel, SQL databases in Python, designed for simplicity, compatibility, and robustness. sqlmodel.tiangolo.com [1] Today I was running some sqlmodel queries through the sqlalchemy orm. Admittedly I’ve not done enough orm queries before, and I’ve done quite a bit of raw sql. I was trying to get objects from two separate models that had relationships setup. session.query(User, Images).where(User.id == 3).all() It is incredibly slow, and gives me the following warning. SELECT statement has a cartesian product between FROM element(s) What I learned from the SQLModel docs is that you should give it a join to correct this and go much faster. session.query(User, Images).join(Images).where(User.id == 3).all() Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://sqlmodel.tiangolo.com/tutorial/limit-and-offset/ [2]: /thoughts/

Open source, not open contribution with Ben Johnson (Changelog Interviews #433) This week we're talking with Ben Johnson. Ben is known for his work on BoltDB, his work in open source, and as a freelance Go developer. Late January when Ben open sourced his newest project Litest... Changelog · changelog.com [1] Ben Johnson was on the Changelog a few years back covering his work on litestream, and talks about why he chose to go open source, but not open contribution. You should have a good reason to move off of sqlite. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://changelog.com/podcast/433 [2]: /thoughts/

- Very inspiring talk, TLDR, you probably don’t need a database server. sqlite will probably be faster, simpler to maintain, and simpler to test your application. Note This post is a thought [1]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: /thoughts/

GitHub - benbjohnson/litestream: Streaming replication for SQLite. Streaming replication for SQLite. Contribute to benbjohnson/litestream development by creating an account on GitHub. GitHub · github.com [1] `litestream` is a sick cli tool for steaming replicas of sqlite. It automatically does daily snapshots, and streams all of the writes to the replica live. install # [2] Install is fast using installer, no compilation, just copy the binary and run. curl https://i.wayl.one/benbjohnson/litestream Note This post is a thought [3]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://github.com/benbjohnson/litestream [2]: #install [3]: /thoughts/

Why I Built Litestream - Litestream Despite an exponential increase in computing power, our applications require more machines than ever because of architectural decisions made 25 years ago. You can eliminate much of your complexity ... litestream.io [1] As applications scale to the edge, to put compute as close to the user as possible, database queries back to the master node get slower and slower. Enter sqlite replication, put the database wtih the application code and replicate from master. Note This post is a thought [2]. It’s a short note that I make about someone else’s content online #thoughts References: [1]: https://litestream.io/blog/why-i-built-litestream/ [2]: /thoughts/