An Introduction to the Force.com Database

Abstract

Data persistence lies at the heart of many applications. The Force.com platform provides a powerful and intuitive data persistence layer. The database provides not only a mechanism for creating persistent objects, but also a way of automatically generating a user interface around these objects. Reporting, tagging and much additional related functionality can also be added to applications, all out-of-the-box Force.com platform features.

You can create, configure and deploy persistent objects using the web-based Force.com Setup menu environment. However, database services are also tightly integrated with the Apex programming language, which has a dedicated syntax for invoking searches and iterating over results.

This article provides an introduction to the database services and the supporting functionality. The article has three major sections: the first looks at the underlying database, data types, supported relationships and query languages; the second looks at the declarative Force.com Setup environment for automatically creating applications based on the data being persisted, while the final section examines the integration between Apex and database services.

Database Services Fundamentals

The Force.com platform provides a powerful database, with many features that make it easy to create applications, including the automatic user interface generation described in the next section. It has some interesting functional characteristics as well - for example you don't have to work with foreign keys in the Force.com database. Instead, you work with a more abstract and meaningful relationship. This section explores the fundamentals and terminology behind the database services.

Objects and Tables

In a relational database, tables store the data. Each table comprises a number of columns of a particular data type. Information is stored in rows of the table. Finally, tables can be related to other tables, using primary and foreign keys to map the rows in one table to rows in another.

The database in Force.com, on the other hand, uses objects to store data. Objects contain the functionality you expect in a table and more, but you should be aware that the difference also points to a difference in functionality. The object comprises a number of fields. Objects can be related to other objects, with relationship fields mapping records in one object to records in another. All attributes of an object are described with metadata, outlined later. Information is stored in records of the object.

When an object is materialized in the Apex language, it is called an sObject, to differentiate them from instances of Apex classes. We’ll use this terminology throughout the article.

Non-Relational Field Types

All fields in an object must be defined as a particular data type. Here's a summary of many of the supported data types:

  • Auto Number - A system-generated read-only sequence number, analogous to the SQL identity type. These fields can be used to provide a unique ID that is independent of the internal object ID. These fields are not used in creating object relationships.
  • Checkbox - For representing Boolean data.
  • Date or Date/Time - For representing dates or date and time combinations.
  • Number - For representing real numbers, with optional decimal points.
  • Email, Phone and URL - Format-validated email, phone and URL string representations.
  • Picklist and Multi-Select Picklists - Represent values from a list.
  • Text and Text Areas - For representing text of various lengths.
  • Currency - A formatted number type, with optional multi-currency support.
  • Formula - A read-only field holding data generated from a formula expression.

Most of these field types should not be surprising. The basic Text, Auto Number and Number are found on many relational databases. The Formula field type is somewhat different to the others. Instead of holding a value, it derives its value from a supplied formula expression. The field is then updated whenever any of the source fields are changed. Formulas are discussed in more detail below.

Checkbox fields, Email fields, URL fields and Phone fields include some automatic formatting capabilities when displayed as part of the automatically generated user interface.

Relational Field Types

The database differs from relational databases in the way that record relationships are implemented. Instead of developers’ having to deal with keys, foreign keys, primary keys and defining relationships in terms of these keys, the database deals in terms of relationship fields. A relationship field stores the ID of the parent record (described below) in a relationship, as well as optionally providing user interface representations in both the parent and child records.

Two types of relationship field types can be defined. Here is a description of each:

  • Lookup Relationship - Creates a relationship that links one object to another object. The relationship field allows you to navigate from records in one object to the related records in another object (both visually, and programmatically).
  • Master-Detail Relationship - Creates a special type of relationship between two objects (the child, or "detail") and another object (the parent, or "master"). Among the additional details a) The relationship field is required on all detail records, b) Once the value of the relationship field has been saved, it cannot be changed, c) Deletes are cascaded from the master to the detail

Lookup relationships can be used to create one-to-one and one-to-many relationships. Master-detail can be used whenever there is a tight binding between two objects. For example, consider a blog and blog posts. If the blog is deleted, the blog posts should go too. They can also be used to create many-many relationships through the use of a junction object, an object related to two other objects in a master-detail relationship.

