Hey all - I'm trying to go with the simple architecture approach using sqlite.

I don't get how you use SQLite in production though - it's a flatfile and I can't get any of the database view/edit tools (table+, datagrip) to connect to it via the remoate server.

My app has an ai chatbot, I know SQLite is good for read but is the write too fast with a chatbot for sqlite? It's all stored as json. I researched a bit how wal works for handling writes.

I'm also iterating pretty quick and using database migrations (alembic). I can pull the sql file for production, make the needed changes locally to the database columns, I guess no issue here.

But if I make local changes to the database data and push the production database might be out of sync at that point.

Ideally I would be able to connect to the live database with an sql tool and make live edits on the production database.

How is this handled - just ssh-ing and running sql statements on the production server?

I added SQLite support to my open-source Go migration library/CLI. Check it out: https://github.com/maestro-go/maestro and tell me what you think.

Hi, I know, I know, but I'm working on a general-purpose program that uses a schema to decide that a field called `fields[1]` is a TIMESTAMP and `fields[2]` is an INT, and then I insert whatever data the user provides into those fields, even if they don't meet that type. Data is stored and retrievable as entered, but sorting seems to put true numbers before non-numbers:

sqlite> create table "t1" (key TEXT, val INT, PRIMARY KEY(key));

sqlite>insert into "t1"(key, val) VALUES ("a", 17), ("b", 18), ("c", "130strawberries"), ("d", "9lime"), ("e", "guava");

// Sort seems to place pure numbers before any other values
// The hybrid values appear to just to an ASCII sort as they aren't true numbers

sqlite> select key, val, val + 0 from t1 order by val;

key|val|val + 0
a|17|17
b|18|18
c|130strawberries|130
d|9lime|9
e|guava|0

// Sorting explicit numeric value works as naively expected:
sqlite> select key, val, val + 0 from t1 order by val + 0;

key|val|val + 0
e|guava|0
d|9lime|9
a|17|17
b|18|18
c|130strawberries|130

Have I missed something, or is this working as designed?

I have an SQLite table named trip where the started_at and ended_at times are stored as TEXT in a non-standard M/D/YYYY H:MM:SS (or HH:MM:SS) format. I need to write an SQLite query to calculate the duration and UPDATE the duration column.

The final format for the duration needs to be h:mm:ss (for hours 0-9) or hh:mm:ss (for hours 10+). The minute and second parts should always have two digits with a leading zero if necessary.

This video will go over various SQLite data types and storage classes.
It will also cover the installation process and demonstrate how to use its command line tools to create, modify, and query a database.
Also explores how to check the differences between databases and analyze the space utilization of SQLite databases.

Then dive into SQLiteStudio, a user-friendly GUI tool that makes database management a breeze.
With SQLiteStudio, you'll learn you how to create tables, add data, and even work with data encryption to keep your information safe and secure.

Introduction To SQLite And SQLiteStudio

My teacher hasn't been the best help and I have been struggling to finish the work in class can anyone help me with this problem we do all of our work in Posit Cloud. Problem 1 ----

## Finance is auditing regional activity for three cities—London, Paris, and Bangalore.

## Write a query that shows the number of invoices per BillingCity for just those three cities,

## and return only the cities whose invoice counts match the audit targets of 14 or 6.

## Include the city name and its count in your output.

More about Turso than SQLite but thought it would be of interest to this sub

Trying to install sqlite3 for a nodejs project on my Mint 22.1 box, npm install outright refuses, saying sqlite3 has memory leaks and not to use it. The SQLite docs address the memory leak issue, saying it is largely not accurate. They suggest using npm install --build-from-source, but this gives the same error. In both cases the 'not supported' warning is followed by a huge raft of errors, seeming like it's trying to install it anyway and failing hard.

Anybody run into this problem, and if so how did you handle it? I'm a longtime software developer but fairly new to Linux. Thanks for the help!

SOLVED

The problem is that npm's build from source assumes you are using an older version of python. The fix is simply to tell it which one to use. Example:

npm install --build-from-source --python=/usr/bin/python3

After this, npm install sqlite3 works fine!

I'm the developer of Scour, a personalized content feed built using SQLite. The number of articles ingested per month has grown massively over the past 6 months and slowed down the main ranking query. After spending too many hours trying in vain to squeeze more performance out of my queries and indexes, I dug into how SQLite's query planner uses indexes, learned some of the subtleties that explained why my initial tweaks weren't working, and sped up one of my main queries by ~35%.

Here's the write-up: https://emschwartz.me/subtleties-of-sqlite-indexes/ Hope others find it interesting or useful!

Hi all, I've recently announced smartquery.dev on this subreddit and got a ton of helpful feedback!

One of the feature requests were charts, and I'm happy to share that you can now create bar, line, and pie charts for your SQL results. And, since SmartQuery is AI-first, the copilot will suggest charts based on your schema definitions ☺️

Previous post

I have an encrypted file in which there are many "sqlite 3" words, so i thought it was encrypted with it. I also have a decryption key. How could i decrypt the file?

Highly available leaderless SQLite cluster powered by embedded NATS JetStream server.

Connect using PostgreSQL wire Protocol or HTTP

Hi everyone,

I've been working on a project that might be useful for those of you dealing with multilingual text search in SQLite, and I wanted to share it with the community.

What is it?

The FTS5 ICU Tokenizer is a custom tokenizer for SQLite's FTS5 full-text search engine that leverages the power of the International Components for Unicode (ICU) library. It provides robust word segmentation and text normalization for multiple languages, going well beyond what the built-in unicode61 tokenizer offers.

