Picking up from where we stopped in Part 1, this is the continuation of Developing REST API with Deno where we talked about the benefits of Deno and built an API that was persisting in the Browser. We’ll continue with integrating Deno and PostgreSQL.
In this post, we’ll use one of the best database open-source platforms called PostgreSQL. Download & Install if you don’t already have it installed.
Create Database User
From pgAdmin4 go to Servers > PostgreSQL Server Name > Login/Groups Roles right-click on Login/Groups Roles and select Create > Login/Group Role…

Now specify, the username in the General tab, Mine is ‘vincent’

in the Definition tab, specify the password, Mine is ‘vincent’

Now, in the Privileges tab, specify the privileges as i have done below and click Save;

Creating Database, Table & Columns
From pgAdmin4 go to Servers > PostgreSQL Server Name > Databases right-click in Databases and select Create > Database

On the pop-up dialog, specify you database name, ours is deno_artists_api, you’ll notice in the image below, that I gave a descriptive comment

Click on Save. Now let us create a table, navigate to Databases > deno_artists_api > Schemas >Public > Tables right-click and select Create > Table…

Give a table name in General tab, ours is artists and navigate to the Columns tab, specify the column names and specify the primary key as seen in the image below

Setup Denon for files-watch
Before getting started with integrating PostgreSQL, let us use the Denon module to watch our project (this is similar to nodemon in NodeJS) so we don’t have to keep restarting it manually.
Current files Structure is:
controllers - artists.ts - config.ts - data.ts - denon.json - routes.ts - server.ts - types.ts - .env - .env.sample - .gitignore
Create a denon.json
{ "$schema": "https://deno.land/x/denon/schema.json", "scripts": { "start": { "cmd": "deno run server.ts", "allow": ["env", "net", "read"] } }, "logger": { "debug": true } }
To Run Execute the command below:
$ deno start
Setup dotenv
Setup ENV Variables for Security, so when our project is hosted. Our credentials are not exposed to the public.
Paste the code below into our .env file.
PORT=7000 DB_NAME=deno_artist_api DB_PASSWORD=vincent DB_USER=vincent DB_PORT=5432 DB_HOSTNAME=localhost
Now paste the following code into our config file to enable us connect to our PostgreSQL database
Config.ts file
import { config } from 'https://deno.land/x/dotenv/mod.ts'; const dbConn = { user: config().DB_USER, database: config().DB_NAME, password: config().DB_PASSWORD, hostname: config().DB_HOSTNAME, port: parseInt(config().DB_PORT) }; export { dbConn };
Now let us update our server.ts file. Just as we had before in the Part 1 of this post.
import { Application } from "https://deno.land/x/oak/mod.ts"; // Added import { config } from "https://deno.land/x/dotenv/mod.ts"; import router from "./routes.ts"; // Update const port = config().PORT || 5000; const app = new Application(); app.use(router.routes()); app.use(router.allowedMethods()); console.log(`Server is running on port ${port}`); // Updated app.listen({ port: +port });
The new data.ts file is as shown below;
import { Client } from "https://deno.land/x/postgres/mod.ts"; import { dbConn } from './config.ts'; // Initialize Postgres Client export const client = new Client(dbConn)
The Controller/artists.ts file is as shown below to reflect the PostgreSQL connection
Add an Artist
import { client } from '../data.ts'; ... ... ... // @route POST /api/v1/artists/ const addArtist = async ({ request, response, }: { request: any; response: any; }) => { const body = await request.body(); const artist = body.value; if (!request.hasBody) { response.status = 400; response.body = { success: false, message: 'Ensure to enter data', }; } else { try { await client.connect(); const result = await client.query( 'INSERT INTO artists(name,genre,followers,net_worth) VALUES($1,$2,$3,$4)', artist.name, artist.genre, artist.followers, artist.net_worth ); response.status = 201; response.body = { success: true, message: 'Artist added successfully', data: artist, }; } catch (error) { response.status = 500; response.body = { success: false, message: error.toString(), }; } finally { await client.end(); } } }; ... ... ... export { getArtists, getSingleArtist, addArtist, updateArtist, deleteArtist };
Denon restarts our server automatically. Lets test in Postm an

Get an Artist
... ... ... // @desc Get all Artists // @route GET /api/v1/artists const getArtists = async ({ response }: { response: any }) => { try { await client.connect(); const result = await client.query('SELECT * FROM artists'); const artists = new Array(); // Postgres in Deno, however, doesn't respond with // objects as desired, so we have to arrange // the data as needed. result.rows.map((artist) => { let obj: any = new Object(); result.rowDescription.columns.map((el, i) => { obj[el.name] = artist[i]; }); artists.push(obj); }); response.status = 200; response.body = { success: true, count: result.rowCount, message: 'Artists retrieved successfully', data: artists, }; } catch (error) { console.log('the error', error) response.status = 500; response.body = { success: false, message: error.toString(), }; } finally { await client.end(); } }; ... ... ...
Denon should automatically restart our server after save. Let us go to Postman and Test

