9 ms·
SQLite-based databases on the Postgres protocol? Yes we can
- zknill 3y agoI thought one of the main attractions of SQLite was that it was embedded, so specifically didn't need an HTTP round trip to query the DB. It seems like this project removes that benefit, so what is it solving for or adding instead? What's the upside?
- pama 3y agoThey mention replication and optimistic concurrency to allow multiple writers to work in parallel.
- zknill 3y agoThe optimistic concurrency is a feature of SQLite, not their sqld server. And I think there are other approaches to replication that still retain the embedded aspect; litefs, litestream. I guess I'm thinking, if it looks like postgres, and it speaks postgres (wire protocol) and it has features or replication like postgres, why wouldn't you use postgres?
- aerio 3y ago>why wouldn't you just use postgres Because postgres is not single file?
- ChoHag 3y ago[dead]
- runeks 3y agoWhy do you want something that writes to a single file?
- onetoo 3y agoThe mental model of "a single DB file" is a easier to grog than a large and seemingly opaque DB server. Most (all?) of the well-known RDBMS are designed to host the data needs of multiple separate applications, which makes configuration look a lot more complicated when all you want is n=1.
- mbreese 3y agoBut then why do you need to write to the DB remotely? It seems like the inflection point moving from "single-local-application" to "writing-to-remote-db" would be an appropriate point to switch from a single file DB to a larger RDBMS. I can think of a few scenarios where it could make sense, like a shared small DB on a LAN that doesn't need a full fledged "server", but a primary/secondary relationship makes sense. But those are pretty few... and n>1. Obviously someone wrote this because they felt a need for it, but I'm struggling to see the use-case. Maybe it's good for CI/or unit testing?
- pastage 3y agoThe use case mentioned for http is: you have edge services that need to be able to query a db and http might be the only open way to communicate. Exactly why you run on the edge or use sqlite is actually off topic. I mean I run User Mode Linux in production because it was just easier than the alternative when we first developed it.
- upon_drumhead 3y agoI self host a lot of stuff at home. I don't need a large RDBMS. A lot of software is written for Postgres and I don't bother running it as I don't want to manage it. I'm really interested in this if I can start using those apps now, without spinning up postgresql.
- ElectricalUnion 3y ago> easier to grog than a large and seemingly opaque DB server. Not really, with this solution now you need to manage a non-replicated, non-high-availability mock of Postgres where every user is 'postgres', dealing with the usual remote RPC call issues and latency. If you need to manage a RDBMS anyways, you probably can manage Postgres; if you can't manage Postgres then it's unlikely that you will be able to manage this solution as well.
- pastage 3y agoObviously because you already run sqlite and need to access it remotely for some reason. You might want to standardize how applications like Datasette works and use a known working library for the postgres wire protocol.
- 3cats-in-a-coat 3y agoThat remains the main attraction of SQLite, but it's also a small, well-written and highly reusable component for other solutions. LibSQL doesn't compete with SQLite, it doesn't replace it. It augments it, so it can scale & address more scenarios than were convenient before.
- rpastuszak 3y agoI liked the idea of using EFS and Sqlite with lambdas ( https://twitter.com/simonw/status/1361453530226192385?lang=en https://twitter.com/simonw/status/1361453530226192385?lang=e...) but then attempted to get super simple setup for using a Sqlite db file with Serverless on Vercel. The main idea was that I could use Sqlite as a nicer/easier to query JSON with almost no upfront infra for a relatively simple project (https://lemonade.sonnet.io https://lemonade.sonnet.io). It worked quite well! But then I had some issues with accessing fs (it _is_ officially supported for reads). I could read the file from one route, but accessing it from another would result in a file not found error... I pivoted when I managed to get it working... by renaming the db file to db.png and getting its path via `import` in Next. I know there's a simpler way to to that.
- roblh 3y agoI dunno if it’s just me, but every time I’ve used NextJs I’ve run into weird pointy edges like that. It looks so polished from the outside but then starts feeling wonky as soon as I start to use it.
- chrisweekly 3y agoYou're not alone. There's a lot to really like and appreciate, but there's also a lot of opinionated decisions baked in, and almost inevitable fighting against the framework. The good news is, there are good alternatives. Remix is worth a close look; if you accept up front that a js server runtime is required, it's fantastic. Or, there's Vite, which (with vite-plugin-ssr) covers most Next.js use cases with fewer opinions and less framework magic.
- rpastuszak 3y agoI think it's an overall productivity boost and it scales pretty well (have been using it since 2017). I think this is more of a limitation of Vercel/their Next config/adapter (the name escapes me atm). For most of my small/medium projects now I use SvelteKit. I used to think it was a bit too magical because of the new syntax, but I tend to write more vanilla JS/TS with it than I expected. Productivity- and dev experience wise it's quite impressive. My workflow is prototyping/spikes, many wip commits then -> rewrite/sometimes TDD for critical parts. ( More info here: https://sonnet.io/posts/wip/ https://sonnet.io/posts/wip/ and https://sonnet.io/posts/code-sober-debug-drunk/ https://sonnet.io/posts/code-sober-debug-drunk/ ) For large apps I'd still stick with Next, although the refresh times in dev mode are a bit annoying and I don't share th3o's optimism re Vercel having so much control over React.
- Scarblac 3y agoAnother benefit is that the database is just a single file, so it can be used as a format to exchange data, etc.
- rjmunro 3y agoAFAICS, it doesn't "remove" that benefit. It just adds a second way to access your databases. I believe FirebirdSQL has done this for a while - you can either link the whole database library to your code, or with the same API, link a client library that talks to a remote database over the network. It's a great idea because you can link to the embedded version for development and unit testing, but upgrade to a full networked DB server for the live deployment.
- torsy 3y agohas anyone tried connecting turso from psql or tableplus? does it work or does it require a custom deployment?
- luckystarr 3y agoWhat are the supported isolation levels? :)
- m1sta_ 3y agoDoesn't DuckDB do this.
- frankreyes 3y agoSQLite is a Library. Just start a new sqld project that imports SQLite and you're done. It's like Django making a fork of SQLite so they can include SQLite in Django? They're just adding more complexity to a well established project. NIH syndrome to me.
- unilynx 3y agoThe article explains they needed to fork SQLite to be able to intercept the WAL.
- frankreyes 3y agoDoes Django need to intercept the WAL? No. NIH syndrome.
- maxpert 3y agoSo while working on Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) I considered something similar, and the thing that stood obvious with my benchmarks was readers can go really fast if I let them do things in process vs over some protocol (be HTTP or Postgres). I had the same problem with rqlite and dqlite, so I made a very conscious design choice to build Marmot into a side-car and not a layer on top. This idea is so tempting and goes against ethos of SQLite IMO.
- tenken 3y agoDamn my dyslexia I read marmot and thought you meant this unrelated project https://github.com/synopse/mORMot https://github.com/synopse/mORMot
- INTPenis 3y agoHow is this different from using an ORM that supports both dialects using middleware? I mean I know it's clearly different, I'm just saying I'd rather use an ORM that supports both dialects. Seems like less overhead and same basic functionality.
- porsager 3y agoI don't think this should turn in to an ORM or not debate, but there are plenty of reasons, especially for the crowd that would do anything to avoid ORMs. Just try to take a peek into the multitude of "ORMs are bad" articles / discussions. For instance - I would love to be able to use https://github.com/porsager/postgres https://github.com/porsager/postgres with sqlite.
- Jemm 3y agoLinked article has out of date code.
- aaviator42 3y agoSomeone might find this useful, so sharing here, since it's relevant. I wrote a small PHP library that gives you a key-value storage interface to SQlite files: https://github.com/aaviator42/StorX https://github.com/aaviator42/StorX I've been dogfooding for a while by using it in my side projects, and it holds up very well (mostly thanks SQLite's robustness!). And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API https://github.com/aaviator42/StorX-API
- Scene_Cast2 3y agoI ran into something similar recently. I'm writing a new backend app. I was thinking of maybe using SQLite for dev and Postgres for staging & prod (bad idea, I know..) I found that there are just so many differences that supporting both leads to having multiple codepaths. Some examples: - only postgres supports `with conn.cursor as cur`, need `cur.close()` or commit or similar with SQLite - Escaping things for columns and table names: postgres has psycopg2.sql.SQL.format() that only works with postgres connections.
- greggyb 3y agoThe things you have described as differences between the DBs are differences in Python libraries.
- amluto 3y agoThe Python SQL ecosystem is IMO pretty bad, and the root cause seems to be that DB-API (PEP 249) is really quite awful. As my least favorite example, DB-API has two transaction modes, both of which are unnecessarily error prone: autocommit on, and autocommit off. Autocommit on makes transactions useless (intentionally), but it still allows commit() and rollback(), so one can write code that thinks transactions are enabled, have it appear to work, and end up with all manner of corruption. Autocommit off has a bad interface. The begin() function is entirely missing, so you can’t pass it arguments. Worse, since there is no explicit start of a transaction, the library can’t catch misuse of a transaction. As a result, every SQL library for Python of any reasonable quality implements its own special API, and nothing is particularly interoperable. This is all sort of fine if the database is only accessed through a higher level library like SQLAlchemy. But sometimes a developer just wants to execute raw SQL against a database, and the situation in Python is not very nice.
- patmorgan23 3y agoDoesn't SQL alchemy make it pretty easy to use raw SQL?
- 3y ago
- la_fayette 3y agoCan I use this for integration testing in ci/cd without a real postgres instance?
- niels_bom 3y agoIf I read it correctly: maybe. The database underneath is still SQLite and has SQLite data types. If your db schema, app code and the db library you use allow you to switch on the fly, then yes. But those are big ifs.