The master object in a master-detail relationship can also contain rollup summary fields. These fields store values aggregated from the child records in the relationship. For example, you can use these fields to count the number of child records, sum values in field of a child record, or determine the maximum/minimum of a field in a filtered set of child record.

Identity Fields

If you are familiar with other databases you may have been surprised to see no indication of primary key columns, or identity columns. The database in Force.com takes care of these for you, and automatically assigns an identity field (called ID) to every object, and manages the identity data in every record.

This identity field usually comes in a 15 character case sensitive form, which you may have already seen while using the platform. For example, looking at the details of a record I see the URL is set to:

 https://na3.salesforce.com/0015000000Gv7qJ

Here, 0015000000Gv7qJ is the identifier for the record. Every record in your application will have such an identifier, and as a result you can view every record (across all objects) by simply using the above URL form. The platform will automatically retrieve the record and the associated metadata, and display that record using the automatically generated user interface (providing you have the appropriate security privileges).

System Fields

Your objects will have a number of read-only system fields automatically associated with them. The ID field, discussed in the previous section, is one such field. Others include:

  • CreatedDate – the Date and time when the object was created
  • CreatedById – the ID of the User who created the object.
  • LastModifiedById - the ID of the User who last modified the object
  • LastModifiedDate – the date and time when the object was last modified by a user
  • SystemModStamp – the date and time when the object was last modified by a user or process, such as a trigger

The database automatically assigns to these fields when an object is created or modified.

The Name Field

The name field of an object is a required field that has a special place in the life of the object. The record name is intended as a human-readable identifier for a record. It's not required to be a unique identifier, but it is supposed to be the primary way users distinguish one record from another. In the automatically generated user interfaces, you’ll see that the value for the name is always displayed as a link to the record itself, to its own detail page.

A name can be one of two types: a text string or an auto-number field. For an auto-number field, you must specify the format for the field and the starting number. Auto number fields increment by one each time a record is created.

Other Database Services

The database is not simply a store for your data. It offers many features, such as the programmatic, web service and user interface components discussed later. There are many more aspects to the database though, and the following list provides a brief introduction to these:

  • Formulas - The formula field type behaves much like a spreadsheet formula - it reflects some calculation based on other other fields and operations on those fields. The formula language is a rich expression language that lets you perform calculations and manipulate strings, dates, numbers and regular expressions. Here's an example of a formula field:
 IF(ISPICKVAL( Contract_Status_c , "Activated"), NOW() - Contract_Activated_Date_c , null)

This formula simply calculates the number of days since a contract was activated. It refers to an existing field (Contract_Status__c) and uses a number of formula operators and functions, including IF, ISPICKVAL and NOW .

  • Validation - Validation rules help improve data quality by preventing users from saving incorrect data. These rules use the same formula syntax as found in formula field types to define a formula which is evaluated each time a record is saved. If the formula for a validation rule evaluates as “True”, the save is aborted and an error message displayed. You can define one or more validation rules that consist of an error condition and corresponding error message. For example, you can create a validation rule to ensure that a number falls within a particular range.
  • Triggers - Triggers, written in the Apex language, are pieces of code that can fire before or after a record is saved, updated or deleted. These are described in the Apex and Triggers section.
  • Labels and Help- Every object and record has a label and can include a description, for internal documentation, and help, which is automatically included in the user interface.
  • Notes and Attachments - You can create, view, and edit notes and add attachments for any record in an object that has this functionality enabled. This provides users of the object the ability to easily add arbitrary text notes, and upload associated documents, for each record.
  • Track Field History - Certain fields on your objects can be configured to track their history. Any time a user modifies any of the field data whose history is set to be tracked, a new entry is added to an automatically created History related list. This History list tracks the date, time, nature of the change, and who made the change.
  • Security - Database services provide a flexible security model that you can use to control who has access to objects, records and/or fields. See the Data Security section for more information.

For the most part, you can simply activate and configure these features on your objects to get the desired functionality.

In a sense, objects are a lot more than simply a description of the fields that you want to store - they're also a bucket for metadata that describes what additional functionality you want to add to fields and records, how you want them displayed, used, and presented.

Let's now look at the visual interface that the platform automatically constructs around objects, followed by the programmatic view of the database.

