Tutorials on Web Applications

Most modern web applications rely on a relational database management system (RDBMS) to store and retrieve troves of related data. When you bring together two modern, open-source technologies, Deno and PostgreSQL , you can build production-grade web applications that... Since Deno was built as an improved, alternative V8 runtime to Node.js, Deno projects can use both the third-party modules developed specifically to work with Deno and the many third-party packages that exist in the npm ecosystem. A popular PostgreSQL client for Node.js projects is node-postgres . If you visit the deno.land/x website and search postgres , then you will come across deno-postgres , a lightweight PostgreSQL driver that was developed specifically for Deno. The API and underlying implementation of deno-postgres is based on two popular PostgreSQL drivers: node-postgres (coincidentally) and pq , a Go PostgreSQL driver. deno-postgres provides abstractions for features and operations that allow you to unleash the full potential of PostgreSQL: connection pools, prepared statements, transactions and typed queries. Below, I'm going to show you how to... To get started, let's use Docker to spin up a new PostgreSQL database on the local machine. Running this command creates a new Docker container based on the official postgres Docker image. The docker run command accepts several options and flags that override image and Docker runtime defaults. For this tutorial, the database's schema design is based on a bank. The schema consists of three tables: To create tables in the database, let's run a SQL file directly against the pg_db_container container. With this approach, we never have to copy the SQL file from the host machine to the container. First, create a SQL file named create-db.sql that creates the customers , accounts and transactions tables: ( create-db.sql ) Then, run the following command, which pipes the contents of the create-db.sql SQL file (as a command string) to the psql command that's ran within the pg_db_container container: Alternatively, you can create a Dockerfile that extends the postgres image and copies the create-db.sql file from the host machine to the container's /docker-entrypoint-initdb.d directory. All scripts within this directory are ran at the time the container starts up (and the container's data directory is also empty). Note : Do not adopt this schema for production. The database does not fully reflect what actually happens in the real world. For example, it does not account for the differences in the number of digits a checking account number has (commonly ) and a savings account number has (commonly ). Also, for simplicity's sake, balance calculations only involve integer values. Remember, the main focus of this tutorial is to demonstrate the capabilities of deno-postgres . Now, create a SQL file named seed-customers.sql that inserts three customer records into the customers table. ( seed-customers.sql ) Then, run the following command to populate the database with these three customers: To verify that the database has been successfully seeded with this data, let's query the database with the psql interactive shell: You will be prompted to enter the superuser's password. Once entered, the terminal launches the psql interactive shell, which runs inside the PostgreSQL database server. Enter the following SQL SELECT statement to query the customers table for the seeded data: Create a new project folder named deno-postgres . Within this folder, create a server.ts file. Within server.ts , write a simple HTTP server that responds to every request with a "Hello World!" message. ( deno-postgres/server.ts ) To run the server, execute the following command: The --allow-net option grants network access to the Deno script. When you visit http://localhost:8000/ (or any URL beginning with http://localhost:8000/ ) in your browser, you will find the "Hello World!" message printed on the page. Oak is an HTTP middleware framework for Deno that includes a router middleware. Both the framework and router are inspired by Koa , so if you have any prior experience working with Koa, then many aspects of the code should look quite familiar. Let's modify the server to only respond to requests sent to a GET / endpoint by enabling routing. ( deno-postgres/server.ts ) Re-run the server. The browser only prints the "Hello World!" message for http://localhost:8000/ . As we progress in this tutorial, each new route (and route handler) we add to this router will demonstrate the features and operations that deno-postgres comes with. To connect to the PostgreSQL database, let's import the PostgreSQL client from deno-postgres . Then, create and initialize a new instance of the PostgreSQL client, like so: Each PostgreSQL client instance manages a connection to a single PostgreSQL database. The configuration options we pass to Client tell the driver everything it needs to know to connect to the PostgreSQL database, such as the database's hostname and the credentials of the superuser. You can find more options here . For encrypted connections to the database via SSL/TLS, click here . Call the client's connect method to connect to the database. Call the client's end method to terminate the connection to the database. To ensure the script starts the server only when the client has successfully connected to the PostgreSQL database, perform both client.connect and app.listen in a try block. Any errors that prevent the server from running will terminate the connection to the database. When you run the server, Deno prompts you to grant access to several PostgreSQL environment variables: Press y to grant access for each requested environment variable. To skip this series of prompts, run the Deno script with the --allow-env flag, like so: If the terminal shows no error messages, and the server returns a "Hello World!" message for a GET / request, then the server has successfully connected to the dockerized PostgreSQL database. The recommended way to execute queries is with prepared statements, especially if your application accepts user input. Prepared statements protect your PostgreSQL database from SQL injection attacks. Since the SQL code (statement template) is pre-compiled, prepared statements are also efficient. The statement template consists of a base SQL statement with placeholders for bind variables. Let's write our first prepared statement for querying the customers table based on the customer's first name. Within the deno-postgres/server.ts file, define a new route on the router for the GET /customers endpoint. This endpoint will return customers from the customers table based on a query parameter, firstName . Using this query parameter, we can tell the query to only find customers whose first name matches the value of firstName . For example, firstName=alice would filter the table for any customer who has a first name of "Alice." To extract query parameters from the URL, import the getQuery method from Oak's helpers module, like so: helpers provides APIs for managing router contexts. Then, using the client.queryArray method, write a route handler for the GET /customers endpoint that... The client.queryArray method executes the query and returns the results of the query as an array. It can accept, at most, two arguments: Note : client.queryArray also accepts named arguments . In the WHERE clause, ILIKE is used for case-insensitive string matching. A % sign in the pattern matches for any sequence of characters. So if the URL does not contain a firstName query parameter (or it's set explicitly to an empty string), then the ILIKE will match for any first_name . Note : Don't use % as a default value for ILIKE conditions in production. This is just a quick and easy implementation that ensures all customers are returned if the user decides not to provide any query parameters. Re-run the server. When you visit http://localhost:8000/customers?firstName=alice in a browser, you will see the results of the query. Note : You can use cURL or an application like Postman to send the request. The above is shown in a Chrome browser using the JSONView extension. In this case, because the customers table had a customer whose first name is "Alice," that record ( id , firstName and lastName ) was returned as an array of values. The first value corresponds to the first column id , the second value corresponds to the second column firstName , and the third value corresponds to the third column lastName . Now, let's check that the prepared statement guards against SQL injection attacks. Within the browser, visit http://localhost:8000/customers?firstName=alice%20OR%201%3D1 . If the value of the firstName query parameter was directly placed within the SQL statement, then the SQL statement would be SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE 'alice' OR 1=1 . With the OR operator and 1=1 condition, the WHERE clause always evaluates to true . You would expect all the rows in the customers table to be leaked. However, that's not what happens: Yet, if use psql to execute this same SQL statement, then all the rows in the customers table are leaked. The client correctly executes the query as SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE 'alice OR 1=1' , not SELECT id, first_name, last_name FROM customers WHERE first_name ILIKE 'alice' OR 1=1 . Let's add another query parameter, lastName , to find customers whose last name matches the value of lastName . Re-run the server. When you visit http://localhost:8000/customers?firstName=alice&lastName=king in a browser, you will see the results of the query. Note : There's also a queryObject method, which accepts the same arguments as the queryArray method, but returns the response as an object with the column names as keys. Altogether, the deno-postgres/server.ts file should look like this: ( deno-postgres/server.ts ) Try using PostgreSQL and Deno for your next project! To learn how to create and deploy a production-ready, REST API with Deno from scratch, check out Halvard Mørstad's course here . Halvard is a software engineer and is the author of Nessie , a modular database migration tool for Deno.