Accessing postgres database from python kernel

[butsuri43]

Problem

Accessing Postgres database from jupyterlite (pyodide).

I would like to start a discussion what is currently possible and what are potential options for the future.

Suggested Improvement

I have been experimenting with this and here is what I found:

• none- of the SQL libraries I tried worked. psycopg2, py-postgresql, asyncpg and PyGreSQL are not pure pythonic, so already the installation fails. psycopg installs without error, but fails on import (ImportError: no pq wrapper available.). pg8000 also installs, but fails connecting raising error on execution of socket.create_connection.

• the one way that works for me is to connect to the database through a REST API proxy that can be easily set-up with https://postgrest.org/en/stable/. Here is a minimal example that works for me: 1) docker-compose.yml file for setting up an example database and the proxy-server:

    version: '3'
    services:
      server:
        image: postgrest/postgrest
        ports:
          - "3000:3000"
        environment:
          PGRST_DB_URI: postgres://app_user:password@db:5432/app_db
          PGRST_OPENAPI_SERVER_PROXY_URI: http://127.0.0.1:3000
          PGRST_DB_ANON_ROLE: web_anon
        depends_on:
          - db
      db:
        image: postgres
        ports:
          - "5432:5432"
        environment:
          POSTGRES_DB: app_db
          POSTGRES_USER: app_user
          POSTGRES_PASSWORD: password
        volumes:
          - ./initdb.sql:/docker-entrypoint-initdb.d/initdb.sql

  2) initdb.sql file with database initial setup:

    create table todos (
      id serial primary key,
      done boolean not null default false,
      task text not null,
      due timestamptz
    );
    insert into todos (done,task) values
      (True,'finish tutorial 0'), (False,'pat self on back');
    create role web_anon nologin;
    grant select on todos to web_anon;

  3) Run docker compose up in the directory with both files to spin-up both servers 4) Inside jupyterlite (e.g. the demo one: https://jupyterlite.readthedocs.io/en/latest/_static/lab/index.html) run:

    %pip install requests
  
    import requests
    import pyodide_http
    pyodide_http.patch_all() # see https://jupyterlite.readthedocs.io/en/latest/howto/content/python.html#fetching-remote-content
    x = requests.get('http://localhost:3000/todos')
    x.json()

  This method works, but requires using postgrest queries instead of standard SQL which is not so ideal...

Are there any options available?

[oscar6echo]

@butsuri43
In my view the easiest and most scalable solution is to convert SQL to GraphQL.

How ? The (or at least one of the) most popular and battle tested GraphQL engine is hasura.
It is open source and I made a demo repo PostgreSQL + Hasura + JupyterLab | JupyterLite for local testing:

• docker compose backend with postgresql, hasura, pgadmin4
• test GraphQL on hasura console
• test GraphQL in JLab notebook
• test GraphQL in JLite notebook

We use it in production, so I can confidently assert that hasura is a fine piece of software - and so is JupyterLite :smiley:

cc @jtpio as making databases accessible from JupyterLite was the subject of a conversation at JupyterCon.

[jtpio]

Thanks @butsuri43 and @oscar6echo.

Having a guide in the JupyterLite documentation to provide pointers for such use cases would be great. This is not a feature that needs to be implemented in JupyterLite directly, and it could likely be used in JupyterLab for example.

It is open source and I made a demo repo PostgreSQL + Hasura + JupyterLab | JupyterLite for local testing:

Thanks for sharing! The link seems to be giving a 404 at the moment though.

[oscar6echo]

Oups - it was a private repo - now public - bad habit from corp world..

Indeed this is a JupyterLite topic as far as user workflows should be catered to, but it is completely separate in terms of implementation.

Note that this topic - accessing database through api - is not unrelated to the broad authentication topic, which should (partially) be implemented in JupyterLite (custom deployments).