PostgreSQL with Deno - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output of this Deno PostgreSQL query code?

Consider this Deno code snippet that queries a PostgreSQL database for all users:

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
const result = await client.queryObject("SELECT id, name FROM users ORDER BY id LIMIT 2");
await client.end();

console.log(result.rows);

If the users table has rows: (1, 'Alice'), (2, 'Bob'), (3, 'Carol'), what will be logged?

Deno

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
const result = await client.queryObject("SELECT id, name FROM users ORDER BY id LIMIT 2");
await client.end();

console.log(result.rows);

A [[1, "Alice"], [2, "Bob"]]

B [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }, { id: 3, name: "Carol" }]

C [{ id: 1, name: "Alice" }, { id: 2, name: "Bob" }]

D undefined

💡 Hint

Look at the LIMIT 2 clause and the queryObject method output format.

The queryObject method returns an object with a rows array of objects matching the selected columns. The query limits results to 2 rows, so only Alice and Bob appear.

📝 Syntax

intermediate

Which option will cause a syntax error in this Deno PostgreSQL connection code?

Identify the option that will cause a syntax error when trying to create a PostgreSQL client in Deno.

Deno

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
await client.end();

A const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 )};

B const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: "5432" });

C const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

D const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass" });

💡 Hint

Check for matching brackets and commas in the object literal.

Option A has a mismatched parenthesis and curly brace: port: 5432 )}; which causes a syntax error.

❓ state_output

advanced

What is the value of variable 'count' after running this Deno PostgreSQL code?

Given this code snippet that counts rows in a table:

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
const result = await client.queryObject("SELECT COUNT(*) AS count FROM users");
await client.end();

const count = result.rows[0].count;

If the users table has 5 rows, what is the type and value of count?

Deno

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
const result = await client.queryObject("SELECT COUNT(*) AS count FROM users");
await client.end();

const count = result.rows[0].count;

A "5" (string)

B 5 (number)

C undefined

D null

💡 Hint

PostgreSQL returns count as a numeric type. Deno's queryObject parses it accordingly.

The COUNT(*) returns a numeric value. The queryObject method parses it as a number, so count is the number 5.

🔧 Debug

advanced

Which option causes a runtime error in this Deno PostgreSQL query?

Look at this code snippet:

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
const result = await client.queryObject("SELECT id, name FROM users WHERE id = $1", ["abc"]);
await client.end();

console.log(result.rows);

Which option will cause a runtime error when running this code?

Deno

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb", hostname: "localhost", password: "pass", port: 5432 });

await client.connect();
const result = await client.queryObject("SELECT id, name FROM users WHERE id = $1", ["abc"]);
await client.end();

console.log(result.rows);

A Runtime error: invalid input syntax for integer: "abc"

B No error, returns empty array if no user with id 'abc'

C Syntax error in SQL query

D TypeError: queryObject expects array parameters

💡 Hint

Check the data type of the parameter and the column type.

The id column is likely an integer. Passing a string "abc" causes PostgreSQL to throw a runtime error about invalid input syntax.

🧠 Conceptual

expert

Which option best describes connection pooling behavior in Deno PostgreSQL clients?

When using the Deno PostgreSQL client library, which statement about connection pooling is correct?

A The Client class uses a global pool shared across all Client instances by default.

B The Client class automatically manages a pool of connections internally without extra setup.

C Connection pooling is not supported in Deno PostgreSQL; each Client creates a new connection every time.

D You must create a Pool instance explicitly to use connection pooling; Client manages a single connection.

💡 Hint

Check the official Deno PostgreSQL module documentation about Client vs Pool.

The Client class manages a single connection. To use connection pooling, you must create a Pool instance explicitly.

Practice

1. What is the main purpose of using the Client from the postgres module in Deno?

easy

A. To manage file uploads in Deno

B. To create a web server in Deno

C. To connect to a PostgreSQL database and run queries

D. To handle user authentication in Deno

Solution

1. Step 1: Understand the role of Client in postgres module

   The Client is designed to establish a connection to a PostgreSQL database and execute SQL queries.

