I guess this is not good practice.

Hi, you are right about that!

The reason web services in general are so popular is that HTTP is just a very efficient, time-tested way of handling stateless server-client communication, like SQL queries.

Adding the capability to your app to make HTTP requests is trivial -- I would use HTTP::Tiny which is in core Perl and has a very user-friendly interface.

To serve responses I would install a modern Perl web application. There are lots of super-lightweight ones including HTTP::Server::Simple, but I would recommend going with something a little more feature-rich so it can handle your database needs natively, along with logging, authentication, tx encryption (https), etc etc. My choice would be Dancer2, which I like in part because it is very pluggable, meaning you can run it very lightweight with very lightweight code. Not least of the useful features is Dancer2::Plugin::Database. Once you've configured your Dancer app with your database settings, you have access to the DB in your code via the database keyword, and can make simple queries with sugar methods like quick_select.

Here's an oversimplified and contrived use case. Suppose you are providing the daily closing stock prices for some market and you have the important data in a table called price. The user has to provide the stock symbol and the date for which the price is requested. Your app will make an HTTP request to your server using a URL like https://your-backend.com/GOOG/date/2018-11-05, and get a neat response of {"price":"1057.79"}.

The entire server code for that is: 

use Dancer2;
use Dancer2::Plugin::Database;

set serializer => 'JSON';

get '/:stock/date/:date' => sub {
    database->quick_select('price', {
        stock => params->{stock},
        date  => params->{date},
    }),
};
 
dance;

[download]
... with something like this in the Dancer2 config file: 
plugins:
    Database:
        driver:        'mysql'
        database:      'test'
        host:          'localhost'
        port:          3306
        username:      'your_app_id'
        password:      's3kr1t'
        on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'"
+ ]
        dbi_params:
            RaiseError: 1
            AutoCommit: 1

[download]
It's pretty simple, and lots of help is available if you need it. It's always good practice to lay the foundation so that if you meet with success your path forward is smooth sailing.

Hope this helps!


The way forward always starts with a minimal test.

In reply to Re: online database for desktop app by 1nickt
in thread online database for desktop app by Anonymous Monk

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.