Coding Artist
Published in

Coding Artist

Full-Stack React With Phoenix (Chapter 6 | Creating a PostgreSQL API Service)

Table of Contents

Chapter 1 | Why Bother?
Chapter 2 | Learning the Basics of Elixir
Chapter 3 | Introduction to Phoenix
Chapter 4 | Implementing React
Chapter 5 | Working With PostgreSQL

Before We Begin

Make sure to install pgAdmin if you have not already.

Also, install Postman which will be used to test out our API service.

Scope of This Chapter

You can think of the previous chapter as a bootstrapped way to get a sneak preview of what we are doing in this chapter. In this chapter, we are going to be creating a PostgreSQL API service with Phoenix so that we can make HTTP requests from a React frontend to create, read, update, and delete from our database.

In the previous chapter, we got started with this using a built-in generator. For learning purposes, we are going to do this programmatically so we can really grasp what’s going on. This is probably the most important chapter in this book so pay attention and I’ll do my best to explain well.

Getting Started

We want to continue in the phoenix_curated_list project. The end goal is to have a curated list of blog posts rendering using data from PostgreSQL. However, we want to start with a clean slate and have all the generated files from the previous chapter removed.

The easiest way to do this will be to create a new project from scratch.

First, let’s create a new project called phoenix_react_curated_list:

mix phoenix_react_curated_listcd phoenix_react_curated_list

Next, make sure to have your PostgreSQL server running.

If we go to config/dev.exs, we can check our configurations for PostgreSQL at the bottom:

# Configure your database
config :phoenix_react_curated_list, PhoenixReactCuratedList.Repo,
adapter: Ecto.Adapters.Postgres,
username: "postgres",
password: "postgres",
database: "phoenix_react_curated_list_dev",
hostname: "localhost",
pool_size: 10,
port: 5432 # I added this, default is 5432

As noted in the comments, I added the port: 5432 line because I like to manually specify the port. The default port is 5432 for the PostgreSQL server so the configuration file omits this line by default.

Recall, Ecto is the Elixir tool used for reading and writing to different databases, modeling our domain data, and writing complex queries.

It’s composed of 4 different parts as noted in the official Phoenix documentation:

  • Repo — A repository represents a connection to an individual database. Every database operation is done via the repository.
  • Model — Models are our data definitions. They define table names and fields as well as each field’s type. Models also define associations — the relationships between models.
  • Query — Queries tie both models and repositories together, allowing us to elegantly retrieve data from the repository and cast it into the models themselves.
  • Changeset — Changesets declare transformations we need to perform on our model data before our application can use it. These include type casting, validations, and more.

When creating a project, Phoenix has also automatically configured a repo which is the connection to our database which we can use to make operations on our database. However, we don’t have any code for models, queries, or changesets.

Let’s go ahead and create the phoenix_react_curated_list_dev database specified in the configuration via Ecto by running:

mix ecto.create

Go ahead and open pgAdmin once this completes.

Note for Mac users: I ran into issues with pgAdmin not opening after installation. Make sure to drag the application found under devices into your applications.

Then, open pgAdmin from the applications folder.

With pgAdmin open, go to File CreateServer:

Name the server Phoenix and match the following information in Connection:

Make sure to enter the password which is postgres by default as we have already mentioned.

We will now be able to manage all the databases on port 5432. This includes the phoenix_react_curated_list_dev database which we just recently created:

Creating Our First Table Programmatically

Back to our project, we need to start the process of programmatically doing what was done using phoenix.gen.html without exception to the views and templates to do CRUD operations through a webpage as we will omit that.

As we did in the previous chapter, we want to create a table called Blogs with the following fields:


title | subtitle | image | link | author

First, we will manually create the model which includes a schema defining the shape of our data and a changeset which we will unpack later.

Under web/models, create a new file called blogs.ex.

In this file, we can start by adding an outermost module and specify the use of a model module within it:

defmodule PhoenixReactCuratedList.Blogs do
use PhoenixReactCuratedList.Web, :model

This model module is defined in web/web.ex:

def model do
quote do
use Ecto.Schema
import Ecto
import Ecto.Changeset
import Ecto.Query

You just need to know that it imports all the Ecto stuff we need to create our complete model.

Next, we add a schema function that specifies the name of our table, the fields, and the types of the fields:

defmodule PhoenixReactCuratedList.Blogs do
use PhoenixReactCuratedList.Web, :model
schema "blogs" do
field :title, :string
field :subtitle, :string
field :image, :string
field :link, :string
field :author, :string


We also include timestamps via timestamps().

The necessity of this schema is that our project now knows how a table in our database is shaped so it can interact with it via the repo accordingly.