Using Data Services with Force.com Builder

The Force.com Setup menu is a declarative web interface that lets you create database objects and configure other aspects of the platform (such as workflow, web services and email services). The interface creates metadata, which is used to generate a default user interface for each database object that you create, with associated list, detail, create, edit, and delete pages. You can create an entire application, including the persistence layer, without any programming using this declarative approach. For a programmatic approach, refer to the Apex section.

This section provides an illustrated tour of the Force.com Setup menu environment, and some of the resulting pages that comprise an application built using the environment.

An Example

As an example, we've created two simple objects, TodoList and TodoItem. TodoItem has a lookup relationship field to the TodoList object. Furthermore, it has a Due Date (of type Date) and Description (of type Text Area) field. Here's the data model:

Db datamodel.jpg

Creating these two objects can be accomplished in 3 minutes of pointing and clicking, using the Force.com Setup menu environment.

The Generated User Interface

Here's a tour of the generated user interface. No additional work was performed at all after creating the two objects, except for populating a few records with data.

List pages are automatically generated for each object and used as the default view for the tab associated with the object. The default list page for the TodoList object is shown below, with a display of the most recently accessed objects:

Db list page.jpg

Alternative views for an object can also created and used. For example, after hitting the "Go" button in the above list, you get the following page.

Db list alternate.jpg

The pages provide intuitive access to the records, buttons for actions such as creating a new record, and in the case of lists, the ability to perform actions on groups of records. Here's the default entry page generated for the TodoList object, which lets users create new records:

Db list default entry.jpg

Note how required fields are denoted with a red bar. Here's the resulting detail page for a TodoList record after entering "Business List" as the name:

Db detailpage.jpg

Note the default Edit, Delete and Clone action buttons. Each object has a number of standard fields that are automatically created, including a Last Modified By, Created By and Owner field. The Created By and Last Modified By fields, which are included for all objects, have values that automatically set.

Here's the default page generated for TodoItem record creation:

Db todoitemcreation.jpg

The user interface generation uses the type information for each field to display an appropriate input component. For example, a date datatype is rendered with a pop-up calendar. Note that since TodoItems are in a 1-many relationship with TodoList records, you can look up a related TodoList item from this creation page too. The search icon next to the TodoList field will pop up a dialog box letting you search for the TodoList that you want this TodoItem related to.

Here's a filled in TodoItem record - a detail page:

Db todoitem detail.jpg

Note the hyperlinks that are automatically included whenever the Name for another record is displayed. For example, if you click on "Home List" you will be redirected to the "Home List" detail page. The user interface provides an intuitive surfacing of the targets of relationships. The system generates not only pages for each object, but also navigations between them. The children in a relationship can also be displayed in a list on the parent detail page. For example, here's the detail page for an item that has children:

Db todochildren.jpg

Menus, Search, Reporting and Tagging

The default user interface for all Force.com applications includes a Sidebar, as shown below:

Db sidebar.jpg

This list of items, which can include items from more than one type of object, lets you quickly access recent items. You can also search through all objects in your database. The configurable sidebar can also include the ability to create new records of a particular object type.

Tags can also be associated with objects. For example, after enabling the tag functionality, you can create and modify tags associated with any record:

Db todoitem detail tags.jpg

You can then find other objects that have those tags, and even permit public tagging - allowing any user that has access to the record to add their own tags.

Finally, the Force.com platform provides a rich reporting environment. Tabular, matrix, and summary reports can be created, as well as graphical representations of these data, including bar graphs and pie charts.

Db reports.jpg

Changing the User Interface

You can modify the generated user interfaces. Page layouts define what the various input and list pages look like, and you also can modify the pages used in various other operations, such as searching. For ultimate customization, you can use the Visualforce user interface layer to generate your own pages.

Apex

The Apex language is tightly integrated with database services. In this section, we'll show how the language can be used to create, persist and update database objects, query the database and iterate over results, and act as the language for writing database triggers. For more examples, see An Introduction to Apex or the Apex Language Reference.

Manipulating sObjects

