[id='sql-cache-store_{context}']
= SQL cache stores

SQL cache stores let you load {brandname} caches from existing database tables.
{brandname} offers two types of SQL cache store:

Table:: {brandname} loads entries from a single database table.
Query:: {brandname} uses SQL queries to load entries from single or multiple database tables, including from sub-columns within those tables, and perform insert, update, and delete operations.

[TIP]
====
Visit the code tutorials to try a SQL cache store in action.
See the link:{code_tutorials_root}/infinispan-remote/persistence[Persistence code tutorial with remote caches].
====

Both SQL table and query stores:

* Allow read and write operations to persistent storage.
* Can be read-only and act as a cache loader.
* Support keys and values that correspond to a single database column or a composite of multiple database columns.
+
For composite keys and values, you must provide {brandname} with Protobuf schema (`.proto` files) that describe the keys and values.
With {brandname} Server you can add schema through the {brandname} Console or Command Line Interface (CLI) with the [command]`schema` command.

[WARNING]
====
The SQL cache store is intended for use with an existing database table.
As a result, it does not store any metadata, which includes expiration, segments, and, versioning metadata.
Due to the absence of version storage, SQL store does not support optimistic transactional caching and asynchronous cross-site replication.
This limitation also extends to Hot Rod versioned operations.
====

[TIP]
====
Use expiration with the SQL cache store when it is configured as read only.
Expiration removes stale values from memory, causing the cache to fetch the values from the database again and cache them anew.
====

[role="_additional-resources"]
.Additional resources
* link:../../apidocs/org/infinispan/persistence/jdbc/common/DatabaseType.html[DatabaseType Enum lists supported database dialects]
* link:../../configuration-schema/infinispan-cachestore-sql-config-{schemaversion}.html[{brandname} SQL store configuration reference]
