Integration must-have - Database (3 of 3)

Integration Must-Have

We present the integration tools – elements that you cannot do without in your daily work. Without them, using systems is much more difficult. Fortunately, enterprise service bus servers help you to connect different systems. Among them stands out the WSO2 Enterprise Integrator, an Open Source data bus with many integration options and over 250 connectors for external systems.

After Web Service and REST , it’s time for the third article in the must-have series.

Database

It is difficult to imagine any system without saving data. The easiest way to store data in databases. Often, the systems even have handy operational databases needed for day-to-day work. Databases can store data in tables, where there is a predetermined column structure like in a spreadsheet or without a fixed structure – storing entire documents with data.

Relational databases

In relational databases, data is stored in tables and records similar to a spreadsheet. The structure of tables and columns is predetermined and each column has a specific data type and length. This approach allows for effective storage and retrieval of data in accordance with a given pattern. You can define connections (relations) between tables in order to define dependencies between tables-objects (e.g. invoice and its individual items). Hence their name. Another common name for the database is SQL, which is the language used to “talk” to the database.

Relational databases are the most common way to work with data. They are most often used to record well-defined data, for example on transactions, financial, accounting, banking, and inventory and warehouse management systems. They can handle large volumes of data quickly. The biggest problem that you have to deal with is changes in structure. If we want to add some information or change its type, we need to modify the structure accordingly – create a new column or change its type.

There are many products that can be used to view, analyze, and run reports. The databases themselves have add-ins with which you can create aggregations and data warehouses.

NoSQL databases

NoSQL databases store data in a different way. There are different models for data storage.

Key-value

The easiest way to store data is with the key-value model. The data is stored in a dictionary or map structure. Most often they are used for: storing images, session data, shopping cart or as caches.
Key-value stores always use the primary key, usually have high performance and are easily scalable. The key-value store should be queried by the key. An example of such a database is Memcached, Redis, ZooKeeper, Apache Ignite or Oracle NoSQL Database.

Documents

The most popular way is to store entire documents. The data format can be JSON, XML or YAML. Databases allow you to search for documents as well as what they contain in the form of a key-value. The document is used for event logging, web analytics or real-time analytics, e-commerce applications. Data is organized in different ways: into collections, tags, metadata, and hierarchies. An example of a document base is MongoDB, Apache CouchDB or IBM Domino.

Column family

The column family most closely resembles relational databases. The column family corresponds to a table, and each key-value pair is written to a row. Additionally, each line has a time stamp so that we can keep the data in sync. The column family is used for event logging, content management, and data expiring. Cassandra or Amazon SimpleDB are an example of a database.

Graph

This data model works well for data that have related elements. These can be social relationships, transport connections or road maps. The graph database is based on nodes (entities), edges (relations) and their properties. It is used in social networks and for routing. An example of a graph database is Amazon Neptune, Oracle RDF or SAP HANA.

Security

The security of databases depends on how the services are made available. We must remember to properly secure access to services and allow them to be called only by authorized systems. Data should only be shared via HTTPS to avoid suspicion. Additionally, calls should be secured with authorization tokens or API keys to make sure that the data will be made available only to authorized systems and persons.

WSO2 Enterprise Integrator support

The WSO2 EI data bus has built-in database support. It is a DataService element thanks to which we have direct access to the data. In DataService, we define connection and queries. WSO2 EI can connect to RDBMS relational databases as well as NoSQL. All kinds of queries are supported: SELECT, INSERT, UPDATE, DELETE, MERGE, as well as calling stored procedures and code blocks. We can also define calling parameters and set their default values. Later, we define the structure and format of the response.

We can choose the XML format, in which we can pass data as elements or attributes, and the JSON format, in which we define the response template. Finally, we define how to invoke the service. Here, too, we have two options to choose from. We can define Web Service invocation methods or REST invocation paths. We can also use both ways interchangeably.

Help!

I need to connect to the database. How do I share a table via the Web Service? The new system only uses REST, how to connect to my database?
We have several years of experience in building and implementing integration. Let me know, we will help you for sure!

Integration must-have – Database (3 of 3)