korkorran/next.js-sql-app

A full-fledged Next.Js app powered by an SQL database. PostgreSQL, MySQL & SQLite ready.

Next.js ☀️ SQL

A full-fledged Next.Js app powered by an SQL database.
Fully compatible with PostgreSQL, MySQL & SQLite

Online demo at https://nextjs-sql-app.vercel.app/

Features

✔️ No Express.js ✔️ Serverless ready ✔️ API Routes

🔒 Authentication

^{with next-iron-session}

• Session
• Sign up/Sign in/Sign out

👩👨 Profile

• Profile picture, name, bio, registration date
• Edit profile

🔧 Account

• Email verification TODO
• Password change
• Password reset TODO

👀 Users and social

• Other user profile
• Posting

🏛 SQL powered

• Work with any SQL database
• PostgreSQL, MySQL & SQLite ready

^{Have any features that interest you, make an issue. Would like to work on a feature, make a PR.}

---

How security is managed?

Vercel serve apps with HTTPS by default, so you have no reasons to have security concerns. Once the user is authentified, the connexion is secured with a next-iron cookie. Then, authentification of the user is super-fast and secure.

Code Features

• Coded in TypeScript.
• This app uses Next.js static generation for all its pages. The main advantage is that the application is super-fast.
• All the dynamic data is fetch within the pages with the great useSWR hook. It enables data caching and make the app even faster.
• Pages that should not be access from public user are protected inside a protected route. If a public user attemps to access to these pages, he is redirected to the login page.
• privates API routes are protected with the next-iron-session utility. If the user is not authentified, the data from the API endpoint is not returned.
• 📱 The design is made with Bulma.io CSS and is fully responsive.
• SQL Database requests are made with the Knex.Js SQL query builder.

Getting Started

First, run the development server:

cp .env.example .env
# edit .env
npm run dev
# or
yarn dev

Open http://localhost:3000 with your browser to see the result.

Deploy

This is a Next.js project bootstrapped with create-next-app.

It can also be deployed locally with npm run build & npm start command.

Be sure to set the environment variable to enable next-iron encryption of the sessions cookies.

To do that, copy the .env.examplefile into .env, and set your own 32 character long password for IRON_PASSWORD.

environments database

• development : set KNEX_ENV to development. SQLite3 stored in file ./dev.sqlite3
• production : set KNEX_ENV to production. PostgreSQL whose credentials are env variables PG_HOST, PG_DATABASE, PG_USER, PG_PASSWORD

edit knexfile.js to change database connections

API docs

All the API endpoints work within Next.js API routes. They can be found in src/pages/api folder. The complex types of JSON body returned by API endpoints can be found in the src/utils/types.d.ts file.

URL	HTTP method	returned Type	Public	Description
/api/user/log-in	POST	LoginResponse	✅	If the user is successfully authentified or not. Gives also details about the user.
/api/user/sign-up	POST	SignupResponse	✅	If the user is successfully registered or not.
/api/user/me	GET	User	❌	Details about the user
/api/user/passwordreset	GET	response	❌	If the password has been changed
/api/user/bioreset	GET	response	❌	If the bio has been changed
/api/private	GET	string	❌	Example text
/api/public	GET	string	✅	Example text

Further work

Please add any issue if you think the app can be improved !