Add: Support for Postgres

[swhyte-rt]

Watchman Version: current version and forward

What were you trying to do?

Run watchman with Postgres as Database

I am just finishing off the implementation of this and can create a pull request.

[adamdecaf]

Yes please @swhyte-rt! Was it just adding the config options? We tried to keep the SQL compatible across the major databases.

[swhyte-rt]

It was adding config options, but also supporting differences in queries. For example Postgres did not like

insert into customer_status (customer_id, user_id, note, status, created_at) values (?, ?, ?, ?, ?);

so I had to change to

insert into customer_status (customer_id, user_id, note, status, created_at) values ($1, $2, $3, $4, $5);

I will test this one more time, but it was throwing error when preparing the statement.

[swhyte-rt]

Here is the documentation explaining that most DB will support ? syntax but Postgres supports $1 and Oracle supports :var1

http://go-database-sql.org/prepared.html

[adamdecaf]

Ah that's too bad. Did you have to branch at each query off a config or flag?

[swhyte-rt]

I have repository interfaces, and have implemented a genericSQL<name of repo>Repository (SQLite, MySQL) and a postgres<name of repo>Repository. It is in these implementation repos that is where syntax difference is.

[swhyte-rt]

@adamdecaf I can let you look at the fork of the work if you want to look at it before doing a pull request.

[adamdecaf]

Sure! I think it's this branch?

master...swhyte-rt:master

[swhyte-rt]

Yep that is it

[swhyte-rt]

@adamdecaf I may have been over thinking this by breaking out separate repositories for generic db and Postgres. I was thinking if you were to ever support nosql databases. I can change this to a single repository with constants for the various queries and check the db type when initializing the query. That would be cleaner.

[adamdecaf]

We have no plans to support NoSQL for storage in Watchman. One repository for all three databases sounds fine so long as we find/replace and always prepare the query.

I was checking with other projects and they've written an implementation for MySQL and Postgres.

• vault: mysql and postgres
• perkeep: mysql and postgres