Whether you want to copy a single table or multiple tables at the same time, Database Sync allows you easily import data from a source database into a destination database.

Getting Started

1. On the top navigation menu, click Data, and then under Imports, select "Database."

Selecting Your Source and Destination Databases

1. Under Source on the left side of the page, select the database and credential you would like to sync into your destination database. If this is your first time syncing data from this source database, you’ll need to set it up using New Database.  Fill out the the JDBC URL, username, and password information and click Add.
2. Under Destination on the right side of the page, select the database and credential you would like to sync data into.

Adding a Table to Your Import

1. For each table that you would like to import: under Source on the left side of the page, type the schema and table name of the source table. (Schemas are required for all databases except MySQL.  For MySQL, you can check the "The database does not have a schema" box.)
2. Under Destination on the other side of the page, select a schema from the dropdown and type the table name you would like to sync that data into.  If the destination table does not exist, Civis will create the table for you.
3. If you’d like to access advanced import settings (such as verifying that source and destination table row counts match or wiping the destination table on each job run), click on the down caret to the right of the destination schema and table field.
4. If you’d like to import multiple tables in the same job, select +Add Another at the bottom of the page.
5. Once your job is set up, click Run Now in the top-right corner of the page.

Default Settings

For each table in your import, there are default settings that, depending on the nature of your source table, could cause an initial job failure.  There are two default settings you should be mindful of:

• Full Refresh On Each Run: No - If the destination table exists, Civis will append the new data to the existing table.
• Truncate Long Text: No - Redshift has a max column width that is smaller than the max width of some other database types.  If you're importing a table with a very wide column (such as TEXT data types), that source table's column's max width may exceed what Redshift is capable of, causing your import to fail.

Table and Column Names

Each of our supported databases handles column case sensitivity differently. We support case sensitive table and column names if supported by the database (see here for database case sensitivity support). 

If the destination database is case sensitive, then we respect the case of the table and column names from the source database. For example, when creating the destination table, we will use the defined table name and match the source table’s column names if the source database is case sensitive. If the source database is case insensitive, we downcase the source table's column names to preserve consistency across case insensitive databases. When verifying the column names, we will check that all column names in the source table match the column names in the destination table. 

If the destination database is case insensitive, then we will translate the case of the table and/or column names to comply with the destination’s specifications. For example, when creating a Redshift destination table, we will lowercase the defined table name and the source table’s column names because Redshift only supports lowercase column names. When verifying the column names, we will check that the lowercase column names in the source table match the column names in the destination table.

Important Note: You cannot have multiple columns with the same name that differ in case only when syncing from a case sensitive database to a case insensitive database.

Database Case Sensitivity Support

The following is a list of the databases and their table and column name support we follow:

• Microsoft SQL Server tables and columns are case insensitive by default but can be set to case sensitive. For more info: SQL Server Docs
• MySQL is always case sensitive for table names but case insensitive for columns. For more info: MySQL Docs
• Oracle is case sensitive. For more info: Oracle Docs
• PostgreSQL is case sensitive. For more info: Postgres Docs
• Redshift is case insensitive. For more info: Redshift Docs
• Snowflake is case sensitive. For more info: Snowflake Docs

Scheduling Your Import

If you’d like to set your import to run on a schedule, click on the clock icon in the top-right corner of the page.  From there you can have the import run on a schedule of your choosing, or else incorporate it into a workflow.  For more information about job automation, see the Scheduling help documentation.

Supported Database Versions

• Microsoft SQL Server - 2019, 2017, 2016, 2014, and 2012
• MySQL - 5.6.x, 5.7.x, and 8.0.x
• Oracle - 12.2.x or newer
• PostgresSQL - 8.2 or newer
• Redshift - 1.0.x or newer
• Snowflake - 3.26.x or newer
• BigQuery

Disclaimer - We have taken these version from the documentation of the databases; therefore, there is potential that a version listed here is not actually supported.  

Advanced Settings

If you are interested in configuring more advance settings for your import, please see Advanced Settings for Database Imports.

For Databases using an SSL Certificate

If your database uses an SSL certificate to validate connections, then you will need to add that certificate. Please see Adding an SSL Certificate to Civis for a Database Sync.

For Databases using SSL Keys

If your database uses SSL Keys to validate connections, then you will need to add additional credentials. Please see Adding an SSL Key Pair to Civis for a Database Sync.