neon-starter-kit: with UI for thorough database interaction, for both Node.js and Vercel webapps

This is a submission for the Neon Open Source Starter Kit Challenge : Ultimate Starter Kit

My Kit

Preview with video here

ogbotemi-2000 / neon-starter-kit

A starter kit for users getting started with the database services of neon.tech

neon-starter-kit

A starter kit for users using Node.js and/or Vercel serverless for getting started with or extending connecting and interacting with their PostgreSQL or MySQL databases hosted by direct effort or abstracted away via serverless SAAS like https://neon.tech

neon-starter-kit.mp4

Features

• A HTTP Node.js server with dynamic support for middlewares and routes to api/ endpoints
• Compatibility with Vercel serverless functions
• Custom configuration via config.json
• A user interface for complete interaction with database
• Option to enable pooled connection for database operation
• Thorough testing of database via queries, the server never crashes; it returns detailed errors instead
• Ready to use templates for directives SELECT, DROP, ALTER, CREATE, INSERT and UPDATE
• Editing support for queries in a basic HTML editor with formatting and styling
• Dropdown containing various datatypes available in SQL for input types that store the type of data for a field - VARCHAR(255), INT...
• Automatic addition of ending…

View on GitHub

Overview

The starter kit has a basic but adequate folder structure and codebase that is quickly extensible and doesn't get in the way of understanding and further developing it

| - server.js  # contains concise routing for GET and POST requests
| - index.html # contains UI for database interaction
| - config.json # is expected to be provided in cloned repo by user
| - css
| - js
| - fonts
| - webfonts
| - api # contains endpoints invoked by the Node.js server or Vercel 

Configurations

A config.json file is imported where needed and read for environment variable values as an improvement over the approach of creating properties loaded from an .env file on process.env as done by dotenv - The values are easily changeable as JSON and default linting marks erroneous JSON files to prevent gotchas.

{
"PGHOST": "<DATABASE_HOST_ADDRESS>",
"PGDATABASE": "<DATABASE_NAME>",
"PGUSER": "<DATABASE_OWNER>",
"PGPASSWORD": "******************",
"ENDPOINT_ID": "<DATABASE_ENDPOINT>"
}

The user is free to adopt using an .env file in similar fashion config.json, in fact, the purpose of config.json is to encourage local development and testing before deploying to production.

Plug-n-play

• Clone the template repository at https://github.com/ogbotemi-2000/neon-starter-kit
• Create a config.json file (nothing will work without one) with the above details specific to your account on neon.tech;
• Run npm start or node server

Remember to add your config.json to .gitignore since it contains secrets that should only remain in your local workspace

Interacting with your database via the UI

The UI offers a simple yet complete editing environment to quickly edit templates for SELECT, UPDATE, ALTER, INSERT, CREATE and DELETE queries.

You can even maintain the styling of keywords by copy-pasting them and editing them without completely clearing them

Performance gains

You are free to write incorrect SQL queries, the backend catches all errors caused by your custom SQL query and sends detailed errors for display for you to iteratively improve on your queries.

🚀 It even features support for enabling pooled connections and thus improve performance!

Room for Extension

You can write more query schema beyond the common queries present on the UI

Left out the ending semi-colon? No worries, such minor bloopers are fixed in the query before it is sent to the backend

The service displays the details and fix for the network error which prevents its complete loading

Complete loading and connection of database to display database name and PostgreSQL version

Displayed result of the `select` query on a database used for testing and development

Link to Kit

It is available as a publicly available template repository here
https://github.com/ogbotemi-2000/neon-starter-kit.git

Your Journey

Putting the 'full' into the stack

I have been using Redis and a smattering of MySQL for backend data storage but I do not think I have grasped using MySQL as expected of a fullstack developer so I try to understand its origins which led me to PostgreSQL. Given its level of maturity as an old-but-still-in-great-use tech, I find myself enjoying a blend of PostgreSQL and MySQL.

@neondatabase driver helped a ton!

I had tried to implement connecting to my hosted database on neon using the postgre package all to no avail until I used @neondatabase/serverless, problem solved.

Using Vanilla JavaScript and HTML for the UI/UX

Native is the best, frameworks only come close in most cases except CSS - TailwindCSS utility classes aided my design sprints and my knowledge of JavaScript made me get away with the less is more philosophy such as abstracting away everything about fetch and/or XMLHttpRequest in only and iframe and a form.

😊😉 Try searching for either fetch(...) or _XMLHttpRequest in the code, there wiil be no matches cause they are not directly used. Simplicity - less is truly more

Optimizations

Further down the line, I will remove unused CSS styles in the used stylesheets via the webapp I had developed prior to this challenge: https://rmrf-css.vercel.app
It is production ready and very thorough in detecting likely used selectors - in inline event listeners, in inline and external scripts.
Do check it out.
Thanks and go towards goodness