Skip to content

Moving to SQL backend

Hi,

There was a discussion on IRC a while ago about moving to a standard SQL backend, which should be done before #433. I could not find an opened issue for this, so here it is :)

If we use a good ORM, I don't think the migration will be really difficult and painful. Questions are:

  • Which ORM?
  • How to handle migration?

Which ORM should we use?

Having a look at ORMs in the NodeJS ecosystem, I found Sequelize, Bookshelf and ObjectionJS.

Sequelize is known to be a bit bloated and have poor docs, apparently. Bookshelf seems to be the de facto standard, has a nice API to defined models and a SQL query builder. ObjectionJS (formerly MoronJS) seems quite close to Bookshelf.

How to handle migration?

All these ORMs support SQLite / MySQL / MariaDB / PostgreSQL. Provided that we don't write fancy code, it should be easy to use whatever db mechanism we want.

I am not sure we need much more than a SQLite backend, so I guess we could just migrate the database to a SQLite database, and let the user configure a more fancy SQL backend afterwards. This would also ease a lot the installation process.

Not sure if this migration should be done automatically? Maybe it would be better to ask users to export data and then reimport after the upgrade? Not sure also about the timings, when should SQL backend land in master branch? (depends also on the date of releases for next versions and end of Cozy support).