sObject is the name of the generic abstract type that is used to represent any persisted object. These persisted objects are treated as first class citizens within the Apex language, which makes the database integration particularly intuitive and easy to use. Assuming the database has an Account sObject, with fields for name and billingcity, then either of the following two lines will create the sObject:

 sObject s = new Account(); 
 Account a = new Account( name='Acme', billingCity='Edinburgh') 

Note the optional use of initial field values in the final example. Apex provides a convenient dot notation for accessing fields within an object, so the following will return the identifier and string of an account object:

 ID id = a.ID; String x = a.name; 

Queries and Embedded Queries

The Force.com platform supports two query languages:

  • Salesforce Object Query Language (SOQL) is a query-only language. While similar to SQL in some ways, it's an object query language that uses relationships, not joins, for a more intuitive navigation of data. You'll see an example in a minute.
  • Salesforce Object Search Language (SOSL) is a simple language for searching all across all persisted objects.

The two query languages can be embedded directly in your Apex code. The following code retrieves an sObject that has the name field assigned to Acme:

 sObject s = [select id, name from account where name='Acme']; 

This code retrieves all matching accounts assigning them to an array:

 String myName = 'Acme'; 
 Account [] accts = [select ID from Account where name=:myName];  

Note the use of the embedded Apex variable in the above query.

SOSL statements evaluate to a list of lists of sObjects, where each list contains the search results for a particular sObject type. Here's an example that searches across the Name field of all Account and Contact sObjects:

 List<List<SObject>> searchList= [FIND 'map*' IN NAME FIELDS RETURNING Account (id, name),Contact]; 
 Account [] accounts= (List<Account>) searchList[0];  
 Contact [] contacts= (List<Contact>) searchList[1]; 