Get a Single Artist
... ... ... // @desc Get an Artists // @route GET /api/v1/artists/:id const getSingleArtist = async ({ params, response, }: { params: { id: string }; response: any; }) => { try { await client.connect(); const result = await client.query( 'SELECT * FROM artists WHERE id = $1', params.id ); if (result.rows.toString() === '') { response.status = 404; response.body = { success: false, message: `No artist found with id ${params.id}`, }; return; } else { const artist: any = new Object(); result.rows.map((a) => { result.rowDescription.columns.map((el, i) => { artist[el.name] = a[i]; }); }); response.body = { success: true, message: 'Artist Retrived Successfully', data: artist, }; } } catch (error) { response.status = 500; response.body = { success: false, message: error.toString(), }; } finally { await client.end(); } }; ... ... ...
Let us Test

Update an Artist
... ... ... // @desc update an Artist // @route PUT /api/v1/artists/:id const updateArtist = async ({ params, request, response, }: { params: { id: string }; request: any; response: any; }) => { await getSingleArtist({ params: { id: params.id }, response }); if (response.status === 404) { response.status = 404; response.body = { success: false, message: response.body.message, }; return; } else { const body = await request.body(); const artist = body.value; if (!request.hasBody) { response.status = 400; response.body = { success: false, message: 'Ensure to enter data', }; } else { try { await client.connect(); const result = await client.query( 'UPDATE artists SET name=$1,genre=$2,followers=$3,net_worth=$4 WHERE id=$5', artist.name, artist.genre, artist.followers, artist.net_worth, params.id ); response.status = 200; response.body = { success: true, message: 'Artist updated successfully', data: artist, }; } catch (error) { response.status = 500; response.body = { success: false, message: error.toString(), }; } finally { await client.end(); } } } }; ... ... ...
Let us try and update an Artist

Delete/ Remove an Artist
... ... ... // @desc Remove an Artist // @route DELETE /api/v1/artists/:id const deleteArtist = async ({ params, response, }: { params: { id: string }; response: any; }) => { await getSingleArtist({ params: { id: params.id }, response }); if (response.status === 404) { response.status = 404; response.body = { success: false, message: response.body.message, }; } else { try { await client.connect(); const result = await client.query( 'DELETE FROM artists WHERE id=$1', params.id ); response.status = 204; response.body = { success: true, message: `Artist with the id ${params.id} Successfully deleted`, }; } catch (error) { response.status = 500; response.body = { success: false, message: error.toString(), }; } finally { await client.end(); } } }; ... ... ...
Let us test the the delete functionality using Postman

Great!, now we no longer have to persist our data in Browser, Lets see how the data shows up in our database

Find the Updated Github REPO and play around with the data.
Summary
We have successfully integrated PostgreSQL database with our Deno REST API, in future we would be integrating Authentication and Web Tokens. Till then, Keep winning!.
Kindly point out any oops you might come across. Will be much appreciated. Thank you!
Related posts
Subscribe for newsletter
* You will receive the latest news and updates on your favorite topics!
How to Downgrade Flutter Version – FIXED
Learn how to downgrade your flutter version to a lower version, in this article we will reduce the flutter version…
Generate Resources for Android Playstore & iOS Apple Store Flutter
In this post, we’ll generate resources for the flutter app we created in the previous post. we’ll be learning how…
Build a Quiz App using Flutter for Android and iOS
In this post we’ll be working with Flutter, a fast-rising Developer tool built by Google to develop an Android App,…
Setup Flutter on Kali, Ubuntu, and any other Linux Distro
In this post, we’ll be learning how to set up your Flutter on Linux for development. Flutter is a fast-rising…
FIXED xcodebuild: Failed to load code for plug-in – Repeatedly Being Asked to Install Command Line Tools
Hey there, updating your MacOS Montery Xcode can be a pin in the a$$ especially when you got Xcode working…
How to Downgrade Flutter Version – FIXED
Learn how to downgrade your flutter version to a lower version, in this article we will reduce the flutter version…
Build a Quiz App using Flutter for Android and iOS
In this post we’ll be working with Flutter, a fast-rising Developer tool built by Google to develop an Android App,…
Setup Flutter on Kali, Ubuntu, and any other Linux Distro
In this post, we’ll be learning how to set up your Flutter on Linux for development. Flutter is a fast-rising…