Bookstack: Postgres support

[partoneoftwo]

I think you should add support for, or change the database layer to Postgresql for future proofing and ease of developing new exciting features.

http://www.craigkerstiens.com/2012/04/30/why-postgres/

https://blog.lateral.io/2015/05/full-text-search-in-milliseconds-with-postgresql/

https://www.quora.com/What-are-pros-and-cons-of-PostgreSQL-and-MySQL

https://www.digitalocean.com/community/tutorials/sqlite-vs-mysql-vs-postgresql-a-comparison-of-relational-database-management-systems

http://www.craigkerstiens.com/2012/04/30/why-postgres/

How about 2226 interesting posts about how great Postgres is: https://hn.algolia.com/?query=postgres&sort=byDate&prefix&page=1&dateRange=all&type=story

When I switched the data layer from Mysql to Postgresql on my own project, I could immediately begin coding new exciting features into the app. These were great because I didn't have to use libraries or hardcore anything, just simply made calls to the database layer. Stuff like awesome searching capability and many other great features.

[ssddanbrown]

Hi @thomasfrivold, Thanks for the suggestion. This would be a fairly large change to require people to use PostgreSQL. MySQL is still very popular and is really simple for people to install and use as many are already familiar with it due to it's heavy usage in open source projects such as Wordpress and others. I also like the fact BookStack can currently be used with either MySQL or MariaDB. I would be happy to look into switching it to PostgreSQL but I feel there would really need to be a reason to switch things up.

Is there anything that you can think of that PostgreSQL could specifically bring to BookStack?

[tpetrauskas]

In Laravel documentation page there is a statement that it supports:

• MySQL
• Postgres
• SQLite
• SQL Server

Would it be possible to choose between those in setup?

[ssddanbrown]

@tpetrauskas Yeah, Thanks to Laravel, supporting PostgreSQL would be really simple for most of the database work. We would need add code to provide separate support for setting up full-text searching and it would be an extra system to test on. The current search indexes & Logic is focused on MySQL and I believe PostgreSQL uses a different syntax for full-text queries.

[partoneoftwo]

Full text searches are really awesomely implemented in Postgres.

Check out these resources:

• https://hn.algolia.com/?query=full%20text%20postgres&sort=byPopularity&prefix&page=0&dateRange=all&type=story

• http://rachbelaid.com/postgres-full-text-search-is-good-enough/

• https://lo.calho.st/databases/an-idiots-guide-to-fulltext-search-in-postgresql/

[simhnna]

I'm also interested in Postgres support.

From the looks of it, either the fulltext search for mysql is not good enough, or it's not used right.

In postgres fulltext search strips words in a language specific way, so search uses the stem of a word. This ensures that searching for "s" will also get results that contain the german "ß". It also strips common filler words, like "a", "and", ...

I'm not sure if mysql can be configured to do that, but postgres does that more or less by default.

As for ease of deployment:
Postgres comes with sane defaults, while mysql needs configuring to get there...

[ghost]

Is this still on the table as an upcoming feature?

[lommes]

Since the search relies on mysql this isn't that easy.

The best solution (in my opinion) would be, to switch the search to scout with a basic provider like tntsearch shipping with bookstack. This would decouple the search from the used database and open up for using any database server you want.

Additionally, with the correct configuration, it would be possible to use different search providers like elasticsearch or algolia and so on.

