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

SQLite is not a toy database

antonz.org

Post reply on HN
Print view View original

321–330 of 364 posts

Re: SQLite is not a toy database

#321

Post by trissylegs »

mikedilger wrote: post #318
haberman wrote: post #178

Earlier quoted context omitted.

> SQLite doesn't really enforce column types[0], the choice is really puzzling to me. This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility: > Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more…

Maybe there could be a fork called SQLITE, an acronym for SQLite Including Type Enforcement

I think that would win the prize for most ungooglable name. (As Google is not very cases sensitive)

Re: SQLite is not a toy database

#322

Post by edwinyzh »

xnx wrote: post #291
petercooper wrote: post #30

With no sense of overstatement here, SQLite is one of my favorite creations in the entire world, so I have a bunch of links some of you might find interesting if you want to dig further: https://github.com/sql-js/sql.js - SQL.js lets you run SQLite within a Web page as it's just SQLite compiled to JS with Emscripten. https://litestream.io/blog/why-i-built-litestream/ - Litestream is a SQLite-powered streaming replica…

It's truly a shame that Web SQL was opposed by Mozilla. The web would be a better place if a tool as powerful as SQLite was available by default on billions of devices.

Can you share more details? Why Mozilla opposed SQLite?

Re: SQLite is not a toy database

#323

Post by dmitrykoval »

tgb wrote: post #7

Does anyone use SQLite as their daily-driver in lieu of R or pandas for data analysis? I don't think I can use the sqlite command-line since I'd want a fully-developed plotting utility, and it seems less convenient to do the actual analysis part through a sqlite connection in python, say.

That's one of the core use cases for Vinum - to provide SQL query engine, complementing Pandas in data analysis (thanks to Arrow), yet retain an ability to use native python and numpy funcs as UDFs. Also, it doesn't require input dataset to fit into memory.

https://github.com/dmitrykoval/vinum

Re: SQLite is not a toy database

#324

Post by edwinyzh »

doomleika wrote: post #148

Two major gripe I had with SQlite 1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution. 2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solutio…

I love that I don't have to define a length for the 'varchar' columns and I can store strings of any length to those columns ;)

Re: SQLite is not a toy database

#325

Post by edwinyzh »

bob1029 wrote: post #4

Don't forget about User-Defined Functions. https://www.sqlite.org/appfunc.html We just started enhancing our SQL dialect with new functions which are implemented in C# code. One of them is an aggregate and it is really incredible to see how it simplifies projections involving multiple rows. One huge benefit of SQLite's idea of UDFs is that you can actually set breakpoints and debug them as SQL is executing.

Yes, that's a very useful feature to extend the functionalities of SQLite. mORMot (https://github.com/synopse/mORMot) added JSON support to SQLite even before JSON1 was introduced.

Re: SQLite is not a toy database

#326

Post by edwinyzh »

bob1029 wrote: post #177
Thaxll wrote: post #173

Earlier quoted context omitted.

"The trick for extracting performance from SQLite is to use a single connection object for all operations, and to serialize transactions using your application's logic rather than depending on the database to do this for you." It's not the right approach because it's hard to get right, you want to offload that to the DB.

It's actually not that hard to get right. You can put a simple lock statement around a SQLiteConnection instance to achieve the same effect with 100% reliability, but with dramatically lower latency than what hosted SQL offerings can provide. Also, the only reason we ever want to lock a SQLiteConnection is to obtain a consistent LastInsertRowId. With the latest changes to SQLite, we don't even have to do this anymore…

Yes, this is exactly how mORMot (https://github.com/synopse/mORMot) implements the DB server with SQLite.

Re: SQLite is not a toy database

#327

Post by bionoid »

jyrkesh wrote: post #217
ben509 wrote: post #16

> There is a popular opinion among developers that SQLite is not suitable for the web, because it doesn’t support concurrent access. No, the issue is it doesn't have high availability features: failover, snapshots, concurrent backups, etc. (Edit: oops, comment pointed out it does have concurrent backups.) SQLite isn't a toy DBMS, it's an extremely capable embedded DBMS. An embedded DBMS is geared towards serving a si…

I learned this the hard way when I stuck some app containers down on a few RPis and mapped the folder of stateful stuff (including a SQLite database) to an NFS share on my NAS. It's....not great.

The FAQ suggests that NFS is problematic for concurrent access:

> But use caution: this locking mechanism might not work correctly if the database file is kept on an NFS filesystem. This is because fcntl() file locking is broken on many NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time.

https://www.sqlite.org/faq.html#q5

Re: SQLite is not a toy database

#328

Post by leijurv »

eleog wrote: post #201

Sqlite is not a toy, but it's so sloppy that it's not really a tool either. Like a saw with a loose blade, it can help you cut but it can also hurt you. My pet peave is the way sqlite accepts non-aggregated columns in a GROUP BY query. You'll get a result in the column, but it's not clear what it means. More of sqlite's sloppyness is detailed here https://sqlite.org/src/wiki?name=StrictMode

> My pet peave is the way sqlite accepts non-aggregated columns in a GROUP BY query.

Funnily enough, this is my favorite feature of sqlite that I wish other RDBMSs had :))

There is no easy equivalent to `SELECT order_id, status AS latest_status, MAX(updated_at) AS updated_at FROM orders GROUP BY order_id`. Well, it's possible, but for example in Postgres you need some silliness with nested queries, `PARTITION BY`, and `ROW_NUMBER()`.

Re: SQLite is not a toy database

#329

Post by 02020202 »

i am fond of kvdb, if you can work with ids or single indices. i would love to use sqlite but it has no native Go implementation. i even made simple layer on top of kvdb that supports filtering on multiple indices and sorting. but native sql(ite) would mean less work :)

Re: SQLite is not a toy database

#330

Post by ivanhoe »

crazygringo wrote: post #146
nalgeon wrote: post #51

Earlier quoted context omitted.

I think that the absence of 'high availability' is not an issue for small websites or web apps. Transactions are ACID, concurrent readers are fully supported. Backups and administrative tasks are super-easy.

So if you're saying everything does support a web database... then what's the reason people aren't using it for websites? Why do you say it works for "small" websites but presumably not large ones? If it's not transactions, concurrent reading, backups, or administrative tasks... then what's the issue you run into? Genuinely curious... I'm wondering if everything I've heard about "don't use SQLite for websites" is wro…

Sqlite is catching up quickly with SQL support, but like with any fast moving technology - unless they're really interested in it - most of devs are lagging few years behind with their knowledge on what can be done with it.

For instance I remember that at some point sqlite didn't have foreign keys, so I couldn't run my existing migrations on it (without rewriting everything) which was a big issue for me at the time. Now I see that they've added the support for referential integrity in the meanwhile, but I had no idea about it because simply there's so many other libs and technologies to follow - one just can't keep track of every single tool in the world obviously - so some great tools just fall out of focus.

My guess is the word will slowly get out and in a few years people will probably shift to using it more in a web world - but it will take some time.

Post reply on HN