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.

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 under Settings -> Database -> Reset 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​