Connecting Deepnote to TiDB Cloud Serverless Cluster

#mysql #tidbcloud #deepnote

Deepnote is a new kind of data notebook beyond Jupyter. While investigating how to connect Deepnote to TiDB Cloud Serverless Tier(a serverless MySQL-compatible cloud database and TiDB Cloud Serverless Tier enforce TLS connections), I found it's not that easy.

There are two ways connecting Deepnote notebooks to MySQL, one is using Deepnote's integrations, one is directly from code.

Using integration

Deepnote provides a built-in MySQL integration, you could use it to connect to MySQL. But unfortunately, when it involves TLS, it just does not work. As you could see in the image, it says "Connections between Deepnote and your data source are encrypted by SSL (TLS)." but there is no place to specify server's cert. Maybe it uses the system bundled CAs to verify? If it does so, TiDB Cloud Serverless Tier uses ISRG X1 root and it could definitely work. But not :-(

Connecting from code

Integration does not work, but nothing prevents us connecting directly from code. To connect to MySQL, first you need a driver. There are three popular Python MySQL drivers:

mysqlclient
PyMySQL
mysql-connector-python.

PyMySQL would be easy to install since it's pure Python, you just need to

!pip install PyMySQL

at top of the notebook. But I used to use mysqlclient. It's not that easy to make everything work if you want to use mysqlclient.

First, simply

!pip install mysqlclient

won't work. mysqlclient is a Python wrapper of libmysqlclient, so you need to install libmysqlclient first. According to https://deepnote.com/docs/custom-environments#default-environment , Deepnote use Debian Buster, so the installation would be

!apt install -y default-libmysqlclient-dev
!pip install mysqlclient

Then when using mysqlclient to securely connecting to MySQL, there are also two points to notice:

We are in Debian Buster, the installed libmysqlclient is actually libmariadbclient. libmaradbclient's interface is not same as libmysqlclient. It leads to mysqlclient doesn't accept ssl_mode tls argument like in other platforms.
We want to connect securely, definitely we want to verify the server's cert. As I said above, TiDB Cloud Serverless Tier uses ISRG X1 root, which is commonly in system built-in CA bundle. You could specify the CA as

ssl={
      "ca": "/etc/ssl/certs/ca-certificates.crt"
    }

For more info about connecting to TiDB Cloud Serverless Cluster securely, you could refer to https://docs.pingcap.com/tidbcloud/secure-connections-to-serverless-tier-clusters .

UPDATE on 2023-03-03: DeepNote has fixed the problem and complete a whole feature for connecting securely https://deepnote.com/docs/securing-connections.