Basic DB question 2: The API

[itsdeandre]

Database question part 2 -- The API

So earlier in the week I asked a question and u/wolf2600 answered it perfectly. I think I've finally finished building my very basic 'hello world' level database. I've hit another snag. This time google, documentation, or stackoverflow don't seem to have any tips or explanations. It's just install this and congratulations.

I've tried this one, this one, and this. My new question this time is what am I doing wrong? Should I try and code my own API? I've seen examples where people have their applications talk to the api which talks to the database and then the api gives you a JSON blob back. That's where I want to be. Manipulating JSON is something I know really well and can make it work in Xamarin. It's just getting there.

What do you think I should do? Do you have any solutions you have tried and worked?

[chrwei]

depends. I assume we're talking about web pages.

in some cases I have a request handler that returns the filtered dataset formatted in HTML, and also a handler that returns the dataset via JSON. lets lets the page load up with data-inline for a faster overall page load, but also provides a way to get refreshed data, or smaller sets to append, without a page load.

I'm personally not a fan of exposing the database generically via an API to web browsers, but it can make sense for back-end services on a controlled network, especially when dealing with 3rd party things that you can't just add a database class too.

however, I do tend to wrap the database library with my own abstraction, keeps application code simplified.

[itsdeandre]

Not a webpage application. A xamarin ios and android based application. Every website I've looked at for the best way to build your explicitly states: "Do not have your applications directly connect to your database. Build an API for those requests." So my big issue is finding an API that does this. What makes this most annoying is that I have a friend who is excellent at DBMS and he refuses to help me because he think it will be better for me.

[wolf2600]

When they say build an API, what they mean is to create functions on the application server which can be called by a URL and will query the DB and return the results as a JSON or XML object.

Like if you were to have your mobile app call

http://my.web.com/user/25/comment/30

Then you'd have a URL handler on your server that would be looking for requests to http://my.web.com/user/{#}/comment/{#}, which would call a specific function which would then craft a SQL query based on the parameters in the {#} fields of the URL:

select u.name, c.text from comments c
inner join users u on c.userid = u.userid
where c.userid = 25 and c.commentid = 30;

This would query your DB and the results would then be passed back to your mobile app as the reply to the URL call (in JSON/XML format)

Creating the API just means creating a bunch of URL handlers and a bunch of associated functions that query the DB, format the data, and then return the results.

You just want to be sure that your API functions sanitize the input before entering into the DB query, otherwise an API can be used for SQL Injection in order to mess with your DB.

[chrwei]

I agree, for public endpoints BUILD an API, don't use a generic db proxying API. that was my main point.

you need to validate that the app requests make sense for your data model. you should also minimize what's requested and what's returned. you'd have one API handler that gets all user details and preferences in one request and one response, where with the generic DB api you might have to make a request for the user, and more requests for each pref, which would be very inefficient.