13

I am currently planing an application that will be used in a company. It is required to build a Desktop Application. At the moment they are not sure if the application should be available on mobile or browser in the near future.

I have two possibilities:

  1. Access the database directly from the Desktop Application

  2. Create a REST API and connect to this

Can I use a REST API if the application stays just a Desktop Application within the company? I know that it's possible, but is it "the right" way? (Best practices)

There are some (possible) advantages and disadvantages for creating directly a REST API:

Disadvantages:

  • Takes longer to develop
  • More complex
  • The server does more work
  • ̶Se̶̶c̶̶u̶̶r̶̶i̶̶t̶̶y̶̶ ̶̶i̶̶s̶̶s̶̶u̶̶e̶̶s̶̶?̶
  • Slower? (The server and the Desktop Application are on the same Network)

Advantages:

  • Migrating to other platforms is easier
  • The Business Logic is also needed when calling directly the database. It won't take much longer to develop
  • Same goes for complexity
  • Security (as mentioned by tkausl in the comments)
  • Maintainability (as mentioned by WindRaven in the comments)
Michele Ceo
  • 3
  • 2
devz
  • 243
  • 1
  • 2
  • 8
  • 5
    You really count `Security issues` as a disadvantage for the REST-API? – tkausl Jan 22 '16 at 13:39
  • 1
    I can protect my API with e.g. OAuth2 and TLS, but this will add up more work. I don't know how secure a direct DB call is. E.g. with JPA. That's why I added a question mark since I'm not really sure. – devz Jan 22 '16 at 13:49
  • 1
    Well, you can't get less security than a direct db-call, unless you give away root-login. – tkausl Jan 22 '16 at 14:07
  • 2
    Another advantage, A change in business logic only needs to be made on the REST API and deployed to the server, not made and then every client updated. (provided the API interface does not change as a result) – RubberChickenLeader Jan 22 '16 at 14:46
  • Is the database running on the same computer as application? If not, I wouldn't even think about direct write access to the database. – CodesInChaos Jan 22 '16 at 15:05
  • The application and the database are not on the same computer. But they are on the same physical network. I can use TSL for the DB to protect the db request. Are there some other problems? This is getting more and more into security =) – devz Jan 22 '16 at 15:46
  • Are you sure if you built a web app they wouldn't know it's not a "real" desktop app? – JeffO Jan 22 '16 at 16:50
  • I thought about it. At the moment it is not sure which technology should be used. But I will definitely propose this solution. – devz Jan 22 '16 at 17:17
  • @devz after 3 years I have the same question. It would be interesting to know what you ended up doing and if you can share some experiences. My scenario is exactly same as yours: desktop application with back-end server and database in the same physical network. In my case I have a lot of tables and some can be very big. I am afraid I wouldn't now how to deal with those big tables, I heard of pagination but sounds it could be quite slow and cumbersome if I want to sort per different columns... – jav Dec 02 '19 at 19:02

3 Answers3

10

When it comes to a large applications with huge database containing milions of records, you soon realize, plain selects, updates, inserts and deletes simply are not enough.

So you start thinking in a different way. You create procedures and triggers to take care of more complicated stuff directly in the database and this is not very good. Databases offer great performance when performing CRUD operations. Long procedures? Not so much.

The problem with procedures

Now imagine, that you switch to a database which does not support the concept of procedures? What are you going to do? You are forced to move the procedures to your code base instead, where you can be pretty sure that once you program it in let's say Java, it will always stay there, no matter which database engine you choose.

Not to mention, your procedures are usually part of your business logic and it is not a good idea to have your business logic splatered across your codebase and database.

Ideally, you should always have a mediator between the database and the client implementing its own business rules. Providing direct access to database is not a good idea, because when you do so, the one with access has direct access to the tables and can do pretty much anything with the data there is.

Disadvantages

  • Takes longer to develop: Of course, you are creating a new system, that is going to be more time consuming than simply giving the client a database connection string and let him write the queries.
  • More complex: Complexity of a system > complexity of a database query.
  • The server does more work: Not necessarily. With good design, caching,... you can move the load from the database server to the one of the mediator.
  • Slower: In terms of development? Yes. In terms of speed when retrieving data? No. You can optimize your mediator using caches (such as - popular as of January 2016 - Redis, Elasticsearch) and actually make it deliver data faster than a plain database query.

Advantages

  • Migrating to other platforms is easier: Migrating to a new database engine? Definitely. Migrating the whole mediator to a new language? Not really.
  • The Business Logic is also needed when calling directly the database. It won't take much longer to develop: As explained previously, the procedures problem.
  • Security: With proper authorization having mediator is definitely much more secure than giving a user direct access to the database, because you restrict him to the end points which run only the queries you want to.
  • Maintainability: One of the best benefits of having a mediator. If there is a bug in an API your clients call, you fix it, push the fix to your VCS repository, build your mediator from the currect version of VCS containing the fix and all your clients are suddenly using the fix, without them needing to download an update. This is simply impossible to do, if the queries are stored directly in the client applications. In that case, clients are forced to update their application.
