Databases

Encore makes it easy to create, connect to, and query PostgreSQL databases, and to perform schema migrations over the life of your application.

Creating a database

To create a database, you create a directory in your Go service package named migrations. Inside you create a file representing your first schema migration named like so: 1_create_tables.up.sql. Inside the file you perform the desired SQL statements. For example:

CREATE TABLE todo_item (
    id SERIAL PRIMARY KEY,
    description TEXT NOT NULL,
    done BOOLEAN NOT NULL DEFAULT FALSE
);

That's it! Your database will be created by Encore when you run your application.

Querying a database

To query your newly created database table from your code, start by importing encore.dev/storage/sqldb. Then, use one of the top-level functions in the package, which mirror the ones provided by the standard library package database/sql. For example, to insert a new row into the todo_item table, you might write:

package todo

import (
    "context"
    "encore.dev/storage/sqldb"
)

type CreateParams struct {
    Description string
}

type CreateResponse struct {
    ID string
}

// Create creates a new todo item.
// encore:api public
func Create(ctx context.Context, params *CreateParams) (*CreateResponse, error) {
    var id string
    err := sqldb.QueryRow(ctx, `
        INSERT INTO "todo_item" (description)
        VALUES ($1)
        RETURNING id
    `, params.Description).Scan(&id)
    if err != nil {
        return nil, fmt.Errorf("todo.Create: %v", err)
    }
    return &CreateResponse{ID: id}, nil
}

Notice how we don't have to worry about how to connect to the database, handle permissions, or anything of the sort. Encore takes care of that. More importantly, this code works out of the box whether you're working locally or deploying to production.

Subscribe to our Engineering Blog

The latest ideas to help you build better software.

You can unsubscribe at any time.

© 2020 Encore. All rights reserved.