Creating a database schema from models

Database schemas based on your models can be created and updated through API Designer, for data sources that support the models. This enables you to develop your models first, and create (and update) your database schema to match them. This process is sometimes referred to as "auto-migration" and is supported by data source connectors for MongoDB, MySQL, Oracle, PostgreSQL, and SQL Server.

Before you begin

Before you begin, you must install the developer toolkit on your local machine. For details, see Installing the toolkit.

You must also do the following:

Create a LoopBack® project. For more information, see Tutorial: Creating a LoopBack project from the command line.
Make the project root directory your working directory; for example:
```
cd acme-bank
```
Change directories to your LoopBack project and enter the following command:
```
apic edit
```
After a brief pause, the console displays this message:
```
Express server listening on http://127.0.0.1:9000
```
API Designer opens in your web browser, initially displaying the login page if you haven't logged in recently.

Note: The login page prompts you to Sign in with IBM Cloud. Enter your IBM Cloud credentials, which authenticates you on IBM Cloud and provides access to the API Manager features such as Publish, Explore, and Analytics. You will continue to work in API Designer locally to create APIs, models and data sources.
Note: If you need to run the editor on a different port, use the following command:
```
PORT=port_number apic edit
```
```
set PORT=port_number && apic edit
```
where port_number is the port number to use.
Create a data source of one of the supported types listed in the following section, with at least one model connected to the data source. For more information, see Tutorial: Creating a model and a data source in the API Designer.

About this task

API Designer can create and update a database schema based on existing models. Doing this will create (or modify) a table for each model, and a column in the table for each property in the model, if the data source is a relational database.

Note: This process creates models including Loopback built-in models, as well as any custom models you have created.

Subsequently, if your models change, you can recreate or update (synchronize) the database schemas accordingly if you need to adjust the database to match the models. When updating, this process will alter the database schema based on how the models have changed.

The following data sources support this feature:

MongoDB. Since MongoDB is a "schema-less" database, the process will only create and update indexes. When updating a schema, if you change existing properties, then existing records in MongoDB will have the old property, but new records will have the new definition with the modified property.
MySQL
Oracle
PostgreSQL
SQL Server

For more information on how this feature works in LoopBack, see Creating a database schema from models.

Procedure

Click .
Click the data source where you want to create a schema. The data source must use one of the supported connectors listed above.
Click Update Schema . The Update Database Schema dialog opens, listing all the models that use the current data source.
Click the models that you want to use to create or update the data source schema.
Note: If you update a schema with an existing table, and properties have been deleted from the corresponding model, the columns corresponding to the deleted properties may be removed from the table, and existing data may be destroyed, depending on the specific connector implementation and the underlying database permissions.
Click Update Schema. If the existing tables already exist, this process will alter the tables based on the structure of the corresponding models.