Every now and then, I think that I have carefully proof-read my message enough times and hit the “Add message” button in tt. But then, in the message tree, I spot another missed typo. My process is then to go to my twtxt.txt and fix it by hand. However, I still have to clean up tt’s cache. This is rather tidious:

1. Recall the sqlitebrowser ~/.local/share/twtxt/tt2.sqlite from my shell history.
   
2. Switch to the “Browse data” tab.
   
3. Go to the messages table and wait a second or two until it’s loaded.
   
4. Sort by the created_at column twice, so that I get descending order.
   
5. Select the first message, which is typically the one in question.
   
6. Find the “Remove currently selected row” button in the tool bar.
   
7. Commit the changes.
   
8. Close sqlitebrowser.
   

So, I finally implemented the removal of messages from the cache in tt. I can now hit d and confirm the removal. Bam! Should have done that ages ago!

Next up is the search, I think.

The perils of UUID primary keys in SQLite
Article URL: https://andersmurphy.com/2026/06/05/the-perils-of-uuid-primary-keys-in-sqlite.html

Comments URL: https://news.ycombinator.com/item?id=48419571

Points: 6

# Comments: 2 ⌘ Read more

Show HN: Mnemo – local-first AI memory layer for any LLM (Rust, SQLite,petgraph)
Article URL: https://github.com/zaydmulani09/mnemo

Comments URL: https://news.ycombinator.com/item?id=48389586

Points: 4

# Comments: 0 ⌘ Read more

@lyse@lyse.isobeef.org AI result ahead, feel free to ignore.

I “asked” the AI at work the same question out of morbid curiousity. It “said” that SQLite converts that integer to floating point internally on overflows and then, when converting back, the x86 instruction cvttsd2si will turn it into 0x8000000000000000, even if the actual floating point value is outside of that range. So, yes, it allegedly actually saturates, as a side effect of the type conversion.

I couldn’t find anything about that automatic conversion in SQLite’s manual, yet, but an experiment looks like it might be true:

sqlite> select typeof(1 << 63);
╭─────────────────╮
│ typeof(1 << 63) │
╞═════════════════╡
│ integer         │
╰─────────────────╯

