Create and deploy a backend API with PostgreSQL database in Go
Tags: go, postgres, tutorial, programming
Published: 2023-10-24 09:05:55

## ✨ TL;DR

In this guide we'll build a simple Hello World backend service in Go, with a single API endpoint and a PostgreSQL database to keep track of how many times you've said hello.

It's implemented using [Encore](https://encore.dev) and we'll deploy it to Encore's free development cloud.

## 💽 Install Encore

Install the Encore CLI to run your local environment:
- **macOS:** `brew install encoredev/tap/encore`
- **Linux:** `curl -L https://encore.dev/install.sh | bash`
- **Windows:** `iwr https://encore.dev/install.ps1 | iex`

## 🔨 Create your app

Create a new Encore application from a starter template with this command:

```bash
encore app create my-app --example=sql-database
```

## 🤔 Using Databases with Encore

In this starter app there is already a database defined. But, let's take this opportunity to learn a bit about how Encore helps you create and use databases, so you can more easily extend this start project further.

### Creating PostgreSQL databases

Encore treats SQL databases as logical resources and natively supports **PostgreSQL** databases.

To create a database, import `encore.dev/storage/sqldb` and call `sqldb.NewDatabase`, assigning the result to a package-level variable.
Databases must be created from within an [Encore service](https://encore.dev/docs/primitives/services-and-apis).

For example, create the `todo` database and assign it to the `tododb` variable:

```
package todo

var tododb = sqldb.NewDatabase("todo", sqldb.DatabaseConfig{
	Migrations: "./migrations",
})
```

As seen above, the `sqldb.DatabaseConfig` specifies the directory containing the database migration files,
which is how you define the database schema.
See the [Defining the database schema](#defining-the-database-schema) section below for more details.

With this code in place, Encore will automatically create the database when starting `encore run` (locally)
or on the next deployment (in the cloud). Encore automatically injects the appropriate configuration to authenticate
and connect to the database, so once the application starts up the database is ready to be used.

### Defining the database schema

Database schemas are defined by creating *migration files* in a directory named `migrations`
within an Encore service package. Each migration file is named `<number>_<name>.up.sql`, where
`<number>` is a sequence number for ordering the migrations and `<name>` is a
descriptive name of what the migration does.

On disk it might look like this:

```
/my-app
├── encore.app                       // ... and other top-level project files
│
└── todo                             // todo service (a Go package)
    ├── migrations                   // todo service db migrations (directory)
    │   ├── 1_create_table.up.sql    // todo service db migration
    │   └── 2_add_field.up.sql       // todo service db migration
    ├── todo.go                      // todo service code
    └── todo_test.go                 // tests for todo service
```

Each migration runs in order and expresses the change in the database schema
from the previous migration.

**The file name format is important.** Migration files must be sequentially named, starting with `1_` and counting up for each migration.
Each file name must also end with `.up.sql`.

The first migration usually defines the initial table structure. For example,
a `todo` service might start out by creating `todo/migrations/1_create_table.up.sql` with
the following contents:

```sql
CREATE TABLE todo_item (
    id BIGSERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT false
);
```

## 🏁 Run your app locally

With that out of the way, let's run our app locally! 🚀

First, make sure you have [Docker](https://docker.com) installed and running. This is required to run Encore applications with SQL databases.

Now start your app by running this command from the app root directory:

```bash
encore run
```

You should see something like this:

![Encore run](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k0a5uutjlac19q9xg4ew.png)

👉 Now open <http://localhost:9400/> to view Encore's [local developer dashboard](https://encore.dev/docs/observability/dev-dash), which gives you tools like an API explorer, local tracing, API docs and architecture diagrams.

![Local Dev Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rwdudk7t5yadva2vmbem.png)

## 📞 Call your API

To see that your app is working, you can ping the API from the API explorer in the local dev dashboard or using this cURL command:

```bash
curl 'http://localhost:4000/hello.There' -d '{"Name":"Gopher"}'
```

You should see this response:

```bash
{
  "Message": "Nice to meet you, Gopher."
}
```

Now ping it again with the same name, and if you see this response your app is working and names are being stored in the database! 🎉

```bash
{
  "Message": "Hey again, Gopher! We've met 1 time(s) before."
}
```

## 🚀 Deploy to the cloud

Deploy your backend to a staging environment in Encore's free development cloud:

```bash
git add -A .
git commit -m 'Initial commit'
git push encore
```

👉 Then head over to the [Cloud Dashboard](https://app.encore.dev) to monitor your deployment and find your production URL.

From there you can also see metrics, traces, connect your app to a GitHub repo to get automatic deploys on new commits, and connect your own AWS or GCP account to use for deployment.

![Encore Cloud Dashboard](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/7kmyqylyk7lxvuvosl4k.png)

### 🎉 Great job - you're done!

You now have the start of a scalable Go backend app running in the cloud, complete with PostgreSQL database.

Keep building with these Open Source [App Templates](https://encore.dev/templates). 👈

If you have questions or want to share your work, join the developers hangout in [Encore's community Slack](https://encore.dev/slack). 👈