API from a remote database

Let's go create a Django REST API from a remote DB with Django 3.

It can be interesting because I should be able to provide an API from a tool by accessing only to his DB. This whatever the system used (Python, PHP, Java ...) and whatever my own knowledge of the system used.

In my development folder:

> django-admin startproject my_apis

Here my project will manage APIs, that is why I call it my_apis, it will only be used for that. But you can create an API in an existing Django project.

So I would do an API targeting a remote tool, with his own DB. First let's go create our Django app in my_apis folder, named app_remote1, then migrations and a super-user:

> python manage.py startapp app_remote1
> python manage.py migrate
> python manage.py createsuperuser
...
> python manage.py runserver

app_remote1 refers to the remote tool. This app will contain all information about our API (models and other Django files, the API itself ...). Indeed to build an Django REST API, we need a basic Django model.

Now if you start the server, you access to http://127.0.0.1:8000/ and http://127.0.0.1:8000/admin.

Remote connection

OK, let's go adding our remote DB into our Django instance. In settings.py: 

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',
        'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    },
 
    'db_remote1': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': 'db',
        'USER': 'user-db',
        'PASSWORD': 'password',
        'HOST': 'host',
        'OPTIONS': {'init_command': "SET sql_mode='STRICT_TRANS_TABLES'"}
    },
}

OK. But we need a router to guide our queries when we will use the remote DB in our models. Thanks to books.agiliq where I took the code below. Beside your settings.py, a new router_remote1.py file:

class MyRouter1:
 
    def db_for_read(self, model, **hints):
        if model._meta.app_label == 'app_remote1':
            return 'db_remote1'
        return None
 
    def db_for_write(self, model, **hints):
        if model._meta.app_label == 'app_remote1':
            return 'db_remote1'
        return None
 
    def allow_relation(self, obj1, obj2, **hints):
        if obj1._meta.app_label == 'app_remote1' or \
           obj2._meta.app_label == 'app_remote1':
           return True
        return None
 
    def allow_migrate(self, db, app_label, model_name=None, **hints):
        if app_label == 'app_remote1':
            return db == 'db_remote1'
        return None

You understand: now each time we will need this remote database in our code, we will be able to point on it mentioning his app_label.

Integrate your router adding this in settings.py:

DATABASE_ROUTERS = [
    'my_apis.router_remote1.MyRouter1',
]

Only now we can use our remote DB in our model, app_remote1\models.py:

from django.db import models
 
class ModelFromRemoteTable1(models.Model):
    id = models.AutoField(primary_key=True)
    name = models.CharField(max_length=200)
    email = models.EmailField()
    country = models.CharField(max_length=200)
 
    class Meta:
        app_label = 'app_remote1'
        db_table = 'your_table'
        managed = False
 
    def __str__(self):
         return str(self.name)

Of course the used table and his fields must exist. Look the meta managed = False, very important to not migrate your table with Django ...

Also very important the app_label to redirect to the well database, the remote.

Now display our remote data in Django with app_remote1\admin.py:

from django.contrib import admin
from .models import ModelFromRemoteTable1
 
class RemoteTable1Admin(admin.ModelAdmin):
 
    list_display = ('id', 'name', 'email', 'country')
    list_display_links = None
    search_fields = ['name', 'email', 'country']
 
    actions = None
    enable_change_view = False
 
    def has_add_permission(self, request):
        return False
 
    def has_change_permission(self, request):
        return False
 
    def has_delete_permission(self, request, obj=None):
        return False
 
admin.site.register(ModelFromRemoteTable1, RemoteTable1Admin)

Look, we do careful to limit authorizations. Indeed we do not want to allow edit from our Django admin, we just build an API, we need the model only. The admin will allow to read our data, it is enough.

Integrate your app adding this in settings.py:

INSTALLED_APPS = [
...
 
    'app_remote1',
    'rest_framework',
]

Now if you start your server, you access to the remote data.

API

OK, let's go create a serializer, in a new file app_remote1\serializers.py:

from rest_framework import serializers
from rest_framework.reverse import reverse
 
from .models import ModelFromRemoteTable1
 
class AppRemote1Serializer(serializers.HyperlinkedModelSerializer):
 
    class Meta:
        model = ModelFromRemoteTable1
        fields = ['url', 'name', 'email', 'country']

Now we can create our API, as a view, in app_remote1\views.py:

from django.shortcuts import render
from .models importModelFromRemoteTable1
 
from rest_framework import viewsets
from rest_framework import permissions
 
from app_remote1.serializers import AppRemote1Serializer
 
 
class AppRemote1ViewSet(viewsets.ModelViewSet):
    queryset =ModelFromRemoteTable1.objects.all().order_by('id')
    serializer_class =AppRemote1Serializer
    permission_classes = [permissions.IsAdminUser]

Let's go displaying our API in app_remote1\urls.py:

from django.contrib import admin
from django.urls import include, path
from rest_framework import routers
from app_remote1 import views
 
router = routers.DefaultRouter()
router.register(r'app_remote1', views.AppRemote1ViewSet)
 
urlpatterns = [
    path('admin/', admin.site.urls),
    path('', include(router.urls)),
    path('api-auth/', include('rest_framework.urls', namespace='rest_framework'))
]

Look the router: we need it to display our API, but it has nothing to do with our first router above. Indeed our first router just handles the remote connection, this second router handles our API.

Hop! Do not forget to add a pagination limit in your settings.py. Without your json will slow down, maybe crashing the page:

REST_FRAMEWORK = {
    'DEFAULT_PAGINATION_CLASS': 'rest_framework.pagination.LimitOffsetPagination',
    'PAGE_SIZE': 100
}

And go to http://127.0.0.1:8000/, you get your remote API.