Is it sensible to connect a desktop client directly to MySQL?

Question

I'm writing a Java desktop client application that retrieves data from a remote MySQL server. For development purposes I have had it connecting directly to the MySQL server (i.e. with DriverManager.getConnection(databaseURL) etc.), but have been intending to move to using a web service (once that'd been built). My question is whether I couldn't just continue with the direct connection?

What is the web service going to give me other than more code to write? I would have to implement my own authentication; what's wrong with just relying on MySQL's?

(I'm phrasing this question rather in the negative, because I get the idea that this paradigm is somewhat frowned upon these days; that's really why I'm asking it, because it seems to me to be a perfectly okay thing to do.)

Thank you for any insight you may be able to give me!

Answer 1

Several reasons (in no particular order):

• Change business logic in just one place, database schema changes have no effect on the clients (just the service)
• MySQLs authorization system is rather coarse
• More secure, since you don't need to open your DB to the outside world
• Web services operate through standart HTTP ports, less trouble with firewalls
• No need to install ODBC drivers

Of course, Web services are not an universal panacea. Some of the above might not apply in your particular scenario, use what's best for you.

Answer 2

Well, so long as you give the client a user with only just the right permissions, I don't see anything wrong with connecting directly to MySQL. Maybe if you're going to have lots and lots of these clients running, which will keep a lot of connections to MySQL open, while a web service could use a single connection and handle the load from all clients.

The true usefulness of a web service is when you have a lot of business logic that can't be in the database. Putting this logic in the client is a very bad idea because users may run outdated versions, and so on. Also, a web service allows you to have different types of clients (such as a Windows client, a web client, etc.) without having to rewrite any logic besides presentation.

Answer 3

A web service will enable you to cache data, reducing the strain on the database. It will also make it easier to expose data from your system to other systems. Another thing is flexability - with a web service as another layer, you can change the connection string/scheme changes for all clients in a single location, you won't have to redistribute the client.
Will you ever need any of these? I usually don't go for paradigms, as you say, but I think a web service can make sense. Still, only you know the design of your application: How many people will use this client? Does a connection to the MySQL server require any special software/configurations on the client computer? (Oracle does, for example).