Farshid/Ashouri

sqlite

61 posts tagged “sqlite”.

  1. 2025

  2. Geopoly in SQLite

    I noticed this morning that one of my Datasette installations had the Geopoly SQLite extension enabled.

    sqlite geopoly gis geojson database
    4 min read

  3. SQLite BLOB literals

    I wanted to construct a string of SQL that would return a blob value: This was while writing a unit test for - for issue 19. I used it in the test here.

    TIL sqlite
    1 min read

  4. Subqueries in select expressions in SQLite - also window functions

    I figured out a single SQL query for the following today. Given a table of GitHub repositories, for each repository return: 1. The repository name 2.

    TIL sqlite
    5 min read

  5. Creating a minimal SpatiaLite database with Python

    When writing a test for datasette-leaflet-freedraw I realized I didn't have a simple tiny recipe for creating an in-memory SpatiaLite database in Python.

    spatialite sqlite python gis database
    1 min read

  6. Identifying column combination patterns in a SQLite table

    Given a large, heterogeneous table I wanted to identify patterns in the rows in terms of which columns were not null.

    TIL sqlite
    1 min read

  7. Finding the SQLite version used by Web SQL in Chrome

    Google Chrome still includes support for Web SQL, the long since abandoned proposed standard for shipping a SQL engine in browser JavaScript.

    sqlite chrome web-sql javascript browser
    1 min read

  8. Compiling and running sqlite3-rsync

    Today I heard about the sqlite3-rsync command, currently available in a branch in the SQLite code repository.

    sqlite compilation cli database synchronization
    1 min read

  9. ---

    — A one-liner to output details of the current Python's SQLite 2025-10-19 Farshid Ashouri TIL,sqlite In investigating llm/issues/164 I found myself needing to know more precise details of the Python…

    python sqlite cli
    1 min read

  10. Replicating SQLite with rqlite

    I tried out rqlite today, a "lightweight, distributed relational database, which uses SQLite as its storage engine".

    TIL sqlite
    1 min read

  11. Embedding paragraphs from my blog with E5-large-v2

    Xeophon suggested that E5-large-v2 as an embedding model that was worth a closer look. It's an interesting model. It's trained to match queries against passages of text.

    llm embeddings ai nlp python
    6 min read

  12. Trying out SQLite extensions on macOS

    Alex Garcia has been building some really cool new custom extensions for SQLite, working in C and Go. So far he's released two: - sqlite-lines, written in C, which adds and table-valued functions, for…

    TIL sqlite
    2 min read

  13. SQLite pragma_function_list()

    The SQLite table-valued function returns a list of functions that have been registered with SQLite, including functions that were added by extensions. Here's how to interpret its output.

    TIL sqlite
    3 min read

  14. How to run MediaWiki with SQLite on a macOS laptop

    Today I got curious about how MediaWiki records page history, so I started digging around and in the process figured out how to run it against a SQLite database on my macOS laptop!

    mediawiki sqlite macos php web-server
    2 min read
  15. 2024

  16. Combining CTEs and VALUES in SQLite

    Here's how to use SQLite's syntax with a CTE to create a temporary table that you can then perform joins against in a query: Try that here.

    sqlite sql cte database
    1 min read

  17. Calculating the size of a SQLite database file using SQL

    I learned this trick today while browsing the code of Blacklite, a neat Java library for writing diagnostic logs to a SQLite database.

    sqlite sql database cli bash
    1 min read

  18. Exporting Amplitude events to SQLite

    Amplitude offers an "Export Data" button in the project settings page. This can export up to 365 days of events (up to 4GB per export), where the export is a zip file containing gzipped…

    amplitude sqlite sqlite-utils data-export data-import
    1 min read

  19. SQLite triggers

    I wrote a Python script, triggers.py, to help me understand what data is available to SQLite triggers for which operations. SQLite triggers are documented here.

    TIL sqlite
    1 min read

  20. GUnion to combine geometries in SpatiaLite

    I was playing around with datanews/amtrak-geojson, which provides GeoJSON for Amtrak stations and track segments.

    spatialite sqlite gis geojson datasette
    1 min read

  21. Combining substr and instr to extract text

    Derek Willis has a Datasette instance full of political campaign emails running on Heroku. Matt Hodges pointed out that a lot of these emails include codes, which are used by ActBlue campaigns to…

    sqlite sql string-manipulation database
    1 min read

  22. Importing CSV data into SQLite with .import

    I usually use my command to import CSV data into SQLite, but for large CSV files (like a 750MB one) this can take quite a long time - over half an hour in this case.

    TIL sqlite
    1 min read