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

Disclaimers:

•  We have taken these version from the documentation of the databases; therefore, there is potential that a version listed here is not actually supported.  
• When importing to BigQuery databases, you may notice that a table named: destinationTableName_civis_dbsync_timestamp was created as well. This is a temporary staging table that is used for accurate upsertions. These tables will be deleted at the end of the import and are set to expire in 1 day after creation.

Behavior of Incremental Refreshes

By default, database imports will not perform full refreshes and will instead update or append data depending on the configuration of your tables and the available columns. The import will try to auto-detect two fields: 

Primary Key: Unique identifier for each row. This value does not need to be an integer if a last modified column is also present but must be an ascending incremental integer if no last modified column is present.

Last Modified Column: Timestamp value of when the row was last updated.

If the columns are present in your table but not correctly detected, these fields can be explicitly configured, see: Advanced Settings for Database Imports for more information.

If no primary key is detected or provided, the import will append the contents of the source table to the destination table, which may cause duplicates. Incremental syncs are generally not appropriate for tables without a primary key column.

If only a primary key column is present, it must be an incremental ascending integer. In this case, a WHERE clause is included in the request to the source database such that only rows that have a primary key value higher than the maximum value in the destination table are imported. These rows will be appended to the table.

If a last modified column is also present, only this value will be used to identify new rows from the source table not already present in the destination table. A WHERE clause is included in the request to the source database such that only rows with a last modified value greater than the maximum value present in the destination table are imported. Rows obtained from the source database where the primary key value is already present in the destination table are upserted.

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.