QuestDB is a relational column-oriented database designed for time series and event data. It uses SQL with extensions for time series to assist with real-time analytics.

If you are not familiar enough with QuestDB, here isdemo link to get in touch.

In this tutorial, we will build simple API and implement QuestDB as timeseries database in our project. Then, we will useGin Framework for handling HTTP routes.

Before we begin, I’ll assume that you:

• Have Go installed on your machine
• Understand the basics of Go language
• Have a general understanding of RESTful API

Running QuestDB

Firstly, we need to run QuestDB in our local. There are several methods to install it, you can findhere. But we will use Docker and the latest questdb Docker image for convenience.

To start QuestDB via Docker, run the following:

docker run-p 9000:9000\-p 9009:9009\-p 8812:8812\  questdb/questdb

Alternatively, macOS users can use Homebrew:

brewinstallquestdbbrew services start questdb

After starting QuestDB, the web console is available on port9000, so navigating tolocalhost:9000 should show the UI which looks like the following:

Alright, QuestDB is ready. Now let's begin to the next step.

Building a REST API in Go using Gin and Gorm

Let’s start by initializing a new Go module to manage our project’s dependencies.

go mod init

Now let's install required dependencies

go get-u github.com/joho/godotenvgo get-u gorm.io/gormgo get-u github.com/gin-gonic/gin

After installation is complete the folder should containgo.mod andgo.sum. Both of the files contain information of the packages that we have installed.

For reference, I published the entire source code in mygithub. Feel free to clone it.

git clone https://github.com/arifintahu/go-api-questdb

Setting up database

Let's start by creating our database connection and models.

// models/tracker.gopackagemodelsimport("time")typeTrackerstruct{Timestamptime.Time`gorm:"type:timestamp" json:"timestamp"`VehicleIdint`gorm:"type:int" json:"vehicleId"`Latitudefloat64`gorm:"type:double" json:"latitude"`Longitudefloat64`gorm:"type:double" json:"longitude"`}

We havetracker models that will record every position of vehicles. Each tracker should have a timestamp, a VehicleId with type of integer, a Latitude and a Longitude with type of float. We should consider if our types are available in QuestDB types or not as statedhere.

Next, we will create setup function to connect to our database. We can interact with a QuestDB database by connecting to variousnetwork endpoints such as Web Console, InfluxDB Line Protocol, PostgreSQL Wire Protocol, HTTP REST API.

We will usePostgreSQL Wire Protocol by connecting to port8812 because we can use gorm as ORM in golang. Before that we need to install gorm driver postgres because we will connect QuestDB using Postgres driver.

go get-u gorm.io/driver/postgres

Then we will write function for database connection.

// models/setup.gopackagemodelsimport("fmt""gorm.io/driver/postgres""gorm.io/gorm")varDB*gorm.DBtypeDBConfigstruct{HoststringUserstringPasswordstringNamestringPortstring}func(dbConfig*DBConfig)ConnectDatabase()error{dsn:=fmt.Sprintf("host=%s user=%s password=%s dbname=%s port=%s",dbConfig.Host,dbConfig.User,dbConfig.Password,dbConfig.Name,dbConfig.Port,)database,err:=gorm.Open(postgres.Open(dsn),&gorm.Config{})iferr!=nil{returnerr}database.AutoMigrate(&Tracker{})DB=databasereturnnil}

Insetup.go, we also define auto migration fortracker model. Therefore, we don't need to create table in our database first.

Writing controllers

Next, we will build simple controllers where we can create and find trackers.

// controllers/trackers.gopackagecontrollersimport("go-api-questdb/models""net/http""time""github.com/gin-gonic/gin")typeCreateTrackerInputstruct{VehicleIdint`json:"vehicleId"`Latitudefloat64`json:"latitude"`Longitudefloat64`json:"longitude"`}funcCreateTracker(c*gin.Context){varinputCreateTrackerInputiferr:=c.ShouldBindJSON(&input);err!=nil{c.JSON(http.StatusBadRequest,gin.H{"data":err.Error()})return}tracker:=models.Tracker{Timestamp:time.Now().UTC(),VehicleId:input.VehicleId,Latitude:input.Latitude,Longitude:input.Longitude,}models.DB.Create(&tracker)c.JSON(http.StatusOK,gin.H{"data":tracker})}funcGetTrackers(c*gin.Context){vartrackers[]models.Trackermodels.DB.Find(&trackers)c.JSON(http.StatusOK,gin.H{"data":trackers})}

In trackers controller, we haveCreateTrackerInput to validate request body inCreateTracker handler, then we just call our DB instance to execute row creation. We also haveGetTrackers handler to fetch all rows.

RESTful routes

We almost there!

The last thing we need to do is creating route handler and application entry point.

// main.gopackagemainimport("go-api-questdb/controllers""go-api-questdb/models""os""github.com/gin-gonic/gin"_"github.com/joho/godotenv/autoload")funcmain(){r:=gin.Default()dbConfig:=models.DBConfig{Host:os.Getenv("DB_HOST"),User:os.Getenv("DB_USER"),Password:os.Getenv("DB_PASSWORD"),Name:os.Getenv("DB_NAME"),Port:os.Getenv("DB_PORT"),}err:=dbConfig.ConnectDatabase()iferr!=nil{panic(err)}r.POST("/trackers",controllers.CreateTracker)r.GET("/trackers",controllers.GetTrackers)r.Run("localhost:3000")}

Inmain.go, we havedbConfig for initializing our database connection and we load our database credentials in.env file. Therefore, we need to add.env file in our project.

We will use default user and password of QuestDB as statedhere

// .envDB_HOST=localhostDB_USER=adminDB_PASSWORD=questDB_NAME=qdbDB_PORT=8812

Alright, let's run out API

go run main.go

Great, our app is successfully running inlocalhost:3000 and successfully migrating new table.

Let's test it out by sendingPOST request tolocalhost:3000/trackers

curl--request POST'localhost:3000/trackers'--header'Content-Type: application/json'--data-raw'{ "vehicleId": 1, "latitude": -7.626923, "longitude": 111.5213978 }'

Then we got

{"data":{"timestamp":"2022-09-09T09:56:01.8970862Z","vehicleId":1,"latitude":-7.626923,"longitude":111.5213978}}

Let's test again by sendingGET request tolocalhost:3000/trackers and we got

{"data":[{"timestamp":"2022-09-09T09:56:01.8970862Z","vehicleId":1,"latitude":-7.626923,"longitude":111.5213978}]}

Yeay we have successfully built API with Go and QuestDB 🌟 🌟 🌟