2. Step 2: Compare with other options

   Creating web servers, managing file uploads, and handling authentication are unrelated to the Client in the postgres module.

3. Final Answer:

   To connect to a PostgreSQL database and run queries -> Option C

4. Quick Check:

   Client connects to DB and runs queries = C [OK]

Hint: Remember: Client is for DB connection and queries [OK]

Common Mistakes:

• Confusing Client with web server modules
• Thinking Client handles file uploads
• Assuming Client manages user authentication

2. Which of the following is the correct way to import the PostgreSQL client in a Deno script?

easy

A. import * as Client from "postgres";

B. import Client from "postgres";

C. const Client = require('postgres');

D. import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

Solution

1. Step 1: Identify correct import syntax for Deno modules

   Deno uses ES module syntax with full URLs for third-party modules, so import must include the URL and version.

2. Step 2: Check each option

   import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts"; uses correct ES module import with URL and version. Options B, C, and D use Node.js style or incomplete imports not valid in Deno.

3. Final Answer:

   import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts"; -> Option D

4. Quick Check:

   Deno imports use full URLs = A [OK]

Hint: Deno imports need full URL with version [OK]

Common Mistakes:

• Using Node.js require() syntax
• Omitting the full URL in import
• Using wildcard import incorrectly

3. Given the following Deno code snippet, what will be the output?

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb" });
await client.connect();
const result = await client.queryObject("SELECT 1 AS number");
await client.end();
console.log(result.rows[0].number);

medium

A. undefined

B. 1

C. Error: Connection not established

D. null

Solution

1. Step 1: Analyze the query execution

   The code connects to the database, runs a query selecting the number 1 as 'number', then closes the connection.

2. Step 2: Understand the result object

   The queryObject method returns an object with a rows array. The first row's 'number' field contains 1, so logging it prints 1.

3. Final Answer:

   1 -> Option B

4. Quick Check:

   Query returns 1, printed output = 1 [OK]

Hint: Query returns 1, check rows[0].number for output [OK]

Common Mistakes:

• Forgetting to await client.connect()
• Accessing wrong property of result
• Not closing the client connection

4. Identify the error in this Deno PostgreSQL code snippet:

import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const client = new Client({ user: "user", database: "testdb" });
const result = await client.queryObject("SELECT NOW()");
await client.end();
console.log(result.rows);

medium

A. Missing await client.connect() before query

B. Incorrect import statement

C. Using queryObject instead of query

D. Calling client.end() before query

Solution

1. Step 1: Check connection setup

   The code creates a client but never calls await client.connect() before running the query.

2. Step 2: Understand consequences

   Without connecting, the query will fail because the client is not connected to the database.

3. Final Answer:

   Missing await client.connect() before query -> Option A

4. Quick Check:

   Must connect before queries = D [OK]

Hint: Always connect client before running queries [OK]

Common Mistakes:

• Skipping client.connect() call
• Calling client.end() too early
• Confusing queryObject with query

5. You want to insert multiple rows into a PostgreSQL table using Deno's postgres client efficiently. Which approach is best to avoid multiple separate queries and ensure all rows are inserted in one operation?

hard

A. Use a single parameterized query with multiple VALUES tuples

B. Call client.queryObject() multiple times, once per row

C. Run a loop with client.connect() and client.end() for each row

D. Insert rows one by one without parameters

Solution

1. Step 1: Understand batch insertion

   Inserting multiple rows in one query reduces overhead and improves performance.

2. Step 2: Evaluate options

   A uses a single parameterized query with multiple VALUES tuples, which is efficient and safe. B runs multiple queries, increasing overhead. C reconnects repeatedly, which is inefficient. D risks SQL injection and is inefficient.

3. Final Answer:

   Use a single parameterized query with multiple VALUES tuples -> Option A

4. Quick Check:

   Batch insert with one query = A [OK]

Hint: Batch inserts use one query with multiple VALUES [OK]

Common Mistakes:

• Running many separate queries instead of one
• Opening and closing connection repeatedly
• Not using parameterized queries risking SQL injection