# Using Databases

Concordia [can use databases](https://concordialang.gitbook.io/concordialang/introduction/databases) to retrieve test data or to set the testing environment up before or after running test scripts.

## Installation

### Installing

{% hint style="info" %}
To reduce the number of downloaded packages, Concordia Compiler 2 does not install database drivers by default anymore - as version 1 did.
{% endhint %}

Use **`--db-install`** plus the driver name. Example:

```
npx concordia --db-install mysql
```

Available driver names:

| Driver Name | Database      | note              |
| ----------- | ------------- | ----------------- |
| `csv`       | CSV files     |                   |
| `xlsx`      | Excel files   |                   |
| `firebase`  | Firebase      |                   |
| `ini`       | INI files     |                   |
| `json`      | JSON files    | Already installed |
| `mysql`     | MySQL         |                   |
| `adodb`     | MS Access     | *Windows only*    |
| `mssql`     | MS SQL Server |                   |
| `postgres`  | PostgreSQL    |                   |
| `sqlite`    | SQLite        |                   |

Database drivers are installed as development dependencies of your application.

{% hint style="info" %}
You can also install or uninstall them with NPM, prefixing the driver name with `database-js-`. Example: `npm i -D database-js-mysql`.
{% endhint %}

### Uninstalling

Use **`--db-uninstall`** plus the driver name. Example:

```
npx concordia --db-uninstall mysql
```

### Upgrading

Just uninstall and then install it again.

## Usage

A [`Database`](https://concordialang.gitbook.io/concordialang/language/concordia#database) is **global** declaration. You must declare it once and then import its file in other features. Example:

{% tabs %}
{% tab title="db.feature" %}

```
Database: My DB
  - type is "mysql"
  - host is "127.0.0.1"
  - name is "acme"
  - username is "tester"
  - password is "secret"
  
Database: Another DB
  - type is "json"
  - path is "./db.json"
```

{% endtab %}
{% endtabs %}

A database can be referenced inside UI properties and Test Events by its name between `[` and `]`.  Example: `[My DB]`.  To reference a database table, separate the table name from the database name with with a dot (`.`) - e.g. `[My DB.profession]`. Let's see an example:

{% tabs %}
{% tab title="example.feature" %}

```
import "db.feature"

# ... other declarations here ...

UI Element: Profession
  - value comes from "SELECT name FROM [My DB.profession]"
    Otherwise, I see "Profession not found."
  - required
    Otherwise, I see "Please select the profession."
    
Before Each Scenario:
  Given that I connect to [My DB]
  When I run the script 'DELETE FROM profession'
    and I run the script 'INSERT INTO profession (name) VALUES ("Enginner"), ("Accountant")' 
    
```

{% endtab %}
{% endtabs %}

Instead of reinventing the wheel, Concordia uses SQL to query and modify databases and files. Even JSON and INI files can be queried with SQL.

In the example above, the property `value` from the UI Element "Profession" indicates that its (valid) values are retrieved from the table "profession" that belongs to the database declared as "My DB". The test event `Before Each Scenario` was declared to connect to the database, clean the table "profession", and then insert new records that the UI Element "Profession" will use.

## See also

* [Supported syntax](https://concordialang.gitbook.io/concordialang/language/concordia#database)
* [Declaring Tables](https://concordialang.gitbook.io/concordialang/language/concordia#table)