Integrated Development Environments

Database Development IDE

Wherever data takes the organization, the database must follow. And wherever  the database goes, database administrators and developers must take the  lead in ensuring a high level of database performance. The DBA and database  developer must accomplish multiple tasks to achieve that goal. 

Database management systems (DBMSes) are evolving to support business  imperatives like big data, unstructured data, deeper levels of business logic,  and always-up web applications. As a result, database developers — not to mention database administrators (DBAs) and data analysts — are charged  with handling whatever the organization throws at them. 

Whether they are working in a traditional RDBMS, an open source version,  a NoSQL database, a document store, or a cloud DBMS, database developers  invariably face a handful of essential tasks. Most database developers can  remain productive on at most a couple of platforms; beyond that, they start  spending as much time learning and switching among different tools as they  do writing SQL statements and scripts. How can they accomplish their tasks  quickly and easily on three, four, or more DBMSes without having to bounce  among different tools?

Integrated Development Environments

This paper examines several of the most common database development  tasks. It describes how to accomplish them quickly and productively through  a single, integrated development environment (IDE), across a wide variety  of both traditional and emerging database platforms.  

The IDE approach to database development holds the greatest promise  for ease of use, a short learning curve, and consistency across database  platforms. Automating and simplifying common tasks enables database  professionals to work more efficiently and spend more of their valuable time  on mission-critical activities.

GUI Database Connection 

A connection to the database server is the starting point for any database operation. DB-Engines tracks almost 350 different DBMSes currently in use, and while few organizations  use that many, administrators are often responsible for multiple instances on two, three, or as  many as half a dozen different DBMSes. Furthermore, the databases could be RDBMS, NoSQL,  or cloud-based. 

Server details, authentication details, and location information vary among database types. A common graphical user interface (GUI) is helpful for connecting to,  or registering, a wide variety of database servers. This simplifies the first point of contact by  making it unnecessary to hunt down different formats for entering username, password,  host, port, and database name.

Through the GUI, the environment makes it possible to connect to dozens of the most frequently  used database servers, including their different versions, so that the developer does not need  to research connection requirement details for each one. The IDE approach accommodates  advanced parameters and different drivers, permissions, and scripts for each connection. By storing the registration details for each database server, teams can also share connection  files so that each user need enter only the password for the initial connection.

GUI Database Navigation

Navigation in a hierarchical explorer is the most familiar mode for browsing, navigating, and  selecting databases and objects. It is intuitive to every developer and even to novice users. To simplify navigation and search among databases and versions, the IDE-based database  development tool displays servers and files on separate tabs.

Right from the GUI, database professionals can use context menus to explore tables, data, and  database objects. They can also select actions to CREATE, ALTER, and DROP tables, databases,  and objects, as shown in Figure 4, without the need to write schema scripts. The tool automates  the process of generating the SQL statements required to create databases and to create/alter/ drop tables, constraints, indexes, views, triggers, scripts, and storage objects.  That is helpful because architecture and nomenclature differ among databases. For example,  a schema in Oracle is analogous to a database and one of its schemas in SQL Server; similarly,  creating a collection in MongoDB is on par with creating a table in Oracle.  

By keeping operations available in context menus and maintaining a layout as consistent as  possible from one database to another, the IDE approach makes it easy for developers and  administrators to work effectively in multi platform environments.

SQL Editor

Creating, editing, and running SQL are at the heart of almost all database development and  administration tasks. Even with variations across databases, accurate SQL requires proper syntax that  the database can execute efficiently and reliable formatting that makes the code easy to follow and  edit. Sophisticated database development tools include not only a SQL editor interface for editing SQL  statements and queries but also a GUI-based, drag-and-drop, visual query interface. 

Commands and syntax vary among procedural layers, or dialects, of SQL. To address development  challenges that users may encounter in working with multiple DBMS, the ideal SQL code editor abstracts  those variations and includes added functions helpful in quickly creating efficient code from a single UI: 

  • Autocompletion through a pop-up list of database objects in the selected database.
  • Separate tabs in the same UI for editing SQL and scripts in multiple databases/platforms simultaneously.
  • Efficient, consistent formatting of keywords, text alignment, indentation, operators, and punctuation marks.
  • Abbreviations for inserting user-defined text, from a few words to entire blocks of code.
  • Automation of frequently used statements such as SELECT, DELETE, and INSERT.
  • Immediate execution of queries, with results displayed in the same window.

Visual Query 

But a GUI-based, drag-and-drop, visual query builder may be better suited to the needs of citizen  developers, line-of-business users who are less technical but who still need to construct and run  queries to perform their job. When it is included in the same tool as the SQL editor, so much the better.  This offers a familiar starting point for some users working with SQL and queries, and it is often found  in a database developer IDE. 

By dragging and dropping tables, views, and columns into a workspace, users unfamiliar with creating  SQL from scratch (or with the differences in syntax among dialects of SQL) can use the GUI-based tool  to generate SQL statements for basic functions.

  • Selecting columns for output 
  • Adding JOINs and UNIONs 
  • Filtering, grouping, and sorting records with WHERE, GROUP BY, HAVING, and ORDER BY clauses 
  • Displaying results 
  • Saving queries for subsequent reuse and sharing 