Community
  • 1
Andy
  • 10,238
  • 4
  • 25
  • 50
  • 1
    How often did you face the challenge of switching to a database not supporting the concept of procedures when you were actually using them? – harpun Jan 23 '16 at 09:29
  • 1
    @harpun Two times in the past 2 years. When I and the team I was working with on a project decided to go from MySQL or PostgreSQL to MongoDB as a part to switching to Node.js and also to evade dead locks and store objects as aggregates directly instead. – Andy Jan 23 '16 at 09:46
  • There is another option than just direct access to db. The "mediator API" does not have to use HTTP, hence the HTTP server does not have to be involved at all. It can be just code-level API. Another question is, provided that you are not satisfied with the speed of your programming language, is whether to having API (REST access) written with some another language/platform than your business logic... – forsberg Sep 30 '18 at 11:53
2

Here's my opinion on the matter: You have the right idea of wanting to use a webservice, but you may be planning on using the wrong tech.

When you say REST, I'm assuming you're talking about Asp.Net WebApi. That's the wrong tech for intranet applications. REST & WebApi are awesome, don't get me wrong, but for any kind of internal application, WCF web services are the way to go in my humble opinion. They allow the client to reference the service endpoint just like a class library, which means you're not dealing in XML or JSON in your desktop client. You're working with classes & objects.

Anyway, yes. You've got the right idea. If they're not sure if they'll need a web based client, then you have to architect your system to easily add it if they decide they want it later. It's much easier to add different types of clients when you have a service oriented architecture.

RubberDuck
  • 8,911
  • 5
  • 35
  • 44
  • 1
    It is stupidly easy to use a client to deserialize json to objects. The classes can even be auto generated from example json by using http://json2csharp.com/ . It works wery well with the http client from the microsoft.aspnet.webapi.client nuget package. You are back in object land faster than dealing with WCF client references I promise, so this should not be the primary driver for choosing WCF. – Esben Skov Pedersen Jan 23 '16 at 11:41
  • @EsbenSkovPedersen there's a difference between stupid easy and *automatic*. – RubberDuck Jan 23 '16 at 11:42
  • Because there never are problems with WCF? That can't be right. – Esben Skov Pedersen Jan 23 '16 at 11:48
  • Who ever said there weren't problems with WCF @EsbenSkovPedersen? I sure didn't. Every piece of tech has its problems. What's your issue? – RubberDuck Jan 23 '16 at 11:50
  • When you say it is easier than stupidly easy you imply that. I don't have an issue. – Esben Skov Pedersen Jan 23 '16 at 11:51
  • No. What I implied is that you don't have to write any additional code with WCF. It's automatic. The framework generates it for you. I never meant to imply that there aren't issues with the framework. There are issues with every piece of software ever written. (And yeah, you apparently do have an issue. You're a very rude person.) – RubberDuck Jan 23 '16 at 11:54
  • I'm sorry if I offended you. I'm not trying to be rude but I simply disagree with what you wrote and noted that. In my experience WCF can cause a whole nother class of problems than writing the five lines of code by hand. – Esben Skov Pedersen Jan 23 '16 at 11:58
  • It's not just five lines of code. With WebApi, I also now need to define my own adapter interface for the service and wire it up as well. Both approaches have their pros and cons. I've seen (i.e. Implemented) web services with WebApi that honestly could have been done better & faster with a WCF service. A lot of people seem to not even know it exists. I'm just proposing that OP learn about it and decide for themselves what is best for their situation. – RubberDuck Jan 23 '16 at 12:05
  • It is five lines if we are not counting auto generated code. – Esben Skov Pedersen Jan 23 '16 at 12:10
1

Look at it this way, it definitely is a common pattern and might reasonably be described as best practice.

Depending on your platform you may find tooling that almost makes the problem go away - Microsoft have ODATA support for connected apps that should makes forms over data straightforward once you've climbed the learning curve.

More pragmatically - define the API you need for your data layer in the desktop application and code to that API. Put all the database access behind that API - which actually makes things better from the perspective of developing the application - and then the location of the actual database access code becomes less significant (the same API could be implemented by code that talks directly to the database or by code that talks indirectly to the database via a rest endpoint). If you start with a direct implementation then the REST version is substantially going to be a wrapper round the same API so you'll not be too badly penalised...

Its probably not quite as simple as I'd like to make it out to be - but its a good pattern, it gives you the flexibility you may need without going to far down the YAGNI route.

marcospereira
  • 218
  • 1
  • 4
Murph
  • 7,813
  • 1
  • 28
  • 41