sqlite> select typeof((1 << 63) - 1);
╭──────────────────────╮
│ typeof((1 << 63) ... │
╞══════════════════════╡
│ real                 │
╰──────────────────────╯

As for cvttsd2si, this source confirms the handling of 0x8000000000000000 on range errors: https://www.felixcloutier.com/x86/cvttsd2si

The following C program also confirms it (run through gdb to see cvttsd2si in action):

<a href="https://we.loveprivacy.club/search?q=%23include">#include</a> <stdint.h>
<a href="https://we.loveprivacy.club/search?q=%23include">#include</a> <stdio.h>

int
main()
{
    int64_t i;
    double d;

    /* -3000 instead of -1, because `double` can’t represent a
     * difference of -1 at this scale. */
    d = -9223372036854775808.0 - 3000;

    i = d;
    printf("%lf, 0x%lx, %ld\n", d, i, i);

    return 0;
}

(Remark about AI usage: Fine, I got an answer and maybe it’s even correct. But doing this completely ruined it for me. It would have been much more satisfying to figure this out myself. I actually suspected some floating point stuff going on here, but instead of verifying this myself I reached for the unethical tool and denied myself a little bit of fun at the weekend. Won’t do that again.)

@lyse@lyse.isobeef.org

Disclaimer: Can’t guarantee that I’m fully awake and I’m being trained at work not to use my brain anymore, so maybe this is complete bullshit. 😪🧟‍♀️

It says here that SQLite uses signed integers:

https://sqlite.org/datatype3.html

In pure bits, 1 << 63 would be 0x8000000000000000, but as a signed value, it gets interpreted as -9223372036854775808. Subtracting 1 yields -9223372036854775809 – but that doesn’t fit in 64 bits anymore. It’s possible that SQLite doesn’t want to wrap around but instead saturates? Haven’t checked. 🤔

With 62 bits, there is enough room.

With 1 << 64, I have no idea how SQLite wants to handle this, because this should immediately trigger a warning, because it doesn’t fit right away. Maybe it gets truncated to 0?

sqlite> select printf('0x%x', 2 * (1 << 64));
╭──────────────────────╮
│ printf('0x%x', 2 ... │
╞══════════════════════╡
│ 0x0                  │
╰──────────────────────╯
sqlite> select printf('0x%x', 0 - 1);
╭──────────────────────╮
│ printf('0x%x', 0 ... │
╞══════════════════════╡
│ 0xffffffffffffffff   │
╰──────────────────────╯
sqlite> select printf('0x%x', 0 - 2);
╭──────────────────────╮
│ printf('0x%x', 0 ... │
╞══════════════════════╡
│ 0xfffffffffffffffe   │
╰──────────────────────╯

Eehhh, what the hell is going on here!?

SELECT
    printf("0x%x", (1 << 63) - 2),
    printf("0x%x", (1 << 63) - 1),
    printf("0x%x",  1 << 63     ),
    printf("0x%x", (1 << 63) + 1),
    printf("0x%x", (1 << 63) + 2)

SQLite yields:

0x8000000000000000 (instead of 0x7ffffffffffffffe)
0x8000000000000000 (instead of 0x7fffffffffffffff)
0x8000000000000000 (correct)
0x8000000000000001 (correct)
0x8000000000000002 (correct)

Huh!? O_o Am I stupid? What am I missing here? Or is this actually a bug? :-?

With 62 bits, everything is spot on:

0x3ffffffffffffffe
0x3fffffffffffffff
0x4000000000000000
0x4000000000000001
0x4000000000000002

And 64 bits rather unsurprisingly also yield:

0xfffffffffffffffe
0xffffffffffffffff
0x0
0x1
0x2

I’m supporting incremental SQLite schema changes to just upgrade from an older database version to whatever the current software version supports. In the past, I already noticed that this is quite expensive in unit tests when each test case runs through the entire schema patches and applies them one by one.

To speed up test execution I now decided that I finally go through the troubles of maintaining both a set of incremental patches and a full schema setup in one go. A unit test verifies that both ways end up with the same structure. This gives me a set of SQLs to check the structures:

SELECT type, name, tbl_name, sql
FROM sqlite_schema
ORDER BY type, name, tbl_name

Unfortunately, the resulting CREATE TABLE SQL queries are formatted differently, depending on whether the full schema was set up in one big step or the structure had been modified with ALTER TABLE. Mainly, added columns are not on their own lines but appended in one physical line. That’s why I wanted an SQL formatting tool. Since I didn’t find one that works decently, I’m now doing some simple string manipulation. Joining consecutive whitespace into a single space character, removing spaces before commas and closing parentheses and spaces after opening parentheses. This works surpringly good enough. Of course, if it fails, the “diff” is absolutely horrendous.

Now for the cool part, my test execution dropped from around 5:05 minutes to just 1:32 minutes! I call that a win.

I just stumbled across PRAGMA table_info('tablename') https://sqlite.org/pragma.html#pragma_table_info, PRAGMA foreign_key_list('tablename') and friends. I guess, I have to play with that, now. It’s probably much better to use than the SQL text approach.

@rdlmda@rdlmda.me I never saw the point of a registry to be honest, as it defeated the point of what I believed to be a truly decentralised non-social social ecosystem. What can and does work however is a search engine and crawler. I used to run one, but I took it down, mostly because it got expensive to operate, at least the implementation I built… Maybe one day i’ll try again with a SQLite backend.

I came across this on “Why Is SQLite Coded In C”, which I found interesting:

“There has lately been a lot of interest in “safe” programming languages like Rust or Go in which it is impossible, or is at least difficult, to make common programming errors like memory leaks or array overruns.”

If that’s true, then encountering those issues means the programmer is, simply, horrible?

Stoolap 0.2 Released For Modern Embedded SQL Database In Rust
Stooplap v0.2 released today as this SQLite alternative for providing embedded SQL database needs while written in the Rust programming language. Stoolap supports both in-memory and persistent storage models… ⌘ Read more

@prologic@twtxt.net porting SQLite would be kick-ass

Mu (µ) is now getting much closer to where I want it to be, it now has:

• A process stdlib module (very basic, but it works)
  
• An ffi stdob module that supports dlopen / dlsym and calling C functions with a nice mu-esque wrapper ffi.fn(...)
  
• A sqlite stdlib module (also very basic) that shows off the FFI capabilities
  

😅

@eldersnake@we.loveprivacy.club What version are you running btw? It’s probably time you upgraded and time I released a new version finally 😂 If you’re running a version that’s pre-SQLite-cache, then yeah I’m not surprised. The SQLite cache version is honestly much better 🤣

CAST(x AS STRING) casts to integer in SQLite
Comments ⌘ Read more

Durable Background Execution with Go and SQLite
1 points posted by roblaszczak ⌘ Read more

Show HN: SQLite Online – 11 years of solo development, 11K daily users
Comments ⌘ Read more

The Write Stuff: Concurrent Write Transactions in SQLite
Comments ⌘ Read more

TinqerJS – LINQ Inspired ORM for TypeScript Supporting Postgres, SQLite
Comments ⌘ Read more

SQLite Pub/Sub, Quickstart, and more — Watermill 1.5 Released
1 points posted by m110 ⌘ Read more

I corrupted my SQLite test database with sed -i s/… $(find …). Clearly, I found too many files. That’s the signal to go to bed.

golang 每日一庫之磁盤鍵值存儲 diskv
diskv你有沒有想過，在某些時候，內存不是萬能的——比如你要存的東西多得像個大水庫，又或者你就想放心大膽地關掉程序然後下班回家。而這時你需要一個靠譜的、基於磁盤的鍵值存儲庫。所以你用 sqlite 嗎？筆者第一想法就是 sqlite.來，認識一下主角：diskv，一個簡單、強大、零依賴、沒有數據庫暴脾氣的 Go 鍵值存儲庫。它的核心理念很簡單：用磁盤做字典，鍵是路徑，值是文件內容。設計哲學— ⌘ Read more

golang 每日一庫之磁盤鍵值存儲 diskv
diskv你有沒有想過，在某些時候，內存不是萬能的——比如你要存的東西多得像個大水庫，又或者你就想放心大膽地關掉程序然後下班回家。而這時你需要一個靠譜的、基於磁盤的鍵值存儲庫。所以你用 sqlite 嗎？筆者第一想法就是 sqlite.來，認識一下主角：diskv，一個簡單、強大、零依賴、沒有數據庫暴脾氣的 Go 鍵值存儲庫。它的核心理念很簡單：用磁盤做字典，鍵是路徑，值是文件內容。設計哲學— ⌘ Read more

golang 每日一庫之 rqlite
rqlite 是一個基於 SQLite 的輕量級、分佈式關係數據庫，旨在提供高可用性和容錯能力。它是一個 將 SQLite 封裝爲分佈式系統的項目，用 Go 編寫，後端使用 Raft 共識算法實現多節點數據複製與一致性。地址–GitHub: https://github.com/rqlite/rqlite文檔: https://rqlite.io目標–SQLite 是一個嵌入式的、本地文件型數 ⌘ Read more

git-remote-sqlite: Single-file Git repos that can replicate with Litestream
Comments ⌘ Read more

LiveStore is a next-generation state management framework based on reactive SQLite and git-inspired syncing (via event-sourcing)
Comments ⌘ Read more

@lyse@lyse.isobeef.org that’s alright haha! i don’t expect anyone to listen/watch in full or with full attention bc it’s so long lmao

the thing with PHP for me is that i… feel like it hits a kind of simplicity that i can understand? it’s so plain but can be very powerful. i quite like that. as much as i can learn something infinitely more powerful, PHP hits a comfortable thing where i can handle things like backend sqlite DBs AND how a page is rendered, without requiring a complex frontend with its own quirks (like ruby on rails, which as much as i know and love it, can be heavy).

but i totally get you! PHP security is very scary. i’m always worried that i’m messing something up. it’s why the PHP application i’m working on i have dockerized by default for a small but extra layer of protection

i’ll try to not get discouraged tysm for your advice

SQLite Extension Hub
Comments ⌘ Read more

git-sqlite: A custom diff and merge driver for sqlite
Comments ⌘ Read more

A Can of Shardines: SQLite Multitenancy With Rails
Comments ⌘ Read more

There are 5 of such “Twters” on this pod that have periods in their nick 😢

sqlite> select count(distinct(nick)) from twters where nick like '%.%';
count(distinct(nick)) = 5
sqlite> select distinct(nick) from twters where nick like '%.%';
 nick = @marado@ciberlandia.pt

 nick = eapl.me

 nick = eapl.mx

 nick = grumpygordie.great-site.net

 nick = @chyrp.doesnm.cc

Hmmm there’s a bug somewhere in the way I’m ingesting archived feeds 🤔

sqlite> select * from twts where content like 'The web is such garbage these days%';
      hash = 37sjhla
  feed_url = https://twtxt.net/user/prologic/twtxt.txt/1
   content = The web is such garbage these days 😔 Or is it the garbage search engines? 🤔
   created = 2024-11-14T01:53:46Z
created_dt = 2024-11-14 01:53:46
   subject = #37sjhla
  mentions = []
      tags = []
     links = []
sqlite>

**Hmmm there’s a bug somewhere in the way I’m ingesting archived feeds 🤔

sqlite> select * from twts where content like 'The web is such ga ...**
Hmmm there’s a bug somewhere in the way I’m ingesting archived feeds 🤔

sqlite> select * from twts where content like ‘The web is such garbage these days%’;

  hash = 37sjhla

feed_url = https://twtxt.net/user/prologic/twtxt.txt/1
content = The web is such garbage these days 😔 Or is it the garbage search engines? 🤔
created = 2024-11-14T01:53:46Z
created_dt = 2024-11-14 01:53:46
… ⌘ Read more

@abucci@anthony.buc.ci Apologies, the basic summary is as follows:

• Decided to rewrite the cache backend.
  
• It will now be a SQLite backend going forward.
  
• I’m planning on no data migration.

**(#axtyevq) @abucci@abucci Apologies, the basic summary is as follows:

• Decided to rewrite the cache backend.
  

• It will now be a SQLite …**
  @abucci @anthony.buc.ci Apologies, the basic summary is as follows:

• Decided to rewrite the cache backend.

• It will now be a SQLite backend going forward.

• I’m planning on no data migration. ⌘ Read more

Doesn’t look like it Hmmm

sqlite> select * from twts where content LIKE '%Linux installation%';
    hash = znf6csa
feed_url = https://www.uninformativ.de/twtxt.txt
 content = I wonder if my current Linux installation will actually make it to 20 years:

    $ head -n 1 /var/log/pacman.log
    [2011-07-07 11:19] installed filesystem (2011.04-1)

It’s not toooo far into the future.

It would be crazy … 20 years without reinstalling once … phew. 🥴
 created = 2025-04-07T19:59:51Z
 subject = (#znf6csa)
mentions = []
    tags = []
   links = []

**(#2znenta) Doesn’t look like it Hmmm

sqlite> select * from twts where content LIKE '%Linux installation%';
    hash = znf6csa
feed_url = ht ...**
Doesn’t look like it Hmmm

sqlite> select * from twts where content LIKE ‘%Linux installation%’;

hash = znf6csa

feed_url = https://www.uninformativ.de/twtxt.txt
content = I wonder if my current Linux installation will actually make it to 20 years:

$ head -n 1 /var/log/pacman.log
[2011-07-07 11:19] installed filesystem (2011.04-1)

It’s not toooo far into the future.

It wou … ⌘ Read more

(#7tkcv5a) @bender@bender Backend is SQLite now. I switched drivers and so far so good. ’m seeing very stable CPU utilization, and a stable m …
@bender @twtxt.net Backend is SQLite now. I switched drivers and so far so good. ’m seeing very stable CPU utilization, and a stable memory consumption of under 100MB ⌘ Read more

(#jihhx5a) @bender@bender Sort of, still fixing bugs. I had so much trouble with this sqlite driver, that in the end I swithced it to another …
@bender @twtxt.net Sort of, still fixing bugs. I had so much trouble with this sqlite driver, that in the end I swithced it to another driver and so far so good 😊 fingers crossed 🤞 ⌘ Read more

(#w4mnpkq) @xuu@xuu Actually… I’m working on a new Sqlite backend/cache for yarnd 🤣 I might revive yarns (_the crawler / search …
@xuu @txt.sour.is Actually… I’m working on a new Sqlite backend/cache for yarnd 🤣 I might revive yarns ( the crawler / search engine) one day 🤞 ⌘ Read more

neat! my watcher is currently sitting at about 75 MB following over 1500 feeds. only about 200 are currently somewhat active.

-rw-r--r--. 1 xuu  xuu   69M Mar 25 20:46 twt.db
-rw-r--r--. 1 xuu  xuu   32K Mar 25 21:34 twt.db-shm
-rw-r--r--. 1 xuu  xuu  5.6M Mar 25 21:34 twt.db-wal

sqlite> select state, count(*) n from feeds group by 1;
hot|7
warm|8
cold|183
frozen|743
permanantly-dead|857

chromem-go：Go 語言 RAG 應用的高效輕量級向量數據庫
前言在開發 RAG（Retrieval-Augmented Generation）應用時，起初你可能更傾向於選擇一款輕量級的向量數據庫，而非複雜的大型數據庫。例如，在關係型數據庫的選擇上，許多人會更願意使用 SQLite 而不是 PostgreSQL 或 MySQL，以減少額外的配置和維護成本。在 Go 語言中，chromem-go 提供了一種簡潔高效的解決方案——它是一款可嵌入到 Go 程序中的 ⌘ Read more

re reading so NewRAMStorage(…) is just something that setups your storage and initial data.. that can probably live with storage/sqlite. The point is the storage package does not import the implementations of storage.Storage It just defines the contract for things that use that interface. Now storage/sqlite CAN import storage and not have a circle dep.

It kinda works in reverse for import directions. usually you have your root package that imports things from deeper in the directory structures.. but for the case of interfaces it reverses where the deeper can import from parents but parents cannot import from children.

- app < storage
      < storage/sqlite
      < controller < storage
                   < storage/sqlite
 
- sqlite < storage

- storage X storage/sqlite

@lyse@lyse.isobeef.org OK. So how I have worked things like this out is to have the interface in the root package from the implementations. The interface doesn’t need to be tested since it’s just a contract. The implementations don’t need to import storage.Storage

• storage/ defines the Storage interface (no tests!)
  
  • storage/sqlite for the sqlite implementation tests for sqlite directly
    
  • storage/ram for the ram implementation and tests for RAM directly
    
• controller/ can now import both storage and the implementation as needed.
  

So now I am guessing you wanted the RAM test for testing queries against sqlite and have it return some query response?

For that I usually would register a driver for SQL that emulates sqlite. Then it’s just a matter of passing the connection string to open the registered driver on setup.

https://github.com/glebarez/go-sqlite?tab=readme-ov-file#connection-string-examples

@bender@twtxt.net oh yeah i remember that part of the docs lol! honestly yeah i think sqlite is fine for the number of users i have which is like, 5 including me, and active users is just… me, but if i were to have more active users i could always spin up a separate instance as jank as that is

i’m pretty sure i’m running this all off sqlite so if i get too many users on here i might be cooked but oh well i can always try to migrate (<– has heard migrations from sqlite to mysql/postgres are hell)

I wrote some code to try out non-hash reply subjects formatted as (replyto ), while keeping the ability to use the existing hash style.

I don’t think we need to decide all at once. If clients add support for a new method then people can use it if they like. The downside of course is that this costs developer time, so I decided to invest a few hours of my own time into a proof of concept.

With apologies to @movq@www.uninformativ.de for corrupting jenny’s beautiful code. I don’t write this expecting you to incorporate the patch, because it does complicate things and might not be a direction you want to go in. But if you like any part of this approach feel free to use bits of it; I release the patch under jenny’s current LICENCE.

Supporting both kinds of reply in jenny was complicated because each email can only have one Message-Id, and because it’s possible the target twt will not be seen until after the twt referencing it. The following patch uses an sqlite database to keep track of known (url, timestamp) pairs, as well as a separate table of (url, timestamp) pairs that haven’t been seen yet but are wanted. When one of those “wanted” twts is finally seen, the mail file gets rewritten to include the appropriate In-Reply-To header.

Patch based on jenny commit 73a5ea81.

https://www.falsifian.org/a/oDtr/patch0.txt

Not implemented:

• Composing twts using the (replyto …) format.
  
• Probably other important things I’m forgetting.

PocketBase：一體化的開源 Go 語言實時後端
PocketBase 是一個開源的實時後端解決方案，採用單一文件的形式進行分發。它由嵌入式數據庫 (SQLite) 和實時訂閱功能組成，提供了開箱即用的文件和用戶管理功能、方便的管理儀表板 UI 以及簡單的 REST 風格 API。本文將深入探討 PocketBase 的功能、安裝、示例代碼和一些高級的應用技巧。主要特性—-嵌入式數據庫PocketBase 內嵌 SQLite 數據庫，這意味着 ⌘ Read more

@bender@twtxt.net I have nothing against GoToSocial, but:

GoToSocial stores statuses, accounts, etc, in a database. This can be either SQLite or Postgres.

snac is simpler. Some JSON files and that’s it. I can read them with jq and less. I can use tar to back them up. I can hand edit them in a text editor.

https://kerkour.com/sqlite-for-servers Optimizing and get rid of SQLITE_BUSY error

https://antonz.org/sqlite-pivot-table/