I am still looking at HugSQL. I started messing around with the quoting option in the connection macro. I noticed that I was not able to get any SQL injection samples to work through HugSQL. (It was a bit odd to try to intentionally do SQL injection after years of trying to prevent it.) I think the Postgres JDBC driver takes care of SQL injection.
To test this, I started looking at Migratus (and its leiningen plugin as well) to make a database in MySQL. I am more of a Postgres guy, so it took a while to get things right. Plus I think that Migratus has to do things a bit differently than regular SQL. I think that creating a table and creating a trigger for that table need to be two separate migrations.
I also had problems with foreign keys. With one foreign key, I got the table name wrong. I had to google the generic (and useless) error message. That led me to googling a few more steps. I had to log in as root, run the command
SHOW ENGINE INNODB STATUS
and look in that wall of text for a section labelled “LATEST FOREIGN KEY ERROR”. The cause was buried in there. If MySQL is saving some text about the cause in this object, why can’t MySQL just print it out to the prompt instead of forcing me to go through all these other steps?
I might do a Migratus project with Postgres just to see if this small, irritating problem happens there too.
Another thing while working on all of this is I have used pg_dump a few times. I found a page online with examples that mostly work. Maybe I have Postgres configured differently, but I have found that for pg_dump, I have to add a few options to get it to work. The –help says that –password is unnecessary, that you should be prompted for it automatically. I have found that I need to add that, as well as –port and –host (even though I have been running it on localhost).