Loading data from a database

Briefly, loading data from a database works as follows:

1. Open the Connector Manager and create a database connector that points to your source database; close the Connector Manager
2. Press "Add data", select "Query database" and use the Query Editor to select a table, columns, provide a filtering condition, etc.
3. Press the "Import" button in the Query Editor to load the query result into EasyMorph

Supported database types:

The native drivers are built into EasyMorph and don't require installing anything else. The ODBC drivers are provided by database vendors (must be 64-bit drivers) and should be configured in Windows prior to using in EasyMorph.

If your database type is not listed above, but it has an ODBC connector, then limited connectivity is still possible: create an ODBC connector and specify the "Other SQL" dialect in it, then query the database by writing custom SQL in the Query Editor. The visual query builder is not available for the "Other SQL" dialect.

Connector Manager

EasyMorph stores connectors (to databases and other systems) in a repository, which is basically just a special file. Connectors and repositories can be created and edited using the Connector Manager. It can be accessed from the Start screen, or by pressing F7.

The Connector Manager allows creating connectors to relational databases, as well as to any other external system supported by EasyMorph, e.g. email services, REST APIs, etc.

Operations that can be performed using the Connector Manager:

• Create, edit and delete embedded and shared connectors
• Create new repositories
• Switch between repositories
• Copy connectors between repositories and projects

Database connectors

A database connector in EasyMorph is a set of connection properties (e.g. server address, port or database name) that is used to connect to particular database. See below example of database connector.

Connectors can be of two types: shared (default type) and embedded

Shared connectors are stored in a repository. The repository is a file that can be located anywhere (e.g. in a network folder). Therefore, shared connectors in the repository can be used by multiple users in multiple projects. On the first launch, EasyMorph automatically creates an empty repository (AppData\Local\EasyMorph\repo.sqlite) if no repository is present. You can switch between different repositories, although EasyMorph works with only one repository at a time. Projects refer to shared connectors by name. Therefore, if you're using the same project with two different repositories (e.g. production and development) make sure the connectors referred to by a project have the same names in both repositories.

A shared repository can also be provided through EasyMorph Server. In this case, additional access restrictions can be applied.

Embedded connectors are stored within the project that uses them. Therefore, if the project is opened on a different computer it will use exactly the same connection settings. Embedded connectors can be used in cases when a project needs to be portable or shareable. Note that EasyMorph includes native drivers for some popular databases, therefore projects with embedded connectors that use native drivers work out of the box, without database client software installed.

Query Editor

Once you have created a database connector, you can start querying the database using the Query Editor. The Query Editor opens automatically when you click the "Add data" button and then select "Query database".

Alternatively, the Query Editor can be invoked by pressing F8.

There are three main areas in the Query Editor: column selector¹, filtering condition ², and result preview³.

A typical workflow is as follows: select columns, create/edit filtering conditions, and preview query results. To create a filtering condition drag a field name onto the condition area. When everything looks good, press the "Import" button⁴ to load the query result into EasyMorph. An "Import from database" action will be created automatically.

Video: Visual Query Builder

Other operations that can be performed using the Query Editor:

• Double-click the column header in the preview datagrid and see/select unique values in that column.
• Group filtering conditions into AND and OR groups. Negate conditions.
• Drag groups and conditions to re-arrange them. Right-click groups and conditions for more options.
• Annotate groups and conditions.
• Use project parameters in conditions.
• Create SQL expressions to use database functions. Such expressions can also be used in filtering conditions.
• Provide a custom filtering condition using SQL WHERE clause syntax (remember that SQL syntax depends on database type)
• Sort by one or more columns (right-click the column header, choose Sort)
• Re-order columns (drag, or right-click the column header and choose Move)
• Load only Top N rows
• Remove duplicate rows (i.e. keep only distinct rows)
• Aggregate and group by selected columns
• Apply post-filter on aggregated and grouped columns
• View auto-generated SQL, copy it into the clipboard
• Write custom SQL queries

Advanced topics

Custom SQL

Besides using visual queries, it is possible to import data from databases using custom SQL queries. This can be convenient in cases that can't be accommodated using a visual query — for instance when a join is required.

Switch Query Editor to the "Custom SQL" mode in order to edit a custom SQL query. You can drag table and field names from the list on the left into the custom query text.

Loading matching database rows

Sometimes it's necessary to load only a subset of rows from a database table, and this subset is defined by a list of identifiers or keys (primary or foreign) in an EasyMorph table. Such a list can be loaded from another data source (e.g. spreadsheet), or be the result of complex transformation logic in EasyMorph. In this case, another action can be of help — "Select matching database rows". This action matches key fields in an EasyMorph table and in a database table, and imports only database rows where the key fields match.

This action can also be used for cases when the source database table is too big to be entirely loaded into EasyMorph, but only a subset of its rows is needed. In such cases, only a few fields can be selected first. Typically, a key field and one or more data fields necessary for filtering. Then filtering is done in EasyMorph using the data fields in order to obtain a resulting list of keys. Finally, the "Select matching database rows" action is used to pull full rows (i.e. all columns) for the resulting list of keys.