[id='configuring-sql-cache-stores-table_{context}']
= Loading {brandname} caches from database tables

Add a SQL table cache store to your configuration if you want {brandname} to load data from a database table.
When it connects to the database, {brandname} uses metadata from the table to detect column names and data types.
{brandname} also automatically determines which columns in the database are part of the primary key.

.Prerequisites

* Have JDBC connection details. +
You can add JDBC connection factories directly to your cache configuration. +
For remote caches in production environments, you should add managed datasources to {brandname} Server configuration and specify the JNDI name in the cache configuration.
* Generate Protobuf schema for any composite keys or composite values and register your schemas with {brandname}.
+
[TIP]
====
{brandname} recommends generating Protobuf schema with the ProtoStream processor.
For remote caches, you can register your schemas by adding them through the {brandname} Console, CLI, or REST API.
====


.Procedure

. Add database drivers to your {brandname} deployment.
+
* Remote caches: Copy database drivers to the `server/lib` directory in your {brandname} Server installation.
+
[TIP]
====
Use the [command]`install` command with the {brandname} Command Line Interface (CLI) to download the required drivers to the `server/lib` directory, for example:

[source,options="nowrap",subs=attributes+]
----
install org.postgresql:postgresql:42.1.3
----
====
* Embedded caches: Add the `infinispan-cachestore-sql` dependency to your `pom` file.
+
[source,xml,options="nowrap",subs=attributes+]
----
include::dependencies_maven/persistence_sql_store.xml[]
----

. Open your {brandname} configuration for editing.
. Add a SQL table cache store.
+
.Declarative
[source,xml,options="nowrap",subs=attributes+,role="primary"]
----
table-jdbc-store xmlns="urn:infinispan:config:store:sql:{schemaversion}"
----
+
.Programmatic
[source,xml,options="nowrap",subs=attributes+,role="secondary"]
----
persistence().addStore(TableJdbcStoreConfigurationBuilder.class)
----

. Specify the database dialect with either `dialect=""` or `dialect()`, for example `dialect="H2"` or `dialect="postgres"`.
. Configure the SQL cache store with the properties you require, for example:
+
* To use the same cache store across your cluster, set `shared="true"` or `shared(true)`.
+
* To create a read only cache store, set `read-only="true"` or `.ignoreModifications(true)`.
. Name the database table that loads the cache with `table-name="<database_table_name>"` or `table.name("<database_table_name>")`.
. Add the `schema` element or the `.schemaJdbcConfigurationBuilder()` method and add Protobuf schema configuration for composite keys or values.
.. Specify the package name with the `package` attribute or `package()` method.
.. Specify composite values with the `message-name` attribute or `messageName()` method.
.. Specify composite keys with the `key-message-name` attribute or `keyMessageName()` method.
.. Set a value of `true` for the `embedded-key` attribute or `embeddedKey()` method if your schema includes keys within values.

. Save the changes to your configuration.

[discrete]
== SQL table store configuration

The following example loads a distributed cache from a database table named "books" using composite values defined in a Protobuf schema:

.XML
[source,xml,options="nowrap",subs=attributes+,role="primary"]
----
include::xml/persistence_sql_table.xml[]
----

.JSON
[source,json,options="nowrap",subs=attributes+,role="secondary"]
----
include::json/persistence_sql_table.json[]
----

.YAML
[source,yaml,options="nowrap",subs=attributes+,role="secondary"]
----
include::yaml/persistence_sql_table.yaml[]
----

.ConfigurationBuilder
[source,java,options="nowrap",subs=attributes+,role="secondary"]
----
include::code_examples/PersistenceSqlTable.java[]
----

[role="_additional-resources"]
.Additional resources
* link:{encoding_docs}[Cache encoding and marshalling: Generate Protobuf schema and register them with {brandname}]
* link:{code_tutorials_root}/infinispan-remote/persistence[Persistence code tutorial with remote caches]
* link:{config_docs}#jdbc-connection-factories_persistence[JDBC connection factories]
* link:{javadocroot}/org/infinispan/persistence/jdbc/common/DatabaseType.html[DatabaseType Enum lists supported database dialects]
* link:{configdocroot}infinispan-cachestore-sql-config-{schemaversion}.html[{brandname} SQL store configuration reference]
