![](<https://www.versio.io/img/icons/database-schema.svg>)

# Database schema importer

The Versio.io database schema importer enables importing and monitoring database schemas with their tables and views. These imports allow you to document and detect changes to ensure auditability.

## Supported database management systems (DBMS)

The database schema importer supports the following relational database management systems:

- Oracle database
- Microsoft SQL Server
- MySQL
- MariaDB
- PostgreSQL

## Configuration

You can find the configuration option in Versio.io at `Environment settings > OneImporter > Configurations > Database schema`.

Here you can edit all existing configurations, create new configurations or delete existing configurations. Each configuration can import multiple databases from a single DBMS. To add a new configuration, enter a `Name` and click `Add` to proceed to the configuration. To edit an existing configuration, click the arrow on the left of your importer name to expand the configuration options.

The following configuration options are available:

| Field | Description | Advice |
| --- | --- | --- |
| **Name** | Meaningful name | Include database name/type |
| **Active** | Option to activate and deactivate the configuration. Data is only imported if the configuration is activated. |  |
| **Database type** | Select required DBMS type |  |
| **Host or IP** | Host name or IP address of the DBMS |  |
| **Port** | Listening port of the DBMS | The default port is set automatically |
| **Username** | User name for DBMS access |  |
| **Password** | Password for DBMS access |  |
| **Database name or SID** | Name or SID of the databases | Only databases located in the above DBMS can be specified |
| **Entity name for database instances** | Name of the entity that database instances are assigned to | Default value is `database`. Please use your own entity names only if you are familiar with the configuration of entities and entity groups. |
| **Entity name for database table instances** | Name of the entity that database table instances are assigned to | Default value is `database-table`. Please use your own entity names only if you are familiar with the configuration of entities and entity groups. |
| **Entity name for database view instances** | Name of the entity that database view instances are assigned to | Default value is `database-view`. Please use your own entity names only if you are familiar with the configuration of entities and entity groups. |
| **Executing OneImporter** | Select a Versio.io OneImporter to import the data based on the importer configuration. | Requires an installed OneImporter: [Installation instructions](<https://docs.versio.io/setup-one-importer/index.md>) |
| **Schedule time (cron format)** | Determine the frequency of imports in CRON format | Learn more about CRON format: [https://en.wikipedia.org/wiki/Cron](<https://en.wikipedia.org/wiki/Cron>) |

*Table: Database schema configuration parameters*

The following image is an example of a database schema configuration:

![Versio.io database schema importer configuration](<https://docs.versio.io/img/importer/database-schema/database-schema-configuration.png>) *Figure: Example of a Versio.io database schema configuration*

## Database user rights

The following database statements are executed by the importer to determine the database schema (tables, views, indexes) depending on the database management system used. The database user to be configured must have the appropriate rights to execute these statements:

| Database management system | Executed SQL statements |
| --- | --- |
| Oracle Database | SELECT table\_name FROM all\_tables WHERE owner= '`USERNAME`'<br>SELECT \* FROM sys.all\_views WHERE owner = '`USERNAME`'<br>SELECT \* FROM all\_tab\_columns WHERE table\_name= '`table-name`' AND owner= '`USERNAME`'<br>SELECT \* FROM all\_ind\_columns WHERE table\_owner= '`USERNAME`' AND table\_name = '`table-name`' |
| Microsoft SQL Server | SELECT \* FROM sys.tables<br>SELECT \* FROM INFORMATION\_SCHEMA.VIEWS<br>exec sp\_columns `table-name`<br>SELECT \* FROM sys.indexes WHERE is\_hypothetical = 0 AND index\_id != 0 AND object\_id = OBJECT\_ID('`table-name`') |
| MySQL + MariaDB | SHOW TABLES<br>SELECT \* FROM INFORMATION\_SCHEMA.VIEWS WHERE TABLE\_SCHEMA = `table-name`<br>SHOW FULL COLUMNS FROM `table-name`<br>SHOW INDEXES FROM `table-name` IN `database-name` |
| PostgreSQL | SELECT \* FROM pg\_catalog.pg\_tables WHERE schemaname= 'public'<br>SELECT \* FROM information\_schema.views WHERE table\_schema = 'public'<br>SELECT \* FROM information\_schema.COLUMNS WHERE table\_name= '`table-name`'<br>SELECT \* FROM pg\_indexes WHERE schemaname = 'public' AND tablename = '`table-name`' |

## Examples

The Versio.io OneImporter imports the database schema based on a specific configuration and stores the different instances in entities defined by you (default: `database`, `database-table` and `database-view`). To view the imported instances in Versio.io, select 'Asset &amp; Configuration Repository → Instance Viewer'. There you can select your specific instance and click it for a detailed view in the instance history viewer.

The following two images are examples of an imported database schema:

![Versio.io database instance overview](<https://docs.versio.io/img/importer/database-schema/database-instance.png>)  
*Figure: Overview of a Versio.io database instance (left) and topology of related table instances (right)*

![Versio.io database table instance overview](<https://docs.versio.io/img/importer/database-schema/database-table-instance.png>) *Figure: Overview of a Versio.io database table instance (left) and topology of the related database instance (right)*
