REST API vs Direct DB Calls – Best Practices for Desktop Applications

apiArchitecturedesktop applicationrest

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:

Access the database directly from the Desktop Application
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)

Best Answer

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.

Related Solutions

Handling Resource Identifiers in REST API Clients

Edited to address question updates, previous answer removed

Looking over your changes to your question I think I understand the problem you are facing a bit more. As there is no field that is an identifier on your resources (just a link) you have no way to refer to that specific resource within your GUI (i.e. a link to a page describing a specific pet).

The first thing to determine is if a pet ever makes sense without an owner. If we can have a pet without any owner then I would say we need some sort of unique property on the pet that we can use to refer to it. I do not believe this would violate not exposing the ID directly as the actual resource ID would still be tucked away in a link that the REST client wouldn't parse. With that in mind our pet resource may look like:

<Entity type="Pet">
    <Link rel="self" href="http://example.com/pets/1" />
    <Link rel="owner" href="http://example.com/people/1" />
    <UniqueName>Spot</UniqueName>
</Entity>

We can now update the name of that pet from Spot to Fido without having to mess with any actually resource IDs throughout the application. Likewise we can refer to that pet in our GUI with something like:

http://example.com/GUI/pets/Spot

If the pet does not make any sense without an owner (or pets are not allowed in the system without an owner) then we can use the owner as part of the "identity" of the pet in the system:

http://example.com/GUI/owners/John/pets/1 (first pet in the list for John)

One small note, if both Pets and People can exist separate of each-other I would not make the entry point for the API the "People" resource. Instead I would create a more generic resource that would contain a link to People and Pets. It could return a resource that looks like:

<Entity type="ResourceList">
    <Link rel="people" href="http://example.com/api/people" />
    <Link rel="pets" href="http://example.com/api/pets" />
</Entity>

So by only knowing the first entry point into the API and not processing any of the URLs to figure out system identifiers we can do something like this:

User logs into the application. The REST client accesses the entire list of people resources available which may look like:

<Entity type="Person">
    <Link rel="self" href="http://example.com/api/people/1" />
    <Pets>
        <Link rel="pet" href="http://example.com/api/pets/1" />
        <Link rel="pet" href="http://example.com/api/pets/2" />
    </Pets>
    <UniqueName>John</UniqueName>
</Entity>
<Entity type="Person">
    <Link rel="self" href="http://example.com/api/people/2" />
    <Pets>
        <Link rel="pet" href="http://example.com/api/pets/3" />
    </Pets>
    <UniqueName>Jane</UniqueName>
</Entity>

The GUI would loop through each resource and print out a list item for each person using the UniqueName as the "id":

<a href="http://example.com/gui/people/1">John</a>
<a href="http://example.com/gui/people/2">Jane</a>

While doing this it could also process each link that it finds with a rel of "pet" and get the pet resource such as:

<Entity type="Pet">
    <Link rel="self" href="http://example.com/api/pets/1" />
    <Link rel="owner" href="http://example.com/api/people/1" />
    <UniqueName>Spot</UniqueName>
</Entity>

Using this it can print a link such as:

<!-- Assumes that a pet can exist without an owner -->
<a href="http://example.com/gui/pets/Spot">Spot</a>

<!-- Assumes that a pet MUST have an owner -->
<a href="http://example.com/gui/people/John/pets/Spot">Spot</a>

If we go with the first link and assume that our entry resource has a link with a relation of "pets" the control flow would go something like this in the GUI:

Page is opened and the pet Spot is requested.
Load the list of resources from the API entry point.
Load the resource that is related with the term "pets".
Look through each resource from the "pets" response and find one that matches Spot.
Display the information for spot.

Using the second link would be a similar chain of events with the exception being that People is the entry point to the API and we would first get a list of all people in the system, find the one that matches, then find all pets that belong to that person (using the rel tag again) and find the one that is named Spot so we can display the specific information related to it.

.NET REST – Combining Single Page Application and DDD with a REST API

You seem to hint at your own answer: don't submit everything in one go. If you think about it, what kind of actions would you like to be able to do in your view? One of them is apparently assigning hotels to room types. So for every action you want to perform in your view you either have a method you call on a endpoint controller, or if you want to work with commands, you'd form a AssignHotelToRoomTypeCommand with all the necessary data for executing that procedure, and send that command to an endpoint.
The more I think about it though, assigning a hotel to a room type seems a bit weird/backwards to me... Is there a reason you are modelling a room type as an entity and not a value object?