How to Generate Data Accessing API Endpoints Without Even Coding

Hands-on Tutorials

As the most common architecture of any Web or Mobile applications, database + backend API + frontend is very typical and classic. While different applications will definitely have their own front-end design, the backend API is usually very similar. That is, they must be able to access the data in the database. In other words, the major responsibility of the backend API is acting as a middleware to receive the requests from the front-end and retrieve the database based on the requests, and then return the results to the front-end so that the users can consume.

Of course, for most of the applications, the backend API also needs a certain level of customising, especially the security requirement such as JWT and so on. However, when there is not too much security concern, there is a way to "generate" your backend API that can access your database without even coding.

In this article, I’ll introduce a Python library can do this for you. Imagine that you are a front-end developer, you just want to test some front-end features in your testing environment and don’t want to waste too much time on writing your own backend API. In this case, the Python library sandman2 is one of the best choices!

Preparation of Sample Database

The sandman2 library supports many database management systems, such as:

SQLite
MySQL
PostgreSQL
Oracle
MS SQL Server
Sybase
Drizzle
Firebird

The sandman2 library supports them as different SQL "dialect", which means that we don’t need to worry about which database we’re using, just tell sandman2 which database it is and it will work out-of-box.

In this article, I will use SQLite as an example because it is the easiest one, which I don’t need to download, install and configure anything.

In one of my previous article, I have introduced a Python built-in library sqlite3 that can generate an SQLite database very easily. I will skip introducing the SQLite library in Python. You can check out my article below for details.

Do You Know Python Has A Built-In Database?

Let’s create a database called "my-test".

import sqlite3 as sl

con = sl.connect('sandman2/my-test.db')

Then, create a table called USER for demonstrating purposes.

with con:
    con.execute("""
        CREATE TABLE USER (
            id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            age INTEGER
        );
    """)

After that, let’s insert some sample rows.

sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)
]

with con:
    con.executemany(sql, data)

OK. Now, we can query the table.

with con:
    data = con.execute("SELECT * FROM USER")
    for row in data:
        print(row)

# Output:
# (1, 'Alice', 21)
# (2, 'Bob', 22)
# (3, 'Chris', 23)

We have got a database with a populated table now. Let’s start to demo the sandman2 library in the next section.

Web-based SQL Client

Before everything, we need to install the sandman2 library. Just using pip.

pip install sandman2

Then, as I stated in the title, we don’t need to write ANY code, just using the command-line interface.

$ sandman2ctl sqlite+pysqlite:///sandman2/my-test.db

The illustration below shows how the connection string is constructed.

Please be noticed that the DB driver is optional. If we keep it empty, SQLAlchemy (sandman2 is built on top of SQLAlchemy) will try to use the default driver. However, it might complain if the driver does not exist. Don’t worry, we can get the name of the default driver library so that we can using pip to install it.

After running the command, the web services started.

Let’s try the web client using any browser to access the admin console.

http://localhost:5000/admin/

We can see that there is already an object USER on the page. That is because we have created this table. If we click USER, we can see that the three rows we inserted previously are displaying in a data table.

On this page, we can click the "Create" button to insert a new row, update an existing row or bulk deleting rows. You will be able to find these features easily, so let me skip those things and show you the most important feature in the next section.

Data Accessing RESTful API

The coolest feature of sandman2 must be the automatically generated RESTful API endpoints of the database we have connected to.

In the above section, keep the web service running, then the API is already there for us to use. You may prefer API testing tools such as Postman or Insomnia, but in this article, I’ll just use "curl" command to keep it simple, so you don’t have to download anything if you just want to test the API endpoints.

1. Get a list of data entries in a table

Remember we have a table called USER? Now, we can simply send GET request to the following URL to get a list of entries.

http://localhost:5000/user/

Please be noticed that

Most of the DBMS such as SQLite and MySQL are not case sensitive, so we can use lower case for the table names safely.
The forward slash / at the end of the URL must not be missed.

Then, let’s test the GET API using curl.

curl http://localhost:5000/user/

It returned all records in the table. However, what if we have too many entries in a table and want to do the pagination? Yes, sandman2 supports that out-of-box!

curl "http://localhost:5000/user/?page=1&amp;limit=2"
curl "http://localhost:5000/user/?page=2&amp;limit=2"

In this example, we use page parameter to specify the page number and the limit parameter to specify the page size. In page 1, we got the first two users Alice and Bob. Then, on page 2, we got the third row for Chris and only 1 row returned because there are no more rows.

2. Get rows by key

Since we defined the primary key, we can also query the table by it.

curl http://localhost:5000/user/3

3. Filter rows by any fields

We can also filter the rows by any fields.

curl "http://localhost:5000/user/?name=Chris"

Currently, there is a limitation that filter on comparison >, <, >=, <= is not supported.

4. Insert a new row

To insert a new row, we need to use the POST method.

curl -X POST -d "{"age":24, "name":"David"}" -H "Content-Type: application/json" http://localhost:5000/user/

Please be noticed that I’m using Windows so that the single quote won’t work in the cmd and I have to use backwards slashes to escape the double-quotes. You may not need those if you’re using Linux or Mac OS.

Also, we have set the "id" field as auto-increment when we create the USER table, so we don’t need to pass in the "id" values.

We can not go to the Web-based SQL client to check the table. The new user David is successfully inserted.

5. Updating a row

We can use the PATCH method to update a row.

curl -X PATCH -d "{"age":30}" -H "Content-Type: application/json" http://localhost:5000/user/3

As shown in the example, it is important to pass the current primary key "id" in the URL, which is "3" in our example. Sandman2 relies on the primary key to find the record to be updated.

If we go back to the web client, it can be seen that the age of Chris has been updated.

6. Deleting a row

We can use the DELETE method to delete a row.

curl -X DELETE -H "Content-Type: application/json" http://localhost:5000/user/3

After running the above CURL command, the user with "id=3" has been deleted.

7. Definition of a table

The sandman2 library supports not only the general data accessing API but also some other advanced API, such as getting the metadata of a table.

curl http://localhost:5000/user/meta

8. Export table to a CSV file

Finally, it also supports exporting data to a CSV file.

curl -o user.csv "http://localhost:5000/user/?export"

Summary

In this article, I have introduced an amazing Python library called sandman2. It is written in Python but you don’t need to write ANY Python code to use it. By simply running it with a valid database connection string, you will get

A Web-based SQL client
An entire set of data accessing RESTful API

It turns out that there is not too much security implementation in sandman2, so please never use it in production. However, security concerns usually mean customised implementation which is almost impossible to be generalised in an out-of-box toolset. Therefore, sandmand2 is one of the excellent libraries that can save us a lot of time in testing and experimenting.

Join Medium with my referral link – Christopher Tao

If you feel my articles are helpful, please consider joining Medium Membership to support me and thousands of other writers! (Click the link above)