Found 36 results for "tag:sqlite"

• Big Endian's Guide to SQLite Storage

  https://blog.jabid.in/2024/11/24/sqlite.html

  I wanted to learn how databases like SQLite store data under the hood, so Idecided to write some code to inspect the database file. SQLitefamously stores the...

  sqlite

• Building a pivot table in SQLite

  https://antonz.org/sqlite-pivot-table/?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  Three ways to create a pivot table in plain SQL.

  sqlite

• Collection of insane and fun facts about SQLite - blag

  https://avi.im/blag/2024/sqlite-facts/

  Some of the interesting and insane facts I learned about SQLite

  sqlite

• Data analysis with SQLite and Python, PyCon 2023 — Data analysis with SQLite and Python, PyCon 2023 557f94c documentation

  https://sqlite-tutorial-pycon-2023.readthedocs.io/en/latest/

  sqlite

• David Heinemeier Hansson | High Performance SQLite

  https://highperformancesqlite.com/interviews/dhh?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  David Heinemeier Hansson, creator of Ruby on Rails, discusses SQLite and its use in Basecamp.

  sqlite

• Home - High Performance SQLite

  https://highperformancesqlite.com/

  A comprehensive video course by database educator Aaron Francis. Learn everything you need to confidently use SQLite in production.

  sqlite

• How (and why) to run SQLite in production | Fractaled Mind

  https://fractaledmind.github.io/2023/12/23/rubyconftw/?utm_source=unknownews

  This is my personal site, where I write about Ruby, programming, and any of my varied fascinations.

  sqlite

• Interactive SQLite Documentation: Experiment with Queries in Real-Time!

  https://blog.sqlitecloud.io/interactive-sqlite-documentation-experiment-with-queries-in-real-time?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  At SQLite Cloud, we are dedicated to making database management as seamless and intuitive as possible. Today, we are thrilled to unveil a groundbreaking addition to our platform - the Interactive SQLite Documentation! Now, alongside our comprehensive...

  sqlite

• Introducing Limbo: A complete rewrite of SQLite in Rust

  https://turso.tech/blog/introducing-limbo-a-complete-rewrite-of-sqlite-in-rust

  we forked SQLite with the libSQL project. What would it be like if we just rewrote it?

  sqlite

