Flask-SQLAlchemy on App Engine connect to MSSQL da

Question

I'm very new to the GCP as a whole, and I need to deploy a Flask app for a project with a client. Deploying an app is simple enough given all of the docs Google has provided, and since using the flexible app engine seems like the easiest way to do it, that's what I'm trying to use.

The issue I'm having though is in trying to connect to an MSSQL database that was setup on a Compute Engine. So far, I've connected to the database locally using pyodbc with some help from Connect to MSSQL Database using Flask-SQLAlchemy.

I was certain running gcloud app deploy would not work, and sure enough it wasn't able to install the pyodbc module. I figured that that wouldn't be the way to go anyway, and based on this page of the docs, it seems like I should be able to connect to the compute engine via its internal IP address.

I don't know how to proceed from here though, because everything in the docs wants me to use a Cloud SQL instance, but given that this data was provided by a client and I'm working on their GCP project, I'm a bit limited to the scenario I've described above.

Answer 1

This has since been resolved.

The issue was that there were no ODBC drivers downloaded on the server, so I needed to create a custom runtime with a Dockerfile in order to first install the drivers.

My solution was greatly aided by this solution: Connect docker python to SQL server with pyodbc

The steps are as follows:

Run gcloud beta app gen-config --custom in your flask app's directory.

Inside of the now created Dockerfile, add these lines before installing the pip requirements.

#Install FreeTDS and dependencies for PyODBC
RUN apt-get update
RUN apt-get install -y tdsodbc unixodbc-dev
RUN apt install unixodbc-bin -y
RUN apt-get clean -y
ADD odbcinst.ini /etc/odbcinst.ini

The file odbcinst.ini should contain the following lines:

[FreeTDS]
Description=FreeTDS Driver
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so

After that gcloud app deploy should work just fine.