Save the model. We now need to create a table following the schema defined in this model. Again, we can do this via a migration.

I mentioned in the previous chapter that you can think of the word migration as expressing the action of moving a table into our database using the field definitions outlined in the schema within our model.

While we won’t be using the phoenix.gen.html generator which generated a ton of extra stuff for us, we can generate the boilerplate of a migration file.

The migration file will be used to create the blogs table in our database following the shape specified in our schema.

To generate the boilerplate migration file, we can run:

mix ecto.gen.migration blogs

The syntax for the code above is as follows:

mix ecto.gen.migration *table specified in schema*

Now, open up this file found under priv/repo/migrations:

defmodule PhoenixReactCuratedList.Repo.Migrations.Blogs do
use Ecto.Migration
def change do end

Within the change function, we need to write the code that will migrate our table to the database when executed.

First, we can add a create table function within the change function:

defmodule PhoenixReactCuratedList.Repo.Migrations.Blogs do
use Ecto.Migration
def change do
create table(:blogs) do

We can then simply paste in the contents of our schema function found in web/models/blogs.ex and prepend add:

defmodule PhoenixReactCuratedList.Repo.Migrations.Blogs do
use Ecto.Migration
def change do
create table(:blogs) do
add :title, :string
add :subtitle, :string
add :image, :string
add :link, :string
add :author, :string

We are now ready to execute our migration by running the following:

mix ecto.migrate

Let’s see if it worked!

Refresh our Phoenix server in pgAdmin:

Expand our schema under Databases/phoenix_react_curtated_list_dev/schemas/public:

Underneath tables, we can see that the blogs table and the table with meta data of our migrations were both added!

If you expand the blogs table, you will see our columns (fields) match exactly like we had specified in our schema including the added timestamp information:

Sweet! Give yourself a pat on the back.

Updating the Router & Adding Our Controller

Now that we have a table, we need to create the API service so we can do CRUD operations via HTTP requests from our frontend.


In our router, we define the routes for our API service. Each route can be used for different types of interactions depending on the type of HTTP request. If you are unfamiliar with HTTP request, check out this handy resource.

For our API service, we need to have HTTP requests that will ultimately execute CRUD operations on our database.

Here’s a table showing the different routes, the HTTP requests, and the interactions that we want as a result for just our create and read operations (we will finish off the rest of the CRUD operations later):

Open web/router.ex so we can add these routes.

We need to specify the HTTP verbage, the routes, the controller, and the function to call in the controller. The functions in our controller will be invoking the interactions with our database to perform our operations.

Here’s what our routes should look like to match the table shown above:

scope "/", PhoenixReactCuratedList do
pipe_through :browser # Use the default browser stack
get "/", PageController, :index
get "/blogs", BlogsController, :index # show all blog posts
post "/blogs", BlogsController, :create # create blog post entry
get "/blogs/:id", BlogsController, :show # show a single blog post

Note: In the previous chapter (when we just generated everything really quickly), we used resources “/blogs", BlogsController. resources is a shorthand for manually writing out all the common types of HTTP requests paths and parameters for us. I’m purposely avoiding this so we can really understand how everything works.


We are now ready to write the BlogsController which we have specified in our routes.

Add a file called blogs_controller.ex under web/controllers.

In this file, we can add the shell of our code:

defmodule PhoenixReactCuratedList.BlogsController do
use PhoenixReactCuratedList.Web, :controller
def index(conn, _params) do end def create(conn, _params) do end def show(conn, _params) do endend

In the code above, we have added the functions which we specified in our router.

Next, we can update _params where parameters are used:

def create(conn, %{"blogs" => blogs_params}) doenddef show(conn, %{"id" => id}) doend

A POST request on /blogs will contain URL parameters that will be used to create a new row in our Blogs table. Therefore, we will just have our parameters stored as blog_params. Recall, parameters are specified in maps.

A GET request on /blogs/:id will show a blog post. We store the id parameter that will be included in the path into a map.

The create and show functions will require a bit more logic. Let’s just finish writing out the index function to show all our blogs in the database.

We have mentioned several times now that our repo is used to connect to our database and provides the means to interact with it. There are several callbacks that can be used to perform operations via our repo.

To fetch all entries we can use, Repo.all().

Therefore, let’s add the following:

def index(conn, _params) do
blogs = Repo.all()

We are going to store the fetching of all entries in our Blogs table in a new variable called blogs.

The Repo.all() callback just needs to know which table to fetch information from and how the table is defined. This, as we know, is specified in our model which is accessible at PhoenixReactCuratedList.Blogs.

Writing out PhoenixReactCuratedList.Blogs is a bit tedious, therefore, we can create an alias so we can just write Blogs which will be equivalent to writing out PhoenixReactCuratedList.Blogs:

