AppCode 2018.3 Help

Connecting to a database

To issue a query to a database, you must connect to a data source. The data source is a location of your data (it can be a server, CSV, or a DDL file). The data source connection includes a name and connection settings that are dependent on the data source type. In AppCode, you can manage these connections in the Data Sources and Drivers dialog (File | Data Sources).

Amazon Redshift

  1. In your Redshift dashboard, create a Redshift cluster.

  2. In settings of the Redshift cluster, copy the JDBC URL.

  3. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  4. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Amazon Redshift.

  5. At the bottom of the data source settings area, click the Download missing driver files link.

  6. From the default drop-down list, select URL only.

  7. Paste the JDBC URL from the Redshift cluster settings to the URL field in AppCode.

  8. In User and Password fields, specify your Redshift credentials.

  9. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Amazon Redshift

Apache Derby

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Apache Derby.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In the URL field, enter the JDBC URL for the connection. Alternatively, select default in the URL only drop-down list, and specify your connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Derby

Azure SQL Database

  1. In your Microsoft Azure account, create an SQL data source.

  2. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  3. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Azure SQL Database.

  4. At the bottom of the data source settings area, click the Download missing driver files link.

  5. To use Azure Active Directory Authentication, select Use Windows domain authentication. To use SQL Authentication, clear the checkbox, and specify your Microsoft Azure credentials in User and Password fields.

    Note : The Use Windows domain authentication checkbox is available only on Windows.

  6. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Microsoft Azure

Cassandra

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Cassandra.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Keyspace, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Cassandra

ClickHouse

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select ClickHouse.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with ClickHouse

Exasol

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Exasol.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In the URL field, enter the JDBC URL for the connection. Alternatively, select default in the URL only drop-down list, and specify your connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Exasol

H2

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select H2.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with H2

HSQLDB

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select HSQLDB.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with HSQLDB

IBM Db2

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Db2.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Db2

MariaDB

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select MariaDB.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with MariaDB

Microsoft SQL Server

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Microsoft SQL Server.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details. The Instance field is optional.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with SQL Server

MySQL

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select MySQL.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with MySQL

Oracle

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Oracle.

  3. At the bottom of the data source settings area, click the Download missing driver files link.

  4. From the SID drop-down list, select a connection type:

    • SID: to use a unique name of an Oracle instance (a process that runs on the workstation).

    • Service name: to use an alias to an Oracle instance (or many instances).

    • TNS: to use tnsnames.ora configuration file. In TNSADMIN field, specify a path to the tnsnames.ora file. In TNS name, specify what service name to use (see a value of net_service_name in tnsnames.ora) if you have more than one service.

    • URL only: to use the JDBC URL.

  5. From the Driver drop-down list, specify the driver type. For more information about driver types, see Oracle JDBC FAQ.

  6. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Oracle

PostgreSQL

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select PostgreSQL.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with PostgreSQL

SQLite

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select SQLite.

  3. At the bottom of the data source settings area, click the Download missing driver files link.

  4. To connect to an existing SQLite database, specify a file path to the database file in the File field.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with SQLite

Sybase

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Sybase.

  3. At the bottom of the data source settings area, click the Download missing driver files link. Alternatively, you can specify user drivers for the data source. For more information about user drivers, see Add a user driver to an existing connection.

  4. In Host, Database, User, Password, and Port fields, specify connection details.

  5. To ensure that the connection to the data source is successful, click Test Connection.

Integration with Sybase

Configuring data source connections

Create a connection to any database with a JDBC driver

If you cannot find a name of a database vendor in the list of data sources, download a JDBC driver for the database management system (DBMS), and create a connection in AppCode. With the JDBC driver, you can connect to DBMS and start working.

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, click the Add icon (The Add icon) and select Driver and Data Source.

  3. Click the Driver link and select Go to driver.

  4. In the Driver files pane, click the Add icon and select Custom JARs.

  5. Navigate to the JAR file of the JDBC driver, select it, and click OK.

  6. In Class and Dialect fields, specify values that you want to use for the driver.

  7. Click Apply.

  8. Return to the created data source connection.

  9. In User, Password, and URL fields, specify your connection details.

  10. To ensure that the connection to the data source is successful, click Test Connection.

Enable the single connection mode

Single connection mode means that the data source and all consoles use one and the same connection. This mode allows you to see the temporary objects in the database tree, or use the same transaction in different consoles. For more information about consoles, read Database Console.

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. Select the data source where you want to enable the single connection mode.

  3. Click the Options tab.

  4. Select Single connection mode.

  5. Click Apply.

Enable the single connection mode

Assign a color to a data source

You can assign a color to a data source (for example, for better differentiation between production and test databases).

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. Select the data source for which you want to assign a color.

  3. Click the Choose color icon (The Choose color icon) and select a color.

  4. Click OK.

Assign a color to a data source

Connect to a database with SSL

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. Select a data source profile where you want to change connection settings.

  3. Click the SSH/SSL tab and select the Use SSL checkbox.

  4. In the CA file field, navigate to the CA certificate file (for example, ca.pem).

  5. In the Client certificate file field, navigate to the client certificate file (for example, client-cert.pem).

  6. In the Client key file field, navigate to the client key file (for example, client-key.pem).

  7. To ensure that the connection to the data source is successful, click Test Connection.

Connect to a database with SSL

Connect to a database with SSH

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. Select a data source profile where you want to change connection settings.

  3. Click the SSH/SSL tab and select the Use SSH tunnel checkbox.

  4. In Proxy host, Proxy user, Proxy password, and Port fields, specify connection details.

  5. From the Auth type drop-down list, select an authentication method:

    • Password: to access the host with a password. Specify the password, and select the Save password checkbox to save the password in AppCode.

    • Key pair (OpenSSH or PuTTY): to use SSH authentication with a key pair. To apply this authentication method, you must have a private key on the client machine and a public key on the remote server. AppCode supports private keys that are generated with the OpenSSH utility.

      Specify the path to the file where your private key is stored and type the passphrase (if any) in the corresponding fields. To have AppCode remember the passphrase, select the Save passphrase checkbox.

    • If your SSH keys are managed by a credentials helper application (for example, ssh-agent ), choose OpenSSH config and authentication agent.

  6. To ensure that the connection to the data source is successful, click Test Connection.

SSH and SSL settings of a data source

Add a JDBC driver to an existing connection

  1. Open the Database tool window (View | Tool Windows | Database) and click the Data Source Properties icon (The Data Source Properties icon).

  2. In the Data Sources and Drivers dialog, select a data source where you want to change a driver, and click the Driver link in data source settings.

  3. In the Driver files pane, click the Add icon (The Add icon) and select Custom JARs.

  4. Navigate to the JAR file of the JDBC driver, select it, and click OK.

  5. In Class and Dialect fields, specify values that you want to use for the driver.

  6. Click Apply.

Add a user driver to an existing connection
Last modified: 28 March 2019