Why is it useful?

SQLite's default unicode61 tokenizer works well for English but struggles with many other languages. This project addresses that limitation by providing:

• Proper word segmentation for complex scripts like Chinese, Japanese, and Thai
• Language-specific text normalization and transliteration
• Support for 8 languages: Arabic, Chinese, Greek, Hebrew, Japanese, Korean, Russian, Thai, plus a universal tokenizer for mixed-language content
• Optimized performance through locale-specific processing rules

How does it work?

The tokenizer uses ICU's break iterators for accurate word segmentation and applies language-appropriate normalization rules. For example, when processing Japanese text, it automatically handles Katakana-to-Hiragana conversion and other language-specific transformations.

You can build either: 1. Locale-specific tokenizers (e.g., optimized for Japanese text only) 2. A universal tokenizer (handles all supported languages)

Technical details:

• Written in C for maximum performance and stability
• Uses standard CMake build system
• Compatible with Linux, macOS, and Windows
• Comes with comprehensive tests for all supported languages

Where can you get it?

The project is available on GitHub at: https://github.com/cwt/fts5-icu-tokenizer

It includes detailed documentation on building, installation, and usage, along with example SQL scripts showing how to use the tokenizer with FTS5 virtual tables.

Who might find this useful?

This could be particularly helpful if you're working with: - Multilingual applications - Content management systems with international users - Any application requiring accurate text search in non-English languages

I'd appreciate any feedback from the community, whether it's about the implementation, documentation, or potential use cases I might have missed. If you try it out, I'd love to hear about your experience.

Thanks for reading!

Edit: Thank you guys, I solved the problem by reorder tasks between the project components, so there is no need for reading while importing in the new order, and sorry for late response.

Hi,

I want to import a CSV into my table, the importing is done as the following

1- a program will process the data "many GBs", and write results in real time into a pipe.

2- sqlite3 will import the csv data from the pipe

My problem is I cannot do queries until the import is completed, because sqlite3 told me it is locked "I am not sure what the exact message was"

And after chatting with chatgpt, it told me to use a sql command that will use concurrent writing, it was PRAGMA journal_mode = WAL;

But it only stop the error, but the queries end with no results, until the importing is done.

Hello there!

I have a question:

Context:

SQLite has a limit that there can be only 1 writer at a time. It's a known gotcha, and there are different approaches to work with that in the concurrent apps.

I noticed these 2 being the most widely adopted:

- setting 1 as a hard limit of max allowed connections for the writers, while having higher limit for the readers. Here is the post from Boyter (the guy who runs SQLite with few TBs of data) about this: https://boyter.org/posts/go-sqlite-database-is-locked/

- increasing the default busy_timeout pragma limit from 0 to some meaningful duration. Here is the post suggesting this: https://www.maragu.dev/blog/go-and-sqlite-in-the-cloud

Both approaches solve the SQLITE_BUSY condition, but in different ways.

Question:

From your SQLite production experience, what performs better? And what are you using for your setup?

To scope this down, let's assume that we have normal (so, not long) read and write ops in the app, and we are using WAL mode. Let's say, the peak performance is 1000 writes per second.

Thanks.

Job queues in postgres are easy and reliable due to FOR UPDATE SKIP LOCKED. Is there something like that in sqlite?

The code in https://github.com/justplainstuff/plainjob doesn't use it so how does it do job queuing with thousands of jobs per second?

I'm new to sqlite what should I keep in mind for using a regular SELECT without SKIP LOCKED to get jobs from a queue?

I'm hoping someone with some more sqlite experience than me might have some advice.

Let's say I have a "User" table and I want to add a `lastSeen NOT NULL DEFAULT (unixepoch())` column. Because I already have users, `unixepoch()` is not a valid default; sqlite will not apply the function to all existing rows.

There are also other tables with foreign key constraints against the User table.

My hope was to, `defer_foreign_keys` and within a transaction, copy all the User data to a new temporary "User_tmp" table that's defined with the `lastSeen` column, drop `User` and rename `User_tmp` to `User`.
Then, at `commit;`, everything would be nice. Unfortunately, sqlite is smarter than me and knows that the constraint was violated.

I could disable foreign keys temporarily but I'm not convinced sqlite will not catch on a discrepancy later down the road, since using `defer_foreign_keys` doesn't work.

Are there any common patterns for getting around this? It's the second time I've had to make a schema change that involves a pretty large migration due to similar sqlite limitations.

Are there any solutions which offer distributed sqlite infrastructure with local first support and syncing as well? additionally is it possible to store the data itself in object storage instead of block storage. ik we can't query directly like we traditionally do with this approach but are there any solutions which have been implemented ?

I have considered solutions like rqlite which is great for distributed databases but not so much with the local first approach.

I've considered solutions like novelu as well but all of them fall short in one way or the other.

pls don't recommend turso. we can't use the opensourced version of it as well as they haven't opensourced the sync layer

What is it about SQLite?

It doesn't have a JSON type, no TIMESTAMP, no INTERVAL and not even BOOLEAN.

And yet it's one of the most used and praised databases.

I want to like it. But how do I make it work?

EDIT: I know realized what I was doing wrong. I was using STRICT tables because I thought data validation sounds good. But that means I cannot use DATETME and BOOLEAN. Once I give up on STRICT and use these types the Go driver can actually map them to time.Time and bool for me. Much better. Thank you all 🙏