Re: What is your favorite front end for user interaction to postgresql databases?

On May 8, 2006, at 3:33 PM, Karen Hill wrote:

> What is your favorite front end for end users to interact with your
> postgresql db? Is it java, .net, web apache + php, MS-Access, ruby on
> rails? Why is it your favorite? Which would you recommend for end
> users on multiple OSes?

This is totally dependent on the individual user's preference:

- Many users comfortable with command line shells prefer psql (this
is my choice).
- Others who like a gui might use pgAdmin (or miriad others)
- If interoperability with MSOffice is your main concern, I could see
Access/ODBC being useful. As a general front-end, probably not.

When you start talking about java v. .net v. php vs. ruby, that's a
whole other ball of wax. That's a lot less about interaction, or even
about databases and a lot more about programmer preference.
Personally, I would recommend:

Python + psycopg (highly interactive, great for complex scripts or
even apps)
Java + JDBC + Hibernate (I think JDBC sucks, but Hibernate totally
rocks and more or less hides it from you)
Shell + psql (great for simple reporting, automating dba tasks, etc.
etc)

> Also, what do you think of having the database management system do
> all
> work (business logic etc) with the front end as a user interface vs.
> having N-tier with an application server handling business logic, and
> the db just accepting data. I currently have the opinion that N-tier
> is not as good as having the db contain the business logic via stored
> procedures. This comes from having to re-invent the wheel every
> time a
> new user app needs to be created.

From personal experience (and others will disagree), I find putting
logic in the database to be a bad idea. I only use stored procedures
for triggers and the like, and I try to avoid those whenever possible.

Here are my reasons why:

- I don't like the PL/pgSQL language (yes there are alternatives, but
they have their own drawbacks)
- It's complex to test and upgrade (we actually wrote non-trivial
infrastructure to automate both)
- It's difficult to debug (compared to external languages like python
or java)
- It's difficult to profile, therefore getting good performance can
be difficult

I had a very complex system coded in stored procedures that performed
poorly and was hard to maintain. It's now refactored into java/
hibernate code that's simpler to understand, performs much better and
is easy to extend and maintain. Of course that's just my particular
case and obviously YMMV.

Stored procs could make a lot of sense if you have many different
clients accessing the db in different ways and you want to strictly
enforce business rules across all of them. I had no such requirements
in my case.

In any case I would strongly recommend doing the simplest thing that
you can get away with. If your business rules can be fulfilled with
grants, views and constraints alone, use them.

-Casey