Queries can also be embedded in a special for syntax. This syntax can also be used to loop through records:

 for (Account tmp : [select id from account where name='yyy']) {  // } 

The above loop processes records one at a time. A more efficient form (which also makes less of an impact on governor limits) uses batched iteration:

 for (Account[] tmp : [select id from Account where name= 'yyy']){     j = tmp.size();      // perform some actions on the accounts }  

The code block in this loop will only execute once per 200 sObjects, with each batch of sObjects being assigned to the array variable.

Apex and Triggers

Triggers are written in Apex, and execute before or after an insert, update, or delete event occurs on an sObject. An example trigger definition is shown below. Note the use of the trigger keyword to introduce the definition:

 trigger myAccountTrigger on Account (before insert, before update) {
  if (Trigger.isInsert) {
    // do something    
  }    
  if (Trigger.isUpdate) {     
  for(Account a: Trigger.new)       
    if (a.name == 'bad')
      a.name.addError('Bad name');  // prevents  update   
  } 
 } 
 

This example fires before any Account sObject is inserted or updated. The Trigger.new array provides access to the array of accounts being inserted or updated. Update and delete triggers can use Tigger.old array to refer to the old versions of the objects being updated or deleted.

Triggers make full use of Apex, allowing you to continue using a familiar language for data manipulation. There are a few restrictions - for example it doesn't make sense to allow call outs to web services from within a trigger as that would unduly lengthen the transaction.

Data Manipulation

Apex code can also contain data manipulation language (DML) operations to retrieve, insert, delete and update data in the database. You can also create and manipulate save points. Here are some examples:

 Acccount a = new account[] {new Account(name='foo'), new Account(name='bar')}; 
 insert a; 
 Savepoint sp = Database.setSavepoint();  
 delete a; 
 Database.rollback(sp);

Data Security

The Force.com platform provides a range of security features to ensure that your organization and its data are protected. This security foundation ranges from user authentication features such as SAML through to IP range restrictions on logons, session security and auditing.

The following section provides an overview of some of the security features that relate to data access. The aim of this section is to give you a taster, and expose just how powerful these features really are.

Administrative Security

Administrative permissions are used to grant or deny access to some areas of the Force.com platform functionality for particular sets of users. Profiles are the primary security feature here, and they provide a way to group users together for easier administration. A user can only belong to a single profile, and only administrators can change profile membership for a user. You can explore these profiles yourself by navigating to the Setup -> Manager Users -> Profiles page.

These profiles determine which objects users in that profile has access to. For example, you can determine which combination of read, create, edit or delete permissions are assigned to the profile for each object. You can also determine which fields within an object are accessible to that profile, as illustrated in the following figure.

Db field level security.jpg

This field-level security also controls visibility of the field, which in turn determines if the fields are available on page layouts or reports for members of the profile. The profile also lets you control access to other aspects of security, such as which tabs or application are available to a user sharing that profile.

Record Security

The permissions detailed in the previous section are fairly coarse - you have permission for a type of access to all the records in an object, or to a set of fields. The Force.com platform gives you a way to implement different access to different data records stored in a single object. This type of security is based on individual rows of data - which opens up a host of interesting data security options.

A core concept in this type of security is record ownership. The owner of a record has all privileges for that record—including the ability to share the record with other users or even transfer ownership of the record. A single user can own a record, or a group of users (identified by a collection of users called a queue).

To see these in action, you’ll have to modify the default organization-wide default. Navigate to Setup -> Security Controls -> Sharing Settings and hit Edit, changing the default access from Public Read/Write on an object to Private for example. When records are locked down like this, record owners are presented with a share button – a way of assigning additional rights to a set of users for a particular record.

The Force.com platform provides a number of record sharing schemes - manual sharing, automatic sharing and programmatic sharing via Apex:

  • With manual sharing you use a Sharing button to grant access to a record. Here's our TodoItem page that now has a Sharing button given that we've just changed the organization wide default for this item to Private:

Db sharing.jpg

Once you’ve hit the Sharing button, you’ll be presented with a screen that lets you add or modify who has access to that single record:

Db sharing2.jpg

  • In some scenarios, you want to automatically share records owned by one group of users with those in another group. A group, here, can be either a public group (a defined set of users) or a role (defining a set of users with that role, and optional subordinates). You can implement this type of sharing by using sharing rules. These rules automatically grant a type of sharing access for records owned by one group of users to another group. These sharing rules can only be used to grant wider access to data, not to restrict data. You can access these rules on the Setup -> Security Controls -> Sharing Settings page. Here is an example of a rule that ensures that members of one role share records with another.

Db sharing rules.jpg

As hinted above, the platform provides a rich set of features to determine who a record should be shared with. For example, you can set up public groups (perhaps all bloggers in your company) and grant this group access to a record. You can also create role hierarchies, which represent your organization’s reporting structure. The sharing features of the platform can be set up so that all superior roles have all the sharing permissions granted to all roles below them in the hierarchy for example.

The Force.com platform supplies a rich set of data security features – ranging from object and field level security, through to record sharing, groups and role hierarchies as a control for record visibility.

Accessing your Data through a SOAP or REST Web Service

The Force.com Web Services API provides direct access to your Force.com data.

There are two related APIs that are apt here: The Force.com REST API, and the Force.com SOAP API.

These APIs allow you to create integrations on your platform of choice, and provide direct access to data within your applications, allowing you to query, create, delete and modify these data. They also provides calls to dynamically access metadata about an sObject. For example, a query can determine which fields are available in an sObject, and what the field types are.

Here's an example of some code written in Java that uses the SOAP API to create a new record:

Account account = new Account(); 
account.setAccountNumber("1123581321");
account.setBillingCity("Edinburgh")
account.setName("My Account");

SObject[] sObjects = new SObject[1]; 
sObjects[0] = account;

// persist the record
SaveResult[] saveResults = binding.create(sObjects);

For full details, see the Force.com SOAP API documentation and the REST API Resource Page.

Summary

This article demonstrates that Force.com offers a powerful persistence layer. The database makes it easy to build applications declaratively using the Setup area of your organization. Besides advanced persistence features, the platform also automatically generates the necessary user interface to access and manipulate the data using metadata about the objects. A rich set of data security features make it easy to limit data visibility. Reporting, tagging and other functionality is also available, as well as a deep integration with the Apex programming language for highly customized applications.

References

About the Author

Jon Mountjoy is the community manager and editor-in-chief at Developer Force. He gets kicks out of learning new things and communicating these to the community. You can find Jon on the Developer Force blog, Twitter and more. Jon says "Thank you to Rick Greenwald for donating bits of his book to this article, and the awesome Platform Documentation team for their great documentation. Thanks also to Rick, Mike Kreaden and Michael Russo for their great suggestions and corrections. Please send me any feedback about the article!"