{"id":10,"date":"2025-04-22T04:26:46","date_gmt":"2025-04-22T04:26:46","guid":{"rendered":"https:\/\/nextialab.com\/?p=10"},"modified":"2025-04-22T04:27:21","modified_gmt":"2025-04-22T04:27:21","slug":"simple-express-and-postgres-project","status":"publish","type":"post","link":"https:\/\/nextialab.com\/index.php\/2025\/04\/22\/simple-express-and-postgres-project\/","title":{"rendered":"Simple Express and Postgres project"},"content":{"rendered":"\n

Assume the next versions:<\/p>\n\n\n\n

$ node --version\nv23.7.x\n$ npm --version\n10.9.x\n$ podman --version\n5.2.x<\/code><\/pre>\n\n\n\n
Create a Node project with npm:<\/p>\n\n\n\n
$ npm init<\/code><\/pre>\n\n\n\n
This will create a new package.json<\/code> file where to define tasks and dependencies. The dependencies needed at least are:<\/p>\n\n\n\n
$ npm install body-parser dotenv express knex pg pug<\/code><\/pre>\n\n\n\n
A quick overview of each one:<\/p>\n\n\n\n
    \n
  • body-parser<\/strong>: needed to transform the body payload into an object in req.body<\/code>.<\/li>\n\n\n\n
  • dotenv<\/strong>: needed to load environment information from a .env<\/code> file such as the database password.<\/li>\n\n\n\n
  • express<\/strong>: handles the web server with high level functions for methods and endpoints<\/li>\n\n\n\n
  • knex<\/strong>: database abstraction to build SQL queries with code<\/li>\n\n\n\n
  • pg<\/strong>: PostgreSQL driver for Node<\/li>\n\n\n\n
  • pug<\/strong>: Template engine<\/li>\n<\/ul>\n\n\n\n
    And for tasks:<\/p>\n\n\n\n
    {\n    \"scripts\": {\n        \"start\": \"node index.js\",\n        \"migrate:make\": \"knex migrate:make\",\n        \"migrate:latest\": \"knex migrate:latest\"\n    }\n}<\/code><\/pre>\n\n\n\n
    Where:<\/p>\n\n\n\n
      \n
    • start<\/strong>: starts the web server, assuming our point of entry is the file index.js<\/code>.<\/li>\n\n\n\n
    • migrate:make<\/strong>: creates a new SQL migration file in the migrations<\/code> folder.<\/li>\n\n\n\n
    • migrate:latest<\/strong>: runs the migrations that have not been already applied to the database<\/li>\n<\/ul>\n\n\n\n
      A knexfile.json<\/code> will be required to store the database connection details that will be used by knex, a basic implementation is:<\/p>\n\n\n\n
      require('dotenv').config();\n\nmodule.exports = {\n    client: 'pg',\n    connection: {\n        database: process.env.DBNAME,\n        user: process.env.DBUSER,\n        password: process.env.DBPASS\n    },\n    pool: {\n        min: 2,\n        max: 10\n    },\n    migrations: {\n        tableName: 't_knex_migrations'\n    }\n};<\/code><\/pre>\n\n\n\n
      This implementation will load the DBNAME<\/code>, DBUSER<\/code> and DBPASS<\/code> information from the .env<\/code> file with the dotenv<\/code> module, and will use them to connect to the database. Also, it will use the table t_knex_migrations<\/code> in the database to store the current migration state. This file will be used by both the knex migration script and the knex module during runtime.<\/p>\n\n\n\n
      The folder structure:<\/p>\n\n\n\n
      project\n|-- migrations\n|   |-- .\n|-- services\n|   |-- db.js\n|-- .env\n|-- .gitignore\n|-- index.js\n|-- knexfile.js\n|-- package.json<\/code><\/pre>\n\n\n\n
      The db.js file is just a nicer way to setup the database connection that can be reused by multiple components:<\/p>\n\n\n\n
      const knex = require('knex');\n\nmodule.exports = knex(require('..\/knexfile'));<\/code><\/pre>\n\n\n\n
      PostgreSQL<\/h2>\n\n\n\n
      PostgreSQL can be either installed locally, run in a container, or deployed in PaaS. Personally, in Linux I prefer to have it installed locally and run it in a container otherwise. For the containerized option:<\/p>\n\n\n\n
      $ podman pull docker.io\/postgres\n$ podman run -d -p 5432:5432 -e POSTGRES_PASSWORD=yourdbpass --name pg_container postgres\n$ podman stop pg_container<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
      Creating a simple Express.js project in Node.js along with a PostgreSQL back end<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[4,6,5],"class_list":["post-10","post","type-post","status-publish","format-standard","hentry","category-tutorials","tag-node","tag-podman","tag-postgres"],"_links":{"self":[{"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/posts\/10","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/comments?post=10"}],"version-history":[{"count":2,"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/posts\/10\/revisions"}],"predecessor-version":[{"id":13,"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/posts\/10\/revisions\/13"}],"wp:attachment":[{"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/media?parent=10"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/categories?post=10"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/nextialab.com\/index.php\/wp-json\/wp\/v2\/tags?post=10"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}