# Database Integrations

> Source: https://docs.synthesized.io/tdk/latest/user_guide/070_integrations/databases/
> For the complete documentation index, see [llms.txt](https://docs.synthesized.io/llms.txt).

This page is the single reference for supported databases, JDBC connection strings, required and permissions.

## Supported Databases

### Supported Data Sources

Synthesized easily connects to a variety of data sources.

**POSTGRES** **ORACLE** **MYSQL** **SQLITE** **MSSQL** **Snowflake** **DB2 LUW** **CSV** **XML** **SAP HANA**

## PostgreSQL

For a PostgreSQL, the basic format of the connect string is:

```
jdbc:postgresql://<host>:<port>/<database>
```

Example of JDBC URL:

```
jdbc:postgresql://localhost:5432/postgres
```

More information about connecting to the PostgreSQL database can be found in the [documentation](https://jdbc.postgresql.org/documentation/use/#connecting-to-the-database).

## SQL Server

The general form of the connection URL for SQL Server is:

```
jdbc:sqlserver://<host>:<port>[;<property>=<value>]
```

Example of JDBC URL which is used to connect to `master` database without encryption:

```
jdbc:sqlserver://localhost:1433;encrypt=false;databaseName=master
```

More information about connecting to the SQL Server database can be found in the [documentation](https://learn.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url?view=sql-server-ver16).

## MySQL

The general form of the connection URL is:

```
jdbc:mysql://<host>:<port>[/<database>][?properties]
```

Example:

```
jdbc:mysql://localhost:3306/sakila
```

> **NOTE**
> `LOAD DATA LOCAL INFILE` requires the MySQL server (or managed instance) to allow local infile operations (`local_infile=1`). The platform automatically enables the JDBC-side property when `USE_FILE_BATCH_INSERT` is set, but you must ensure the server configuration permits it.

More information about connecting to the MySQL database can be found in the [documentation](https://dev.mysql.com/doc/connector-j/en/connector-j-reference-jdbc-url-format.html).

## Oracle

When connecting to an Oracle database using JDBC, the URL format depends on the type of connection method. Some of the connection methods are described below.

### Service Name Connection

The format of the JDBC URL to connect Oracle databases via service name:

```
jdbc:oracle:thin:@//<host>:<port>/<serviceName>
```

Example:

```
jdbc:oracle:thin:@//localhost:1521/DB
```

### TNS URL Format

`tnsnames.ora` entries can be included in the JDBC URL to connect to Oracle databases:

```
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host>)(PORT=<port>))(CONNECT_DATA=(SERVICE_NAME=<service>)))
```

Example:

```
jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB)))
```

More information about connecting to the Oracle database can be found in the [documentation](https://docs.oracle.com/en/database/oracle/oracle-database/21/jjdbc/data-sources-and-URLs.html).

## Db2

Basic URL template for Db2 (type 4 connectivity) is:

```
jdbc:db2://<host>:<port>/<database>
```

Example:

```
jdbc:db2://localhost:50000/testdb
```

More information about connecting to the Db2 database can be found in the [documentation](https://www.ibm.com/docs/en/db2/12.1?topic=cdsudidsdjs-url-format-data-server-driver-jdbc-sqlj-type-4-connectivity).

## SQLite

The general form of the connection URL for SQLite is:

```
jdbc:sqlite:<path_to_sqlite_file>
```

Example of JDBC URL with the absolute path to the SQLite database file (`test.db`):

```
jdbc:sqlite:/var/data/test.db
```

## SAP HANA

For a SAP HANA database, the basic format of the connect string is:

```
jdbc:sap://<host>:<port>/<database>
```

Example of JDBC URL:

```
jdbc:sap://ec2-35-177-245-71.eu-west-2.compute.amazonaws.com:39015
```

## Snowflake

For a Snowflake database, the basic format of the connect string is:

```
jdbc:snowflake://<account_identifier>.snowflakecomputing.com/?db=<database>&warehouse=<warehouse>&schema=<schema>
```

Example of JDBC URL:

```
jdbc:snowflake://xy12345.eu-central-1.snowflakecomputing.com/?db=MYDB&warehouse=TDK_WAREHOUSE&schema=PUBLIC&role=TDK_ROLE
```

> **NOTE**
> Use your Snowflake programmatic access token (API token) as the **password** when configuring the connection.

More information about connecting to the Snowflake database can be found in the [documentation](https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure).

## CSV File

For a CSV file, the basic format of the connect string is:

```
s3://<file storage>:<port>/<bucket>/<directory>
```

Example of JDBC URL:

```
s3://minio:9000/csvinput/data/
```

## XML/XSD File

For a XML file, the basic format of the connect string is:

```
xml:s3://<file storage>:<port>/<bucket>/<directory>/<file.xsd>
```

Example of JDBC URL:

```
xml:s3://minio:9000/my-root/data/CCR001.xsd
```

## Required Permissions

### PostgreSQL

```sql
-- Read-only (recommended for source)
GRANT CONNECT ON DATABASE mydb TO tdk_user;
GRANT USAGE ON SCHEMA public TO tdk_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tdk_user;

-- Read-write (for target database)
GRANT ALL PRIVILEGES ON DATABASE mydb TO tdk_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO tdk_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO tdk_user;
```

### MySQL

```sql
-- Read-only (recommended for source)
GRANT SELECT ON mydb.* TO 'tdk_user'@'%';

-- Read-write (for target database)
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER
ON mydb.* TO 'tdk_user'@'%';
```

### Oracle

```sql
-- Read-only (recommended for source)
GRANT CONNECT TO tdk_user;
GRANT SELECT ANY TABLE TO tdk_user;

-- Read-write (for target database)
GRANT CONNECT, RESOURCE TO tdk_user;
GRANT CREATE TABLE TO tdk_user;
GRANT UNLIMITED TABLESPACE TO tdk_user;
```

### SQL Server / Azure SQL

```sql
-- Read-only (recommended for source)
CREATE USER tdk_user FOR LOGIN tdk_login;
GRANT SELECT ON SCHEMA::dbo TO tdk_user;

-- Read-write (for target database)
CREATE USER tdk_user FOR LOGIN tdk_login;
ALTER ROLE db_datareader ADD MEMBER tdk_user;
ALTER ROLE db_datawriter ADD MEMBER tdk_user;
ALTER ROLE db_ddladmin ADD MEMBER tdk_user;
```

### Snowflake

Snowflake privileges are granted to a role; set the connecting user’s `PASSWORD` to your Snowflake programmatic access token (API token).

```sql
-- Read-only (recommended for source)
GRANT USAGE ON WAREHOUSE tdk_warehouse TO ROLE tdk_role;
GRANT USAGE ON DATABASE mydb TO ROLE tdk_role;
GRANT USAGE ON SCHEMA mydb.myschema TO ROLE tdk_role;
GRANT SELECT ON ALL TABLES IN SCHEMA mydb.myschema TO ROLE tdk_role;
GRANT SELECT ON FUTURE TABLES IN SCHEMA mydb.myschema TO ROLE tdk_role;

-- Read-write (for target database)
GRANT USAGE, OPERATE ON WAREHOUSE tdk_warehouse TO ROLE tdk_role;
GRANT USAGE, CREATE SCHEMA ON DATABASE mydb TO ROLE tdk_role;
GRANT USAGE, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE TEMPORARY TABLE
  ON SCHEMA mydb.myschema TO ROLE tdk_role;
GRANT SELECT, INSERT, UPDATE, TRUNCATE ON ALL TABLES IN SCHEMA mydb.myschema TO ROLE tdk_role;
GRANT SELECT, INSERT, UPDATE, TRUNCATE ON FUTURE TABLES IN SCHEMA mydb.myschema TO ROLE tdk_role;
```

See [Database Permissions](https://docs.synthesized.io/tdk/latest/user_guide/010_get_started/database_permissions#snowflake_permissions) for the full breakdown, including the object ownership required for `ALTER TABLE` and `DROP SCHEMA …​ CASCADE`.

## Troubleshooting

- [Database-Specific Troubleshooting](https://docs.synthesized.io/tdk/latest/user_guide/080_troubleshooting/database_specific/)
- [SSL Connection Issues](https://docs.synthesized.io/tdk/latest/user_guide/080_troubleshooting/database_specific/ssl_issues)