Migrate database to Postgres

[slvnperron]

SQLite is very nice for beginners because there is nothing to setup and configure. But is has many drawbacks:

• Since it's file-based, it can't be used on PaaS like Heroku where the file system is ephemeral
• It doesn't scale to multiple bot instances
• Not suitable for enterprise usage

We have decided to switch to Postgres because:

• Of it's popularity
• Extensions like JSON/JSONB storage
• Wide availability on Heroku, AWS, Azure
• Good performances / scalability
• Liberal license

This will be available in Botpress@0.1 as it is a breaking change

This is our top priority right now (in order to deploy to Heroku and support enterprise clients), so we'd appreciate any thoughts you may have.

@rocel @DanyFS

[matheusmatos]

We have to think how database must and will be used. Let's look some examples:

First example:

I'm a beginner, and I have created a simple chatbot that responds info about weather using an API. I just need a database for save basic user info. SQLite is easy to start and make tests, however, I wanna make my bot scalable and switch to Postgree in production is the sensible thing to do.

In this case, the ideal scenario is the possibility to use both. It's not possible with Knex?

A solution could be the use of an ORM/ODM instead a SQL query builder, like Waterline (although this guy would not recommend: https://kev.inburke.com/kevin/dont-use-sails-or-waterline/) - But exists other options.

We can discuss more about this possibility?

Second example:

I'm a mobile application with 500.000 active users on Apple Store/Google Play. I wanna create a chatbot for my app, and I'll use Botpress for it. I already have a main database, and i'll storage new data about users in this database. How I would do that? In theory, I have two options:

• Use my own private rest API, writing a private botpress-myapi module.
• Connect with my database server directly, in this case, I can use things like the following:
  • botpress-mongodb
  • botpress-mysql
  • botpress-postgree
  • botpress-cassandra
  • etc.. a lot of data source modules

In this case, the event.db instance will not be used. Is this the idea?

Thirdy example:

I'm the same mobile application with 500.000 active users. I'll storage info about users on my main database, but, data about contexts and sessions will be storaged in a different location, something like Redis or Memcached would be great. What I must do?

The idea is to use a botpress-redis module for it? In this casebot, event.db also will not be used.

---

My main point is: the core database feature is for just simple projects, or would be used with large projects too? :yum:

[slvnperron]

Thanks Matheus! Very clear response and valid points. I'll try to explain my view on this, let me know what you think:

Ex 1

We are already using Knex, but unfortunately we reached the limit of what a generic query builder can do. The problem is with Query Builders and ORMs is that:

• Each databases have special data types, and you can't handle them all using ORMs and QBs. For example you can't do operations on dates with Knex nor with Waterline and even Sequelize.
• If you do slightly more complicated queries than what a traditional CRUD app needs, then you need custom queries (for example look at the botpress-analytics module). In these cases, you would need to write custom queries for every possible databases, which would take forever to create a module and maintain it. Some modules would work with some databases, some would not. To me this is a deal breaker.
• Abstractions by definition means that the features you have is the common determinator of all the supported platforms. You then have no advantage to use other databases as you can't even leverage their strengths (except if you hand-code for every possible databases).

Thus the conclusion that: we need only one database that fits best most scenarios. WordPress chose MySQL. We chose Postgres for different reasons.

Ex 2

That's a scenario that most bots will encounter. I think that you should simply store the info like you would do if it was a regular NodeJS application, i.e. completely ignore botpress if it's not related to bots. It's 100% fine to develop custom logic that is not a module and interacts with your own backend if it's not reusable.

My view on modules is that if it's not reusable as-is by other people, then it shouldn't be a module.

Ex 3

Using Postgres as your main storage engine will work 99.9% of the time. If you want a Redis or a Memcached for caching, that's fine! I think there should be a botpress-caching module for that later if it finds useful. But caching to me is a different than storage and shouldn't be mixed.

---

I think the bottom line is: the built-in database should be used by botpress and the modules for bot specific features, not as an application storage engine. You could use it for your app, but don't consider it as "the only solution".

Does that make sense? 😄

[slvnperron]

It's funny because I just read the article you sent and he says almost exactly the same thing:

Waterline is the ORM that powers Sails. The goal of Waterline is to provide the same query interface for any database that you would like to use. Unfortunately, this means that the supported feature set is the least common denominator of every supported database. We use Postgres, and by default this means we can't get a lot of leverage out of it.

[wac2007]

Why don't keep SQLite as a default on localhost to speed the development and use modules like @matheusmatos asked before?

• botpress-mongo
• botpress-postgres
• botpress-mysql
• botpress-redis

This way we keep the things simple and the developer choose what solution fits their needs.

[slvnperron]

Thanks @wac2007. See my answer regarding multiple data storage engine. Modules need to have a fixed storage engine, otherwise they would need to implement each. It's not as simple as a get/set, botpress modules make complex queries (see botpress-analytics for example) that rely on database-specific syntax and features.

Matheus and I talked about it extensively yesterday and we decided to go for an hybrid: SQLite by default using knex, but some specialized modules (like analytics) will require Postgres.

What do you think?

[matheusmatos]

I understand your points.

My primary vision about that, is that bots doesn't needs a database as primary requirement. Look:

The simple Placebot makes a query on GoogleMaps and return the results as a generic template on Messenger.

My point is: exists bots without database.

It's not like a blog, that need a database. Doesn't exists blog without a database (or a system to persist data).

So, each project must implement your own layer of database using botpress modules or custom implementations.

However, I know that the core of botpress needs to save configs and modules options. Also, some modules need to use it, like your example of botpress-analytics.

In this case, we have to ship a primary database and the Postgres seems the best option.

Let's go! :muscle:

[wac2007]

I think if we are start developing, we want to keep things simple as possible and SQLite is the best option in this case.

We can put plugins who needs complex queries requiring complex databases like postgres. You have botpress-analytics as an example but, if I start to making a bot, i don't need it this time. I can migrate to a postgres later and do some tests with postgres.

PS: I don't know if my english is good enough but I'm trying to do the best I can xD

[slvnperron]

@wac2007 that's our reasoning, makes sense guys. Thanks a lot for your inputs! And your english is 100% clear :D

[rocel]

@slvnperron you wrote "SQLite by default using knex, but some specialized modules (like analytics) will require Postgres."; which means that in a fresh install, you'd have both databases running?

[slvnperron]

@rocel in a fresh install only SQLite, some modules however will require Postgres (hosted somewhere or installed locally). The good news is that I think that I can get analytics to work with both SQLite and Postgres with minimal query rewrite! Looks like there's going to be 0 module/feature impacted by the transition at the moment.

[slvnperron]

That being said I think we should recommend using Postgres for any bot that goes into production / or past initial experimentation phase. Of course one could stay indefinitely with SQLite (like Boost) if there is no need to scale in the near future.

[slvnperron]

Database migration to Postgres

[wac2007]

This card isn't public. (at least for me)

[slvnperron]

@wac2007 sorry, I referenced this card on our internal trello board 😆

This is almost done! :)

[slvnperron]

Done in the sp-postgres branches in the core and all the modules.

Closing this 💃