Grails and PostgreSQL

In addition to the Austin Groovy and Grails User Group, I also go to the  Austin Postgres Users Group.

At the last APUG meeting there was no set agenda or presentation. They just had a Q&A with whoever wanted to answer. The company that hosts the APUG is a heavy Postgres user.

I had a few questions. The basic thrust was: What are some things DBAs wish developers knew about databases or did differently?

One answer was that developers should learn to think in sets. If you need to change a bunch of records in the database, just run a query that will update them all at once. A lot of developers would get a query (with an ORM) that would put the objects into a list, then iterate through the list and update them one at a time. This would be a much heavier load on the database. Just run a query.

In GORM, you can call .withSession on your domain classes to get the Hibernate session, and use that to run queries.

Another suggestion was to deal with views instead of tables. I really do not have any knowledge about that WRT Grails.

The last thing that I remember is that you should not think that you are the only person who touches the database. Someone else will want to run queries, and perhaps add data to it. Many web frameworks (like Grails and Rails) have database constraints if you access the database through the web app. But outside the app, you are on your own.

One small project I will start is I will go through the Grails constraints (see here and here) and see how to create them in Postgres. I might do MySQL as well. There are a few that Grails will put in the database when it is generated by Grails, like uniqueness, foreign keys, and whether or not a field can be null. But for most of them, they only exist in Grails.

Image from “Evangelia (pars II)”, an 11th century manuscript housed at the Bibliothèque nationale de France. Source gallica.bnf.fr / BnF; image assumed allowed under Fair Use.