3. Add a SQL database
In the previous step you deployed your app to production using the Encore Platform. Now you'll add a SQL database to store some data.
Add a new API endpoint
We'll add a new API endpoint, hello.There
that takes a name as input
and responds with a personalized greeting. The database will store
the names of people and how many times you've met.
Create a new file named hello/there.go
with the following contents:
package hello
import (
"context"
"fmt"
)
type ThereParams struct {
Name string
}
// There responds with a personalized greeting.
//encore:api public
func There(ctx context.Context, params *ThereParams) (*Response, error) {
greeting, err := generateGreeting(params.Name)
if err != nil {
return nil, err
}
return &Response{Message: greeting}, nil
}
// generateGreeting generates a personalized greeting for name.
func generateGreeting(ctx context.Context, name string) (string, error) {
msg := fmt.Sprintf("Hello, %s!", params.Name)
return msg, nil
}
Run your app with encore run
.
Open Encore's developer dashboard by visiting http://localhost:4060 in your browser.
Navigate to the API Documentation by clicking API in the top left.
There you'll see your new hello.There
endpoint.
Make an API Call by clicking on Call
and enter "Jane"
as your name.
You'll immediately see a personalized response:
{"Message": "Hello, Jane!"}
It works! Now it's time to add a database to track how many times you've met.
Create a database table
Inside your hello
service, add a new directory named migrations
.
Inside this folder, create a new file named 1_create_table.up.sql
, with the
following contents:
CREATE TABLE people (
name TEXT PRIMARY KEY,
count INT NOT NULL
);
This will create a database table named people
, with name
as the primary key
along with count
tracking how many times you've met.
Query the database
Now it's time to use the database in your API.
Go back to hello/there.go
and modify the import
declaration at the top
to include encore.dev/storage/sqldb
:
import (
"context"
"fmt"
"encore.dev/storage/sqldb"
)
Then, modify the generateGreeting
function at the bottom to look like this:
func generateGreeting(ctx context.Context, name string) (string, error) {
// Make an "UPSERT" (insert or update) query,
// inserting a row if it doesn't already exist
// and otherwise updating it by incrementing count.
var count int
err := sqldb.QueryRow(ctx, `
INSERT INTO "people" (name, count)
VALUES ($1, 1)
ON CONFLICT (name) DO UPDATE
SET count = people.count + 1
RETURNING count
`, name).Scan(&count)
if err != nil {
return "", err
}
var greeting string
if count > 1 {
greeting = fmt.Sprintf("Hey again %s! We've met %d time(s) before.", name, count-1)
} else {
greeting = fmt.Sprintf("Nice to meet you, %s!", name)
}
return greeting, nil
}
This will query the database, and insert a row for the given name with count = 1
.
If the row already exists, it instead increments count
.
Finally it returns the stored count
value.
We scan this value into the count
variable and then generate a personalized
greeting based on the count.
Try it out by running encore run
. Either open the API Documentation and try again,
or run in a separate terminal:
$ curl http://localhost:4060/hello.There -d '{"Name": "John"}'
{"Message": "Nice to meet you, John!"}
$ curl http://localhost:4060/hello.There -d '{"Name": "John"}'
{"Message": "Hey again John! We've met 1 time(s) before."}
$ curl http://localhost:4060/hello.There -d '{"Name": "John"}'
{"Message": "Hey again John! We've met 2 time(s) before."}
Wonderful, it works!
Deploy to production
To deploy this to production, simply push this code to Encore:
$ git add -A
$ git commit -m "Add hello.There endpoint with a personalized greeting"
$ git push
[... stuff ...]
remote: refs/heads/main: triggered build nba83nfoa2
To encore://hello-world-4x3b
84a7586..17f9750 main -> main
Head over to your app on app.encore.dev.
Once the deploy completes, go to the API page and make an API call
to your new hello.There
endpoint.
You should see the same thing as before:
{"Message": "Nice to meet you, John!"}
{"Message": "Hey again John! We've met 1 time(s) before."}
Excellent! You now have a database up and running in production, and an API hooked up to it.
Next steps
This concludes the Encore tutorial, but there's lots more you can do with Encore. Explore the documentation, notably the How-to Guides to get more ideas on how you can use Encore to build better software faster.