grab beautiful studio ghibli stills & check a cool trick to copy sqlite databases
Recent searches
Search options
#sqlite
12 posts11 participants0 posts today
Continued thread
GRDB seems the way to go. Anyone got experience with shipping a custom SQLite build to load extensions?
https://github.com/groue/GRDB.swift/discussions/1591
GitHubCan I load an SQLite extension in GRDB? · groue GRDB.swift · Discussion #1591I want to load an SQLite extension and then use it with raw SQL in GRDB. Is that possible, or am I ill-fated here?
HackW/E@LinuxHotel oh boy, `spatialite` doesn't use a geographic index? You need to do this complicated nested queries to use it?
Maybe I need to switch to #postgis
#sqlite #gis #spatialite
A faster way to copy SQLite databases between computers – alexwlchan
LinkA faster way to copy SQLite databases between computers
https://example.com/faster-sqlite-copy
Summary: 本文介紹了一種更快速且穩定地在不同電腦間傳輸 SQLite 資料庫的方法。作者面臨傳統使用 rsync 直接複製 SQLite 資料庫的瓶頸，特別是在資料庫規模增大且含有多個索引時，複製時間顯著增加且容易出錯。解決方案是利用 SQLite 的 .dump 指令將資料庫導出為包含建表及插入指令的純文字檔，該檔案大小往往比原始資料庫小許多，主要因為索引只以指令形式存在，不重複儲存數據。再將這個文字檔壓縮成 gzip 檔案，透過 ssh 傳輸到本機後解壓，再依序執行文字檔內容重建資料庫。這樣不僅減少了傳輸檔案大小，也避免了傳輸過程中資料庫被異動導致損壞的問題，進而提升傳輸速度與穩定性。最後作者分享實際例子，一個 3.4GB 的資料庫壓縮後文字檔僅約 240MB，縮小約 14 倍，顯著節省傳輸時間及資源。
Key Points: → ★ 問題描述：隨著 SQLite 資料庫及索引規模增大，使用 rsync 複製資料庫耗時且容易出錯。
→ ★ 主要瓶頸：索引會額外複製重複資料，導致檔案體積膨脹。
→ ★ 解決方案：利用 sqlite3 的 .dump 指令將資料庫轉成 SQL 指令的純文字檔，內容包含建表、插入資料以及索引建立語法。
→ ★ 優點：純文字檔大小常小於原始資料庫，gzip 壓縮後更為顯著，減少資料重複複製。
→ ★ 實務流程：① 在遠端伺服器透過 ssh 指令產生壓縮的純文字檔，② 使用 rsync 傳輸該檔案到本地，③ 解壓縮並利用 sqlite3 將 SQL 指令匯入重建資料庫，④ 清理暫存文字檔。
→ ★ 穩定性提升：先產生純文字轉存版本，避免在傳輸過程中資料庫被更新造成不一致導致打開錯誤。
→ ★ 範例數據：3.4GB 的 SQLite 資料庫，轉 dump 後文字檔約 1.3GB，用 gzip 壓縮後約 240MB，縮減近 14 倍。
→ ★ 實務價值：大量或多索引的 SQLite 資料庫能透過此方法更有效率且安全地傳輸與備份。
Keywords: #SQLite #資料庫備份 #資料庫傳輸 #gzip壓縮 #rsync
readhacker.newsA faster way to copy SQLite databases between computersDumping a SQLite database to a text file can make it much smaller, which means you can download it faster.
A faster way to copy SQLite databases between computers
alexwlchan.netA faster way to copy SQLite databases between computersDumping a SQLite database to a text file can make it much smaller, which means you can download it faster.
A faster way to copy SQLite databases between computers
alexwlchan.netA faster way to copy SQLite databases between computersDumping a SQLite database to a text file can make it much smaller, which means you can download it faster.
DB-Engines Ranking climbers of the month:
DB-EnginesDB-Engines RankingPopularity ranking of database management systems.
Replied in thread
What may be interesting is to look at https://data-star.dev, a #hypermedia library (that blows #htmx out of the water), with a back-end in #golang that can potentially replace an entire typical Rube Goldberg front-end stack, depending on the use case.
The maintainers have very interesting talks on YT to highlight the innovative approach, which involve #SSE. The maintainer uses a bootstrap that by default has #SQLite (and #NATS, which 
recently announced relicensing to #BUSL).
DatastarDatastar - The hypermedia framework.Datastar helps you build reactive web applications with the simplicity of server-side rendering and the power of a full-stack SPA framework.
Building a Flask-SQLite Boilerplate: A Step-by-Step Guide
Kickstart your next web application with a robust Flask-SQLite boilerplate. This guide walks you through setting up your development environment, creating a user authentication system, and managing da...
https://news.lavx.hu/article/building-a-flask-sqlite-boilerplate-a-step-by-step-guide
SQLite może być świetny w dużej skali. Zdziwieni? Czy faktycznie system zarządzania bazami danych kojarzący się z malutkimi, prostymi aplikacjami ma szansę zaistnieć w większych przedsięwzięciach? Warto zapoznać się z tym tekstem.
#SQLite #skalowanie #ArchitekturaOprogramowania
https://rivet.gg/blog/2025-02-16-sqlite-on-the-server-is-misunderstood
rivet.gg · SQLite-on-the-Server Is Misunderstood: Better At Hyper-Scale Than Micro-ScaleWe're Rivet, a new open-source, self-hostable serverless platform. We've been in the weeds with SQLite-on-the-server recently and – boy – do we have a lot of thoughts to share. Give us a star on GitHub, we'll be sharing a lot more about SQLite soon!
That feeling when you forgot your password but it's been a local install anyway so you just sqlite3 into the database and generate a new bcrypt with Ruby.
Ok. Just culled ~20,000 of 25,000 #tumblr posts accumulated via #RSS over a month. I've gotta get to work on my own project applying basic statistics to this stuff. Think "reverse chronological + $all_your_own_filtering_and_sorting_and_bucketing". Not sure if I should do that within the confines of a #thunderbird add-on, or try some other approach. I really, really want to be able to use #sql for this stuff. It's a natural fit. But #sqlite seems to be a no-go for #WebExtensions. There is only #IndexedDB, which, in my limited experience with it, is absolute garbage to work with.
Hey all, I wrote a #PHP #SQLite #OpenSource forum called #Threadbare - https://github.com/Moult/threadbare
If your community is mailing list scale (i.e. not huge multilingual multicategory nested threads) check it out. If you're looking for an alternative to works-only-with-JS needs-containerisation logs-everything and want something minimal with performance near to a static site, it might be for you. Would love feedback and testing and security advice!
See https://community-test.osarch.org/ for demo.
Finally came up with a project to learn #Rust with. Not moving particularly fast, but it's making me spend a lot of time considering the implementation of data structures with ownership and lifetimes in mind.
I've been away from system-level programming for a while and last time I did any it was #C++ which is its own kind of pain.
Working on building up structures backed by a #sqlite #database as well, so getting #SQL practice also.
Replied in thread
@fractaledmind you're everywhere now if #SQLite is in the game, aren't you? 
沙丁魚罐頭：使用 Rails 實現 SQLite 多租戶架構
➤ 克服 ActiveRecord 的限制，實現高效能多租戶架構
✤ https://blog.julik.nl/2025/04/a-can-of-shardines
本文探討了在 Rails 應用程式中實作「每個租戶一個資料庫」架構的挑戰。作者分享了過去在維護一個使用此架構的應用程式時遇到的問題，並指出現有的多租戶 gem (如 Apartment) 也存在類似的線程安全問題。文章深入探討了 ActiveRecord 連線管理機制，以及 Rails 如何使看似簡單的任務變得複雜，並提供了一個可行的解決方案：一個中間件，能有效處理多租戶環境下的資料庫連線。作者強調了使用 SQLite 作為小型專案的優勢，例如易於配置、備份及除錯，並指出此架構能有效限制資料庫規模，降低複雜性。
+ 這篇文章深入淺出地分析了 Rails 多租戶架構的難點，對於正在考慮類似方案的開發者來說，非常有參考價值。
+ 作者的經驗分享很實用，提供的中間件解決方案讓我看到了希望，可以嘗試應用在我
#Rails #SQLite #多租戶 #資料庫架構
Julik Tarkhanov · A Can of Shardines: SQLite Multitenancy With RailsThere is a pattern I am very fond of - “one database per tenant” in web applications with multiple, isolated users. Recently, I needed to fix an application I had for a long time where this database-per-tenant multitenancy utterly broke down, because I was doing connection management wrong. Which begat the question: how do you even approach doing it right? And it turns out I was not alone in this. The most popular gem for multitenancy - Apartment - which I have even used in my failed startup back in the day - has the issue too. The culprit of does not handle multithreading very well is actually deeper. Way deeper. Doing runtime-defined multiple databases with Rails has only recently become less haphazard, and there are no tools either via gems or built-in that facilitate these flows. It has also accrued a ton of complexity, and also changes with every major Rails revision. TL;DR If you need to do database-per-tenant multitenancy with Rails or ActiveRecord right now - grab the middleware from this gist and move on. If you are curious about the genesis of this solution, strap in - we are going on a tour of a sizeable problem, and of an API of stature - the ActiveRecord connection management. Read on and join me on the ride! Many thanks to Kir Shatrov and Stephen Margheim for their help in this.
Continued thread
Thank you!
In #SQLite: LIKE is case-insensitive by default for ASCII characters (like normal English letters A–Z).