Add support for sqlite as DB

[brlbil]

I would be a great addition to support sqlite as an alternative DB.

[rklos]

+1 for this idea. I have only a one small board, so the whole fully functional RDBMS with network support, replication and multiple access is quite too much.

[nickbe]

Despite the fact that this might be useful for some I don't think it makes any sense here. Planka's is constructed as a team oriented application. Even though it can be used just for yourself the current database is much more reliable at the least.

Most web applications already have a problem not relying on the real abilities of databases and the current trend to support multiple datbabases makes it almost impossible to even scratch the surface of what a database could do. Sqlite as cute but it's not even close to what postgres can really do. So I would rather support using the database capabilites much more extensive than moving further away from this.

[rklos]

@nickbe so that’s why developers use ORMs (in this case knex). You as a Developer don’t care about the database you’re currently using, because responsibility is on the ORM. Adding a support for Sqlite won’t be hard because Knex supports it out of the box. Also regarding your first argument - Sqlite is very reliable database (a lot of developers still thinks that it’s only for development or testing purposes, but it can be used on production with sucess).

[meltyshev]

Problem to add support for other databases in id generation function, also maybe some data types will be incompatible (need to test). Also there are 2 raw queries in server/db/migrations/20180721220409_create_user_account_table.js. If someone does PR, of course we'll add support for SQLite, but for now other tasks are the priority.

[nickbe]

@nickbe so that’s why developers use ORMs (in this case knex). You as a Developer don’t care about the database you’re currently using, because responsibility is on the ORM. Adding a support for Sqlite won’t be hard because Knex supports it out of the box. Also regarding your first argument - Sqlite is very reliable database (a lot of developers still thinks that it’s only for development or testing purposes, but it can be used on production with sucess).

It's about the fact that people should put more effort into using the native features of specific databases instead of trying to satisfy the bare minimum capabilities of many. Databases like Postgres are very powerful, but only if you concentrate on using them beyond the limits of typical frameworks - FAR beyond these limits. In terms of databases performance most web systems behave like toys because programmers don't even know about these details anymore - let alone how to move code into the database instead of away from it.

As for the reliabilty of sqlite - it's not capable of handling stored scripts, large stored queries (not 3 tables kid stuff) or large number of concurrent cursors. For small projects and few users it CAN be used, but if your ambitions go beyond that it can't even begin to compete with a server side database. So no, I think sqlite especially for this kind of system would be bad choice.

[TreeOfSelf]

+1 For this, all the arguments that SQLite being a "toy" and not capable seem completely incorrect and misguided (minus the possible issue of data incompatibility and raw queries). A Kanban board is not going to bring SQLite to its knees. Far more complex and involved projects run on SQLLite, just because it's a file based database doesn't mean it isn't powerful, common misconception. It has a lot of pros over a traditional database. The biggest one for this project being, if you don't already have a Postgres server, you would have to install it to use this application. Which I probably am not the only one who isn't willing to do that. By allowing SQLite you would have the option of just dropping it in to install.

Additionally, the argument SQLite wouldn't work because it "doesn't handle concurrent cursors" makes absolutely no sense when the entire application and database connection is ran from a single node.js server. Your Kanban board isn't going to be receiving four million users a simultaneously, SQLite would be perfect. It's absolutely doable and is worth the effort to not make people setup Postgres for just one simple board.

[zahidhanif]

+1 adding my support for this. The biggest negative is concurrency but that's not really an issue for many self-hosted apps, easpcially ones that are used in small teams or for simple use cases. Many of the projects I self-host use a SQLite back end and they all work fine without have to run a full blown RDBMS. For example my Plex SQLite database is hundreds of MB. It makes the backup and portability of the docker apps much easier too.

[christophenne]

So would contributions be accepted on this, or is it a clear no?

I use planka only for personal stuff, I am the single user and I run it locally on my machine. Running a postgres that does almost nothing all day long is really no problem, but if I don't have to do it, I would prefer not to. sqlite would really be a huge win for this kind of use case – granted, it's a special one, but still a valid one. If it's not worth it for the core maintainers (which I understand – clearly no business case for this), it might still be an interesting task for contributors. Maybe at least checking if it could be done without disrupting half of the code base – in such case I would also understand if it does not get supported.

[meltyshev]

The problem with adding SQLite (or any other database) is that, with the current server-side tech stack and structure, it will become very challenging to maintain. The current ORM (Waterline) has very limited features, and to implement search for cards in the archive/trash (in v2), I had to write a raw query. Imagine doing that for each dialect and then changing adapters to ensure everything works the same way.

The only solution I can see to do this properly is to put all query methods into separate "classes", load them in a hook, and use only these methods in the code. To support another database, you'd just need to create a new hook and override all the query methods. However, in this case, migrations would also need to be separated, as the types and migration logic could differ per database (which also adds complexity to maintenance since you'd have to write two migrations instead of just one).

This task is not as simple as it seems, but it's certainly solvable, and we've already started moving query methods into a separate hook in v2. The best approach might be to wait for the release, after which anyone can copy the hook and try creating a new adapter in a separate repo. From there, we can figure out how to make it easy to install.

[christophenne]

@meltyshev I see, thanks. I was not aware or forgot that there is a v2 in progress, but it absolutely makes sense to wait for that to be stable and have a look then.