Proposal for a new SQLiteStore implementation in zarr-python v3

[auxym]

Background

A proposal for a ZipStore spec is being discussed here: https://github.com/zarr-developers/zarr-specs/pull/311/files

ZipStore would be a convenient way to implement single-file zarr storage. Single file stores can be useful for many use cases which are discussed in this proposal.

There is also broader discussion around other archive formats here: zarr-developers/zarr-specs#209. In this issue, it was suggested I open another issue in order to discuss the imlementation of a single-file store based on sqlite.

Proposal

I'd like to propose bringing back SQLite storage as an alternative to, or possibly even a replacement for, zip storage. I say bring back, as sqlite storage was available in zarr-python v2. Sqlite would bring the following advantages over zip:

• Mainly: supports key deletion, overwriting and setting partial values. This would allow the store to support all array operations such as deleting, overwriting, resizing and appending. ZipStore supports adding new arrays only, but not resizing or appending to them.
• sqlite is rather ubiquitous. Maybe not as much as zip (zip is built-in many OS default file browsers, for example), but you can find implementations of sqlite in basically any programming language on any platform.
• Provides at least some protection against data loss in case of a crash or power loss. sqlite itself provides full DBMS-like ACID guarantees. In the context of this proposal, this mostly means that the set operation is atomic: a value will be either fully and durably written to a key, or not at all. In constrast, a zipstore could be made unreadable (or very difficult to recover) if, for example, there is a crash before the file directory (footer) is written. This issue plagues HDF5 in a way, my understanding is that a crash during a write on a HDF5 file can render the whole file unreadable. I understand the HDF5 group had started to implement a rollback journal to solve this, but it was abandoned. By basing a zarr single-file store on sqlite, we get journaling for free, essentially.

Draft implementation

https://github.com/auxym/zarr-sqlite

This implementation re-uses the same database schema as in zarr-python v2 and many of the same SQL statements, but it implements zarr-python v3's Store ABC.

This experimental implementation has the following limitations:

