Using other SQL databases

[saidimu]

Does openbox use any MySQL-specific features?

I'm curious about the possibility of alternatives like MariaDB or PostgreSQL.

[jmiranda]

I don't recall using any MySQL-specific features, but I can check back through the code at some point. There are a few Liquibase change sets that are MySQL-specific (see below for an example) but those change sets should be ignored when run on other databases.

https://github.com/openboxes/openboxes/blob/master/grails-app/migrations/0.5.x/alter-table-engine-innodb.xml

I use MariaDB on my development laptop and haven't had any issues. I have never tried to install on PostgreSQL, but it's theoretically possible since Liquibase supports PostgreSQL 8.2 (and above) out of the box (http:https://www.liquibase.org/databases.html).

To test it out, one would just need to install postgres, create a new "openboxes" database, create an "openboxes" user with password "openboxes", and then set the database credentials in openboxes-config.groovy to point to the newly created database. Please note that I said openboxes-config.groovy instead of openboxes-config.properties. If you already have an openboxes-config.properties, you can create an openboxes-config.groovy right next to it and add the following DSL. Both properties files will be read on startup.

dataSource {
    pooled = true
    driverClassName = "org.postgresql.Driver"
    dialect = org.hibernate.dialect.PostgreSQLDialect
    url = "jdbc:postgresql:https://localhost:5432/openboxes"
    username = "openboxes"
    password = "openboxes"
}

[jmiranda]

Receiving renewed interest in this one so I'm going to post an email conversation from 2016. It's a little light on details, but provides some context on what was done.

On Jul 18, 2016 at 5:52 PM daisy wrote:
Hi, does this work with postgresql?

On Jul 18, 2016 at 6:21 PM Justin Miranda wrote:
Theoretically, OpenBoxes could work with PostgreSQL but I haven't tried it yet. We use a database migration tool called Liquibase that creates DDL statements for the desired database. I think there are a few MySQL-specific migrations, but those should be ignored when using other database systems.
... and if you'd like to explore this option I can put together a proof-of-concept using PostgreSQL. If you're interested, I'll do some preliminary work on it when I get back from vacation in early August.

On Jul 19, 2016 at 12:48 AM daisy wrote:
Wow that would be great, but I don't want you to spend time on it in case we go with another system. Do you want me to confirm with you before you proceed?

On Jul 19, 2016 at 9:28 AM Justin Miranda wrote:
Sure, that sounds good. However, I might put a few hours in regardless since I've wanted to support PostgreSQL for awhile now. Also, I'd be happy to schedule a quick walk-through if you wanted to get a more in-depth look at the features.

On Aug 5, 2016 at 2:06 PM Justin Miranda wrote:
Hey Daisy - I had a chance to test out OpenBoxes using Postgres and encountered some issues with the Liquibase migration > (which was not unexpected). With a little work, I think those migration scripts could be refactored to work properly in Postgres. > Let me know if you're interested in using OpenBoxes and I'll keep plugging away.

On Aug 5, 2016 at 2:56 PM daisy wrote:
Yup! Still interested. Thanks!