Connecting databases

A datasource is a database registered in an app: a connection with a name, a type and credentials, available to everything in that app that needs real data — the APIs' SQL steps, the scripts, the data model (and, through it, the GraphQL API and the screens). The connection is registered once; from then on the whole app refers to it by name.

In the example app Gestão de Clientes, the datasource is called crm — a PostgreSQL database with the accounts, contacts and opportunities tables. It is the one you will see in every figure of this chapter.

#Where you find the datasources

There are two doors in, and you will use both:

• The Data panel — in the sidebar of the app workspace, Data tab, Datasources section. It is the everyday place: each datasource expands into a tree with the database's tables, views and programming, and each one's menu gives access to every action.
• The Datasources page — the app's complete list, with each connection's type and creation date ("Databases accessible to this app's APIs and scripts."). It is also where the platform's shortcuts point — for example the Add the first one link that appears on a SQL step when the app has no datasources yet. On small screens, the app's navigation shows Datasources directly.

#Creating a datasource

You will need the database's connection details: server address, port, database name, user and password — the exact fields vary with the type (the Supported types page details each one).

#From the Data panel

1. Open the sidebar's Data tab.
2. In the Datasources section, click the + button (New datasource). A modal opens — "Connect a database to this app. Everything is encrypted at rest."
3. Fill in the Internal name and choose the Type.
4. Fill in the connection fields of the chosen type.
5. Click Test connection and wait for the "Connection OK." — the Testing the connection and security page explains what the test does and how to read the errors.
6. Click Save. The tree now shows the datasource, and its Model screen opens next — ready for you to import tables.

#From the Datasources page

1. Open the Datasources page and click Add datasource.
2. The New datasource page opens — "Register the credentials and test the connection before saving. Everything is encrypted at rest." The form has two sections: Identification (internal name and database type) and Connection (credentials and connection parameters).
3. Fill in, test with Test connection, and click Create.
4. You return to the list, with the confirmation "Datasource created.".

#The internal name is the identity

The Internal name (e.g. warehouse-prod, or crm in our example) is not a decorative label — it is the identifier by which the APIs and the scripts call the connection:

• In a Python script: db("crm").query("select * from contas").
• In an API's SQL step: the step's Datasource field lists the registered names.

Which is why:

Rule	What happens if it fails
Unique within the app	"A datasource with the name … already exists in this project."
No collisions with another datasource	"The name … collides with the datasource …"
Stable — change it only on purpose	See "Renaming a datasource" below

#Editing the connection

The database moved server, changed password, or you want to turn SSL on:

1. In the Data panel, open the datasource's ⋯ menu and choose Edit connection.
2. The modal opens with everything filled in except the password — the field is now called Password (empty = keep). Leave it blank to keep the current password; type to replace it.
3. Change what you need, click Test connection to confirm, and then Save. The confirmation is "Connection saved.".

#Renaming a datasource

Double-click the datasource's name in the tree of the Data panel and type the new name (or change the Internal name in Edit connection). The tables already imported into the model follow the new name automatically.

#Deleting a datasource

1. On the Datasources page, click the bin icon on the datasource's row — or, in the Data panel, open the ⋯ menu and choose Delete datasource.
2. Read the confirmation carefully: "APIs and scripts that use this datasource will no longer be able to run. This action is permanent." In the tree, the warning adds that the associated model leaves too.
3. Confirm with Delete datasource.

What the deletion removes — and what it does not touch:

Goes	Stays
The registered connection (name, type, credentials)	The database itself — nothing is deleted on the origin server
That datasource's tables in the app's model	The APIs and scripts that used it (they keep failing until they point at another datasource)

#Where the connection is used

Registering the datasource is the first step; the value is in what it unlocks:

• The object tree — expand the datasource in the Data panel to see Tables, Views and Programming (functions, procedures and triggers). Each table has View data (opens the console with a select ready) and Import to model.

• The data model — the imported tables become entities of the model, with relations and friendly names. It is the model that feeds the app's GraphQL API and the APIs' Table blocks. The data model chapter covers this in depth.

• The APIs — on a SQL step, pick the database in the Datasource field and write the SQL query. The API's arguments come in as :nomeDoArg and the previous step's result as :prev — "Values are always parameterised — never concatenated." There is a Return only the first row switch for single-record queries.

• The scripts — in Python, import the access and query by name:

  from api_manager import db
  
  def main(input):
      contas = db("crm").query(
          "select id, nome from contas where cidade = $1", ["Lisboa"]
      )
      return {"total": len(contas)}
  

  The parameter markers ($1, ?, :1, …) vary with the engine — the table is on the Supported types page.

#Why don't I see…?

• …the Add datasource button? Creating, editing and deleting datasources is reserved for whoever has the administrator profile in the app. With a developer profile you consult the list and use the datasources, but you do not touch the connection.
• …datasources on my API's SQL step? The app has none yet — the step shows "This app has no datasources." with the Add the first one link.
• …tables on the API's Table block? The Table block reads from the model, not from the datasource directly: "Import tables in a datasource's "Model" tab first."