The IDE approach offers a uniform, consistent UI for the most common dialects of SQL and meets the needs  of novice users as well as experts. Even on an unfamiliar DBMS, database developers can focus on the data  sets they are trying to extract, rather than on the syntax, punctuation, and formatting of SQL statements.

Generating and Executing Object Scripts 

Another common task in database development and administration is to generate SQL scripts  for database objects such as tables, views, constraints, and indexes (see Figure 8). These object  scripts are often used to copy or migrate the database objects from one environment to another  (i.e. development to production, or updating the version of a database platform, such as from Oracle  11g to Oracle 12c). Because the constructs and SQL syntax are often different between platforms,  you can only copy objects between the same platforms.. 

Many database IDEs now integrate with widely used version control systems to manage and track  changes to scripts. Making version control accessible from inside the tool enforces the consistency  of database objects and SQL across developer teams. It also extends the benefits of version  management and protection beyond the traditional realm of application development to the world  of database development.

Data Comparison

Having created and run queries, developers often want to compare before- and after-versions  of resulting data sets. Similarly, they want to compare before- and after-versions of schemas or files they have generated. Every developer’s toolbox contains a few generic utilities for comparing,  but a comprehensive IDE for database development includes powerful, easily accessible features  for comparing data sets and files within the tool. 

In the IDE, developers can compare query results, with differences in table or view data shown  in highlighted, color-coded rows and columns that indicate where data was deleted, changed,  or added from one version to the other. 

Inside the same IDE, developers can compare the DDL of objects from different databases on the same  platform in a graphical interface. Differences at both database and object levels display in various colors. 

Once the differences between schemas and object scripts are visible, the tool allows developers to  synchronize the DDL of the selected target database objects to that of the source database objects.  It takes into account the preparatory steps for a successful synchronization such as reviewing  dependencies, mapping missing columns, and configuring the script. 

Using the same tool and comparison engine, developers can examine differences side by side for a  variety of common tasks: 

  • Data contents of objects that exist in multiple schemas 
  • Table/view data 
  • Folders and text files stored locally and remotely 
  • Content of application tabs currently open in the tool 
  • Content of clipboard history 

Visual Analytics

Within database teams, developers and administrators routinely manipulate data through intermediate  formats like CSV and XLS. The utilities they use to edit, import, and export records among tables,  databases, and DBMSes are table stakes in the world of database development. One example of a business objective for database developers involves analysis of a data set on which  a user makes a decision or takes an action. Instead of handing off a data set to data analysts and  sending them into different applications to create charts and dashboards, the IDE approach includes  features that enable visual analytics in the same interface where the data sets were generated.  

Visual analytics features allow users to pull query results into worksheets and easily create visualizations  of data comprehensible to the decision makers who most need them. Through the GUI, database  professionals drag and drop data sets to create charts, graphs, and dashboards they can save and share  throughout the organization. Working in the same tool used to build and run the original queries, data  analysts can build data visualizations including diagrams, pivot tables, trend lines, reference lines, box  plots, and other graphs commonly used by business managers to analyze their data. 

The visual analytics capability results in higher productivity among the database developers, DBAs,  and data analysts who make analysis of the data more useful to the entire organization.

Conclusion 

Database development in many organizations is expanding beyond traditional  DBMSes to emerging platforms like NoSQL and cloud. It involves collaboration  among database developers, DBAs, and data analysts. As the variety of  database platforms and vendors grows, it is natural for users to adopt more  tools to accomplish their tasks. 

The IDE approach to database development tools accommodates the widest  variety of needs. It smooths the way for database professionals — even those  working on unfamiliar platforms and dialects of SQL — to maximize their  productivity while minimizing their learning curve. Organizations find it to be  cost-effective because they can reduce their overall investment in database  development tools by selecting a single product that addresses multiple  platforms, instead of needing separate products for each platform. 

With a single product, users can accomplish their tasks across dozens of  DBMSes with a common interface and consistent workflow.

Aqua Data Studio 

Aqua Data Studio is productivity software for database developers, DBAs, and analysts.  It allows users to develop, access, manage, and visually analyze data. Whether in relational, NoSQL,  or cloud databases, data is easily and quickly accessible. Aqua Data Studio supports all major database  vendors, works on all major operating systems (Windows, Mac OS X, Linux), and is localized to nine  languages. With easy connectivity, simple query execution, table relationship diagramming, data visual  analytics, and core database administration, Aqua Data Studio incorporates the key capabilities needed  to help database professionals work more efficiently and keep databases running optimally. 

Since 2002, Aqua Data Studio has offered features that meet the needs of the ever-changing world of  database development. While continually improving user productivity in basic tasks — navigating databases,  creating SQL statements, generating object scripts, manipulating data — Aqua Data Studio has grown to  support emerging technologies, making it the ideal tool in the era of multiplatform database development.