Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite Is Serverless

sqlite.org

Post reply on HN
Print view View original

191–200 of 453 posts

Re: SQLite Is Serverless

#191

Post by at_a_remove »

nicholaides wrote: post #185

I think a good under-appreciated use case for SQLite is as a build artifact of ETL processes/build processes/data pipelines. Seems like lot of people's default, understandably, is to use JSON as the output and intermediate results, but if you use SQLite, you'd have all the benefits of SQL (indexes, joins, grouping, ordering, querying logic, and random access) and many of the benefits of JSON files (SQLite DBs are jus…

Using SQLite in my ETL processes is something I have done for over a decade. It's just so convenient and, at the end, I have this file that can be examined and queried to see where something might have gone wrong. All of my "temporary" tables are right there for me to look at. It is wonderful!

Re: SQLite Is Serverless

#192

Post by tn1 »

pm90 wrote: post #166

What I really want: SQLite storage backend driver for s3/gcs. No need for disks then. I haven’t been able to find such a solution though; and am not technically proficient enough in C (the Lang SQLite is written in) to do so myself.

The challenge with this is that S3 and friends are object stores, meaning you upload or download the whole file each time. As you can imagine, this will cost you tremendous bandwidth even to insert one row.

Furthermore, it doesn't solve the multiple writers problem, because (afaik) there's no way to lock a file on S3.

Re: SQLite Is Serverless

#193

Post by travbrack »

bane wrote: post #149

I love SQLite, but people approach it from a classic RDBMS angle which confuses them. Here's the deal: SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file. That's it. Stop overthinking it. Can you write a microservice that stores its data in a big JSON file that you've built some code around to read/write to? Yes. It's just a file, but you have to build all the read/w…

I've seen several software projects that are built for rdbms let you use sqlite. It works.

Re: SQLite Is Serverless

#194

Post by bane »

bshipp wrote: post #180
bane wrote: post #174

Earlier quoted context omitted.

> The main argument I hear for why Sqlite deserves second class status in the DB marketplace is the difficulty in handling multiple writes simultaneously. SQLite is not in the DB marketplace. It's in the file format marketplace. It handles multiple writes in exactly the same way CSV handles multiple writes. If you want to handle multiple writes with SQLite, you handle it the same way as CSV.

I disagree. CSVs don't have write-ahead logging and are not able to selectively lock portions file for writes. And I'd say that not only is sqlite in the db marketplace (albeit for a specific subset of database application types) it's one of the largest players.

Why not? Just roll your own write-ahead log when you are writing to CSVs! That's all the SQLite code is doing.

SQLite does not selectively lock portions of the file for writes. It locks the entire file using the Operating System's own file handling services.

SQLite is a file format. CSVs, XML and JSON are also huge in the dB marketplace. That doesn't make them RDBMSs.

Re: SQLite Is Serverless

#195

Post by catalogia »

bane wrote: post #188
derefr wrote: post #172

Earlier quoted context omitted.

Consider: it’s totally possible to strip down Postgres until all you have left is an embedded RDBMS of the style of SQLite. (I’m not sure why nobody has done this yet, actually.) Would you call the result “just a file format”? Such an instance of “embedded Postgres” would still have a huge sprawling catalog (PG_DATA) directory attached to each use of it, so it wouldn’t be contained to a single file. But neither is SQ…

SQLite is a file format with a familiar API and uses SQL as the logic for searching/adding data to the file. Approach it exactly the same way you'd approach using a CSV file and all the confusion and overthinking about it goes away. Approach it as a stripped down RDBMS and you end up with all kinds of questions about support for this or that RDBMS familiar service. You can write your own SQLite file reader/writer. He…

> No! SQLite is not an embedded RDBMS. It's a file format.

You keep saying it's a file format, but it's quite possible to use sqlite without persisting anything to a file at all.

    rc = sqlite3_open(":memory:", &db);

Re: SQLite Is Serverless

#196

Post by cle »

> It is important to understand these two different definitions for "serverless". When a database claims to be "serverless", be sure to discern whether they mean "classic serverless" or "neo-serverless".

It's really not important to understand that distinction, because this author seems to be the only one making it. Everyone knows what "serverless" means at this point, and it's not an embedded DB.

Re: SQLite Is Serverless

#197

Post by notduncansmith »

wpietri wrote: post #148
geokon wrote: post #24

a bit tangential, but when do you move form using in-application data structures (maps, trees, vector/arrays) to using a database? Is it basically when the data doesn't fit in memory? I've been programming for almost a decade and I've never come across needing a database... (for context, it's ten years without anything web related) I'm interested in them and I'd love to learn SQL but I can't even think of a use case…

I'm sure people will give you the orthodox answer, so let me give you mine: when other things become more important than performance, ease of development, and code clarity. Many years ago I happened to meet the creator of Prevayler, an open-source persistence framework that provided ACID guarantees and was thousands of times faster than a database as long as your data fit in RAM. I tried it out for a project and we l…

This is exactly the style in which I build applications these days. Most of the time I just describe it as “hella caching” but it is a different paradigm from treating the database as the primary state engine. The speed and simplicity of working on in-memory structures are great. When you outgrow a single server’s RAM capacity, you can use Kafka or another durable message queue as your application’s WAL and shard your data across multiple servers.

Re: SQLite Is Serverless

#198

Post by bane »

nickthemagicman wrote: post #187
bane wrote: post #149

I love SQLite, but people approach it from a classic RDBMS angle which confuses them. Here's the deal: SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file. That's it. Stop overthinking it. Can you write a microservice that stores its data in a big JSON file that you've built some code around to read/write to? Yes. It's just a file, but you have to build all the read/w…

If there's no Inter Process Communications and it doesn't use the OS to write to the filesystem...how does an application write to SQLlite db? I'm a noob and just curious.

It uses the OS to write to the filesystem. It just does some clever management of data needing to be written. You can build your own if you are clever enough to read write to your own file format too.

If you want to build an RDBMS using SQLite as the core, you can. You can also do it using uncompressed WAV audio files if you are clever and hate yourself enough.

To use SQLite in such a scenario you simply have to write the entire RDBMS minus the file handling routines. This includes a connection pooling mechanism and a single process to isolate the connection to the SQLite file so that the OS doesn't get angry when you try to have multiple things writing to it.

Re: SQLite Is Serverless

#199

Post by logicallee »

aikah wrote: post #146
logicallee wrote: post #145

Earlier quoted context omitted.

Any protocol. People are saying this is a common solution. So is there a standard dummy tool like that?

> So is there a standard dummy tool like that? My point is that by that time SQlite doesn't fit "Serveless" as defined by the linked article anymore.

True

Re: SQLite Is Serverless

#200

Post by bane »

lazyant wrote: post #183
bane wrote: post #149

I love SQLite, but people approach it from a classic RDBMS angle which confuses them. Here's the deal: SQLite is a file format with a nice API that uses SQL as the paradigm for reading/writing to the file. That's it. Stop overthinking it. Can you write a microservice that stores its data in a big JSON file that you've built some code around to read/write to? Yes. It's just a file, but you have to build all the read/w…

Sure. Or a light RDBMS where the main not supported case is concurrent writes.

No, SQLite provides no RDBMS functionality. It is not an RDBMS. It is a file format.

Saying that a program that opens and reads/writes a file through a file format API is a light RDBMS turns almost every program in history into an RDBMS.

If SQLite didn't force you to use SQL as the read/write logic, absolutely nobody would confuse it for an RDBMS. That's because it's a file format.

Post reply on HN