Name	Name	Last commit message	Last commit date
parent directory ..
tests	tests
.gitignore	.gitignore
README.md	README.md

PoC Software Pool 2024 - Day 02 - ORM

Day purposes

✔️ Discover ORM

✔️ Execute SQL query directly from code

✔️ Understand ORM pro and cons

Introduction

This morning, you wrote raw SQL queries to manage resources in a database.
But you can also do it directly from your code with a client 😄
It's an efficient way, but what if you have a big database with many relations and tables? It will be hard to correctly maintain your code and follow updates in your database. What will happen if you miss an update and broke your database? How will you manage a data migration?

There are many disadvantage to manually manage your database, and it's not relevant in small project. As well, you must learn the query language and specificities of the database you use (like PostgreSQL here).

To solve this problem, you can use an ORM. It stands for Object Relational Mapping and help you to code faster, better and automatically synchronize your codebase with your database 😍 More than that, your database can be directly defined from your code or from a schema. You don't have to learn a query language and some ORM can handle multiple databases.
The last killer feature of ORM is the code generation, you can generate all the basics functions (CRUD for instance) and call it from your code.

💡 Even if ORMs are perfect for small or simple projects, it will always be better to use raw SQL for a production application. If you require performance and control, you should manage your database manually or with tools that still give you control on your code.

Step 0 - Setup

📌 Tasks:

In the day02 folder of your pool directory, create a folder ORM:

mkdir -p day02/ORM

We will go back to Typescript so let's initialize a new NodeJS project:

npm init

You can now add Typescript and ESLint/Prettier using the knowledge you acquired from day01 😉

💡 Don't hesitate to go back to previous steps if you need it.

Step 1 - Put the base

📑 Description:

The purpose is to create and work with a PostgreSQL database that contains these resources:

RecordCompany
Artist
Contact
Music

📚 Documentation:

Here's a schema to clear your mind:

It's similar to the one from the SQL subject, but this time you will create it yourself 👍

To be more precise, you will define a schema and your ORM will create it for you using raw SQL queries 😉

To do that, we will use Prisma! It's a powerful ORM that supports resource definition outside of code. It follows a schema and generate both code and SQL queries for you 💥
With Prisma, a schema is a database and a model is a table.

You should look at the Prisma documentation to understand how it works 😄

📌 Tasks:

Installation

Let's install the prisma binary in our project:

npm install --save-dev prisma

We can now initialize prisma:

npx prisma init

💡 npx is a tool to execute a binary installed locally in a NodeJS project.

You should see a new prisma folder in your current directory, which means that you're ready to start!

Schema

To begin, we will create only one table.

In the file prisma/schema.prisma, create a model Artist with the following properties:

id: the unique key of your model, it must be a string automatically generated as uuid.
name: Artist's name
nationality: Artist's nationality

It's really important to define a key id when you create a resource, this way you can distinguish two resources with the same data but not the same identifier. You can use a simple number and increment it, but it's safer to use a uuid.

For now, nothing is concrete, continue to step 2 to execute your first query directly from Typescript 🚀

Step 2 - Things get serious

📑 Description:

You defined your model but there is no running database at this time. We are now using an ORM so everything will be manage by it, we just need to give it a connection URL.
The purpose of this step is to create a database, migrate our schema in and setup a client.

We will use a PostgreSQL database because it's efficient and one of the most used database across the world 🔥

To easily start it, we will use the official docker image.

Don't worry about docker for now, you will learn it during day04 👀

📌 Tasks:

Configuration

First, we must configure our environment to give prisma access to our future database. To centralize our configuration, we will use the .env file. This way we will be able to use these values from our host and keep only one source of truth.

Update the .env file with the following variables:

DB_USER: Database username
DB_PASSWORD: Database user password
DB_HOST: Host location (here it's localhost)
DB_PORT: Listening port of your PostgreSQL server, by default it's 5432
DB_NAME: Name of your database
DB_URL: Connection url, it's composed of all information defined before. Set it to: postgresql://$DB_USER:$DB_PASS@$DB_HOST:$DB_PORT/$DB_NAME

💡 You can create multiple databases in a single PostgreSQL server, so it's important to specify a name.

Start the database

Our environment is ready, let's create a postgres database 🙂

To make it easier to start for everyone, we will directly add the setup command in our package.json. This way, you can run the database without effort.

It's common to add external dependencies' setup directly into the package.json 😉

Add the following script to your package.json:

"dev:db": "docker run --name ${DB_NAME} -e POSTGRES_PASSWORD=${DB_PASS} -e POSTGRES_USER=${DB_USER} -e POSTGRES_DB=${DB_NAME} -p ${DB_PORT}:${DB_PORT} -d postgres:alpine",

You can now start the database:

# Add the variables to your environment
source .env

# Start your database
npm run dev:db

You can verify that your database is successfully started with docker container ls 🙂

Migrate the schema

We will now ask prisma to generate the database from our schema:

# Migrate data
npx prisma db push

You can also generate the schema from a database using the introspection.

You can verify with DataGrip that your database has been successfully created 😉

Client

Let's install the Prisma client in our dependencies:

npm install @prisma/client

Then let's generate the code from the schema:

npx prisma generate

Code

Finally, we can start coding 🥳

Create a folder src and a file client.ts, then paste the following code inside:

// Import the Prisma Client constructor
import { PrismaClient } from '@prisma/client';

// Constructor client
const prisma = new PrismaClient();
export default prisma;

This will instantiate a prisma client that we'll use in the following steps 😄

If you're curious, here's why this matters

Then, create an index.ts file still in src with the following content:

import prisma from './client';

// Declare an asynchronous main
async function main() {
  console.log('Database connected');
}

// Run main
main()
  .catch((e) => {
    // Throw on error	
    throw new Error(`Failed to initialize database: ${e}`);
  })
  .finally(async () => {
    // Disconnect client after main
    await prisma.$disconnect();
  });

You can now add a start script into your package.json and run npm run start to launch it 🚀

✔️ Validation:

If everything goes well, you should see the message Database connected in your terminal.

Step 3 - CRUD with an ORM

📑 Description:

As usual, a resource must expose a CRUD interface to be manipulated.

📌 Tasks:

Let's write functions to create, read, update and delete an Artist 😄

You will create a directory src/models and code all your functions in the file artist.ts.

You can copy the logic of the previous step 😉

You can now import the client to call CRUD functions generated by prisma.

⚠️ Don't forget that manipulation in your database takes time, so your function must be asynchronous.

C for Create

Add an asynchronous function named createArtist.

It must take a parameter data, which is an object that contains all the properties of your artist (name and nationality)

The function must create a new Artist in the database and return it.

R for Read

Create an asynchronous function named getArtists that will return all the Artists stored in your database.

But you'll also need to recover a single artist.
To do this, add an asynchronous function named getArtist.

It must take as parameter an id of type string and return the Artist found with this id.

U for Update

Create an asynchronous function named updateArtist.

It must take as parameters:

id: artist's identifier
data: information to update

This function must update the artist's data, save it in the database and return it.

⚠️ It must be possible to give any properties supported by Artist.

For example:

updateArtist(id, { name: 'newName' }); // This works
updateArtist(id, { name: 'newName', nationality: 'French' }); // This works too
updateArtist(id, { name: 'newName', unknownProperty: 'Unknown' }); // Error

💡 You should have a look to the Partial keyword in Typescript.

D for Delete

Create an asynchronous function named deleteArtist.

It must take as parameter an id of type string and delete it.

💡 Find more information about CRUD here.

Testing

Tests are important, even more when it's about resource manipulation.
If something is broken in it, your whole application will be broken.

To gain time, we created a Jest testing suite for you ✨

✔️ Validation:

Setup and run the tests

First, extract the step3_tests.zip file given to you.
You should have a jest.config.json file that you have to put at the root of your project and the tests folder with our first file inside it 🥳

Then, you should add Jest to your project:

npm install -D jest ts-jest @types/jest

Finally, add the following scripts to your package.json:

"test": "jest tests -i --env=node",
"test:cov": "jest -i --coverage tests --env=node",
"test:watch": "jest -i --watchAll tests --env=node"

You can now run npm run test to make sure your code works as expected:

If the tests don't pass, make sure you've respected the folders, files and function names, if it's all good then your logic is certainly wrong 😉

Step 4 - Contact Artists

📑 Description:

If you correctly remember the first step, our database is composed of 4 tables: Artist, Contact, Music and RecordCompany. To simplify the work, we code our project step by step.

📌 Tasks:

Let's add the table Contact in our schema and our first relation 💪

In a relational database, there are 3 types of relationship:

One to One: one entity is linked to another.
For example, an Artist can have only one Contact.
One to Many: also called Many to One, it's an entity that can be linked to more than one kind of another entity.
For example, a RecordCompany can work with many Artist but an Artist can only have one RecordCompany.
Many to Many: Many entities from a table are linked to many entities from another table.
For example an Artist can sing multiple Music and a Music can be singed by multiple Artist.

Here's a schema to illustrate these relationships:

📚 Documentation:

💡 Click here for more information about relationships in Prisma.

If you have well followed these explanations, you can figure out that we will create a One to One relationship 😉

Schema

Let's first update our Prisma schema with the new model Contact 😃

It must be composed of the following properties:

id: unique identifier automatically generated (same as Artist)
email: Artist's email (string)
phone: Artist's phone (string)
spotify: Artist's Spotify link (string)
youtube: Artist YouTube channel's link (string)

You will also need to create the relation between Artist and Contact 😉

To do so, update your models to:

Link Artist to Contact
Declare the One To One relation between them
Add a cascade behavior on deletion and update.

💡 If an Artist is deleted, his Contact must follow. This is what we call cascade.

⚠️ You have to create an artistId field with an artist relation in Contact, and a contact relation in Artist to make the tests

You can apply your changes using this command:

npx prisma db push

If you encountered an error, your schema is certainly false. Try to debug it, or ask the staff if you're really stuck 😄

The return of CRUD

Let's add the model functions that we will interact with.

Still in src/models, create the file contact.ts.

You should now have understood the concept, so explanations will be brief 😉

C for Create

Create an asynchronous function createContact.

It must take as parameters:

artistId: Artist's identifier to link with
data: Contact's properties (email, phone, spotify, youtube)

This function must create a Contact and connect it to the selected Artist to be able to return it.

⚠️ If the artist doesn't exist, it must throw an error (like the one in index.ts)

U for Update

Create an asynchronous function updateContact.

It must take as parameters:

id: Identifier of the Contact to update
data: Contact's properties to update (email, phone, spotify, youtube)

This function must update the Contact with the given data and return it.

If the artist doesn't exist, it must throw an error ❌

Same as updateArtist, data can contain any properties of Contact so you will also need to use Partial 😉

D for Delete

Create an asynchronous function deleteContact.

It must take as parameter the id of the Contact to remove and delete it.
Once again, throw an error if the Contact doesn't exist.

R for Read

It's not relevant to retrieve a Contact without his Artist.

Instead of create getContacts and getContact, you will update all the Artist model functions to include the Contact in his result.

T for Test

As usual, you should test your functions to verify their behavior.

Replace the content of your tests folder with the files given for this step and run them with npm run test 🧪

Step 5 - Skilled artists

📑 Description:

We added the first relation of our Artist, let's add the second with RecordCompany 💪

📚 Documentation:

This time, it will be a One to Many relationship 😄

📌 Tasks:

Schema

Update your Prisma schema to add the model RecordCompany with the following properties:

id: same as usual, you are a professional now 💯
name: Record's name
artists: List of Artist working with this Record

You can now link them in a One To Many relation.

⚠️ You have to name your new Artist fields recordCompanyId and recordCompany for the tests to work.

CRUD (again)

You are now used to this, create a file recordCompany.ts in src/models.

R for Read

Create an asynchronous function getRecordCompanies that will return all RecordCompany stored in the database.

Create an asynchronous function getRecordCompany.

It must take as parameter an id and return a RecordCompany if one match it.

C for Create

Create an asynchronous function createRecordCompany.

It must take as parameter the data object that contains the company properties (name)

💡 Yes we could directly pass the name here instead of an object, but it's a more generic way that'll work even if we add more fields later.

The function must create a new RecordCompany in the database and return it.

U for Update

Create an asynchronous function updateRecordCompany.

It must take as parameters:

id: record company's identifier
data: an object that contains the company properties to update (name)

The function must update the RecordCompany in the database and return it.
If the RecordCompany doesn't exist, throw an error.

D for Delete

Create an asynchronous function deleteRecordCompany.

It must take as parameter the company id and delete the RecordCompany in the database.

If the RecordCompany doesn't exist, throw an error ❌

Link our entities

Since RecordCompany and Artist are standalone entities, it will be necessary to create function to link/unlink an Artist to a RecordCompany 🙂

Still in src/models/recordCompany.ts, we will add 2 new functions.

Link

Create a function addArtistToRecordCompany.

It must take as parameters:

artistId: Artist identifier
recordCompanyId: Record company identifier

Your function will connect these entities using their id.

Don't forget error handling 😉

Unlink

Create a function removeArtistFromRecordCompany, which takes the same parameters than the previous function.

It will perform the reverse behavior, which is to disconnect these entities using their id 😃

Testing

You know how this goes now!

Copy the given test files and run them to check that you implemented everything correctly 🧪

Step 6 - Active artists

📑 Description:

Finally, we are near the end! Only one entity remains: Music.

This step is intentionally less guided, if you reached it, you should have understood everything about relational databases, CRUD and how you should code it 🚀

📌 Tasks:

Schema

Update your schema with the model Music. It will be composed of the following properties :

id: unique identifier
name: music's name
link: public link to the music (it could be whatever you want YouTube, Spotify...)
artists: list of Artist in featuring

You will need to create a Many To Many relation between Artist and Music.

Don't forget to update other models if it's necessary 😉

You already know it

You already know what to do and how to do it 🚀

We want to:

create
read
update
delete
link an artist to a music
remove an artist from a music

You can also write your own tests based on the ones we gave you for the previous steps to make sure everything works as intended 👍

Don't forget to handle errors 😃

Bonus

Congratulations for completing this day!

Here are some bonuses for you if you still have time:

Yesterday, you created a cool application following a strong and resilient architecture called MVC.

An advantage of MVC is its layered architecture, you can easily update a part of your application without refactoring everything.

If you remember well, your data storage was a simple JSON file, what about replacing it with a relational database?

You know how MVC works, you know how to store data in a relational database, you know an easy way to interact with database directly from your code...

Let's mix all your knowledge to add a permanent storage to your application 🚀

Indeed, you are free to add/update models, refactor the application and do whatever you find useful.

Have fun!

To go further

Some links to additional resources and cool stuff:

Organization

🚀 Don't hesitate to follow us on our different networks, and put a star 🌟 on PoC's repositories.

Files

Typescript

Directory actions

More options

Directory actions

More options

Latest commit

History

Typescript

Folders and files

parent directory

README.md

PoC Software Pool 2024 - Day 02 - ORM

Introduction

Step 0 - Setup

📌 Tasks:

Step 1 - Put the base

📑 Description:

📚 Documentation:

📌 Tasks:

Installation

Schema

Step 2 - Things get serious

📑 Description:

📌 Tasks:

Configuration

Start the database

Migrate the schema

Client

Code

✔️ Validation:

Step 3 - CRUD with an ORM

📑 Description:

📌 Tasks:

C for Create

R for Read

U for Update

D for Delete

Testing

✔️ Validation:

Step 4 - Contact Artists

📑 Description:

📌 Tasks:

📚 Documentation:

Schema

The return of CRUD

C for Create

U for Update

D for Delete

R for Read

T for Test

Step 5 - Skilled artists

📑 Description:

📚 Documentation:

📌 Tasks:

Schema

CRUD (again)

R for Read

C for Create

U for Update

D for Delete

Link our entities

Link

Unlink

Testing

Step 6 - Active artists

📑 Description:

📌 Tasks:

Schema

You already know it

Bonus

To go further

Organization