Build a CRUD API in Julia with Oxygen.jl

CRUD – Create, Read , Update and Delete is an important concept to master and know for building most applications. In this tutorial we will explore how to build a simple CRUD API in Julia using the Oxygen Framework – which is like the Flask equivalent in Julia.

Oxygen.jl is a Julia package for building web apps with a simple syntax. Unlike Genie Framework, oxygen uses a similar api like Flask to make it easy to build web apps. Let us see how to build our CRUD API.

Our CRUD API consist of the following

  • Struct: used for our model structure and schema
  • HTTP methods coming from Oxygen.jl
  • SQLite: used to handle our database and data storage

Since we do not yet a simple ORM to manage our data in Julia we will use SQL to handle the storage and management of our data.

Installation and Setup

We will first activate a julia environment in order to track the packages we will be using and then install the following packages such as Oxygen.jl, StructTypes , SQLite, HTTP.

]activate .
> add Oxygen HTTP SQLite StructTypes

Basic App

Let us build a basic app(app.jl) using Oxygen.jl and then update it according with the various parts i.e. database, crud endpoints,etc.

using Oxygen
using HTTP 

@get "/" function(req::HTTP.Request)
     return "Hello Julia Web App"
end 

serve(port=8001)

To run the app you can use

julia --project app.jl

Setting Up the Database

We will be using Structs to model our schema and use it as a guide to structure our data and its serialization.

using SQLite 
db_path = "blog.db"
db = SQLite.DB(db_path)

SQLite.execute(db, "CREATE TABLE IF NOT EXISTS blogpost(id INTEGER PRIMARY KEY,title TEXT NOT NULL,content TEXT NOT NULL)")

struct BlogPost
    id::Int
    title::String
    content::String
end
# Add a supporting struct type definition so JSON3 can serialize & deserialize automatically
StructTypes.StructType(::Type{BlogPost}) = StructTypes.Struct()


function create_blog(blogpost::BlogPost)
    DBInterface.execute(db, "INSERT INTO blogpost (title, content) VALUES (?, ?)", (blogpost.title, blogpost.content))
    return 
end

Storing and Handling Data

In order to store the data being added to the application, we will use SQLite, you can also use postgres or other packages. It is always advisable to create functions to simply task, hence we will create our functions for our CRUD equivalent SQL statements.

 function create_blog(blogpost::BlogPost)
    DBInterface.execute(db, "INSERT INTO blogpost (title, content) VALUES (?, ?)", (blogpost.title, blogpost.content))
    return 
end

function read_blog(id::Int)
    result =  DBInterface.execute(db, "SELECT * FROM blogpost  WHERE id = ?", (id,))
    if !isempty(result)
        row = first(result)
        return BlogPost(row[1], row[2], row[3])
    else
        return nothing
    end
end

function read_all_blog()
    result = DBInterface.execute(db, "SELECT * FROM blogpost")
    return [BlogPost(row[1], row[2], row[3]) for row in result]
end

function update_blog(blogpost::BlogPost)
    DBInterface.execute(db, "UPDATE blogpost SET title = ?, content = ? WHERE id = ?", (blogpost.title, blogpost.content, blogpost.id))
end

function update_blog_by(blog_id::Int64,blogpost::BlogPost)
    DBInterface.execute(db, "UPDATE blogpost SET title = ?, content = ? WHERE id = ?", (blogpost.title, blogpost.content, blog_id))
end

function delete_blog(id::Int)
    DBInterface.execute(db, "DELETE FROM blogpost WHERE id = ?", (id,))
    return nothing
end

CRUD API endpoints

For our CRUD API points we will use Oxygen.jl to create our endpoints that maps to our HTTP methods respectively.

@post "/api/v1/blogs/" function(req::HTTP.Request)
    # convert the request body to Struct and pass it to the database
    data = Oxygen.json(req,BlogPost)
    create_blog(data)
    return data
end 


@get "/api/v1/blogs/" function(req::HTTP.Request)
    data = read_all_blog()
    return data
end 

@get "/api/v1/blogs/{blog_id}" function(req::HTTP.Request,blog_id::Int)
    data = read_blog(blog_id)
    return data
end 

@patch "/api/v1/blogs/{blog_id}" function(req::HTTP.Request,blog_id::Int)
    # convert the request body to Struct and pass it to the database
    data = Oxygen.json(req,BlogPost)
    update_blog(data)
    return data
end 

@delete "/api/v1/blogs/{blog_id}" function(req::HTTP.Request,blog_id::Int)
    return delete_blog(blog_id)
end 

We have seen how to build a simple CRUD API using Oxgyen Framework in Julia, you can also check out the video tutorial below

Thank you for your attention

Jesus Saves

By Jesse E.Agbe(JCharis)

2 thoughts on “Build a CRUD API in Julia with Oxygen.jl”

  1. The code did not run well. Adding my first item goes well:
    {
    “id”: 1,
    “title”: “First Blog”,
    “content”: “My first blog item”
    }
    But after using 127.0.0.1:8001/api/v1/blogs, I get the error: “message”: “The Server encountered a problem”. I also bought your Udemy course, the same problem. I am using julia version 1.8.2. Any suggestions?

Leave a Comment

Your email address will not be published. Required fields are marked *