SQLite now supports recursive CTEs | Hacker News Reader

SQLite now supports recursive CTEs

sqlite.org

114 pointschacham1522 comments

This is awesome. I've used WITH RECURSIVE in postgres to do graph traversals (even replaced neo4j with postgres), and now this is also available with sqlite. I can see running tests in development becoming a whole lot easier.

So you run tests on SQLite and use Postgres in production? Sounds like a disaster waiting to happen, if it hasn't already.

You only test on developer workstations before pushing to production? Sounds like a disaster waiting to happen, if it hasn't already.

Lots of us do this. If you're using an ORM then it makes sense to do it to keep unit test time down. We do it but have a full integration suite against the primary engine (SQL Server). Not had one discrepancy.

Your CI server should be running a stack very similar to production, but there are excellent reasons to use SQLite/H2/etc. in dev.

Being able to do a "git clone foo; cd foo; do my job" is a great way to start working, especially if you have struggled with front-end stylists using real data and back-end services, or any other specialised, possibly short-term, experts who are working on your project and don't have a need to learn basic db admin skills.

Never mind if you use RAC in production - you're going to make some concessions in your dev environment.

is postgres as quick as neo4j, in your use case? ive really been into neo4j lately, but im curious if postgres could also be used as a graph database.

Yes, on the queries that I've done (mostly friendship graphs, and doing queries like getting friends of friends) postgres was pretty much on par with neo4j. I'm way more familiar with tuning postgres though, so there's that.

I really liked the cypher query language of neo4j, it was super easy to learn and is way simpler to express complex traversals across a graph. Writing a query in postgres to traverse a graph isn't as straightforward, and unless you're doing it often will take a long time to get used to.

i wasnt expecting that answer...

how large was your friends table? maybe it was quick when doing friends of friends because the friends table was already wholly loaded into memory?

i wonder if your relationship traversals spanned across different tables, if postgres would still be as quick?

It is strange that SQLite will get this before MySQL. But good job to the SQLite team!

When it comes to SQL standard support my bet is always on SQLite before MySQL (except for any multi user/role stuff, of course).

The other exception I'd note is type-related stuff, since, while MySQL isn't always in line with the standard there, SQLite has deliberately chosen a different course than teh standard when it comes to types.

And praise the lord for it. SQLite's simplified type set makes my life easier.

This support was added in SQLite 3.8.3 and released in early February

https://sqlite.org/changes.html

I've been using it in some internal code at work since late February. It works pretty well. I was surprised by how fast it is.

The following query computes an approximation of the Mandelbrot Set and outputs the result as ASCII-art

That's the biggest nerd-out I've seen in weeks.

Nerds like weird tricks when they discover Turing-completeness in their favorite language.

Free credits for the first person to port it to an embedded blinkenlights platform.

I am really happy with this.

I also like the variety of examples they're providing. Granted, the Mandelbrot Set ASCII-Art is not something I'd see myself using, it's pretty cool!

Fantastic news! My major gripe with SQLite was that it didn't support recursion (and subsequently had to move to a different database despite filling all of my other requirements); I'm glad to see this is no longer the case.

yo .. that sudoko solver SQL and Madelbrot SQL is sick !!!

I wrote some code in PostgreSQL to deal with children's deriving properties from parents, etc.

Finally I can have this in :)

This is awesome. Is there an Ubuntu PPA that keeps up with these releases?

Reply on news.ycombinator.com