# Dynamic creation of REST APIs from third-party databases with Django-Schema-Sprout Working with third-party or legacy databases can be a real headache. We've all been there - you're stuck dealing with a database you didn't design, and every change feels like a nightmare. Django's `inspectdb` helps create models from an existing schema, but it's not perfect, especially when the legacy database has tons of tables and views. What's more, if there is one change in the database, you're back to square one, needing to regenerate everything manually. That's why, some time ago, I decided to create [django-schema-sprout](https://pypi.org/project/django-schema-sprout/). It's a package designed to make your life easier, allowing you to dynamically create Django models, views, and serializers straight from the database schema. Currently, there is only support for Postgres databases, but MySQL support and some additional features are on the roadmap. ### Live example (TL;DR You can find the repository with code here [https://github.com/grumpy-miner/django-schema-sprout-example](https://github.com/grumpy-miner/django-schema-sprout-example)) One of the third-party databases that I used to work with was AACT (*Aggregate Analysis of*[*ClinicalTrials.gov*](https://aact.ctti-clinicaltrials.org/)*).* It contains all information (protocol and result in data elements) about every study registered in [ClinicalTrials.gov](http://ClinicalTrials.gov) You can get access to the Postgres database and start querying all the results, but what if you want to create Django ORM models and use them in your application? Well, let's start with creating a new Django project. (Since AACT uses Postgres v11 we'll be using Django==4.1.13) ```bash django-admin startproject django-schema-sprout-example ``` Install django-schema-sprout: ```bash pip install django-schema-sprout==0.1.1 ``` If needed also install (psycopg2). ### Updating `settings.py` In your settings, add `rest_framework`, `drf_yasg` and `django_schema_sprout` into the list of installed apps ```python INSTALLED_APPS = [ ... "rest_framework", "drf_yasg", "django_schema_sprout", ] ``` Set up your database and add `SchemaSproutDBRouter` in your database routers ```python DATABASES = { "default": { "ENGINE": "django.db.backends.sqlite3", "NAME": BASE_DIR / "db.sqlite3", }, "aact": { "ENGINE": "django.db.backends.postgresql_psycopg2", "NAME": "aact", "USER": os.getenv("AACT_USER"), "PASSWORD": os.getenv("AACT_PASSWORD"), "HOST": os.getenv("AACT_HOST"), "PORT": os.getenv("AACT_PORT"), }, } DATABASE_ROUTERS = [ "django_schema_sprout.router.SchemaSproutDBRouter", ] ``` Last but not least configure `STATIC_ROOT` and `REST_FRAMEWORK` ```python STATIC_ROOT = os.path.join(BASE_DIR, "staticfiles") REST_FRAMEWORK = { "DEFAULT_PAGINATION_CLASS": "rest_framework.pagination.LimitOffsetPagination", "PAGE_SIZE": 100, # we don't want to limit page size as there are a lot of records in AACT database } ``` ### Add `schema_sprout.py` file ```python from django_schema_sprout.schema_sprout import SchemaSprout AACT_PUBLIC_SPROUT = SchemaSprout("aact") # pass the name of your database from settings AACT_PUBLIC_SPROUT.create_models(readonly=True) # Default False, we have readonly access to AACT db ``` By the end of the day, it doesn't matter where you instantiate `SchemaSprout` as it is a Singeltone based on args, additionally `create_models` and `create_model` function will do nothing if the model is already dynamically created (unless there were new tables added). ### Add your sprout router URLs to urlpatterns What we want to do now, is to update our `urls.py`. Let's add URLs for swagger and router URLs from `AACT_PUBLIC_SPROUT` ```python from django.urls import path, re_path, include from rest_framework import permissions from drf_yasg.views import get_schema_view from drf_yasg import openapi from sprout_schema_example.schema_sprout import AACT_PUBLIC_SPROUT schema_view = get_schema_view( openapi.Info( title="AACT API", default_version="1.0.0", description="Documentation for the AACT API", ), public=False, permission_classes=(permissions.AllowAny,), ) urlpatterns = [ path("api/", include(AACT_PUBLIC_SPROUT.router.urls)), re_path( r"^swagger(?P\.json|\.yaml)$", schema_view.without_ui(cache_timeout=0), name="schema-json", ), path( "swagger/", schema_view.with_ui("swagger", cache_timeout=0), name="schema-swagger-ui", ), path("redoc/", schema_view.with_ui("redoc", cache_timeout=0), name="schema-redoc"), ] ``` #### Run collectstatic and runserver ```bash ./manage.py collectstatic ./manage.py runserver ``` Since we create all models, views, and serializers at the start it will take some time (especially since there are tons of tables in the database). You can see some warnings at the beginning as there are not all correct permissions set to visible tables. ```bash WARNING:root:Couldn't get table description for table univ_als_studies. Reason: permission denied for table univ_als_studies WARNING:root:Couldn't get table description for table univ_studies. Reason: permission denied for table univ_studies WARNING:root:Couldn't get table description for table universities. Reason: permission denied for table universities WARNING:root:Couldn't get table description for table covid_study_ids. Reason: permission denied for table covid_study_ids ``` Django-Schema-Sprout should create an endpoint for each table in third-party database. Url schema for those tables follows the pattern `api//` You can go to check out Swagger at http://127.0.0.1:8000/swagger/ and see all endpoints that were created. ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710514204144/995f73ee-a172-4f43-a67b-47f20ec7df6f.png align="center") ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710514094397/e57af120-5f6c-4627-8575-88cac69499a3.png align="center") Let's try out requesting one of the endpoints: ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710514279377/edaffa50-0b54-45a4-8eb0-2fe70ea99388.png align="center") ### Creating only specific models. If you want to create endpoints just for specific tables instead of everything you can use `create_model` instead of `create_models` Let's modify `schema_sprout.py`, let's also see what will happen if we don't want to create a model with `readonly=True` ```python from django_schema_sprout.schema_sprout import SchemaSprout AACT_PUBLIC_SPROUT = SchemaSprout("aact") AACT_PUBLIC_SPROUT.create_model(table_name='studies', table_nspname='ctgov', readonly=False) ``` Now we have all CRUD methods for studies ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710514697611/0929945e-6c87-474a-97c9-f6c1ff31a681.png align="center") In this case, trying to update or delete any records will raise an error as we don't have permission to do that ![](https://cdn.hashnode.com/res/hashnode/image/upload/v1710514871625/4ffbfa40-dfac-4aed-a093-e0d3f7354185.png align="center") ### Using Django-Schema-Sprout's dynamically created models Sometimes we need to create a view that will apply some additional business logic. Let's see how we can use the models that django-schema-sprout generated. Let's run `./manage.py shell` ```python from django_schema_sprout.schema_sprout import SchemaSprout schema_sprout = SchemaSprout("aact") schema_sprout.create_models(readonly=True) # let's get model, use '_' to access model CtgovDesigns = schema_sprout.models.get('ctgov_designs') CtgovDesigns.objects.all().count() # >>> 482207 ``` ### That's all folks So, that's the scoop on `django-schema-sprout`. If you've ever felt the pain of dealing with third-party databases in Django, this tool might just make your day a bit better. I'm keen to see how `django-schema-sprout` evolves with your input. It's open source for a reason – to get better with every contribution, idea, or piece of feedback. So, if you've got something to say or want to add your touch to the project, don't hold back, feel free to open a Pull Request or open an Issue at [https://github.com/grumpy-miner/django-schema-sprout](https://github.com/grumpy-miner/django-schema-sprout) Give `django-schema-sprout` a whirl in your next project.