• The implementation relies on the standard library sqlite3 library and is not async. Async implementation would require either a 3rd party lib (for example https://aiosqlite.omnilib.dev/en/stable/index.html) or implementing our own async wrapper around a worker thread for example.

• Does not support partial writes for the moment because I didn't take the time to implement it, but it should be possible through sqlite's "blobopen" API.

• I've done only some rudimentary testing, there are probably many bugs or oversights remaining.

Notes on performance

I ran some very quick "write" benchmarks, and sqlitestore appears to be up to 2X faster than LocalStore on "small" arrays or chunks, up to around 100 kb (2X at 10 kb, 1.2X at 100 kb), but slower on larger chunks (0.6X at 1 MB).

Nevertheless, speed is not the main reason behind the sqlite store, and given the differences, I'd call the performance "roughly equivalent" to local storage.

Possible implementation of atomic writes of array data and metadata

SQLite could in theory support an atomic transaction where all chunks of an array as well as the array metadata are written as part of the same transaction and then committed. However, this would require higher level support in the storage API. The current implementation commits after every single "set" operation.

Final notes

Is there any interest in integrating an SQLite-backed single-file store in zarr-python and/or in the zarr specification? If so, I can work towards cleaning up the code, adding more tests and submitting a PR (or a draft spec).

[d-v-b]

this is awesome work! Since sqlite support comes with python, adding this zarr-python wouldn't add dependencies, but it would add a bit of maintenance. Personally I'd be interested in seeing it in zarr-python mainly to ensure that there's an alternative to zip archives, but I'd be curious to hear from other zarr-python devs about whether this would be good fit.

[MosGeo]

Here is my implementation. I used aiosqlite for async. I have not done extensive testing but it seems to work. I can create and retrieve data from it. I've also played around with implementing duckdb store. It also works but that is not async. I still have not done benchmarking to see which is better.

from __future__ import annotations

import asyncio
import logging
from pathlib import Path
from typing import TYPE_CHECKING, Iterable, Optional

import aiosqlite
from zarr.abc.store import ByteRequest, Store
from zarr.core.buffer import Buffer
from zarr.core.buffer.core import default_buffer_prototype
from zarr.core.common import concurrent_map
from zarr.storage._utils import _normalize_byte_range_index

if TYPE_CHECKING:
    from collections.abc import AsyncIterator

    from zarr.core.buffer import BufferPrototype


logging.basicConfig(level=logging.ERROR)


class SQLiteStore(Store):
    """
    A Zarr store implementation backed by an SQLite database.
    This class provides support for reading, writing, deleting, partial writes, and listing of Zarr array data using an SQLite backend.
    """

    supports_writes: bool = True
    supports_deletes: bool = True
    supports_partial_writes: bool = True
    supports_listing: bool = True

    _filename: Path
    _lock: asyncio.Lock
    _connection: Optional[aiosqlite.Connection] = None

    ZARR_TABLE_NAME = "ZARR_TABLE"

    def __init__(
        self,
        filename: Path | str,
        *,
        read_only: bool = False,
    ) -> None:
        # docstring inherited
        self._logger = logging.getLogger("SQLiteStore")

        super().__init__(read_only=read_only)
        if isinstance(filename, str):
            filename = Path(filename)
        if not isinstance(filename, Path):
            raise TypeError(f"'filename' must be a string or Path instance. Got an instance of {type(filename)} instead.")

        # Finalize the prepare things
        self._filename = filename
        self._lock = asyncio.Lock()

    async def _open(self) -> None:
        # docstring inherited
        async with self._lock:
            connection = await aiosqlite.connect(self._filename, check_same_thread=False, detect_types=0, isolation_level=None)
            await connection.execute(f"""CREATE TABLE IF NOT EXISTS {self.ZARR_TABLE_NAME} (key TEXT PRIMARY KEY, value BLOB)""")
            await connection.commit()
            self._connection = connection

        await super()._open()

    async def clear(self) -> None:
        # docstring inherited
        self._check_writable()
        async with self._lock:
            await self._connection.execute(f"""DROP TABLE {self.ZARR_TABLE_NAME}""")
            await self._connection.execute(f"""CREATE TABLE {self.ZARR_TABLE_NAME} (key TEXT PRIMARY KEY, value BLOB)""")
            await self._connection.commit()

    def __eq__(self, other: object) -> bool:
        # docstring inherited
        return isinstance(other, type(self)) and self._filename == other._filename

    async def delete(self, key: str) -> None:
        """Remove a key from the store."""
        # docstring inherited
        self._check_writable()

        # delete the key
        async with self._lock:
            curser = await self._connection.execute(f"DELETE FROM {self.ZARR_TABLE_NAME} WHERE key = ?", (key,))
            if curser.rowcount == 0:
                raise KeyError(key)
            await self._connection.commit()

    async def exists(self, key: str) -> bool:
        # docstring inherited
        cursor = await self._connection.execute(f"SELECT 1 FROM {self.ZARR_TABLE_NAME} WHERE key = ? LIMIT 1", (key,))
        one_item = await cursor.fetchone()
        return one_item is not None

    async def get(self, key: str, prototype: BufferPrototype | None = None, byte_range: ByteRequest | None = None) -> Buffer | None:
        # docstring inherited
        self._logger.info(f"Get function started with key {key}.")

        # Create a buffer if needed
        if prototype is None:
            prototype = default_buffer_prototype()

        # Make sure it is open
        if not self._is_open:
            await self._open()

        # Get the data
        cursor = await self._connection.execute(f"SELECT value FROM {self.ZARR_TABLE_NAME} WHERE key = ?", (key,))
        value = await cursor.fetchone()

        # Get the informaton (right now it ignores the byte range)
        if value is None:
            return None

        # Convert the reuslts to buffer
        buffer = prototype.buffer.from_bytes(value[0])

        if byte_range is not None:
            start, stop = _normalize_byte_range_index(buffer, byte_range)
            buffer = prototype.buffer.from_buffer(buffer[start:stop])

        return buffer

    async def set(self, key: str, value: Buffer, byte_range: tuple[int, int] | None = None) -> None:
        """Store a (key, value) pair.

        Parameters
        ----------
        key : str
        value : Buffer
        """
        self._logger.info(f"Set function started with key {key}.")

        self._check_writable()

        # Get the informaton (right now it ignores the byte range)
        async with self._lock:
            if byte_range is not None:
                cursor = await self._connection.execute(f"SELECT value FROM {self.ZARR_TABLE_NAME} WHERE key = ?", (key,))
                buf = await cursor.fetchone()
                buf[byte_range[0] : byte_range[1]] = value
            else:
                buf = value

            await self._connection.execute(f"REPLACE INTO {self.ZARR_TABLE_NAME} (key, value) VALUES (?, ?)", (key, buf.as_buffer_like()))
            await self._connection.commit()

    async def list(self) -> AsyncIterator[str]:
        # docstring inherited
        async with self._connection.execute(f"SELECT key FROM {self.ZARR_TABLE_NAME}") as cursor:
            async for (key,) in cursor:
                yield key

    async def list_prefix(self, prefix: str) -> AsyncIterator[str]:
        # docstring inherited
        self._logger.info(f"Listing [refix started with prefix {prefix}.")

        prefix = prefix + "%"
        async with self._connection.execute(f"SELECT key FROM {self.ZARR_TABLE_NAME} WHERE key LIKE ?", (prefix,)) as cursor:
            async for (key,) in cursor:
                yield key

    async def list_dir(self, prefix: str) -> AsyncIterator[str]:
        # docstring inherited
        self._logger.info(f"Listing dir started with prefix {prefix}.")

        if prefix != "" and prefix[-1] != "/":
            prefix = prefix + "/"

        like_prefix = prefix + "%"
        len_prefix = len(prefix)

        query = f"""
            SELECT DISTINCT 
                CASE
                    WHEN instr(substr(key, ?), '/') = 0 THEN substr(key, ?)
                    ELSE substr(key, ?, instr(substr(key, ?), '/') - 1)
                END AS entry
            FROM {self.ZARR_TABLE_NAME}
            WHERE key LIKE ?
        """

        params = (
            len_prefix + 1,
            len_prefix + 1,
            len_prefix + 1,
            len_prefix + 1,
            like_prefix,
        )

        async with self._connection.execute(query, params) as cursor:
            async for (key,) in cursor:
                yield key

    async def set_partial_values(self, key_start_values: Iterable[tuple[str, int, bytes | bytearray | memoryview]]) -> None:
        # docstring inherited
        self._check_writable()

        # All the key-ranges arguments goes with the same prototype
        async def _set(self, key: str, value: Buffer, byte_range: tuple[int, int] | None = None) -> None:
            return await self.set(key, value, byte_range=byte_range)

        args = []
        for key, start, value in key_start_values:
            args.append((_set, key, value, start))

        await concurrent_map(args, _set, limit=None)  # TODO: fix limit

    async def get_partial_values(
        self,
        prototype: BufferPrototype,
        key_ranges: Iterable[tuple[str, ByteRequest | None]],
    ) -> list[Buffer | None]:
        # docstring inherited
        self._logger.info(f"Get partial function started with key {key_ranges}.")

        # All the key-ranges arguments goes with the same prototype
        async def _get(key: str, byte_range: ByteRequest | None) -> Buffer | None:
            return await self.get(key, prototype=prototype, byte_range=byte_range)

        return await concurrent_map(key_ranges, _get, limit=None)

    async def close(self) -> None:
        """Close the store and the underlying SQLite connection."""
        if self._connection is not None:
            self._connection.close()
            self._connection = None

[d-v-b]

@MosGeo how does your implementation compare to the one @auxym did here?

[newville]

@d-v-b, as a user (but not contributor), and wanting to expand our use of zarr, I would love to see single-file stores, especially so for bundling a File storage for distribution, archiving, and use in downstream applications. SQLite seems ideal to me for this (with clear benefits over Zip).

Having converters to/from SQLite to File store would be great for those cases where ultimate performance is needed. But I will also say that the performance hit indicated by @auxym is probably acceptable as a tradeoff for a single, archival file.

What would be needed to get this supported in zarr-python? Would it help for the work of @auxym (and/or @MosGeo) to be converted into a Pull Request? Are there any concerns that need to be addressed for this to be included?

[newville]

any thoughts? I'm trying to understand why this has gotten so little attention.

[maxrjones]

I think the next step here would be to add a SQLiteStore implementation in the experimental namespace (https://github.com/zarr-developers/zarr-python/tree/main/src/zarr/experimental), to try out direct support of this functionality without committing to long-term maintenance yet.

Before you open a PR, it'd be helpful to find a core-dev to commit to a review. I'm not a good candidate because I don't use single-file Zarr stores. Is anyone else from @zarr-developers/python-core-devs interested in supporting this feature via a code review?

[d-v-b]

important context here is that zarr-python 2.x shipped with zarr implementations for a lot of database backends, and while this was useful, it also added to the ongoing maintenance burden. So I wonder if a stand-alone package that depends on zarr-python, but doesn't require time from zarr-python maintainers, might be the ideal solution.

[newville]

@maxrjones @d-v-b How would an "experimental" namespace helps unless "advertising a lack of support" is the goal?

Zarr version2 implementations for "lot of database backends" led to maintenance problems so you got rid of all of them? Including one that you said was unique in that it added no dependencies and has clear advantages as "single file storage" over an existing "single file storage" that has known problems? If I view the history correctly, the Sqlite store was added in early 2019, and removed with multiple other stores in late 2024, and mentioned in 7 issues/prs in the 5 years when it was supported. The removal message (#1791) did not allege maintenance burden but said "The logic for doing this is to thin down the v2 codebase."

It seems like the common theme is that you want to not support this. You are not even promising to review a Pull Request of probably around 300 lines of code. For reference, src/zarr/storage currently has over 3,700 lines of code, and src/zarr/core has over 14,000 lines of code.

I was hoping to be able to use Zarr+sqlites for distributing complex datasets, and as an long-term, archival format for largish scientific data as an alternative to HDF5 (which has several flaws of its own), and to json bundles with encded numpy arrays. Sqlite3 seems much more "archival" than HDF5 to me, and the Zarr approach to compression codecs seems more robust and "archive friendly" too.

The responses here are pretty discouraging for the idea getting a supported single-file storage solution for Zarr, even though there was one for many years. It would be easy to infer that the Zarr developers are not very interested in people using Zarr for distribution or archiving of complex datasets. Is that a fair reading?

[normanrz]

When preparing the v3 release, we wanted to reduce the number of stores in the core zarr-python package. We designed the store interfaces, so that stores can be easily implemented as community packages. I think a Sqlite-based store would be a great community package!

[auxym]

So I wonder if a stand-alone package that depends on zarr-python, but doesn't require time from zarr-python maintainers, might be the ideal solution.

I'll leave that decision up to the zarr dev team (of course). The implementation linked in the proposal (https://github.com/auxym/zarr-sqlite) is already usable in this manner, and I wouldn't mind maintaining it like that for the moment.

The main downside, in my opinion, is risk of fragmentation. That is, we could end up with multiple zarr-in-sqlite formats that are not compatible with each other. Then someone shares with you a file they say is zarr, and you can't figure out how to open it, because it's one of 9 incompatible sqlite-zarr implementations, 6 of which are unmaintained and abandoned, or something like that (obviously unrealistic worst case scenario to illustrate the issue).

My ideal scenario is that sqlite would have been chosen as the "one blessed single-file zarr format", but that ship has sailed (and zip was chosen).

Personally, I don't have any interest in "other databases", my interest is single-file zarr with more features than zip offers. SQLite is only one way to achieve this goal (and happens to be relatively simple, robust and efficient). However, as an "in between" alternative, perhaps the zarr devs could could consider using an approach similar to Pandas to_sql/read_sql (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html). The same codebase supports any DB that is supported by either SQLAlchemy or ADBC. The proposal above would need only relatively minor modifications to achieve this (I think). The zarr spec would need to specify only a db schema that is implementable in any SQL database.

[normanrz]

Thanks! You are raising a valid point here. Interoperability is an important topic for the Zarr project.
I think there is more that we can do to improve interoperability on the Store level. We can expand the store section of the Zarr specification. That requires coordination among multiple implementations, because zarr-python is an important implementation in the Zarr ecosystem, but not the only one.

[maxrjones]

The responses here are pretty discouraging for the idea getting a supported single-file storage solution for Zarr, even though there was one for many years. It would be easy to infer that the Zarr developers are not very interested in people using Zarr for distribution or archiving of complex datasets. Is that a fair reading?

I think it should be clear from the responses above, but just in case not I want to explicitly answer that this is not a fair reading. The Zarr developers are working to support distribution and archiving of complex datasets, not only through the engagement here but also daily work to improve performance and usability.

[mkitti]

The responses here are pretty discouraging for the idea getting a supported single-file storage solution for Zarr, even though there was one for many years. It would be easy to infer that the Zarr developers are not very interested in people using Zarr for distribution or archiving of complex datasets. Is that a fair reading?

I would say "no" that's not a fair reading. The message is pretty clear above. Put this in a distinct repository or package like https://github.com/auxym/zarr-sqlite . If you would really want to, we could even transfer the repository to the zarr-developers Github organization. I would encourage you to cooperate with other developers using sqlite.

With modularity this also has the benefit of being easier to maintain in that its progress and development can occur independent of zarr-python, which has to be slower and more deliberative. That is I would expect development to be faster if this were independent of this repository.

My ideal scenario is that sqlite would have been chosen as the "one blessed single-file zarr format", but that ship has sailed (and zip was chosen).

I do not think there is "one blessed single-file zarr format" nor do I think there should ever be one. Some organizations that use Zarr may find it convenient to standardize on a particular container given their needs and their userbase, but I do not think that should occur centrally in zarr-developers.

The only reason I think it makes sense to have a ZipStore implementation here in zarr-python is that zipfile is a standard Python library, but I think that is a pretty weak reason. Many of the cited deficiencies of zip above are really limitations of the Python standard library rather than zip files in general. Key deletion is one such example. In fact, the capability to remove a file from a zip archive in the zipfile standard library was merged three days ago after more than a decade of development and debate. Relying on the standard library has really held back Zarr in zip files, and I think we probably would have been better served by encouraging multiple implementations of Zarr in zip files.

The development of Zarr in sqlite files in Python is better served by the fast developer cycles available to independent modular community package(s). With multiple implementations there is now an opportunity to compare them and refine a common specification. This is the philosophy applied to the development of Zarr itself.