So I guess it is still on the list but rather waiting for a generous PR, since it is not crucial. (see https://github.com/BookStackApp/BookStack/issues/674#issuecomment-361419997 and the following comment by @ssddanbrown)

[ssddanbrown]

Thought I'd weigh in on the above, Especially since my last comment on this is fairly old now.

As of v0.16 I re-wrote the search system to not rely on MySQL full text indexes and now a custom indexing system is used which basically just splits the words and scores them in their own indexed table (search_terms).

Now that we don't rely on any MySQL specific features it does make it easier to connect to other databases. There are still a few areas which use Raw DB queries simply because the complexity is hard to convert to fluent syntax. I had a really quick go at attempting SQLite support recently but came across some issues with the migrations.

Honestly, I don't think adding Postgres/Sqllite support would be too difficult, Maybe just a day of sitting down and working things out. I'm more worried about the added support complexity going forward.

Maybe if we add Postgres/Sqllite support but make it clear that using those may be more unstable/less supported.

Just to answer your question @evitalis, Yes, Still on the table but not urgent as not too highly request and not a massive value add upon what already exists.

I will swap this from 'Open to discussion' to 'Enhancement' now that we don't rely on MySQL full-text indexes.

[kimreyio]

I'd be willing to put in $$$ if there is a bounty on the addition of SQLite.

[yebyen]

For whatever it is worth, I'm sure there are some valuable features in postgres like WAL-e and WAL-g cloud storage Write-Ahead backups.

I started shaving this yak when I wanted to back up the content of my bookstack deployment which uses the Helm chart, that includes MariaDB as a dependency. I had a bit of a scare because the default behavior of the database chart is to generate some passwords and store them in a secret, and there's a bug that causes the secret to be overwritten sometimes, rendering the database inaccessible because the content has been stored with a password in the PVC.

I was able to resolve that by resetting the password using an init container and custom my.cnf temporarily disabling authorization so I could set any password with kubectl exec. Anyway, that got me thinking about other things that could go wrong, and I went looking for backup config in the bitnami chart, didn't find any options that looked quite as nice as my WAL config through postgres.

Are any of you using Kubernetes to host bookstack, and if so do you use on-cluster or off-cluster databases? And in any case how do you keep them backed up?

I understand how to take a backup just one time with mysqldump, but it seems like it would be much better to have it on a fixed schedule ideally recurring automatically.

I don't think my cloud provider does automated MySQL yet, so I'm on my own without some kind of support for backups. (This is my argument in favor of postgres, since it's much more broadly supported across cloud providers, and quite easier to keep perfectly backed up IMHO)

[ghost]

@ssddanbrown Just curious on the status of this. It has been over a year and multiple versions have released since this got switched from open for discussion. I know at least in my case I have no desire to deploy or deal with MySQL but am interested in this application. From discussion in this thread others are also interested, and as noted in #1442 GitHub may be a barrier of entry for some people to give additional input on this.

[ssddanbrown]

Hello @evitalis,

This has not really moved to be honest. I think I started looking to support SQLite at some point but came across a bunch of extra edge-cases then I got distracted so it just kind of dropped off.

Being totally open, Over the last year I've become more tired of supporting what I see as horizontal growth of the project (Supporting extra options and variants of what exist) rather than upwards growth (Supporting new features and improvements that benefit existing users).

I understand that this kind of thing would open the project to a bunch of new users, and that's fantastic, especially if those users are eager to use BookStack, but at the end of the day it would add additional maintenance and support effort and this is not a paid-for project where we're trying to get new customers, We're just trying to build a good documentation platform for certain use-cases.

It's kind of ironic that with open source projects, it feels so viable to broaden support and options since the code is just sitting there, out in the open, ready to be changed. Even more so than with close source projects, whereas the closed source options often have more resources to apply.

I mentioned in my last comment that we could add support for extra DBs but "make it clear that using those may be more unstable/less supported". Looking back at that, I think "_Would I be happy mentioning them but not fully testing on them? No, I'll still need to test on all DBs when changes are made_" and "_Will I be anxious at work and feel bad if I see people report migration errors for these DBs, after a release? Of course_".

I know that money has been offered above, and that's very generous, but I'm still uncomfortable with having money become a variable in the project and, while that may help implementation, it's the ongoing support & costs (Effort, not monetary) I'm more concerned with.

Don't think I'm discounting this though. This is still something I'd like to see, I'm just not sure we could support it to the level I'd want to right now. In regards to relative interest, This issue is in about 35th place in terms of upvoted requests.

Hope you have a wonderful evening,
Dan

[ghost]

Thank you for the feedback but if laravel already supports this what additional code may be needed to get it working? I agree that sometimes horizontal work doesn't feel as fulfilling, but as an open source dev myself I can tell you it still has benefit.

I won't be able to deploy this in my environments while there is no PSQL support but I will certainly keep an eye on this for if it ever gets implemented.

[kimreyio]

Recommend closing this issue and #367

Bounty has been declined and the added compatibility/db migration and regression testing wouldn't even be welcomed with a PR, per Dan's clarification above. I very much appreciate the sensible and tactful response.

[kingdonb]

I didn't hear anyone say a PR wouldn't be accepted, but it's not the end of the world if this issue closes without a fix. Thanks for your thoughts.

[ssddanbrown]

I'll leave this open, so that it can easily be found by others, and it would be something I'd like to support, just maybe much further in the future. A PR would probably only be accepted if provided by a longer-term contributor that is familiar with the DB being implemented as was committed to being able to provide some level of support going forward.

Thanks everyone for your understanding.

[lpar]

I don't think my cloud provider does automated MySQL yet

Same problem here. The reliance on MariaDB is really making it a pain to deploy.

[theAkito]

I have read all the comments on this issue. I understand both sides' arguments, however I'm still for adding PostgreSQL support. It seems like everyone shared a bit of their experience, so I will try to share my practical experience, as well.
Recently, I finally set up my own Bookstack for my personal documentation, etc. I am really satisfied with how the software works and how great it is, so I decided to use it as our new documentation centre at our enterprise, as well.
Now the thing in my case is

1. usually you use at least PostgreSQL in an enterprise.
2. I agree with the author's examples and resources that explain the advantages of PostgreSQL of other SQL-based databases.
3. People working with databases professionally probably are more familiar with PostgreSQL. For example, for me it would be harder to manage MySQL/MariaDB than PostgreSQL.
4. Taking your quote @ssddanbrown

Being totally open, Over the last year I've become more tired of supporting what I see as horizontal growth of the project (Supporting extra options and variants of what exist) rather than upwards growth (Supporting new features and improvements that benefit existing users).

I can respond from my personal perspective, that adding PostgreSQL as an option would definitely benefit me as an existing user. 😄

1. I may sound like a heretic to a lot of existing Bookstack users, but if there are only a few options that are realistic and maintainable, I would even accept and welcome dropping official support for MySQL/MariaDB and replacing it with official support for PostgreSQL. (I personally don't see a point in using any other SQL-based database, anyway...)

Now let's be honest: MySQL is known for its supposed "simplicity". However, that is not a strong argument for how beneficial software is, especially in a high performance related area i.e. databases.
So I think it would be fine dropping a bit of "simplicity" (again, for others and me MySQL would actually even be more difficult) for a plain better solution (in terms of options available in the SQL world).

P.S.: I use PostgreSQL for all my private servers, if possible. I have never seen a reason to use anything below it, except for super super small amounts of data, but that is a thing for SQLite, not MySQL.

---

That said, the following is directed at @yebyen.

Are any of you using Kubernetes to host bookstack, and if so do you use on-cluster or off-cluster databases? And in any case how do you keep them backed up?

I will be setting it up in a Kubernetes environment. How did you set up your instance, after all?
Regarding your question, I will use an on-cluster database.

[kingdonb]

@theAkito I have been using kubedb, which for as far as on-cluster database solutions go, makes administering an MySQL or MariaDB cluster or machine about as easy as administering a PostgreSQL cluster... that is to say, you write a couple of CRDs and it is handled for you. I like this better than the built-in Bitnami mariadb that is included in the bookstack stable chart.

I will say also that since I began to follow this issue, DigitalOcean has since added support for MySQL managed instances, I would probably still go with an on-cluster database because it simplifies cost and performance management to have everything in the same interface, but I would rather go for something like KubeDB than a one-off volume managed database. I have more options for Postgres hosting than MySQL, but I can see the value of keeping things the same from a maintainer POV. I would not be as likely to adapt to a change that eschews MySQL entirely for PostgreSQL. Making such a change seems likely to create mistrust from users who were not planning on changing or being forced to change from one database format to another.

The most ideal solution IMHO would be adopting an ORM layer, if one supporting both MySQL and PostgreSQL was available. I don't know enough about PHP as a Rails dev, for us this is generally no-brainer, so long as you stay within the bounds of the safety net that the ORM provides for you. Having no frame of reference for what that picture is like in PHP land, I can't offer guidance on how to resolve this issue permanently, unfortunately. But I can understand too the perspective that all complexity that an ORM with driver architecture adds is unwanted, especially around database features like transactions and maybe something like composite keys that might behave differently from one driver to the next. It seems like the more databases are supported by an ORM, the more potential complexity it adds to the picture, (but supporting multiple database formats without any ORM sounds like a non-starter, from my perspective.)

One way to emphasize any limitations to the support of various databases in an ORM layer would be to add a test suite, that provides coverage only for the database formats that are officially supported. That way even if a database ORM provides support for three or four different databases, a contributor can show that only one or two of them are covered by tests. This strategy has the added advantage that community contributions can be accepted (if your database format is not supported by the project as of yet but happens to work just fine, add your own tests for that format in a PR. Congrats, now you're the maintainer of those tests!)

I think it's hard to strike a balance between "PostgreSQL is the de-facto winner for enterprise databases" and "MySQL/MariaDB is the de-facto winner for hobby projects" or however you see the properties and favorable characteristics of these databases. I love following this project, and I'm glad that it has included support for Kubernetes hosting. If the people maintaining are not interested in using an ORM or switching databases or database access strategies, I won't begrudge it myself. Open source efforts being on a volunteer-only basis, as it was in any case!