Question: can i somehow use function jsonb_array_elements?

[felipecao]

What are you trying to do?

I'm using testcontainers-postgres==0.0.1rc1 combined with Flask-SQLAlchemy==3.1.1 and I have a somewhat complicated query that uses jsonb_array_elements. The query works fine when i run the code, but my tests fail with the following error message:

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       psycopg2.errors.UndefinedFunction: function jsonb_array_elements(json) does not exist
E       LINE 4: FROM jsonb_array_elements(invoice.invoice.current_attributes...
E                    ^
E       HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Therefore, I'm wondering whether there's a way to instrument how I interact with testcontainers so that I'm able to use jsonb_array_elements

Where are you trying to do it?

Provide a self-contained code snippet that illustrates the bug or unexpected behavior.
Ideally, include a link to a public repository with a minimal project where someone from the
testcontainers-python can submit a PR with a solution to the problem you are facing with the library.

I don't have an example on a public repo, but I can paste parts of the query below:

            select(...)
            .join(
                select(literal_column("value->>'value' as erp_supplier_id"))
                .select_from(func.jsonb_array_elements(InvoiceModel.current_attributes).alias("attrs"))
                .where(
                    literal_column("attrs.value->>'name'") == "erp_supplier_id",
                    literal_column("attrs.value->>'value'") != "N/A",
                    func.trim(literal_column("attrs.value->>'value'")) != "",
                    literal_column("attrs.value->>'value'").is_not(None)
                )
                .limit(1)
                .lateral("supplier_attr"),
                literal_column("true"),
                isouter=True
            )

and this is how i'm instrumenting testcontainers for all my tests:

import os
from unittest import TestCase
from urllib.parse import urlparse

import psycopg2
from sqlalchemy.orm import sessionmaker, scoped_session
from testcontainers.postgres import PostgresContainer

from app import create_app, db


class BaseRepositoryTest(TestCase):
    _postgres = PostgresContainer("postgres:15")

    @classmethod
    def setUpClass(cls):
        cls._postgres.start()

        cls.create_schemas(cls._postgres)
        os.environ["DATABASE_URL"] = cls._postgres.get_connection_url(cls._postgres.get_container_host_ip())

        cls.app = create_app("testing")
        cls.app_context = cls.app.app_context()
        cls.app_context.push()

        db.create_all()

    @classmethod
    def tearDownClass(cls):
        db.drop_all()
        cls.app_context.pop()
        cls._postgres.stop()

    @classmethod
    def create_schemas(cls, postgres: PostgresContainer):
        url = urlparse(postgres.get_connection_url())

        dbname = url.path.lstrip("/")
        user = url.username
        password = url.password
        host = url.hostname
        port = url.port

        connection = psycopg2.connect(
            dbname=dbname,
            user=user,
            password=password,
            host=host,
            port=port,
        )

        with connection.cursor() as cur:
            cur.execute("create schema my_schema;")
            connection.commit()

    def setUp(self):
        # Explicitly create a new connection and transaction
        self.connection = db.engine.connect()
        self.transaction = self.connection.begin()

        # Use sessionmaker to bind the session configuration to the connection
        session_factory = sessionmaker(bind=self.connection)
        self.session = scoped_session(session_factory)

        # Temporarily replace the session in use by the db object
        db.session = self.session

    def tearDown(self):
        # Ensure the database is emptied after tests
        self.transaction.rollback()
        db.session.remove()

Runtime environment

Provide a summary of your runtime environment. Which operating system, python version, and docker version are you using?
What is the version of testcontainers-python you are using? You can run the following commands to get the relevant information.

Paste the results of the bash below

uname -a
echo "------"
docker info
echo "------"
poetry run python --version
echo "------"
poetry show --tree

Darwin felipes-macbook-pro.home 23.6.0 Darwin Kernel Version 23.6.0: Mon Jul 29 21:14:30 PDT 2024; root:xnu-10063.141.2~1/RELEASE_ARM64_T6030 arm64
------
Client:
 Version:    27.4.0
 Context:    desktop-linux
 Debug Mode: false
 Plugins:
  ai: Ask Gordon - Docker Agent (Docker Inc.)
    Version:  v0.5.1
    Path:     /Users/felipe/.docker/cli-plugins/docker-ai
  buildx: Docker Buildx (Docker Inc.)
    Version:  v0.19.2-desktop.1
    Path:     /Users/felipe/.docker/cli-plugins/docker-buildx
  compose: Docker Compose (Docker Inc.)
    Version:  v2.31.0-desktop.2
    Path:     /Users/felipe/.docker/cli-plugins/docker-compose
  debug: Get a shell into any image or container (Docker Inc.)
    Version:  0.0.37
    Path:     /Users/felipe/.docker/cli-plugins/docker-debug
  desktop: Docker Desktop commands (Beta) (Docker Inc.)
    Version:  v0.1.0
    Path:     /Users/felipe/.docker/cli-plugins/docker-desktop
  dev: Docker Dev Environments (Docker Inc.)
    Version:  v0.1.2
    Path:     /Users/felipe/.docker/cli-plugins/docker-dev
  extension: Manages Docker extensions (Docker Inc.)
    Version:  v0.2.27
    Path:     /Users/felipe/.docker/cli-plugins/docker-extension
  feedback: Provide feedback, right in your terminal! (Docker Inc.)
    Version:  v1.0.5
    Path:     /Users/felipe/.docker/cli-plugins/docker-feedback
  init: Creates Docker-related starter files for your project (Docker Inc.)
    Version:  v1.4.0
    Path:     /Users/felipe/.docker/cli-plugins/docker-init
  sbom: View the packaged-based Software Bill Of Materials (SBOM) for an image (Anchore Inc.)
    Version:  0.6.0
    Path:     /Users/felipe/.docker/cli-plugins/docker-sbom
  scout: Docker Scout (Docker Inc.)
    Version:  v1.15.1
    Path:     /Users/felipe/.docker/cli-plugins/docker-scout

Server:
 Containers: 106
  Running: 2
  Paused: 0
  Stopped: 104
 Images: 6
 Server Version: 27.4.0
 Storage Driver: overlay2
  Backing Filesystem: extfs
  Supports d_type: true
  Using metacopy: false
  Native Overlay Diff: true
  userxattr: false
 Logging Driver: json-file
 Cgroup Driver: cgroupfs
 Cgroup Version: 2
 Plugins:
  Volume: local
  Network: bridge host ipvlan macvlan null overlay
  Log: awslogs fluentd gcplogs gelf journald json-file local splunk syslog
 CDI spec directories:
  /etc/cdi
  /var/run/cdi
 Swarm: inactive
 Runtimes: runc io.containerd.runc.v2
 Default Runtime: runc
 Init Binary: docker-init
 containerd version: 472731909fa34bd7bc9c087e4c27943f9835f111
 runc version: v1.1.13-0-g58aa920
 init version: de40ad0
 Security Options:
  seccomp
   Profile: unconfined
  cgroupns
 Kernel Version: 6.10.14-linuxkit
 Operating System: Docker Desktop
 OSType: linux
 Architecture: aarch64
 CPUs: 11
 Total Memory: 7.653GiB
 Name: docker-desktop
 ID: 3b4683d1-31f6-498c-8fd1-1f5f722d431e
 Docker Root Dir: /var/lib/docker
 Debug Mode: false
 HTTP Proxy: http.docker.internal:3128
 HTTPS Proxy: http.docker.internal:3128
 No Proxy: hubproxy.docker.internal
 Labels:
  com.docker.desktop.address=unix:///Users/felipe/Library/Containers/com.docker.docker/Data/docker-cli.sock
 Experimental: false
 Insecure Registries:
  hubproxy.docker.internal:5555
  127.0.0.0/8
 Live Restore Enabled: false

WARNING: daemon is not using the default seccomp profile
------

[tool.poetry] section not found in /Users/felipe/code/autonifai/autonifai-backend/pyproject.toml
------