How to Build a Server with Hanami::API and Postgres
Published: 2023-06-20 17:50:38

---
title: How to Build a Server with Hanami::API and Postgres
published: true
date: 2023-05-16 22:46:41 UTC
tags: 
canonical_url: https://carlosmv.hashnode.dev/how-to-build-a-server-with-hanamiapi-and-postgres
---

DISCLAIMER: This is a basic article about Hanami::API, showing how to perform CRUD operations using Postgres through PG driver. I'm writing this article to continue learning about Ruby so probably my code is not idiomatic.

Hanami-API is a lightweight and fast Ruby framework for building HTTP APIs. It is a part of the Hanami web framework, which is made up of smaller, single-purpose libraries that can be used independently or together in a Hanami application. The framework provides a simple and effective way to build and deploy HTTP APIs in Ruby, allowing developers to create endpoints with different HTTP methods, extract parameters from the URI, and parse the payload of incoming requests. In this article, we will explore how to get started with Hanami-API by creating endpoints for a simple to-do list application.

## Hanami

Hanami is a **full-stack** Ruby web framework. It's made up of smaller, single-purpose libraries.

Hanami is made of the following libraries:

- [**Hanami::Router**](https://github.com/hanami/router) - Rack-compatible HTTP router for Ruby

- [**Hanami::Controller**](https://github.com/hanami/controller) - Full-featured, fast and testable actions for Rack

- [**Hanami::View**](https://github.com/hanami/view) - Presentation with a separation between views and templates

- [**Hanami::Helpers**](https://github.com/hanami/helpers) - View helpers for Ruby applications

- [**Hanami::Mailer**](https://github.com/hanami/mailer) - Mail for Ruby applications

- [**Hanami::Assets**](https://github.com/hanami/assets) - Assets management for Ruby

According to its [README](https://github.com/hanami/hanami), these components are designed to be used independently or together in a Hanami application.

# Hanami::API

It's a minimal, extremely fast, lightweight Ruby framework for HTTP APIs.

### PG driver

Pg is the Ruby interface to the Postgres RDBMS. It works with Postgres 9.3 or later.

### Requirements

- Ruby 3.0+ installed.

### Installation

First, we create a folder for our project. And in it, we create two files, `gemfile` and [`config.ru`](http://config.ru).

Inside `gemfile` file, we write the following lines:

```
gem "hanami-api"
gem "puma"

```

Then, inside [config.ru](http://config.ru), we write the following code:

```
# frozen_string_literal: true

require "bundler/setup"
require "hanami/api"

class App < Hanami::API
  get "/" do
    "Hello, world"
  end
end

run App.new

```

Then, we run `bundle exec rackup` in the command line.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1679946969870/e6ddbbb6-2800-4664-9b70-a36d7a18cb03.png?auto=compress,format&format=webp)

## Creating the endpoints

We create a new file, `app.rb`, to write all our endpoints.

We will have 5 endpoints.

```
class App < Hanami::API
    list_tasks = [
        {"id": 1, "title": "Buy almonds"},
        {"id": 2, "title": "Buy Nuts"}
    ]
    get "/" do
      "Hello, world"
    end

    get "/tasks" do
       json list_tasks
    end

```

In `app.rb` file, we create the `App` class. In this class, we will put all our endpoints. We create an array with 2 hashmaps with items to buy.

We define a GET endpoint with the URI `/tasks`. This endpoint will show all the elements in the array. We use the `json` method to send the response in JSON format.

We have to make a few changes in the [`config.ru`](http://config.ru) file. We replace the `App` class with `require_relative 'app'`.

```
require "bundler/setup"
require "hanami/api"
require "hanami/middleware/body_parser"

require_relative 'app'

run App.new

```

Now, we run `bundle exec rackup` .

**database.rb**

We create a `database.rb` file, to initialize the database and write the controllers to perform CRUD operations.

```
require "pg"

class Database

    DB_HOST = 'localhost'
    DB_PORT = 5432
    DB_NAME = 'hanami_db'
    DB_USER = '<USER>'
    DB_PASSWORD = '<PASSWORD>'

    def initialize(db_name:DB_NAME, user: DB_USER, password:DB_PASSWORD, host: 'localhost', port: 5432)
        @conn = PG.connect(dbname: db_name, user: user, password: password, host: host, port: port)
      end

   def close
        @conn.close
    end 
end

```

In this code snippet we define several constants `DB_HOST`, `DB_PORT`, `DB_NAME`, `DB_USER`, and `DB_PASSWORD` to store the details of the database connection.

This class has an `initialize` method that accepts several arguments for database `db_name`, `user`, `password`, `host`, and `port`. It sets instance variable `@conn` using the `PG.connect` method call with the provided values for `db_name`, `user`, `password`, `host`, and `port`.

The `PG.connect` method call seems to be using the `pg` gem which is a PostgreSQL client for Ruby

The `close` method is defined to close the `@conn` object.

**Create a record**

```
def create_records(params)
        sql = "INSERT INTO records (name, profession) VALUES ($1, $2) RETURNING *"
        result = @conn.exec(sql, params)
        return result
    end

```

The `create_records` expects a parameter `params` to be passed into it. To perform an insert the data passed into the `records` table.

The `sql` variable is a string containing an SQL statement that specifies the insertion query. It contains placeholders `$1` and `$2` for the values of the `name` and `profession` of the new record to be inserted.

The method then calls the `exec` method on the instance variable `@conn`, passing in the `sql` variable and `params` as arguments. The `exec` method is used to execute an SQL query through the open database connection using the provided `params`.

**Retrieve all the records**

```
def all_records
        sql = "SELECT id, name, profession FROM records"
        result = @conn.exec(sql)
        return result
    end

```

This method performs a selection query on a `records` table to return all records.

The `sql` variable is a string containing an SQL statement that selects all columns from the `records` table.

The method then calls the `exec` method on the instance variable `@conn`, passing in the `sql` variable as an argument. The `exec` method is used to execute an SQL query through the open database connection.

**Retrieve a record**

```
def get_record(params)
        sql = "SELECT * FROM records WHERE id = $1"
        result = @conn.exec(sql, params)
        return result

    end

```

The method `get_record` expects a single parameter to be passed into it. The parameter is a value to be inserted into the `id` column in the `records` table.

The method first creates an SQL statement that selects all columns from the `records` table where the `id` column equals the passed parameter value. The `sql` variable is a string that includes a parameter placeholder, `$1`, which is a reference to the first parameter passed into the method.

The method then calls the `exec` method on the instance variable `@conn`, passing in both the `sql` variable and the `params` variable as arguments. The `exec` function replaces the `$1` placeholder in the SQL string with the value of the `params` variable.

The result of the query execution is then stored in the `result` variable.

**Update a record**

```
def update_record(params)
        sql = "UPDATE records SET name=$2, profession=$3 WHERE id=$1 RETURNING *"
        result = @conn.exec(sql, params)
        return result
    end

```

The method constructs an SQL statement that updates the `name` and `profession` columns of the `records` table for the row where the `id` column equals the first parameter passed to the method. The new values for the `name` and `profession` columns are the 2nd and 3rd parameters passed to the method. The `RETURNING` clause at the end of the SQL statement specifies that the updated row should be returned as the result of the query.

The `sql` variable is then passed to the `exec` method on the `@conn` object, along with the `params` parameter, which is an array containing the values for the parameters referenced in the SQL statement. The `exec` method executes the SQL statement against the database and returns the result as an array of rows.

Finally, the method assigns the array returned by `exec` to the `result` variable.

**Delete a record**

```
def delete_record(params)
        sql = "DELETE FROM records WHERE id=$1"
        result = @conn.exec(sql, params)
        return result
    end

```

This looks like a Ruby method named `delete_record` that accepts a single parameter `params`.

The method constructs an SQL statement that deletes a row from the `records` table where the `id` column equals the first parameter passed to the method.

The `sql` variable is then passed to the `exec` method on the `@conn` object, along with the `params` parameter, which is an array containing the value for the parameter referenced in the SQL statement. The `exec` method executes the SQL statement against the database and returns the result as an array of rows.

Finally, the method explicitly returns the `result` variable, which should be an array containing information about the success/failure of the delete operation.

So, this is a method to delete a row from the `records` table based on the given `id`.

**Complete** `database.rb` **file**

```
require "pg"

class Database

    DB_HOST = 'localhost'
    DB_PORT = 5432
    DB_NAME = 'hanami_db'
    DB_USER = '<USER>'
    DB_PASSWORD = '<PASSWORD>'

    def initialize(db_name:DB_NAME, user: DB_USER, password:DB_PASSWORD, host: 'localhost', port: 5432)
        @conn = PG.connect(dbname: db_name, user: user, password: password, host: host, port: port)
      end

    def create_records(params)
        sql = "INSERT INTO records (name, profession) VALUES ($1, $2) RETURNING *"
        result = @conn.exec(sql, params)

    end

    def all_records
        sql = "SELECT id, name, profession FROM records"
        result = @conn.exec(sql)
        return result
    end  

    def get_record(params)
        sql = "SELECT * FROM records WHERE id = $1"
        result = @conn.exec(sql, params)
        return result
        @conn.close

    end

    def update_record(params)
        sql = "UPDATE records SET name=$2, profession=$3 WHERE id=$1 RETURNING id, name, profession"
        result = @conn.exec(sql, params)

    end

    def delete_record(params)
        sql = "DELETE FROM records WHERE id=$1"
        result = @conn.exec(sql, params)
    end

    def close
        @conn.close
    end 

end

```

### Endpoints

**app.rb**

```
class App < Hanami::API

    use Hanami::Middleware::BodyParser, :json
    #endpoints here

end

```

Here we initialize the `App` class and specifies that we will be using the `Hanami::API` module. The `use` statement adds a middleware to our endpoint to parse JSON bodies, making it possible to extract data from incoming JSON requests with ease.

**POST Request**

```
 post '/records' do
      db = Database.new
      new_record = db.create_records([params[:name], params[:profession]])
      db.close
      json new_record.first.to_h
    end

```

The `POST` endpoint expects incoming requests to have a `name` and `profession` parameter in their body. It create a new instance of the database, then creates a new record in the database using the `create_records` method, passing in an array containing the `name` and `profession` parameters. This method returns a collection of created records, and the code selects the first record from this collection and converts it to a hash before returning it as JSON.

The `db.close` statement is closing the connection to the database.

**GET Requests**

```
# Get all records
    get '/records' do
      db = Database.new
      result = db.all_records
      db.close
      json result.map { |record| { id: record['id'], name: record['name'], profession: record['profession'] } }
    end

    # Get a record by its id
    get '/records/:id' do
      db = Database.new
      result = db.get_record([params[:id]])

      if result.ntuples.zero?
        db.close
        json ({status: status(404), message: "Entry #{params[:id]} not found"})
      else
        db.close  
        json result.first.to_h
      end  
    end

```

The `/records` endpoint retrieves all records and returns the data as a JSON response. It first creates a new instance of the `Database` class, calls the `all_records` method to retrieve all records, then closes the connection to the database. After that, it maps the records returned into a new array containing only the relevant fields, such as `id`, `name`, and `profession`, before rendering it as a JSON response.

The `/records/:id` endpoint retrieves a single record with the specified ID. This piece of code creates a new instance of the `Database` class, then it calls the `get_record` method, passing in the `id` parameter from the URL. If the query returns no records, the code returns an error message as a JSON response with a 404 status. If the query returns a record, the code converts it to a hash and returns it as a JSON response.

```
put '/records/:id' do
      db = Database.new
      record = db.get_record([params[:id]])

      if record.ntuples.zero?
        db.close
        json ({status: status(404), message: "Entry #{params[:id]} not found"})
      else  
        result = db.update_record([params[:id], params[:name], params[:profession]])
        db.close
        json result.first.to_h
      end  
    end

```

This endpoint updates a single record with the specified ID. We create a new instance of the `Database` class, then calls the `get_record` method, passing in the `id` parameter from the URL. If the query returns no records, the code returns an error message as a JSON response with a 404 status.

If the query returns a record, the code proceeds to update it with information from the HTTP request body.

**DELETE Request**

```
delete '/records/:id' do
      db = Database.new
      record = db.get_record([params[:id]])

      if record.ntuples.zero?
        db.close
        json ({status: status(404), message: "Entry #{params[:id]} not found"})
      else  
        db.delete_record([params[:id]])
        db.close
        json "Records deleted"
      end  
    end

```

Thisendpoint deletes a single record with the specified ID. Again, we start by creating a new instance of the `Database` class, then calls the `get_record` method, passing in the `id` parameter from the URL. If the query returns no records, the code returns an error message as a JSON response with a 404 status.

If the query returns a record, the code proceeds to delete it by calling the `delete_record` method with an array containing the `id` parameter that it received as part of the HTTP request.

**Complete** `app.rb` **file**

```
require_relative 'database'

class App < Hanami::API

  use Hanami::Middleware::BodyParser, :json

    # Create a new record
    post '/records' do
      db = Database.new
      new_record = db.create_records([params[:name], params[:profession]])
      db.close
      json new_record.first.to_h
    end

    # Get all records
    get '/records' do
      db = Database.new
      result = db.all_records
      db.close
      json result.map { |record| { id: record['id'], name: record['name'], profession: record['profession'] } }
    end

    # Get a record by its id
    get '/records/:id' do
      db = Database.new
      result = db.get_record([params[:id]])

      if result.ntuples.zero?
        db.close
        json ({status: status(404), message: "Entry #{params[:id]} not found"})
      else
        db.close  
        json result.first.to_h
      end  
    end

    # Update a record
    put '/records/:id' do
      db = Database.new
      record = db.get_record([params[:id]])

      if record.ntuples.zero?
        db.close
        json ({status: status(404), message: "Entry #{params[:id]} not found"})
      else  
        result = db.update_record([params[:id], params[:name], params[:profession]])
        db.close
        json result.first.to_h
      end  
    end

    # Delete a record
    delete '/records/:id' do
      db = Database.new
      record = db.get_record([params[:id]])

      if record.ntuples.zero?
        db.close
        json ({status: status(404), message: "Entry #{params[:id]} not found"})
      else  
        db.delete_record([params[:id]])
        db.close
        json "Records deleted"
      end  
    end  
end

```

### Conclusion

I'm continuing to learn about Ruby and Hanami. In this article, I add a database using PG Driver. I have an issue trying to implement the database operations, especially related to closing the pool after every query. I just want to initialize the database and close the pool when an operation is finished, but when another operation started, the pool was already closed.

The alternative that I found to solve this issue was to initialize a new instance of a database in every operation. I didn't try to use this alternative in the controllers, I don't if it would be more appropriate. Please level your thoughts about it or if you have any solution.

Thank you for taking the time to read this article.

If you have any recommendations about other packages, architectures, how to improve my code, my English, or anything; please leave a comment or contact me through [**Twitter**](https://twitter.com/Carlos_marcv), or [**LinkedIn**](https://www.linkedin.com/in/carlos-marcano-a2135a134/).

The source code is [here](https://github.com/carlosm27/hanami-api-postgres).

### Resources

- [Pg driver README](https://github.com/ged/ruby-pg)

- [Introducing Hanami::API](https://hanamirb.org/blog/2020/02/26/introducing-hanami-api/).

- [Hanami::API documentation.](https://github.com/hanami/api/blob/main/README.md)

- [Hanami documentation.](https://hanamirb.org/)

- [Hanami README.](https://github.com/hanami/hanami)

- [try-hanami-api repository](https://github.com/makevoid/try-hanami-api).

- [Getting Started with Hanami-API.](https://carlosmv.hashnode.dev/getting-started-with-hanami-api-ruby#heading-references)