Prepare a user for the database and a database
You could continue to ssh into your server, then run psql, and set up a table schema that way. In fact, I recommend ssh’ing right now for two reasons: to add a user to linux that will be used as a dummy to reduce the permissions in the sql server. Call it say, esp32User. It doesn’t need sudo privileges. Then, go back to Tomi’s tutorial and review where he sets up a postgress username for his main user: this time, do it for the esp32User account. For subsequent tasks, I find it useful to use pgAdmin from my desktop, since the schema can sometimes be important and you can visually check that everything’s working correctly.
Open up pgAdmin and set up a server with the name of the website. Then, I create the database “API_server_database.” Then, click through schemas and then public and look for tables. Right click and create the table “esp32data.” This is where the esp32 will be able to insert data.
My table has columns for:
- time (timestamp without timezone, where I set under “constraints” the default value to be CURRENT_TIMESTAMP. This will basically log what time the data was received by the server, which for my purposes is as good as having the esp32 report something)
- location (char var. The esp32 can tell us where it is, although it’s hard-coded in micropython to be “desk”
- temperature (double precision)
- humidity (double precision)
- pressure (double precision)
- wetness (double precision, although this is not used by my esp32 – my esp8266 in the basement uses this to check for leaks)
You will want to, in the table properties, under the security tab grant INSERT and SELECT options to your distinct psql username (esp32User). This means if someone somehow gains access to the esp32, they will not be able to delete any data from your SQL database by sending other requests. This will also be precluded by our REST API, as we will see later. It should now be ready for use.
Build a REST API
Our postgres SQL system is allows remote connections to the host, so we could have users updating our tables from other locations. In fact, as you might expect, you could insert values directly to the table from the microcontroller relatively simply. The linked codebase was written for an esp8266 and in the C-based Arduino language, but those are minor details. So why don’t we do this?
The first reason is that I didn’t know I could do a remote connection on such a simple device as an esp32 until I started this project, because I hadn’t worked with SQL much. The second reason is better: an additional layer of authentication and security. Remember, the esp devices are not particularly secure, unfortunately. If someone gained access to the physical device, I would like to limit the amount of interaction they have with the server. I’ll do this by implimenting a python-based REST API as an intermediary. This also simplifies the esp32’s job, because it only needs to interact with the outside world using http requests.
REST stands for representational state transfer, and these systems are ubiquitous on the internet. If you need to interact with a 3rd-party API, it will probably be through a REST api. If you are going to build your own: it makes sense for it to be a REST api. Ours will be simple to build because we don’t need to add too many capabilities.
The first thing we want to do is pick a URL for interaction with the API. In my case, I just chose the Scratchwork.xyz/SQL_API. We want to then go into the site map we set up in nginx, located in /etc/nginx/sites-available/$YOUR_SITE_NAME and add an entry ABOVE the default home (“/”) location thusly:
location /SQL_API {
include uwsgi_params;
uwsgi_pass unix:home/$USER_NAME/SQL_API/SQL_API.sock;
}
As currently configured, nginx looks through this list in order, falling back on the most general location if a specific location isn’t available. So when a request ends up at our server looking to interact with the location Scratchwork.xyz/SQL_API, it will now get routed to the uwsgi socket SQL_API, which is exactly what we want.
Now, we need to make sure the python code on the other side of that socket is actually doing something useful!
We should already have the flask framework set up from the previous tutorial. But now, rather than serving a static html page, we’ll have some interaction depending on what request we get. We’re going to use SQLAlchemy package to forward the API requests to the SQL server, so first thing’s first: from the command line on your server, add SQLALchemy and other useful packages to the virtual environment for this service:
source $PROJECT_NAME_ENV/bin/activate
pip install SQLAlchemy Flask-RESTful Flask-HTTPAuth psycopg2 #SQLAlchemy will require psycopg2 to be installed. Flask-RESTful and Flask-HTTPAuth will be used for authenticating requests and parsing HTTP POST requests.
Now we can work on the python code itself. The example is hosted at here under the “SQL_API” folder. Essentially, we have two tasks to accomplish: receive and authenticate the request to update the SQL table, and then update the SQL table. This is all wrapped within a flask framework. Luckily, there are several packages that make these tasks easy. Take a glance through the code, there’s very little to it. The libraries are doing the heavy lifting!
We have to define what we want to happen in our REST API: in our case, we just make a simple test case for a get request, and define a parser that will parse the message uploaded in an incoming post request. Once we parse that data, we insert it into the SQL database. If you wanted, you could clean up my code: the way that the SQLAlchemy is sending the request is not very pythonic, but it works fine for our simple example. If you were doing a lot of database work in python, you should consider reading the SQLAlchemy docs and writing your code in a way that fits with python a bit better.
I’ll give a big warning that you would want a more secure way to authenticate, both in terms of creating the connection string The connection string uses the domain localhost, but if this process were on a different physical server, we could use the IP address or the custom domain where postgres was installed. that’s used to authenticate with Postgres, as well as storing the acceptable username+password combinations for our REST Api. I’ll let you go to other sources for best practices on that.
The most important thing here is the definition of “post” under the class “SQL_interaction.”