DEV Community

Xiang Zhang for TiDB Cloud Ecosystem

Posted on • Updated on

Connecting Deepnote to TiDB Cloud Serverless Cluster

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

Image description

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 :-(

Image description

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:

  1. mysqlclient
  2. PyMySQL
  3. mysql-connector-python.

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

!pip install PyMySQL
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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

  1. 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.
  2. 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"
    }
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (2)

Collapse
 
jackiefrost profile image
Jack

maybe try this one TiDB Serverless

Collapse
 
shiyuhang0 profile image
shiyuhang0

Hope the MySQL integration can work with Serverless tier soon, or there is a TiDB Cloud integration.