Loading data from a database

Briefly, loading data from a database works as follows:
  • Create a database connector using the Connector Manager
  • Query the database using the Query Editor — pick table, columns, provide a filtering condition, etc.
  • Press Import button in the Query Editor to load query result into EasyMorph

Database connectors

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

Connectors can be of 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 that connectors referred by a project have same names in both repositories.

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.

Connector Manager

Connectors and repositories can be created and edited using the Connection Manager. It can be accessed from the Start screen, or in menu Project, or in Project Overview sidebar, or by pressing F7.

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

Hint: it is possible to protect a shared repository from accidental change by unqualified users using a write password. Note that users will still be able to use (read) all connectors in the repository even if it's protected with a password. To protect a repository from unauthorised access use standard Windows file permissions applied to the repository file.

Queries and Query Editor

Once you have created a database connector, you can start querying the database with the help of Query Editor, invoked when you press "Query database" button. This button can be found on the Main toolbar, or the Start screen. Alternatively, Query Editor can be invoked by pressing F8.

There are three main areas in the Query Editor: column selector (1), filtering condition (2), and result preview (3).


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 "Import" button (4) to load query result into EasyMorph. A "Import from database" transformation will be created automatically.

Hint: a query can be saved in a separate file (with extension .query) and shared with other EasyMorph users, if needed.

Video: Visual Query Builder

Other operations that can be performed using the Query Editor:

  • Double-click 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 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)
  • Save queries, open saved queries
  • View auto-generated SQL, copy it into the clipboard
  • Write custom SQL queries

Examples that use a database connector: Formula1, Inc5000.

Advanced topic: 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). Such list can be loaded from another data source (e.g. spreadsheet), or be a result of complex transformation logic in EasyMorph. In this case, another transformation can be of help — "Select matching database rows". This transformation matches key fields in an EasyMorph table and in a database tables, and imports only database rows where the key fields match.


This transformation can also be used for the cases when the source database table is too big to be loaded into EasyMorph entirely, but only 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, "Select matching database rows" transformation is used in order to pull full rows (i.e. all columns) for the resulting list of keys.


Read next: Generating and pasting data