SQLite Integration

[stevefan1999-personal]

I have a database plugin for sale, powered by TypeORM.

I have also made sure it could work with most of the database out of the box that supports multiple database backend, including MySQL, Postgres, SQLite, SQL Server and of course SQLite. But to install it there might be some patches needed. I'm still coming up with some idea of how to run the integration test in NodeBB main DB test suites. Once I got 100% conformance on all supported databases mentioned, this will be on https://privjs.com/ soon.

[julian]

@stevefan1999-personal That's very very cool I'm sure there are many out there who would want to have NodeBB run on MySQL

[gotwf]

@julian said in SQLite Integration:

@stevefan1999-personal That's very very cool I'm sure there are many out there who would want to have NodeBB run on MySQL

Uh, surely you mean MariaDB? Or maybe Percona?

Call me a rebel if you will but I eschew products that sold out to the dark side. Especially anything Larry has his hands in.

Side Bar: For those into MySQL'esque rdbms, Percona XtraDB Cluster is pretty sweet.

[julian]

@gotwf Yeah MariaDB. Whenever I need MySQL, I end up using that drop-in replacement

[gotwf]

@julian Heh, yes, I guess the term "MySQL" has become generic in same sense as "Kleenex".

[chung-leong]

I've just finished writing code that enables the use of SQlite3 with NodeBB. It passes the test suite and seems to work pretty well. Made the necessary change to nodebb-plugin-dbsearch too. Will a pull request be considered or is SQLite support too obscure?

[julian]

@chung-leong not obscure at all, we'd be happy to take a look

[phenomlab]

@julian Would MySQL support be available ?

[julian]

Well, SQlite and MySQL are similar in many ways... so theoretically, yes.

The problem isn't whether it is possible but whether the implementation is performant. We've built NodeBB around the concept of NoSQL data structures, specifically, those found in Redis (e.g. hashes, sorted sets, and to a much lesser degree, simple sets).

SQL can mimic these structures, yes. However the power of a relational database is that the schema is defined beforehand, and indexed for speed. NoSQL kind of threw that out the window, and if someone came up to us and asked us for a database schema, we'd just throw up our hands and

So whatever SQL implementation is added, it needs to take into account our need to be able to create sorted sets on-the-fly.

What that looks like in reality (and what the pgsql driver does)... is use god tables; one giant table for every sorted set in NodeBB. That is technically an anti-pattern, but it does work.

[chung-leong]

Okay, I'll send a pull request.

Adding MySQL support would be pretty tricky, I think. To emulate certain operations on sorted sets, I ended up just pulling the fully data set and processing it in JavaScript. Since SQLite is in-process, that's not a huge issue. You can't really do the same with a real RDBMS. The expectation level is different too. If you're choosing SQLite as a storage mechanism, you know you're sacrificing scalability for the sake of a reduction in hardware requirement.

[julian]

@chung-leong said in SQLite Integration:

To emulate certain operations on sorted sets, I ended up just pulling the fully data set and processing it in JavaScript.

Yes, this is what I was afraid of... there are certain concessions that need to be made for an SQL driver when you build your software around the expectation of a schema-less data store

Nevertheless, I am looking forward to reviewing.