Is it the SQL dialect there lacking or is it the built-in functions?

I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.

I like SQLite (qualifying not for you, simonw, but for others). But I hate that I can't be lazy by using arrays in SQLite... because they don't exist. group_concat is a poor approximation.

Also, I genuinely dislike how loose SQLite is with allowed syntax. Probably it's preference. But even interactively I prefer to know immediately that I messed up a query. SQLite is so forgiving I've often wasted time trying to understand why my results are nonsense (because I typoed in the query and SQLite didn't fail the query).

But I also strongly dislike Python for that reason and I know where you stand there. Maybe SQLite/PostgreSQL is similar to the dynamic/static language preference divide.

I'm increasingly finding myself using SQLite's JSON features for array stuff - they're surprisingly convenient once you get into the habit of using them. A couple of recent examples:

- https://til.simonwillison.net/sqlite/sort-by-number-of-json-...

- https://til.simonwillison.net/sqlite/function-list#user-cont...

Depends on what easily means.

Sqlite can't do custom format date parsing and regex extract. How do we extend something like this?

If we go beyond a simple function to window function, I imagine it would be even harder.

At this point, we nlmight as well use postgres.

Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0].

If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]

[0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust...

[1]: https://github.com/multiprocessio/go-sqlite3-stdlib

[2]: https://github.com/nalgeon/sqlean

Wow this is helpful. I'm using sqlite for some of my projects and always bothered that some functions are missing. WITH RECURSIVE is too mind bending.

This seems like I can add a lot more functions to it, not just regex extract.

Came here to complain and learned something useful.

Probably also worth noting: you don't need to build (many kinds of) extensions as C-compatible code and separate .so files that you load.

SQLite is an in-process database. You can give it a callback func to execute. So your regex-extract can literally just call a function in your code: https://sqlite.org/appfunc.html

edit: Python's stdlib documentation concisely shows how easy this can be: https://docs.python.org/3/library/sqlite3.html#sqlite3.Conne... Basically every SQLite library should have something similar. This extreme ease of extending is a big part of why SQLite has so little built-in.

Funny you should mention those specific examples - I have Datasette plugins adding custom SQL functions to SQLite for both of them!

- https://datasette.io/plugins/datasette-dateutil

- https://datasette.io/plugins/datasette-rure

The entire point is to bring your own functions to SQLite, since it is presumably running in-proc and can be integrated with trivially.

https://sqlite.org/appfunc.html

We currently use this path to offer a domain-specific SQL-based scripting language for our product.