#sqlite
I really want to run #SQLite in production :blobfoxbongo:
A note on querying #sqlite from #emacs and exporting the results in a buffer formatted as CSV https://shawnhoover.dev/notes/emacs-sqlite-csv.html
Doncs ja tinc el contenidor de les meves tasques corrent al servidor... Tot molt senzill amb #Codeigniter #SQLite i #Podman encara no l'havia provat en mòbil però és veu força bé...
Anyone got experience with using SQLite in production? Please share!
Been impressed with Turso usage of #libSQL to the edge with replication and multi-tenancy, especially the performance. Great post about the underlying architecture including #sqlite https://compileralchemy.substack.com/p/libsql-diving-into-a-database-engineering
Query is a Rust server for your remote SQLite databases
https://github.com/gc-victor/query
#rust #sqlite
I have never seen such a simple graph database implementation as this sqlite schema
https://github.com/dpapathanasiou/simple-graph/blob/main/sql/schema.sql
My latest project, still a "tech preview" but starting to come to life and supports some simple demo cases. Check it out!
https://gitlab.com/cmars232/ddcp
DDCP (Database-to-Database Copy) replicates #SQLite databases over #Veilid
It's a Git-like CLI for VLCN's CR-SQLite (https://vlcn.io), a Rust-native networking layer for reconciling database changes and an agent that publishes a database to peers.
some notes of mine, on upgrading/transitioning alembic to an async set-up and what I'm especially proud of, on how to differentiate the environment for alembic in a, in my opinion, most easy and concise way:
https://hrdina.me/posts/async-alembic-notes/
Would love to read some opinions/comments!
#blogging #blogpost #alembic #sqlalchemy #python #notes #async #sqlite #mysql
#100DaysOfCode : #NodeJS #Express #SQLite #VanillaJS
#Day43 : continuing Newsletter Express project
Source Code : https://github.com/teotimepacreau/Express-Newsletter
Changes :
- implemented a functionality to allow the client to render the email in a new tab
Learned :
- handling query parameters in Express
#WebDev #Frontend #CSS #WebDesign #LearnWebDev
#Javascript #LearnToCode
#LearnJavascript #JS #emailgeeks #bentos #UI #UserInterface
ooooh a spatialite competitor? https://til.simonwillison.net/sqlite/sqlite-tg
If you still think that #SQLite will not scale with your small startup traffic, check this out: https://voidstar.tech/sqlite_insert_speed/
Hey #RStats, keen to get takes on this.
I really like data-management solutions like #duckdb, #ApacheArrow, #sqlite etc which help you manage large datasets whilst keeping your analysis local.
My question is, how do you approach version control with these tools? How do you make your work reproducible? Not talking about your code (just use Git), but the data it operates on. I can create a duckdb database, but it's not obvious how I should share this with others.
Syntax for CSS nesting, SQLite Internals, and manage your dotfiles
👉 Please retweet if you ❤ Plurrrr. Thanks! 👍
Introducing a family of #OpenSource #LocalFirst #Dart and #Flutter #CRDT packages including support for #Sqlite, #PostgreSQL, #Hive and even plain old hashmaps.
With a #sync layer to enable #realtime communication between any combination of those.
Try the demo: https://draw.cachapa.net
As a regular #PostgreSQL user, I am accustomed to using the 'percentile_cont()' function to calculate quartiles for data sets. The need just arose to calculate quantiles in #SQLite, and a) there is no built-in aggregate function for this purpose, and b) existing approaches that I found are inaccurate in some circumstances.
Doing the calculation accurately requires several subqueries or CTEs. My code for grouped quantiles is here: https://splinterofthesingularity.blogspot.com/2023/09/median-and-quartiles-by-group-in-sqlite.html.
Always use strict mode in SQLite: https://www.sqlite.org/stricttables.html
Was doing some munging of public transit data in Excel format into an SQLite database to take advantage of datasette and was bitten by loose typing. Seems like an archaic usage born of an era of MongoDB and friends.
Public agencies need to level up and start releasing things in database format like SQLite but the ecosystem and tooling is not quite there.
Canonical's dqlite (distributed SQLite) has been forked by its original author as cowsql:
J'ignorais qu'il y avait eu une tentative de sortir SQLite4 mais qui n'a jamais abouti et dont les travaux ont servi à améliorer SQLite3.
SQLite4: SQLite4
https://sqlite.org/src4/doc/trunk/www/index.wiki
And of course, #sqlite everything with #corrosion. 😍
Yeah... I'm going to swap some things this weekend. 😎
would be great to have time (be paid) to create a sample #clusterless app on #AWS to put data behind a #DuckDB WASM frontend
https://duckdb.org/2021/10/29/duckdb-wasm.html
So instead of Athena/Glue integration that can work against a complete corpus, have DuckDb over the last 30 days for investigations etc
https://github.com/ClusterlessHQ/aws-s3-log-pipeline
quick reminder, https://chris.wensel.net
I want to do this with #sqlite https://datasette.io as well (I did it in a previous role and it was awesome)
#100DaysOfCode : #NodeJS #Express #SQLite #VanillaJS
#Day42 : Newsletter Express project
Source Code : https://github.com/teotimepacreau/Express-Newsletter
Changes :
- reworked completely all styling to get a handcrafter #Bento UI
- when unsub, if checks if email is already in the DB, if not it sends an error notif
Learned :
-focus-visible
-SQL conditions
- adding a js parameter that is added to the SQL selection
#WebDev #Frontend #CSS #WebDesign #LearnWebDev #Javascript #LearnToCode #LearnJavascript #JS #emailgeeks
I started working with SQLite recently —
(SQLite is one of the databases that PostFreely supports. This support was inherited from WF)
So far I like SQLite
There is no database-server. And the database seems to just be a single file
(Might make for easy backups)
There is even a command-line tool, that lets you inspect and work with a SQLite data file
I am assuming that SQLite has a much lower "footprint" than MySQL, Postgres, and others
Show HN: ElectricSQL, Postgres to SQLite active-active sync for local-first apps
https://electric-sql.com/
#ycombinator #apps #crdt #crdts #data #database #development #elixir #lo_fi #local_first #mobile #postgres #replication #software #sqlite #sync #typescript #web
I think the reason I was initially appalled by #sqlite not storing "dates" is that it went against my expected level of abstraction. Probably #postgres does something similar under the hood, but presents a more robust date/datetime interface. 🤔 Who's to say which approach is more right?
(I still prefer the higher level of abstraction Postgres provides)
#sqlite vs #json vs #csv
What If OpenDocument Used SQLite?
🔗https://www.sqlite.org/affcase1.html 🔗 🟠 https://news.ycombinator.com/item?id=37553574
"file over app"
🔗https://twitter.com/GalaxyKate/status/1703514991679128038
@sebsauvage agreed. #SQLite getting incredibly popular for all kinds of use cases. And including production DB at relatively high scale too.
What popped up in my head as an extension of this thought experiment, having taken an interest for #WebAssembly (server-side Component Model + #WASI) was SQLite + #Wasm ..
https://www.sqlite.org/wasm/doc/trunk/index.md
And then picture - in this case - OpenDocument as a WASI interface, backed by SQLite.
🤔 What if #OpenDocument used #SQLite?
Benefits would include:
- Smaller documents
- Faster File/Save times
- Faster startup times
- Less memory used
- Document versioning
- A better user experience
A nice thought experiment by the SQLite folks..
In today's #SQLite post, I show how simple snapshotting and restoring local databases can be when your entire database is simply a file on the filesystem: https://fractaledmind.github.io/2023/09/14/enhancing-rails-sqlite-local-snapshots/
Why build an API when you can ship your #Database! https://fly.io/blog/skip-the-api/ 😀
Loving all this new development happening around #sqlite: LibSQL, Turso, LiteFS etc.
💎 Enhancing your Rails app with SQLite: Branch-specific databases
➥ fractaledmind.github.io
「 Over the course of this series, I want to lay out some of the key tweaks we can make to our Rails applications to make working with SQLite more powerful and pleasurable. To start, I want to focus on a developer experience (DX) improvement for local development 」
https://fractaledmind.github.io/2023/09/06/enhancing-rails-sqlite-branch-databases/
I ran into a pretty strange issue today w/`sqlite3_prepare_v2` in part of a #Swift project that has been working for months and that I hadn't changed.
What I did change is switching from the system library to my own build of #SQLite using Amalgamation. It turns out that the LIMIT clause isn't supported for UPDATE and DELETE.
https://www.sqlite.org/compile.html#enable_update_delete_limit
Sqlite-Utils 3.35.1 is Out: https://github.com/simonw/sqlite-utils/releases/tag/3.35.1 #linux #update #foss #release #sqliteutils #sqlite
This will excite only a very niche audience, but we've released v1.0 of our #FERC #XBRL extraction library, which consumes the taxonomies and filings published by FERC via RSS and uses them to build #SQLite databases that are much more amenable to analytical usage. it works for FERC Forms 1, 2, 6, 60, and 714 which started using XBRL in 2021. We're now working on integrating the 2022 data.
SQLSync is a collaborative offline-first wrapper around #SQLite. It is designed to synchronize web application state between users, devices, and the edge.
https://github.com/orbitinghail/sqlsync
#DenoKV is in Open Beta https://deno.com/blog/kv-open-beta
#Deno now has a #database built into it's core!!
When you run it locally it's free and backed by #SQLite, and when you use #DenoDeploy it uses #FoundationDB
Comparative analysis of embedded databases performance on single board computer systems using Python
https://ieeexplore.ieee.org/document/10000475
Abstract:The article deals with comparative analysis of SQL and NoSQL embedded databases performance on single-board computer systems, such as #SQLite, #LMDB and #UnQLite. The aim of the article is to perform comparative testing of CRUD operations performance through database connection software implemented using Python, on single-board computer systems...
What is an example of an open source system with an exceptionally good test suite?
The example I gave today is #SQLite, with 100% branch coverage, MC/DC testing and 600 (!) test lines per line of code.
- How SQLite is tested: https://www.sqlite.org/testing.html
- MC/DC for SQLite: https://corecursive.com/066-sqlite-with-richard-hipp/#testing-and-aviation-standards
- SQLite architecture: https://www.vldb.org/pvldb/vol15/p3535-gaffney.pdf
Anyone have experience doing database migrations from #SQLite to #PostgreSQL? Is it complicated?
@timwilson
> "Is it crazy to think that a simple system like this would run just fine with SQLite instead? "
Not crazy at all, #SQLite will do fine in a read-only context. Much less (communications) overhead than #PostgreSQL or #MariaDB, so likely considerably faster.
I've got a PHP website with a PostreSQL backend. It's entirely read-only in production, and its largest table has about 10,000 rows. Postgres, PHP, and a Caddy proxy all run in separate Docker containers.
Is it crazy to think that a simple system like this would run just fine with SQLite instead? In the longer term I’d like to move the whole thing to running with the Django Rest Framework and rework the front-end bit entirely.
#TIL that sqlite doesn't have a datetime column - it stores dates as text ("YYYY-MM-DD HH:MM:SS"), real numbers (days since noon on Nov 24, 4714 B.C (!!)) or an integer (unix epoch)
https://www.sqlite.org/datatype3.html#date_and_time_datatype #sqlite #database #db
SQLite is an amazing piece of software... I've watch this several times:
Bulk export of highlighted content in your books from your Kobo ebook reader is straightforward with this trick:
https://ambience.sk/bulk-export-highlights-from-kobo-ebook-readers/
#ebooks #ebook #kobo #export #highlights #hacking #sqlite #tips #hacks
Weekend hack/toy! 😏 https://github.com/nileshtrivedi/turso-row-level-security-demo
A tiny #cloudflare Worker that provides authorization layer on top of #Turso / #sqlite db by reflecting on policies defined in the database itself. Now you can send SQL queries directly from the frontend safely, even for a multi-user app. 😱
Don't use it for anything serious! 😀
#SQLite has released its latest version, 3.43, which includes new features such as support for Contentless-Delete FTS5 Indexes, performance upgrades for #JSON processing, and various other enhancements and bug fixes.
https://alternativeto.net/news/2023/8/sqlite-3-43-unveiled-with-new-features-and-json-performance-improvements/
Les performances de #NextCloud sont absolument nullissimes. Je sais je me répète, mais par curiosité après une remarque de @pyg l'année dernière sur le stockage de fichiers dans #SQLite, je voulais tester les perfs de #Paheko niveau téléchargement de fichier.
Scénario : une asso partage un fichier PDF de 1 Mo.
Paheko, stockage dans SQLite : ~250 req/sec.
Paheko, stockage en local : ~280 req/sec
Paheko, stockage en local + X-SendFile : 330 req/sec
Et maintenant…
NextCloud : 8 req / sec !!!
@jwildeboer so much wisdom in this. But remember, #SQLite is just a placeholder for any light embedded database.
If your software needs a database, you should always have a #SQLite option. This makes it far easier to offer your software as a simple #container or flatpak that is completely self-contained. I promise you, in 90% of the cases, that is sufficient. I know you all aim for super big scale deployments, but it almost always starts with a test install that should be as easy as possible to get up and running.
So I'm slowly typing out the #SQlite WASM bindings for #TypeScript, based on the docs. And the awe I have for SQLite has multiplied. Every single part of the API is *meticulously* documented, even the most obscure lowlevel WASM function. Simply amazing.
If there ever is a software Nobel prize, Dr. Hipp and company should be the first ones to receive it...
Our tooling is mostly #Python based. We primarily use @pandas_dev for data wrangling, and @dagster for orchestration.
We publish our smaller, more relational outputs as #SQLite databases and bigger skinny tables as Apache #Parquet datasets.
Historically we've focused on wrangling semi-structured data like spreadsheets, VisualFoxPro DBs, XBRL, and piles of CSVs into clean tables, but recently we've also started using OCR and ML to extract tables from regulatory PDFs in bulk (millions of pages).
Three ways to create a pivot table in plain SQL.
#SQLite #Database #SQL #Programming
https://antonz.org/sqlite-pivot-table/
Is there a static site generator that can use a #SQLite DB as source?
I can sync my https://pinboard.in bookmarks to a SQLite database using https://github.com/jacobian/pinboard-to-sqlite, but I need to be able to generate HTML pages from that DB.
Does anyone have a good #ORM for @deno_land that works with #SQLite?
I’m using the SQLite dependency, but it requires a bit of mapping. Any advice/hints would be appreciated. #TypeScript
:blobcat_tap: ok let's gooooooo :blobcat_tap: the Secret Project is no longer secret :blobcat_tap:
Introducing #TiddlyPWA – https://tiddly.packett.cool/ – Storage & Sync solution for #TiddlyWiki @TiddlyWiki@fosstodon.org that turns it into a modern offline-first web app with client-side encryption! Syncs with a simple #sqlite backed server that can be hosted on @glitchdotcom@mastodon.social in one click! 🚀
Blog Post | TW Talk Announcement | Codeberg Repo | Patreon
Version 2 is out and ready to try! We’ve got #Playwright! We’ve got #Litestream! We’re all in on #SQLite and #FlyIo! It’s totally free for personal (non-commercial) use!
Grab a copy while it’s fresh
https://github.com/piepworks/blaze-starter
Okay I'm releasing my #Ada bindings to #SQLite : https://git.sr.ht/~nytpu/sqlite-ada
I'm quite proud of the bindings because they're thick bindings to SQLite specifically, so unlike SQL libraries that abstract over multiple DBMSes, it exposes SQLite's rather broad featureset. It actually binds to things that I've never seen bound in any SQLite binding for any language, like loading both shared & static SQLite extensions, doing incremental BLOB I/O, and having very granular mapping of exceptions to SQLite errors so you can handle specific expected errors and let the rest propagate upwards.
