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:
- Nhost Database UI (recommended).
- Hasura Console.
- 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
- Go to Database in the left menu.
- Click New table.
- Enter a name for the table.
- Add columns.
- Select a Primary Key (usually the
id
column). - (Optional) Select an Identity column. Identity is for integer columns only and is usually selected for the
id
column so theid
is automatically incremented (1,2,3...) on new rows. - (Optional) Add Foreign Keys.
- 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:
- Nhost
- SQL
CREATE TABLE "public"."customers" (
"id" bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
"name" text NOT NULL
);
Edit Table
- Go to the Database in the left menu
- Click on the context menu of the table you want to change and click Edit table.
- Edit (add, change, delete) the table's columns.
- Click Save.
Here's an example of how to edit a customers
table by adding an address
column:
- Nhost
- SQL
ALTER TABLE "public"."customers" ADD COLUMN "address" text;
Delete Table
- Go to the Database in the left menu
- Click on the context menu of the table you want to delete and click Delete table.
- Click Delete to confirm deleting the table.
Example: Delete a customers
table:
- Nhost
- SQL
DROP TABLE "public"."customers";
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.
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.
- Develop locally using the Nhost CLI.
- Push changes to GitHub.
- 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 ofCustomers
. - Use plural names for tables. E.g.
customers
instead ofcustomer
. - use underscore (
_
) instead of camelCase for table names. E.g.customer_invoices
instead ofcustomerInvoices
. - use underscore (
_
) instead of camelCase for column names. E.g.first_name
instead offirstName
.
Next Steps
- Learn PostgreSQL Tutorial - Full Course for Beginners (YouTube).
- Learn more about how to manage your Postgres database in Hasura.
- Learn about the GraphQL API.