defmodule PhoenixReactCuratedList.BlogsController do
use PhoenixReactCuratedList.Web, :controller
alias PhoenixReactCuratedList.Blogs #insert this line

# ....

Now, we can finish off our index function:

def index(conn, _params) do
blogs = Repo.all(Blogs)

The code above will fetch all of our blogs within the Blogs table.

The final step to return blogs as a JSON object when our frontend makes the HTTP request. We will do that next.

Returning a JSON Object Via a View

Before creating our view, we need to specify the name of JSON object that we want to return and pass the blogs stored in blogs. Therefore, we can add:

def index(conn, _params) do
blogs = Repo.all(Blogs)
render conn, "index.json", blogs: blogs

Now, let’s create a view called blogs_view.ex under web/views.

We can start by adding a typical view like we did in our introduction to Phoenix:

defmodule PhoenixReactCuratedList.BlogsView do
use PhoenixReactCuratedList.Web, :view

Now, let’s manually add a render function that will enumerate through our blogs and call another function called blogs_json which creates the JSON object:

def render("index.json", %{blogs: blogs}) do
blogs:, &blogs_json/1)
def blogs_json(blog) do
title: blog.title,
subtitle: blog.subtitle,
image: blog.image,

In the code above, render enumerates through the blogs map which contains all the fetched rows in our Blogs table. &blogs_json/1 is shorthand for calling blog_json and passing in blog row on the current iteration. /1 is required at the end to specify the blogs_json function call will just have one parameter passed in.

blogs_json(blog) takes in the blog post and uses its information to create a map that is the Elixir equivalent of a JSOB object.

The render function ultimately returns blogs which will contain the map for each blog post. Because we passed in index.json, the HTTP request will return a JSON object containing an array called blogs with an individual object for all of our blog posts.

Let’s manually add a row in our Blogs table and test out this API.

Open pgAdmin and refresh our Phoenix server.

Then, expand our blogs table and then expand columns.

We need to make two quick edits before we manually enter in a row.

We need our timestamp columns (inserted_at and updated_at) to be populated automatically using the following sql expression: now()

Right-click inserted_at and click properties.

Select the definition tab and add now() as the default value:

Repeat this same process for updated_at.

Now, let’s enter in a row of data.

Right-click blogs, hover over View/Edit Data, and then click All Rows:

We are now able to go through and add values for each field in our row:

Here’s the information to use to fill in:

Full-Stack React With Phoenix (Chapter 1 | Why Bother?)
Benefits of an Elixir Backend
Mike Mangialardi

Note: Leave the timestamp field empty as they will be generated automatically.

Once it is all entered, save and then click the lightning bolt to execute:

Cool beans! We are now ready to test out our API.

To do this, we first need to fire up our server:

mix phoenix.server

Then, open Postman which you should have installed earlier. Make sure GET is selected, paste in the path to fetch our blogs, and click send:

It worked! We have received a blogs array with a JSON object containing an object for each blog post (row in our database). In this case, we have our single object for the row we just entered.

Awesome! This is really exciting because this means we can now make an HTTP request from our React frontend and use this data that is returned to populate our UI.

We will try this out next.

Populating Our UI With React

We still have to finish off our API service to do all the other CRUD operations. However, we will do this in the next chapter. To finish off this chapter, let’s make the HTTP request that we tested our in Postman and write React code so that a UI card will be rendered with each row in our database.

We can start by following the same steps outlined in Chapter 4 to setup our React frontend.

First, we can install our main dependencies:

npm install --save react react-dom babel-preset-react

Next, we configure our brunch-config.js file to apply presets for Babel and React:

