Repository: apify/crawlee-python
File: src/crawlee/storage_clients/_sql/_storage_client.py (Python)

Code before:
        """Async context manager exit."""
        await self.close()

    @property
    def engine(self) -> AsyncEngine:
        """Get the SQLAlchemy AsyncEngine instance."""
        if self._engine is None:
            raise ValueError('Engine is not initialized. Call initialize() before accessing the engine.')
        return self._engine

    def get_dialect_name(self) -> str | None:
        """Get the database dialect name."""
        return self._dialect_name

    async def initialize(self, configuration: Configuration) -> None:
        """Initialize the database schema.

        This method creates all necessary tables if they don't exist.
        Should be called before using the storage client.
        """
        if not self._initialized:
            engine = self._get_or_create_engine(configuration)
            async with engine.begin() as conn:
                self._dialect_name = engine.dialect.name

                if self._dialect_name not in {'sqlite', 'postgresql', 'mysql', 'mariadb'}:
                    raise ValueError(
                        f'Unsupported database dialect: {self._dialect_name}. Supported: sqlite, postgresql, mysql, '
                        'mariadb. Consider using a different database.',
                    )

                # Create tables if they don't exist.
                # Rollback the transaction when an exception occurs.
                # This is likely an attempt to create a database from several parallel processes.
                try:
                    # Set SQLite pragmas for performance and consistency
                    if self._default_flag:
                        await conn.execute(text('PRAGMA journal_mode=WAL'))  # Better concurrency
                        await conn.execute(text('PRAGMA synchronous=NORMAL'))  # Balanced safety/speed
                        await conn.execute(text('PRAGMA cache_size=100000'))  # 100MB cache
                        await conn.execute(text('PRAGMA temp_store=MEMORY'))  # Memory temp storage
                        await conn.execute(text('PRAGMA mmap_size=268435456'))  # 256MB memory mapping
                        await conn.execute(text('PRAGMA foreign_keys=ON'))  # Enforce constraints
                        await conn.execute(text('PRAGMA busy_timeout=30000'))  # 30s busy timeout
                    await conn.run_sync(Base.metadata.create_all, checkfirst=True)
                    from crawlee import __version__  # Noqa: PLC0415

                    db_version = (await conn.execute(select(VersionDb))).scalar_one_or_none()

                    # Raise an error if the new version creates breaking changes in the database schema.
                    if db_version and db_version != __version__:

Review comment (suggestion):
Maybe this set could saved in something like `self._supported_dialects` or `cls._self._supported_dialects` ? It could be re-used later in the error string and also in other places




Code after:
        exc_traceback: TracebackType | None,
    ) -> None:
        """Async context manager exit."""
        await self.close()

    @property
    def engine(self) -> AsyncEngine:
        """Get the SQLAlchemy AsyncEngine instance."""
        if self._engine is None:
            raise ValueError('Engine is not initialized. Call initialize() before accessing the engine.')
        return self._engine

    def get_dialect_name(self) -> str | None:
        """Get the database dialect name."""
        return self._dialect_name

    async def initialize(self, configuration: Configuration) -> None:
        """Initialize the database schema.

        This method creates all necessary tables if they don't exist.
        Should be called before using the storage client.
        """
        if not self._initialized:
            engine = self._get_or_create_engine(configuration)
            async with engine.begin() as conn:
                self._dialect_name = engine.dialect.name

                if self._dialect_name not in self._SUPPORTED_DIALECTS:
                    raise ValueError(
                        f'Unsupported database dialect: {self._dialect_name}. Supported: '
                        f'{", ".join(self._SUPPORTED_DIALECTS)}. Consider using a different database.',
                    )

                # Create tables if they don't exist.
                # Rollback the transaction when an exception occurs.
                # This is likely an attempt to create a database from several parallel processes.
                try:
                    # Set SQLite pragmas for performance and consistency
                    if self._default_flag:
                        await conn.execute(text('PRAGMA journal_mode=WAL'))  # Better concurrency
                        await conn.execute(text('PRAGMA synchronous=NORMAL'))  # Balanced safety/speed
                        await conn.execute(text('PRAGMA cache_size=100000'))  # 100MB cache
                        await conn.execute(text('PRAGMA temp_store=MEMORY'))  # Memory temp storage
                        await conn.execute(text('PRAGMA mmap_size=268435456'))  # 256MB memory mapping
                        await conn.execute(text('PRAGMA foreign_keys=ON'))  # Enforce constraints
                        await conn.execute(text('PRAGMA busy_timeout=30000'))  # 30s busy timeout
                    await conn.run_sync(Base.metadata.create_all, checkfirst=True)
                    from crawlee import __version__  # Noqa: PLC0415

                    db_version = (await conn.execute(select(VersionDb))).scalar_one_or_none()