Getting Started

So far, we've worked with web applications that lose their data once the server is restarted. For the data to be persisted and for it to be available after the restart, it needs to be stored. This can be done, for example, by using a database. Next, we'll learn the principles of using a relational database for storing and retrieving data. We'll be using PostgreSQL as the database.

Relational databases are discussed in more detail in introductory databases courses. Here, we expect that you have some previous experience from working with SQL -- if this is not the case, or working with SQL has been a long while ago, you can refer to the W3Schools SQL tutorial for SQL basics.

To meaningfully proceed in the course, you need to have an access to a database.

Starting to use PostgreSQL

Taking PostgreSQL into use can be done using multiple approaches. Here, we'll list two options.

• Using the walking skeleton as recommended in Course Tools or otherwise running PostgreSQL locally using Docker or some other virtualization software.
• Using a hosted service. There exists a variety of services that provide a hosted PostgreSQL. These include e.g. ElephantSQL and Amazon RDS. Both services mentioned previously have a free tier option, which allows testing out applications. As the application and the number of users grows, the services also provide ways for scaling the database for a fee.

We strongly recommend using the first option for development.

Example: Using ElephantSQL

If you use the Walking skeleton, you may skip this part.

As an example, we outline here how to get started with ElephantSQL. First, read the getting started with ElephantSQL document on the ElephantSQL site and create an account there.

For our purposes, we've created a Tiny Turtle instance on ElephantSQL. The following image shows what the details page on ElephantSQL looks like -- in the image, sensitive information has been grayed out.

In the following examples, we will be working with a table that contains information about names. The table has two attributes -- an id (identifier) that is automatically added and incremented (SERIAL) and also acts as the primary key (PRIMARY KEY), and a name which is a variable length string (TEXT) and must always have a value (NOT NULL). The create table statement for such a table is as follows:

CREATE TABLE names (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

The table can be created in ElephantSQL admin board in the browser tab.

Create a table in your database now using the above SQL create table statement. Once the table has been created, we add two names. The names can be added using the following SQL insert statement. Note that we do not assign a value to the id-attribute, as the database creates an identifier automatically due to the SERIAL keyword in the id attribute definition.

The following SQL statement adds two names to the table names. The names are Donald and Mickey.

INSERT INTO names (name) VALUES ('Donald'), ('Mickey');

After we have executed the above SQL insert statement, there should be two rows in the table. We can verify this by running a select query that selects all the rows in the table.

SELECT * FROM names;

Example with Postgres.js

Now, you could also run a program that accesses the database. The following example uses Postgres.js library and would output the names from your database (after entering correct database credentials from your ElephantSQL instance).

import postgres from "https://deno.land/x/postgresjs@v3.4.4/mod.js";

const sql = postgres({
  host: "server-elephantsql.com",
  database: "database-and-default-database",
  username: "database-and-default-database",
  password: "password",
  port: 5432,
  max: 2, // use at most 2 concurrent connections
});

const rows = await sql`SELECT * FROM names`;

console.log(rows);

Note that in the subsequent examples, we assume that you are working with the walking skeleton that injects the database credentials to the application through the environment file. If this is not the case, when trying out the examples, modify the code so that database credentials matching your database are used.

Example with Deno Postgres

The following example uses Deno Postgres as the PostgreSQL library to achieve the same outcome as the above example. We are slowly transitioning away from the use of Deno Postgres, and this is left as an example here.

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

const client = new Client({
  hostname: "server-elephantsql.com",
  database: "database-and-default-database",
  user: "database-and-default-database",
  password: "password",
  port: 5432,
});

await client.connect();
const result = await client.queryObject("SELECT * FROM names;");
await client.end();

console.log(result.rows);

PostgreSQL clients

Regardless of the chosen database, it is also meaningful to have a database client that can be used to access the database. By default, there is the psql console that provides terminal access to the database.

There are other options as well, though. If you use VSCode, you can use e.g. SQLTools (documentation) or Microsoft's PostgreSQL for Visual Studio Code.

If you do not use VSCode, or wish to have a separate client, the PostgreSQL wiki lists PostgreSQL Clients for different operating systems.