• Maximum Speed SQLite Inserts

  https://blog.julik.nl/2025/01/maximum-speed-sqlite-inserts

  In my work I tend to reach for SQLite more and more. The type of work I find it useful for most these days is quickly amalgamating, dissecting, collecting and analyzing large data sets. As I have outlined in my Euruko talk on scheduling, a key element of the project was writing a simulator. That simulator outputs metrics - lots and lots of metrics, which resemble what our APM solution collects. Looking at those metrics makes it possible to plot, dissect and examine the performance of various job flows. You can, of course, store those metrics in plain Ruby objects and then work with them in memory - there is nothing wrong with that. However, I find using SQL vastly superior. And since the simulator only ever runs on one machine, and every session is unique - SQLite is the perfect tool for collecting metrics. Even if it is not a specialized datastore. One challenge presented itself, though: those metrics get output in very large amounts. Every tick of the simulator can generate thousands of values. Persisting them to SQLite is fast, but with very large amounts that “fast” becomes “not that fast”. I had to go through a number of steps to make these inserts more palatable, which led to a very, very pleasant speed improvement indeed. That seems worth sharing - so strap in and let’s play. Setting the scene Let’s generate our data first and see how far we can push our little setup. rng = Random.new(42) metrics = %w( foo bar baz bad bleg ) values = (500_000).times.map do |n| {name: metrics.sample(random: rng), value: rng.rand} end We will assume we are inserting from Hash objects representing column-value mappings. We will use a fresh database for every test and keep it in memory to not even care about the filesystem performance - for now: def create_db db = SQLite3::Database.new(":memory:") db.execute("CREATE TABLE metrics (name VARCHAR NOT NULL, value FLOAT NOT NULL)") db end and add a timing helper: def timed(&blk) t = Process.clock_gettime(Process::CLOCK_MONOTONIC) yield delta = Process.clock_gettime(Process::CLOCK_MONOTONIC) - t warn "Took #{delta} seconds" end First - the naive insert: timed("Naive") do db = create_db first_record = values.first cols = first_record.keys.join(", ") placeholders = (["?"] * first_record.length).join(", ") sql = "INSERT INTO metrics (#{cols}) VALUES (#{placeholders})" values.each do |cols_to_values| db.query(sql, cols_to_values.values) end end This gives Naive - 2.3065050000150222 seconds. Surely we can do better than that. Transactions for bulk insert are great, let’s use one: timed("With transaction") do db = create_db db.transaction do first_record = values.first cols = first_record.keys.join(", ") placeholders = (["?"] * first_record.length).join(", ") sql = "INSERT INTO metrics (#{cols}) VALUES (#{placeholders})" values.each do |cols_to_values| db.query(sql, cols_to_values.values) end end end That gives With transaction - 1.8898840000038035 seconds. Better, but by far not the improvement we need. Let’s use a prepared statement next: timed("With transaction and prepared statement") do db = create_db first_record = values.first cols = first_record.keys.join(", ") placeholders = (["?"] * first_record.length).join(", ") sql_stmt = "INSERT INTO metrics (#{cols}) VALUES (#{placeholders})" db.transaction do prepared_stmt = db.prepare(sql_stmt) values.each do |cols_to_values| prepared_stmt.execute(cols_to_values.values) end end end This gives: With transaction and prepared statement - 0.6456299999845214 seconds - much better. But we can go further. By default SQLite optimizes for durability (at least on my version). Since we are working with a local database and we do not care about a potential crash, we can “downgrade” the durability of the storage engine to get more speed: timed("With pragmas, transaction and prepared statement") do db = create_db db.query("PRAGMA synchronous = OFF") db.query("PRAGMA journal_mode = OFF") first_record = values.first cols = first_record.keys.join(", ") placeholders = (["?"] * first_record.length).join(", ") sql_stmt = "INSERT INTO metrics (#{cols}) VALUES (#{placeholders})" db.transaction do prepared_stmt = db.prepare(sql_stmt) values.each do |cols_to_values| prepared_stmt.execute(cols_to_values.values) end end end Still better: With pragmas, transaction and prepared statement - 0.6219140000175685 seconds - this is already a substantial improvement, but we can give the crank another turn. Host parameter stuffing How can we make it even faster than that? Well, the INSERT SQL statement supports multiple tuples in sequence, as long as they have the same cardinality. A bit like so: INSERT INTO metrics (name, value) VALUES ('foo', 1.0), ('bar', 2.0), ('baz', 4.0) We can assign our placeholders in the prepared statement and then pass our bound parameters in the end: db.query("INSERT INTO metrics (name, value) VALUES (?, ?), (?, ?)", ["foo", 1.0, "bar", 2.0"]) But there is a limit - the maximum number of bound variables per SQL statement, varies with the version of SQLite. Sadly, the sqlite3 gem does not support querying for sqlite3_limit(), but the info says: To prevent excessive memory allocations, the maximum value of a host parameter number is SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999 for SQLite versions prior to 3.32.0 (2020-05-22) or 32766 for SQLite versions after 3.32.0. We can thus assume the value to be 999 for now (but you can query for the version using SQLite3::VERSION or try to find another way to access the sqlite3_limit API). What we need to do is figure out how many of our records we can stuff into a single INSERT - since we cannot really “split” the records, we always need to insert all values pertaining to a single record in one statement. max_bindvars = 999 first_record = values.first cardinality = first_record.length records_per_statement, _ = max_bindvars.divmod(cardinality) This shows us that we can at most stuff records_per_statement into a single INSERT (the remainder is not really useful here). We will use 2 statements, one of which we will prepare - since it is going to be reused. The first one will fit as many records as we can and bind variables for all of them - 999 bindvars or less, depending on the cardinality of our records. The second one will contain enough bindvars to fit the remaining records, and will be used only once - in fact, we do not even need to prepare it. timed("With multirow inserts, pragmas, transaction and prepared statement") do db = create_db db.query("PRAGMA synchronous = OFF") db.query("PRAGMA journal_mode = OFF") first_record = values.first # We need to group our records into blocks of at most max_bindvars values cardinality = first_record.length row_placeholder = "(" + (["?"] * cardinality).join(", ") + ")" # = (?, ?, ?) max_bindvars = 999 max_records_per_statement, _ = max_bindvars.divmod(cardinality) prepared_statement_for_max = nil cols = first_record.keys.join(", ") db.transaction do values.each_slice(max_records_per_statement) do |records_subset| bound_params = records_subset.flat_map(&:values) if records_subset.length == max_records_per_statement prepared_statement_for_max ||= begin placeholders_for_larger_chunk = ([row_placeholder] * max_records_per_statement).join(", ") sql_max = "INSERT INTO metrics (#{cols}) VALUES #{placeholders_for_larger_chunk}" db.prepare(sql_max) end prepared_statement_for_max.execute(bound_params) else # This is the last slice which is smaller placeholders_for_smaller_chunk = ([row_placeholder] * records_subset.length).join(", ") sql_rest = "INSERT INTO metrics (#{cols}) VALUES #{placeholders_for_smaller_chunk}" db.query(sql_rest, bound_params) end end end end Running all of our implementations then gives us: Naive - 2.7048650000069756 seconds With transaction - 2.3600640000076964 seconds With transaction and prepared statement - 0.637083999987226 seconds With pragmas, transaction and prepared statement - 0.6406159999896772 seconds With multirow inserts, pragmas, transaction and prepared statement - 0.3141590000013821 seconds We can see that using multirow inserts gives us a 2x speedup. Splendid. Memory databases to disk Of course, this is with memory databases - so it is probably very fast because of that. But what if I told you that you can actually serialize a memory DB onto disk very quickly, just using the builtin SQLite functions? A little-known feature of SQLite called online backup can be used to prepare your database in memory, do all of the bulk operations – and then write it out onto the filesystem, in a very fast (and consistent) way. The API in the Ruby gem is not pretty - but it is there and it works, and it works well (has been for more than a decade, in fact). Let’s put it to use: def write_to_disk(source_db, filename) destination_db = SQLite3::Database.new(filename) b = SQLite3::Backup.new(destination_db, 'main', source_db, 'main') begin b.step(1) end while b.remaining 0 b.finish destination_db.close end Running the code gives us: Naive - 2.706573000003118 seconds With a prepared statement - 0.990191999997478 seconds With transaction and prepared statement - 0.627656000026036 seconds With pragmas, transaction and prepared statement - 0.6277800000098068 seconds With multirow inserts, pragmas, transaction and prepared statement - 0.3135960000217892 seconds These timings include serialization to disk using the backup API. And produces a few SQLite3 files of exactly the same size. Comparing disk and memory performance Out of curiosity, I would like to show what kind of performance we can have if we perform the same “accelerated” inserts on a disk DB, with the same 500000 rows: Disk DBs: Naive - 474.50496300001396 seconds With transaction - 2.3882359999988694 seconds With transaction and prepared statement - 0.7706829999806359 seconds With pragmas, transaction and prepared statement - 0.7386469999910332 seconds With multirow inserts, pragmas, transaction and prepared statement - 0.3565039999957662 seconds Memory DBs: Naive - 2.3065050000150222 seco

  sqlite

• Maxteabag/sqlit: A user friendly TUI for SQL databases. Written in python. Supports SQL server, Mysql, PostreSQL and SQLite, Turso and more.

  https://github.com/Maxteabag/sqlit

  A user friendly TUI for SQL databases. Written in python. Supports SQL server, Mysql, PostreSQL and SQLite, Turso and more. - Maxteabag/sqlit

  sqlite

• My workflow for writing SQL(ite) queries (2024 edition) · Jamie Tanna | Software Engineer

  https://www.jvt.me/posts/2024/06/07/sql-workflow/?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  Writing about my recent workflow for writing, executing, and sharing SQL queries with others.

  sqlite

• Optimizing SQLite for servers

  https://kerkour.com/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 people trying it will encounter poor performances and the dreaded SQLITE_BUSY error. But what if I told you that by tuning a

  sqlite

• Python's sqlite3 with extensions

  https://antonz.org/sqlean-py/

  A drop-in replacement for the sqlite3 module, bundled with essential extensions.

  sqlite

• QuadrupleA/sqlite-page-explorer: Visual tool to explore SQLite databases page-by-page, the way they're stored on disk and the way SQLite sees them.

  https://github.com/QuadrupleA/sqlite-page-explorer

  Visual tool to explore SQLite databases page-by-page, the way they're stored on disk and the way SQLite sees them. - QuadrupleA/sqlite-page-explorer

  sqlite

• SQL Polyglot

  https://codapi.org/sql/

  Try running a query anywhere from PostgreSQL to DuckDB in your browser.

  sqlite

• SQL Window Functions Explained

  https://antonz.gumroad.com/l/sql-windows

  About window functionsWindow functions are probably the most confusing section of SQL. You might think, "So what? They just came up with some additional functions". Not really. "Window functions" is a separate language built into regular SQL. And it's more complicated than everything you know about SELECTs.In short, window functions assist in making great analytical reports without Excel. Maybe you want to calculate monthly sales percentages over the year? Window functions. Split marketing channels into effective and ineffective ones? Window functions. Choose the top 10 clients for each segment? Same.About the bookThis book is a clear and visual introduction to window functions. Clear — because I can describe complex topics in a readable way. Visual — because I have prepared a hundred pictures and GIFs to help you understand SQL windows.Window functions are a complex topic. So the book only teaches a little at a time. It gives just enough theory and a lot of practice, because it’s the only way to turn abstract knowledge into skills.The book starts with SQL windows basics (Part 1) and goes through frame nuances (Part 2) to some pretty advanced stuff (Part 3).Book contentsHere is what you will learn:Why use window functionsWindows and functionsRankingOffsetAggregationRolling aggregatesStatisticsFramesROWS and GROUPSRANGEEXCLUDEFILTERPracticeFinanceClusteringData cleaningAbout the authorI'm Anton Zhiyanov, a Python/Golang developer and SQLite enthusiast. I work on open source, teach courses and blog about programming.In 2021 I launched a course on SQL window functions. It now has 1000+ graduates and an average rating of 5 stars based on 300+ student reviews.In 2023 I decided to write a book based on the original course. Here it is.Book details296 pages100+ pictures and GIFs56 interactive exercises (with solutions)PDF 17.8 × 23.3 cm (7 × 9.2 inches)

  sqlite

• SQLime — Online SQLite playground

  https://sqlime.org/

  SQLime is an online SQLite playground for debugging and sharing SQL snippets.

  sqlite

• SQLite Features You Didn’t Know It Had: JSON, text search, CTE, STRICT, generated columns, WAL

  https://slicker.me/sqlite/features.htm?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  SQLite has evolved far beyond a simple embedded database. Explore modern features like JSON, FTS5, window functions, strict tables, and more.

  sqlite

• SQLite in Production: Lessons from Running a Store on a Single File | ultrathink.art Blog

  https://ultrathink.art/blog/sqlite-in-production-lessons?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  We run a production Rails store on SQLite — not Postgres, not MySQL. A single file on a Docker volume. It works surprisingly well until two containers try to...

  sqlite

• SQLite3 Fiddle

  https://sqlite.org/fiddle/index.html

  sqlite

• SQLiteStudio

  https://sqlitestudio.pl/

  sqlite

• Screwtape / sqlite-schema-diagram · GitLab

  https://gitlab.com/Screwtapello/sqlite-schema-diagram?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  GitLab.com

  sqlite

• Why you should probably be using SQLite

  https://www.epicweb.dev/why-you-should-probably-be-using-sqlite?utm_source=unknownews

  Where you store your application data has enormous impacts on your entire application. There are implications on the entire stack based on what you decide to...

  sqlite

• coleifer/sqlite-web: Web-based SQLite database browser written in Python

  https://github.com/coleifer/sqlite-web?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  Web-based SQLite database browser written in Python - coleifer/sqlite-web: Web-based SQLite database browser written in Python

  sqlite

• dgllghr/stanchion: A SQLite extension that brings column-oriented tables to SQLite

  https://github.com/dgllghr/stanchion

  A SQLite extension that brings column-oriented tables to SQLite - dgllghr/stanchion: A SQLite extension that brings column-oriented tables to SQLite

  sqlite

• ellie/atuin: 🐢 Magical shell history

  https://github.com/ellie/atuin?utm_source=unknownews

  🐢 Magical shell history. Contribute to ellie/atuin development by creating an account on GitHub.

  sqlite

• frectonz/sql-studio: SQL Database Explorer [SQLite, libSQL, PostgreSQL, MySQL/MariaDB]

  https://github.com/frectonz/sql-studio

  SQL Database Explorer [SQLite, libSQL, PostgreSQL, MySQL/MariaDB] - frectonz/sql-studio

  sqlite

• https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance

  https://fractaledmind.github.io/2024/04/15/sqlite-on-rails-the-how-and-why-of-optimal-performance

  sqlite

• https://sqlite-internal.pages.dev/?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  https://sqlite-internal.pages.dev/?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  sqlite

• https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3

  https://www.digitalocean.com/community/tutorials/how-to-use-the-sqlite3-module-in-python-3

  sqlite

• https://www.geoffreylitt.com/2025/04/12/how-i-made-a-useful-ai-assistant-with-one-sqlite-table-and-a-handful-of-cron-jobs

  https://www.geoffreylitt.com/2025/04/12/how-i-made-a-useful-ai-assistant-with-one-sqlite-table-and-a-handful-of-cron-jobs

  sqlite

• https://www.sqlitetutorial.net/sqlite-python/create-tables

  https://www.sqlitetutorial.net/sqlite-python/create-tables

  sqlite

• https://www.sqlitetutorial.net/sqlite-python/creating-database

  https://www.sqlitetutorial.net/sqlite-python/creating-database

  sqlite

• sqlite-utils

  https://sqlite-utils.datasette.io/en/stable/index.html?utm_source=hackernewsletter&utm_medium=email&utm_term=data

  sqlite

• tcgoetz/GarminDB: Download and parse data from Garmin Connect or a Garmin watch, FitBit CSV, and MS Health CSV files into and analyze data in Sqlite serverless databases with Jupyter notebooks.

  https://github.com/tcgoetz/GarminDB

  Download and parse data from Garmin Connect or a Garmin watch, FitBit CSV, and MS Health CSV files into and analyze data in Sqlite serverless databases with Jupyter notebooks. - tcgoetz/GarminDB

  sqlite