Skip to main content

Database

Every Nhost project comes with its own Postgres database. Postgres is the world's most advanced open-source relational database and it's the most popular SQL database for developers.

There are three ways of managing your database:

  1. Nhost Database UI (recommended).
  2. Hasura Console.
  3. Connect directly to the database.

Schemas

Generally, you should use the public schema for your project. It's also ok to add custom schemas for more advanced usage.

The two schemas auth and storage are reserved for Nhost Auth and Nhost Storage. You're allowed to modify permissions and add relationships. However, never modify any tables or remove relationships that were added by Nhost inside the auth and storage schemas.

Manage Tables

Create Table

  1. Go to Database in the left menu.
  2. Click New table.
  3. Enter a name for the table.
  4. Add columns.
  5. Select a Primary Key (usually the id column).
  6. (Optional) Select an Identity column. Identity is for integer columns only and is usually selected for the id column so the id is automatically incremented (1,2,3...) on new rows.
  7. (Optional) Add Foreign Keys.
  8. Click Create.

When a table is created it is instantly available through the GraphQL API.

Here's an example of how to create a customers table:

Edit Table

  1. Go to the Database in the left menu
  2. Click on the context menu of the table you want to change and click Edit table.
  3. Edit (add, change, delete) the table's columns.
  4. Click Save.

Here's an example of how to edit a customers table by adding an address column:

Delete Table

  1. Go to the Database in the left menu
  2. Click on the context menu of the table you want to delete and click Delete table.
  3. Click Delete to confirm deleting the table.

Example: Delete a customers table:

Postgres Access

It's possible to access your Postgres database directly with your favorite Postgres client.

info

For projects older than September 2022, it's not possible to connect directly to the database via a connection string. We're working on a one-click fix that can be expected to be available in October 2022.

Go to Settings in the left menu and click on Database. You'll find the connection string and credentials to connect to your database.

Database Connection Info

Reset Postgres Password

It's possible to reset the database password that was provided when the project was created.

  1. Go to the Overview page of your project.
  2. Click on Project Settings.
  3. Click on Reset Database Password.
  4. Either copy the newly automatically generated password, or type your own new database password.
  5. Click Reset Database Password.

Migrations

To track database changes, use the Nhost CLI to develop locally and use our Git integration to automatically deploy database migrations live.

  1. Develop locally using the Nhost CLI.
  2. Push changes to GitHub.
  3. Nhost automatically deploys changes.

Learn how to do development with the Nhost CLI.

Seed Data

Seed data is a way of automatically adding data to your database using SQL when a new environment is created. This is, for the moment, only applicable when you're using the Nhost CLI to develop locally. When you're running nhost up for the first time, seed data is added.

In the future, seed data will also be added to new preview environments.

Seed data should be located in nhost/seeds/default/ and are executed in alphabetical order.

Example: Two seed scripts with countries and products.

nhost/seeds/default/001-countries.sql
nhost/seeds/default/002-products.sql

Backups

Databases on the Pro and Enterprise plans are automatically backed up daily.

Best Practices

  • Use lower-case names for tables. E.g. customers instead of Customers.
  • Use plural names for tables. E.g. customers instead of customer.
  • use underscore (_) instead of camelCase for table names. E.g. customer_invoices instead of customerInvoices.
  • use underscore (_) instead of camelCase for column names. E.g. first_name instead of firstName.

Next Steps