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

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 2

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 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).

Answer 4

This feeds into the other answers, but consider that if you allow clients to connect directly to a central server this will give them the necessary credentials to run any query within the database they're working in. I'm not sure how good the support is in MySQL for limiting resource usage but I'm betting that right now a determined client could connect to your database server and launch a query so badly written it brings your DB server to its knees. They wouldn't necessarily do this with malicious intent, perhaps they're just trying to implement an extra feature that you didn't think of.

So basically, what the web service lets you do is take away some power from the user. Power that they don't need in this case. Unless clients run their own database server of course, but that's a different environment entirely. (if they want to crash their own DB, that's their business)

Answer 5

Connecting directly to the database entails, obviously, having the interface to your database directly accessible from the Internet. Which means that unless you are very careful with how you setup your permissions, you could be making your entire database vulnerable to attack. Whereas with a Web Service, the only access any user can have to the database is the access/functionality that is available through the Web Service. I would submit that properly securing and maintaining a MySQL database that is open on the net is more of a hassle than the extra code involved in creating a Web Service authentication layer.

Plus if you need to change your tables in any way, you would have to update your client applications, instead of just updating the Web Service, if your queries are generated by the client.

Answer 6

More of a question than an answer, what are the speed implications between the 2 approaches? i.e. direct connection to mysql or using a web service