Using virtual foreign keys

From DreamFactory
Jump to: navigation, search
DreamFactoryTutorialsUsing virtual foreign keys

"Virtual foreign keys" are a powerful feature that allow you to impose virtual foreign key relationships between data in unrelated tables. Tables can be either in the same database or in different databases (SQL to SQL, and SQL to NoSQL), and you don't need to alter the database schema. Virtual foreign keys are a convenient way to orchestrate complex queries on disparate data without having to manually code server-side logic.

Once you've set up a virtual foreign key relationship, it's easy to perform CRUD operations that read, write, update, and delete records on the "virtually" related data, all with a single API call that passes the virtual foreign key as an API parameter. Try these examples in the 'API Docs' tab of the DreamFactory Admin Console or from the command line with cURL.

API Endpoint

GET https://{url}/api/v2/{api_name}/_table/{table_name}?related={virtual_foreign_key_field}

API Docs Screenshot

Swagger-virtual-related.png

Example - Fetch contact and related customer records in the same SQL database with username as a virtual foreign key.

Say we have two tables in the same SQL database that we need to connect: a 'contact' table with basic contact information and a 'customer' table that lists transactions of items we've sold to customers. These tables don't have a foreign key relationship in our database schema, but the contact table has a field called 'email' and the customer table has a field called 'username', which is an email address.

We can use the customer table's 'username' field as a virtual foreign key to the contact table's 'email' field, allowing us to make a single API call to GET a set of contact records and related records in the customer table where the customer 'username' matches contact 'email'.

To set up the virtual foreign key, navigate to the Schema tab and select the field you want to be a virtual foreign key. In this example, we select the 'username' field in the 'customer' table (we know that 'username' is really an email address in our customer data). Then in the 'username' field definition form, check 'Foreign Key', 'Virtual Foreign Key', 'contact' for Reference Table, and 'email' for 'Reference' field.

Virtual-foreign-key-same-db.png

To see the virtual foreign key relationship from the 'contact' table, go to Schema and select the 'contact' table. Under Relationships, you'll see that 'contact' now has a 'has_many' relationship to the customer table.

Virtual-foreign-key-same-db-contact.png

Now we can make API calls to the 'contact' table and GET both contacts and related customer records in a single API call.

  • Table name: contact
  • Related parameter in API call:
    customer_by_username
  • Request URL:
    https://foo.com/api/v2/mysql/_table/contact?related=customer_by_username

Example - Fetch contact and related customer records in a different SQL or NoSQL database with username as a virtual foreign key.

Note: DreamFactory currently supports virtual foreign key relationships for disparate databases as long as the API calls a SQL database. In other words, you can't currently call a NoSQL database with an API call and fetch data related by virtual foreign key from a different SQL or NoSQL database (but you can call a SQL database with an API call and fetch data from a different SQL or NoSQL database).

This example is similar to the previous example, except that the tables reside in two completely different databases (for example, two different MySQL databases, one SQL Server database and an Oracle database, one MySQL database and one MongoDB database, etc.). Say that we need to connect a 'contact' table in database one with a 'customer' table in database two. These tables obviously can't have a foreign key relationship in our database schema, but the contact table has a field called 'email' and the customer table has a field called 'username', which is an email address.

Even though the tables reside in two isolated databases, we can still use the customer table's 'username' field as a virtual foreign key to the contact table's 'email' field, allowing us to make a single API call to GET a set of contact records and related records in the customer table where the customer 'username' matches contact 'email'.

To set up the virtual foreign key, navigate to the Schema tab and select the field you want to be a virtual foreign key. In this example, we select the 'username' field in the 'customer' table. Then in the 'username' field definition form, check 'Foreign Key', 'Virtual Foreign Key', 'Foreign Reference Service', the name of the database where the 'contact' table resides, 'contact' for Reference Table, and 'email' for 'Reference' field.

Virtual-foreign-key-diff-db.png

To see the virtual foreign key relationship from the 'contact' table, go to Schema and select the 'contact' table. Under Relationships, you'll see that 'contact' now has a 'has_many' relationship to the customer table that's in a completely different database.

Virtual-foreign-key-diff-db-contact.png

Now we can make API calls to the 'contact' table and GET both contacts and related customer records in a single API call. The API call is the same as the previous example, except that the related field name (our virtual foreign key) is prefixed by the database service (API name) where it resides. In this example, the 'customer' table is in a database service called 'db' while the 'contact' table is in a different database service called 'mysql'.

  • Table name: contact
  • Related parameter in API call:
    db.customer_by_username
  • Request URL:
    https://foo.com/api/v2/mysql/_table/contact?related=db.customer_by_username

Common Errors

local.ERROR: Symfony\Component\Debug\Exception\FatalThrowableError: Call to a member function getName() on null
  • You must delete the virtual relationship and clear your cache