plugins: {
babel: {
presets: ["es2015", "react"],
// Do not use ES6 compiler in vendor code
ignore: [/web\/static\/vendor/]

In addition, we add a whitelist in under the npm options in this file so that it is clear that we are going to use react and react-dom:

npm: {
enabled: true,
whitelist: ["phoenix", "phoenix_html", "react", "react-dom"]

We can then create the target for our app in the index.html.eex template (delete everything else):

<div id="app"></div>

The app template contains a pre-populated header which we can remove:

<header class="header">
<nav role="navigation">
<ul class="nav nav-pills pull-right">
<li><a href="">Get Started</a></li>
<span class="logo"></span>
^^^ delete this

In web/static/js, let’s make a complete React project directory:

We just add containers and presentationals folders. Containers refer to active components where the API request will be made and the retrieved data will be passed down to a presentational component which presents something to our UI using the inherited props.

Open app.js and let’s add the following code:

import "phoenix_html"
import React from "react"
import ReactDOM from "react-dom"
import Blogs from "./containers/Blogs"
class App extends React.Component {
render() {
return (
<Blogs />

In the code above, we define the top of our React hierarchy which will nest our container components called Blogs.

Let’s write Blogs in a new file called Blogs.js within the containers folder.

Let’s add the beginning of our code:

import React from "react"class Blogs extends React.Component {
componentWillMount() {
//add API request
render() {
//create a BlogCard component for each blog post in API request response
return (
export default Blogs

As the comments indicate, we need to add the API request and the code to render a BlogCard component for each blog post.

To make HTTP requests, we will use a library called axios.

Let’s install that:

npm install --save axios

Then, let’s import it:

import axios from "axios"

Next, we can add the API request which will look like this:

componentWillMount() {
.then(function (response) {
.catch(function (error) {

This code can be read as: “Hey Axios! Can you get all the blog posts in our database doing a GET request on this path? After you do that, log the response for us so we can make sure it worked. If something goes wrong, just go ahead and log that too.”

Let’s compile our frontend code by running brunch build and then check the console in our local host:

Woohoo! We can see our blogs array with the object for our single row in the database.

Now, we can store the blogs array from our response into a variable and use that to update a property in the local state (which we also will add):

class Blogs extends React.Component {
constructor() {
this.state = { blogs: [] };
componentWillMount() {
.then(response => {
this.setState({ blogs: });
.catch(error => {

The next step is to iterate through each object in the blogs array and render a BlogCard component (which we haven’t created yet) for each blog post that passes down the information within the current object as props:

render() {
const posts =, index) =>
key = { index }
title = { blog.title }
subtitle = { blog.subtitle }
image = { blog.image }
link = { }
author = { }
return (

Notice that we inject all of the BlogCard components stored in posts by doing {posts}.

Let’s import the BlogCard component which we will create next:

import BlogCard from "../presentationals/BlogCard"

Now, create a new file under the presentationals folder called BlogCard.js.

For writing the code that will render a blog card to the UI, we are going to use a CSS framework called Bulma. It comes with a predefined component which will be perfect for this.

We can use a CDN to easily add Bulma as a stylesheet in the head of web/templates/app.html.eex:

<link rel="stylesheet" href="">

Back in BlogCard.js, let’s add the shell of our code and a predefined blog card component from Bulma with updates to change class to className so that it works with React:

import React from "react"class BlogCard extends React.Component {render() {
return (
<div className="card">
<div className="card-image">
<figure className="image is-4by3">
<img src="" alt="Image" />
<div className="card-content">
<div className="media">
<div className="media-content">
<p className="title is-4">John Smith</p>
<p className="subtitle is-6">@johnsmith</p>
export default BlogCard

Then, let’s update the information to inject the passed down props:

render() {
return (
<div className="card">
<div className="card-image">
<figure className="image is-4by3">
<a href={}>
<img src={this.props.image} alt="Image" />
<div className="card-content">
<div className="media">
<div className="media-content">
<p className="title is-4">{this.props.title}</p>
<p className="subtitle is-6">{this.props.subtitle}</p>
<p className="subtitle is-6">By {}</p>

To finish off, delete all the code in web/static/css/phoenix.css. Then, add the following code in app.css which just adds a flexbox container to center our blog cards horizontally and vertically:

#app {
display: flex;
align-items: center;
justify-content: center;
flex-direction: row;
flex-wrap: wrap;

Let’s run build brunch one more time and check the local host:

Woohoo! We have successfully fetched our blogs using our Phoenix API server and rendered them with our React frontend!

Final Code

Available on GitHub.

Concluding Thoughts

I’m not sure about you, but I’m pretty pumped about our functioning API service. This is an incredibly useful skill to know. We still have to finish off the API service for the rest of our CRUD operations. However, let’s take a break at this point and do that in the next chapter.

Chapter 7

Chapter 7 is now available.

Sign Up for Notifications

Get notified when each chapter is released.

Mike Mangialardi
Founder of Coding Artist




Providing journeys for developers who see the web as their canvas

Recommended from Medium

Combining python and flutter : Part 1

Algorithmic Trading with bloXroute

Removing the Title field from a SharePoint list

Introducing CafeSwap’s New CAFFEINE BUYBACK VAULTS with Brand New Features

Need a Hobby? Teach Yourself How to Code.

Building Search System from Scratch

My Tech Journal: Intro and Day 1

PolarDB Series (1): Get Started in 10 Minutes

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michael Mangialardi

Michael Mangialardi

UI Developer in Southwest Virginia. Soli Deo Gloria.

More from Medium

Access tailwind colors in TypeScript

When to use Multi-Page Apps?

Zero to Next.js Example with WSL2 in W11

File System Based Routing in Next JS