Tutorials on Sql

Are you ready to dive into the world of powerful database management with PostgreSQL and NodeJS? This guide is designed for beginners who want to understand how to use PostgreSQL in their NodeJS projects. Whether you're new to databases or looking to expand your skills, this tutorial will help you get started with confidence. In this guide, we'll cover: By the end of this tutorial, you'll have a solid foundation for building applications with PostgreSQL and NodeJS. For a more visual representation and in-depth details, you may refer to this video tutorial by me, Kristian Dupont, on \newline’s YouTube channel. Before we begin, you'll need to have PostgreSQL installed on your system. Here are two ways to do it: If you prefer using Docker, you can run PostgreSQL in a container with this one-liner: This command will: Now that we have PostgreSQL installed, let's set up our NodeJS project and connect to the database. Let's create a simple Koa server that we'll use to handle our API requests. Create a new file named server.mjs : Now, let's implement three endpoints for basic CRUD operations on a users table. First, let's create our users table. Run this SQL command in your PostgreSQL client: Now, let's add our routes to server.mjs : These routes allow you to: At this point, you should be able to start your server with the following command line: It should print that the server is running on port 3000. You can try it out by opening this route in a browser: http://localhost:3000/users — which should give you an empty array. Use a tool like cUrl or Postman to try out the POST route and see that you can create users in your database. The guide above forms part of a broader context, which includes working with a monorepo , setting up npm workspaces in monorepos , sharing configurations across packages, setting up the frontend with Vite , React , and Tailwind , and more. The full stack comprises the following technologies: This development stack provides end-to-end type safety, a greater confidence level when refactoring, and the ability to work with the database using plain SQL while still maintaining a type-safe architecture. Mastering PostgreSQL doesn't have to be intimidating. With this guide as a base, you can embark on your journey towards advanced database management using a full stack of contemporary technologies. To enhance your skills further, check out my course Fullstack Typescript with TailwindCSS and tRPC Using Modern Features of PostgreSQL . Happy learning!

Sequelize is a promise-based SQL ORM for Node.js, with support to Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL! During this tutorial, we will go through the creation of a simple library database using Sequelize and Node.js system. Models are the soul of Sequelize, we use them to represent the data of our tables, both on a row-level as a model instance, or as a table structure level as a model. Let's create our first model to represent a book in our library. But first using NPM we need to install the following libraries: Let's create a db.js file, to initialize our Sequelize database connection Now, let's create a book.js file to store our model Now let's add an index.js file to serve as our application entry point. Here we will have a main function that we will use to create our first object in the database. Let's have some fun with some crud operations inside our main function. Let's list all the books in our database: Here is the data I got in my DB after running the findAll query: To delete an entry in the database, we use .destroy() method: To update a model we use .update() method: To drop an entire table, we would use the .drop() method: The way we index our data in our database is essential for the functionality of our system, a good index will allow you to properly retrieve data faster, for example, the 2 following queries, gets us the same data The difference is that, since the latter is using the primary Key index, to search for the element will be faster. Models can relate to each other. Let's say we have an author model that relates to the book model where a book could have a single author and an author could have multiple books. Let's create the Author.js file: And we need to update our Book.js file in the following manner to include the authorId foreign key: These are one too many relations. In Sequelize we use the method .belongsTo() and .hasMany() to properly define the relation. In our index.js lets do the following: We've been through a lot within this short post but with these few examples, you should have a good grasp on the core functionalities of Sequelize. Sequelize is a really potent tool for working with SQL databases from within Node.js. If you want to keep going deeper into it, I recommend following it up with learning about Sequelize transactions and migrations. Have fun and keep coding! Check out the documentation of the modules we used in this post: If you have any questions - or want feedback on your post - come join our community Discord . See you there!