But before you can confidently create, read, update, or delete data, you need to understand how Firestore actually organizes information. It doesn't look like a SQL database, and treating it like one is the fastest way to end up with a messy, hard-to-query data structure.

In this tutorial, you'll learn how Firestore's NoSQL data model works, then build a small task management app to practice every CRUD operation with the Firebase Web SDK (v9+, modular). By the end, you'll be able to add tasks, query them, update nested fields and arrays, and delete data safely without leaving orphaned subcollections behind.

Table of Contents

• Prerequisites

• How Firestore Structures Data

• Step 1 – Set Up Your Firebase Project

• Step 2 – Initialize the SDK

• Step 3 – Create: Adding Tasks

• Step 4 – Read: Querying Tasks

• Step 5 – Update: Modifying Tasks

• Step 6 – Delete: Removing Tasks

• Debugging Common Issues

• Conclusion

Prerequisites

Before you start, make sure you have the following:

• Node.js v18 or later (node --version)

• A Google account to create a Firebase project (the free Spark plan is enough for this tutorial)

• Basic familiarity with JavaScript, including async/await and ES modules

• A code editor and a terminal

You don't need prior experience with Firebase or NoSQL databases, as this guide builds that understanding from the ground up.

How Firestore Structures Data

If you're coming from a relational (SQL) background, the first thing to unlearn is the idea of tables with a fixed schema and foreign key joins. Firestore is a document-oriented NoSQL database, and it organizes data around two core concepts: collections and documents.

• A collection is a named bucket that holds documents. Think tasks, users, or orders.

• A document is a single record inside a collection, identified by a unique ID. It stores data as key-value pairs, similar to a JSON object.

Here's the catch that trips up a lot of newcomers: documents don't need to share the same fields. One task document can have a dueDate field while another doesn't. Firestore doesn't enforce a schema at the database level, that responsibility shifts to your application code.

Nesting and subcollections

Documents can hold two kinds of nested data:

• Maps, which are objects nested directly inside a document (for example, a metadata field containing { priority, dueDate })

• Subcollections, which are entire collections nested under a specific document (for example, every task can have its own comments subcollection)

This gives you a structure that looks like a tree:

tasks (collection)
 └── taskId (document)
      ├── title: "Article title"
      ├── completed: false
      ├── tags: ["writing", "firebase"]
      ├── metadata: { priority: "high", dueDate: <timestamp> }
      └── comments (subcollection)
           └── commentId (document)
                ├── text: "CRUD Article"
                └── createdAt: <timestamp>

Supported data types

Firestore documents can store several native types. The ones you'll use most often are:

Why this matters before writing CRUD code

Every CRUD operation you'll write later depends on this structure:

• Create means adding a document to a collection, with an auto-generated or custom ID.

• Read means fetching either a single document by ID or a set of documents matching a query.

• Update means modifying fields on an existing document, including nested maps and arrays.

• Delete means removing a document, and Firestore will not automatically clean up its subcollections (a common gotcha you'll see in Step 6).

With the mental model in place, let's set up a project and start writing code.

Step 1 – Set Up Your Firebase Project

Head to the Firebase console and create a new project.

1. Click Add project, give it a name (for example: crud-tasks-demo), and follow the setup wizard (Google Analytics is optional for this tutorial).

2. Once the project is created, open the left sidebar and click Databases and Storage and then Firestore.

3. Click Create database. Choose a location close to you, and for this tutorial, start in test mode so you can read and write without configuring security rules yet.

Note: Test mode leaves your database open to anyone for 30 days. Never ship an app to production without proper Firestore security rules, we'll touch on this in the Debugging section.

You should now see an empty Firestore database, ready to receive your first collection.

Step 2 – Initialize the SDK

Create a new project folder and install the Firebase Web SDK:

mkdir firestore-crud-demo && cd firestore-crud-demo
npm init -y
npm install firebase

Grab your project's config object from Project settings - General - Your apps - Web app in the Firebase console (register a new web app if you haven't yet).

Create a firebase-config.js file:

// firebase-config.js
import { initializeApp } from "firebase/app";
import { getFirestore } from "firebase/firestore";

const firebaseConfig = {
  apiKey: "YOUR_API_KEY",
  authDomain: "YOUR_PROJECT_ID.firebaseapp.com",
  projectId: "YOUR_PROJECT_ID",
  storageBucket: "YOUR_PROJECT_ID.appspot.com",
  messagingSenderId: "YOUR_SENDER_ID",
  appId: "YOUR_APP_ID",
};

const app = initializeApp(firebaseConfig);
export const db = getFirestore(app);

Every CRUD example from here on imports db from this file. Keep your actual config values out of version control (use environment variables in a real project).

Step 3 – Create: Adding Tasks

Firestore gives you two ways to create a document: let Firestore generate the ID, or set your own.

Auto-generated ID with addDoc()

// create-task.js
import { collection, addDoc, Timestamp } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function createTask() {
  try {
    const docRef = await addDoc(collection(db, "tasks"), {
      title: "Write CRUD article",
      completed: false,
      tags: ["writing", "firebase"],
      metadata: {
        priority: "high",
        dueDate: Timestamp.fromDate(new Date("2026-09-15")),
      },
      createdAt: Timestamp.now(),
    });
    console.log("Task created with ID:", docRef.id);
  } catch (error) {
    console.error("Error creating task:", error);
  }
}

createTask();

Custom ID with setDoc()

Use this when you want to control the document ID yourself, for example, matching it to an ID from another system.

import { doc, setDoc } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function createTaskWithId(taskId) {
  await setDoc(doc(db, "tasks", taskId), {
    title: "Review pull request",
    completed: false,
    tags: ["code-review"],
  });
}

createTaskWithId("task-001");

Adding a document to a subcollection

To add a comment under a specific task, you reference the parent document first:

import { collection, addDoc, Timestamp } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function addComment(taskId, text) {
  await addDoc(collection(db, "tasks", taskId, "comments"), {
    text,
    createdAt: Timestamp.now(),
  });
}

addComment("task-001", "First draft done");

Step 4 – Read: Querying Tasks

Fetching a single document

import { doc, getDoc } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function getTask(taskId) {
  const snapshot = await getDoc(doc(db, "tasks", taskId));
  if (snapshot.exists()) {
    console.log(snapshot.id, snapshot.data());
  } else {
    console.log("No such task.");
  }
}

getTask("task-001");

Fetching an entire collection

import { collection, getDocs } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function getAllTasks() {
  const snapshot = await getDocs(collection(db, "tasks"));
  snapshot.forEach((doc) => {
    console.log(doc.id, doc.data());
  });
}

getAllTasks();

Filtering with queries

import { collection, query, where, orderBy, limit, getDocs } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function getUrgentPendingTasks() {
  const q = query(
    collection(db, "tasks"),
    where("completed", "==", false),
    orderBy("metadata.priority"),
    limit(10)
  );

  const snapshot = await getDocs(q);
  snapshot.forEach((doc) => console.log(doc.id, doc.data()));
}

getUrgentPendingTasks();

Heads up: combining where() on one field with orderBy() on another often requires a composite index. Firestore will throw an error in your console with a direct link to create it. More on this in Debugging.

Real-time updates with onSnapshot()

Instead of fetching once, you can subscribe to live changes. This is useful for a task list that updates instantly across devices:

import { collection, onSnapshot } from "firebase/firestore";
import { db } from "./firebase-config.js";

const unsubscribe = onSnapshot(collection(db, "tasks"), (snapshot) => {
  snapshot.docChanges().forEach((change) => {
    console.log(change.type, change.doc.id, change.doc.data());
  });
});

// Call unsubscribe() when you no longer need updates (e.g., component unmount)

Step 5 – Update: Modifying Tasks

Partial update with updateDoc()

Unlike setDoc(), updateDoc() only touches the fields you specify. Everything else on the document stays untouched.

import { doc, updateDoc } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function completeTask(taskId) {
  await updateDoc(doc(db, "tasks", taskId), {
    completed: true,
  });
}

completeTask("task-001");

Updating a nested field with dot notation

You don't need to rewrite the whole metadata map to change one property inside it:

await updateDoc(doc(db, "tasks", "task-001"), {
  "metadata.priority": "low",
});

Updating arrays safely

Directly overwriting an array field is risky in concurrent scenarios. Use arrayUnion() and arrayRemove() instead:

import { doc, updateDoc, arrayUnion, arrayRemove } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function addTag(taskId, tag) {
  await updateDoc(doc(db, "tasks", taskId), {
    tags: arrayUnion(tag),
  });
}

async function removeTag(taskId, tag) {
  await updateDoc(doc(db, "tasks", taskId), {
    tags: arrayRemove(tag),
  });
}

arrayUnion() won't add a duplicate value, and arrayRemove() removes every matching instance. Both operate atomically on the server.

Step 6 – Delete: Removing Tasks

Deleting a document

import { doc, deleteDoc } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function deleteTask(taskId) {
  await deleteDoc(doc(db, "tasks", taskId));
}

deleteTask("task-001");

The subcollection trap

Here's the gotcha mentioned earlier: deleting tasks/task-001 does not delete its comments subcollection. Those comment documents become orphaned, they still exist in your database. They're just unreachable through the UI unless you know the path.

To clean up properly, delete the subcollection's documents first, then the parent:

import { collection, getDocs, doc, deleteDoc, writeBatch } from "firebase/firestore";
import { db } from "./firebase-config.js";

async function deleteTaskWithComments(taskId) {
  const commentsRef = collection(db, "tasks", taskId, "comments");
  const commentsSnapshot = await getDocs(commentsRef);

  const batch = writeBatch(db);
  commentsSnapshot.forEach((commentDoc) => {
    batch.delete(commentDoc.ref);
  });
  batch.delete(doc(db, "tasks", taskId));

  await batch.commit();
}

deleteTaskWithComments("task-001");

writeBatch() groups multiple deletes into one atomic operation. Either all of them succeed, or none do.

Deleting a single field

If you only want to remove one field without deleting the whole document, use deleteField():

import { doc, updateDoc, deleteField } from "firebase/firestore";
import { db } from "./firebase-config.js";

await updateDoc(doc(db, "tasks", "task-001"), {
  metadata: deleteField(),
});

Debugging Common Issues

FirebaseError: Missing or insufficient permissions

Your security rules are blocking the request. If you're still in test mode, check whether your 30-day window expired (rules revert to deny-all after that). For a real app, review your rules in Firestore and then Rules and make sure they match the paths you're reading/writing, including subcollections, which need their own rule blocks.

Function addDoc() called with invalid data. Unsupported field value: undefined

Firestore rejects undefined values outright, unlike null, which is allowed. This usually happens when a form field is empty and you pass it straight into your write call. Filter out undefined fields before writing, or default them to null.

The query requires an index

This shows up when you combine where() and orderBy() on different fields, as in the Step 4 example. Firestore can't serve that query with automatic indexes. The error message includes a direct link that pre-fills the composite index for you in the console, click it, wait a minute or two for the index to build, and rerun your query.

Reads adding up fast / quota warnings

Every document returned by getDocs() counts as a read, even inside a loop calling getDoc() repeatedly. Avoid fetching a whole collection just to filter it client-side, push filtering into your query with where() instead, and use limit() on anything that could grow unbounded.

Orphaned subcollections after delete

If you notice documents you thought you deleted still consuming storage or showing up in exports, check for subcollections under the deleted document's path. As shown in Step 6, deleteDoc() never cascades, cleanup is always your responsibility.

Conclusion

You now have a working mental model of Firestore's structure and hands-on experience with every CRUD operation using the Web SDK v9+. Here's a quick recap:

From here, there are a few natural next steps once you're comfortable with the basics:

• Transactions, for reads and writes that must succeed or fail together (for example, transferring a task between two users)

• Batch writes, which you already saw in Step 6. They're useful anytime you need to touch multiple documents atomically

• Composite indexes, for more advanced filtering and sorting combinations

• Pagination with startAfter(), for loading large collections in chunks instead of all at once

If you haven't already, it's worth revisiting how to model your data before you write queries against it. Decisions made at the modeling stage (like whether to nest data or use a subcollection) directly shape which of these CRUD patterns will feel natural versus awkward later on.

Then someone asks a question like this one:

Which engineers have recent context on the services affected by last night's incident?

That question is easy to understand and hard to write. In SQL it becomes four or five joins. Each join builds an intermediate result that is wider than the answer you actually want, and then throws most of it away. The query gets slower as your tables grow, and it gets harder to read every time you come back to it.

A graph database is built for that question.

In this handbook you will build a working knowledge graph from an empty database, load real data into it from Python, and write the queries that make the idea click.

You'll also learn the parts that tutorials usually skip: how to decide what becomes a node, why your first data model is probably wrong, how to make loading fast, and how to read a query plan when something is slow.

You don't need any graph experience to follow along. If you've written SQL, you already know enough.

The same question asked of the same data, two ways. On the left, a relational database matches rows at query time and throws most of them away. On the right, a graph follows connections that were already stored when the data was written. The rest of this handbook is really about that difference.

All the code and the dataset are in one place: github.com/ronidas39/knowledge-graph-python-neo4j. Every script in this handbook runs, and every number is measured against the committed dataset. You can clone it and reproduce it all as you read.

Table of Contents

• The Data We'll Use

• The Words You'll Need

• What You're Building

• What a Graph Database Actually Stores

• Index-free Adjacency, the Idea That Makes it Fast

• When a Graph is the Wrong Choice

• How to Set Up Neo4j and the Python Driver

• The Modeling Decision That Matters Most

• Three Modeling Mistakes Almost Everyone Makes

• Modeling Backwards From Your Questions

• Three Modeling Patterns Worth Knowing Early

• Loading Data From Python

• Loading at Scale with UNWIND

• Loading From a CSV File

• Updating and Deleting

• Working with Neo4j Data Types

• Your First Cypher Queries

• The Multi-Hop Query That Justifies the Whole Thing

• Variable Length Paths and How to Keep Them Safe

• What an Index Actually is

• Constraints, and the Trap That Will Catch You

• What the Planner Does With Your Query

• Six Problems You'll Actually Hit

• Transactions and What Happens When Things Fail

• Testing Code That Talks to a Graph

• From Graph to Knowledge Graph

• Why AI Systems Keep Rediscovering Graphs

• Building a Knowledge Graph from Text

• The Complete Script

• Where to Go Next

The Data We'll Use

Every example in this handbook runs against the same small dataset, so you can follow along from the first query to the last without ever loading something new.

It models a software team, because that's a domain most readers can check against their own experience. It's entirely made up, thought: no real company, service, or person appears in it, and the email addresses use example.com (this is reserved by RFC 2606 precisely so documentation can't accidentally point at somebody's real address).

The data are connected by four relationship types:

Fourteen nodes and sixteen relationships for thirty records in total. That's deliberately tiny, because at this size you can hold the whole graph in your head and check every answer by eye. This is exactly what you want while the ideas are new. Nothing here behaves differently at a million nodes. It's only slower to verify.

Two details are worth noticing before they matter later. One engineer owns nothing, which is the only reason the OPTIONAL MATCH example has anything to show. And Commerce has exactly one member, who is also an owner, which turns out to expose a Cypher trap that silently drops rows. Neither is an accident.

The complete loading script is at the end of this handbook, and you can run it before reading any further if you'd rather have the data in front of you.

The Words You'll Need

Every term in this handbook is defined where it first appears, but it helps to have them in one place. If you've never touched a graph database, read this table once and come back to it whenever a word stops making sense.

Two conventions are used throughout, and they're worth knowing before you meet them:

Relationship types are written in SCREAMING_SNAKE_CASE (OWNS, MEMBER_OF) and labels in PascalCase (Engineer, Service). Neo4j doesn't enforce either, but every codebase and every piece of documentation follows them, so matching the convention makes your queries readable to everyone else.

The full language reference lives in the Cypher Manual, and it's genuinely good. When something in this handbook raises a question, that's where to look next.

What You're Building

Before any of the parts, here's the shape of the whole thing. Four moving parts: the data you start with, the Python driver that loads it, the graph that Neo4j stores, and the answers that come back out in a form a language model can use without inventing anything.

Reading left to right: your data is CSV files, an existing database, or plain text a model pulls triples out of. The Python driver is one driver object for the whole application, execute_query() to run Cypher, and UNWIND to batch a thousand rows into one round trip. Neo4j is where it lands, and it runs identically on Docker, EC2 or Aura because only the connection URI changes. Constraints and indexes are created here before the load, never after.

What you get back is multi-hop answers that hold up at 75,500 nodes, with a path behind each one you can cite.

Three things worth noting: first, you don't need all of it on day one, since Docker, the driver and a handful of nodes is already a working system. Also, every number here was measured against the committed 75,500 node dataset on Neo4j 5.26.29 Community, not estimated. And the arrows only go one way, because nothing in this handbook writes back from the model into the graph, which is a boundary worth keeping until you trust the extraction.

On which version to install: don't worry about matching mine exactly. Everything here was measured on Neo4j 5.26.29 Community, and 5.26 is the long-term support release, which Neo4j supports until June 2028. From 2025 onward they name releases by date instead, so you'll see 2025.01, 2025.02 and so on rather than 5.27. Those are fully compatible with the Cypher and the drivers used here, so the queries in this handbook run unchanged on them.

Two things do vary, and neither is about the version number. Timings depend on your machine, so treat my numbers as ratios rather than targets. And the constraints beyond IS UNIQUE need Enterprise, which is an edition difference rather than a version one. The neo4j:5 Docker tag used below gives you the latest 5.x, which is a good default.

You don't need all of it on day one. Docker, the driver, and a handful of nodes is already a working system. Everything else in this handbook is what you add when the graph stops fitting in your head.

What a Graph Database Actually Stores

A graph database stores three things. That's genuinely all of it.

The drawing works one concrete example. An Engineer node holds name: "Ada" and an email. An arrow labelled OWNS carries since: 2026-03-01. A Service node holds name: "payments". Callouts point at each piece in turn. They name which part is the node, which is the label, which is the property, and which is the relationship. The last one they name is the property that sits on the relationship rather than on either end.

The panel underneath contrasts that last one with tables, and it's the piece with no clean relational equivalent. To record that Ada has owned payments since March, a relational schema needs a join table you invented only because rows can't point at each other.

Nodes are the things in your domain: an engineer, service, incident, or team.

Relationships connect exactly two nodes. Every relationship has a direction and a type. An engineer OWNS a service. An incident AFFECTS a service. The direction is stored, and you'll see shortly that you can traverse a relationship in either direction regardless of how it was stored.

Properties are key and value pairs. They live on nodes and on relationships. An engineer node might carry a name and an email. An OWNS relationship might carry the date that ownership started, which is a fact about the connection rather than about either end of it.

Nodes also carry labels, which group them. A node labelled Engineer is an engineer. A node can have more than one label. Labels are how you tell the database to look only at engineers instead of scanning everything you have ever stored.

Here's the same small piece of information in both worlds.

That last row is worth pausing on. In a relational schema, saying "Ada has owned payments since March" needs a column on the join table, and that join table is an implementation detail you invented to work around the fact that rows can't point at each other. In a graph, it's a property on the relationship, which is exactly where the fact belongs.

Index-free Adjacency, the Idea That Makes it Fast

This is the one piece of theory worth understanding properly, because everything else follows from it.

In a relational database, a relationship between two rows is a value you match at query time. The orders table has a customer_id, and when you join, the database looks up matching values. It's good at this. There are indexes and query planners and decades of optimisation behind it. But it's still, fundamentally, a search.

In a graph database, a relationship is a record stored on disk that points directly at both of its nodes. When the database walks from a node to its neighbour, it doesn't search for the neighbour. It follows a pointer.

The name for this is index-free adjacency.

This is where the connection physically lives. Relationally it's a value, a foreign key the database has to find. In a graph it's a pointer beside the node, so following it is a read rather than a search.

The consequence is the thing that matters. Because traversal follows pointers out of nodes you already have in hand, the cost of a traversal is proportional to the size of the part of the graph you touch, not the size of the graph in total. A database ten times larger doesn't make a two-hop query slower.

Compare that with a join. Each additional join reads another table and builds a wider intermediate result. Adding a hop adds work that scales with your data volume.

Two curves on the same axes: cost of one query against how much data the database holds. The four-join line climbs steeply as the data grows. The two-hop traversal line stays low and nearly flat. At the small end they sit almost on top of each other, which is the note the figure makes: on a laptop with test data both look fine, and that's why this surprises people in production.

One key caveat drawn on the figure itself: The axes carry no units, because none were measured, and no benchmark is being claimed. The point is the shape of the two curves, which follows from how each one works.

This is why the difference shows up as your data grows rather than on your laptop with test data. Both approaches look fine on ten thousand rows.

A relational database is excellent at answering questions about sets of rows. A graph database is excellent at answering questions about paths between things. Most systems have both kinds of question, which is why most companies end up running both kinds of database.

When a Graph is the Wrong Choice

Every graph tutorial on the internet tells you graphs are wonderful. Here's the other half, because knowing when not to use something is what separates an engineer from an enthusiast.

Use something else when your queries are aggregations over big uniform sets. "Total revenue by region by month" is a relational or columnar question. A graph will answer it, and it will be slower and more awkward than a warehouse would be.

Use something else when your data has no meaningful relationships. A table of log lines is a table of log lines. Modeling each one as a node connected to nothing buys you nothing and costs you storage.

Use something else when you need one thing to be extremely fast and nothing else. A key-value store answering "give me session 4471" will beat everything, because it does exactly one thing.

A graph is the right choice when the connections are the point. Fraud rings, recommendations, access control, dependency analysis, lineage, org structures, supply chains, and knowledge graphs for AI systems. These share one trait: the interesting questions are about how things connect, and the number of hops isn't fixed in advance.

If your query never goes more than one hop, you probably don't need a graph. If your query goes three hops and the number of hops depends on the data, you almost certainly do.

How to Set Up Neo4j and the Python Driver

For this project, you need a database and a driver.

Option A: Neo4j Aura, No Installation

The fastest route is Neo4j Aura, Neo4j's managed cloud service. There's nothing to install, and there's a genuinely free tier.

Go to console.neo4j.io, sign in, and choose Create instance. You'll be shown several tiers side by side, and this is the screen to read carefully rather than click through:

Pick Free for this handbook. 200,000 nodes is far more than anything here needs.

Watch the running total at the bottom of that page. The console shows a live hourly rate and a projected monthly cost, and both update as you change tiers.

A paid tier can read as roughly $0.36 per hour. That is about $259 a month if you leave it running. It's very easy to click past that while concentrating on the instance name. If you only want to learn, the number at the bottom should say $0.

Once you confirm, Aura shows you a credentials dialog exactly once:

• Username, which is always neo4j

• A long generated password

• A warning that reads "Note that the password will not be available after this point"

That warning is literal. Click Download and continue to save a .txt file with the connection details, or copy the password somewhere safe first. If you lose it, you can't retrieve it, you can only reset it.

The downloaded file looks like this:

NEO4J_URI=neo4j+s://xxxxxxxx.databases.neo4j.io
NEO4J_USERNAME=neo4j
NEO4J_PASSWORD=<your generated password>
NEO4J_DATABASE=neo4j
AURA_INSTANCEID=xxxxxxxx
AURA_INSTANCENAME=demo

The instance then shows Creating... in the console and takes a few minutes. During that window the hostname already resolves in DNS and port 7687 already accepts TCP connections, but the database behind it isn't up yet, so a driver will fail with Unable to retrieve routing information. That error during the first few minutes means "not ready", not "misconfigured". Wait and retry rather than changing your connection string.

The +s in neo4j+s:// means the connection is encrypted and the server's certificate is verified. Aura requires encryption, and that verification is the only difference from a local instance that matters for this handbook.

If Aura Refuses to Connect and You're Sure it's Running

Aura is healthy, the browser connects, Python won't. Something on the network, usually a corporate proxy, VPN or antivirus, terminates your TLS connection, reads it, and re-encrypts it with its own certificate. Your browser was told to trust that certificate. The driver wasn't, so it correctly refuses and you get ServiceUnavailable: Unable to retrieve routing information while the database was fine throughout.

There's one failure here that wastes people hours, because the error message points at the wrong thing.

You connect, and the driver says:

neo4j.exceptions.ServiceUnavailable: Unable to retrieve routing information

"Routing" sounds like a cluster problem, so people go and check the instance, recreate it, and try a different region. Often none of that is the cause.

Check the certificate directly:

import socket, ssl
ctx = ssl.create_default_context()
with socket.create_connection(("xxxxxxxx.databases.neo4j.io", 7687), timeout=15) as raw:
    with ctx.wrap_socket(raw, server_hostname="xxxxxxxx.databases.neo4j.io") as s:
        print("TLS OK", s.version())

If that prints something like CERTIFICATE_VERIFY_FAILED: self-signed certificate in certificate chain, the database is fine. Something on your network is intercepting TLS. Corporate proxies, some VPNs, and several antivirus products do this: they terminate your encrypted connection, inspect it, and re-encrypt it with their own certificate. Your browser trusts that certificate because the software installed its root into the system store. Python does not, because it ships its own trust store.

You have three options, in order of preference.

1. Add the interceptor's root certificate to Python's trust store, which is the correct fix and keeps verification on:

export SSL_CERT_FILE=/path/to/corporate-root.pem

2. Use a network that's not intercepted, such as a mobile hotspot, which is the quickest way to confirm the diagnosis.

3. Fall back to neo4j+ssc://, which encrypts but accepts a self-signed certificate:

driver = GraphDatabase.driver("neo4j+ssc://xxxxxxxx.databases.neo4j.io", auth=AUTH)

The ssc stands for self-signed certificate. Your traffic is still encrypted, but the driver no longer checks who's on the other end, so anyone already intercepting can keep doing it undetected. Use it to unblock yourself while learning, and don't ship it to production.

Every Aura query in this handbook was verified over exactly this route, on a network that turned out to be running TLS inspection.

Option B: Docker, One Command

If you would rather keep everything on your machine, Docker is the shortest path. Everything in this handbook was written and tested against exactly this container.

docker run -d --name neo4j-graphbook \
  -p 7474:7474 -p 7687:7687 \
  -v neo4jdata:/data \
  neo4j:5

Port 7474 serves Neo4j Browser, the query UI you'll use in a moment. Port 7687 is Bolt, the binary protocol the Python driver speaks.

Set the initial password on the volume before the database starts for the first time, because the setting is ignored once a database exists:

docker volume create neo4jdata
docker run --rm -v neo4jdata:/data neo4j:5 \
  neo4j-admin dbms set-initial-password yourpassword

Then open http://localhost:7474 and sign in with neo4j and that password.

We have two panels here.

1. What you believe: your script dials bolt://localhost:7687 and reaches the Docker container running neo4j:5 with your data.

2. What's happening: a native Neo4j, usually Neo4j Desktop, is already listening on 127.0.0.1:7687, so it shadows the Docker port mapping and your container is never reached at all. Your script authenticates against that other database, and the driver reports an authentication failure. Nothing in that message mentions ports.

Find out who holds it with lsof -nP -iTCP:7687 -sTCP:LISTEN. If something else owns it, move your container with docker run -p 7475:7474 -p 7688:7687 neo4j:5 and connect on 7688 instead.

A trap worth knowing about: if you already run Neo4j Desktop, or any other Neo4j, it's probably already listening on 7687. A native process holding that port takes precedence over a Docker port mapping, and the symptom is confusing: the container starts fine, Browser loads, and your driver reports an authentication failure, because it's quietly talking to the other database.

If that happens, map the container somewhere else with -p 7475:7474 -p 7688:7687 and point your driver at bolt://localhost:7688. Check what holds the port with lsof -nP -iTCP:7687 -sTCP:LISTEN.

Option C: a Cloud Server You Control

There is a third option worth walking through, because it's closer to how you would actually run this for a team, and because it teaches you what the other two hide. You put Neo4j on a small Linux server in the cloud.

Everything below is exactly what I ran to produce the screenshots in this handbook. It uses AWS, but the shape is identical on any provider.

Step 1. Find out which account you're about to spend money in.

This sounds obvious and it's the step people skip.

aws sts get-caller-identity
aws configure get region

The first prints the account number and the user. The second prints the region. If either isn't what you expected, stop and fix your profile before creating anything.

Step 2. Find the current Linux image.

Instead of hardcoding an image ID from a blog post, ask AWS for the latest one:

aws ssm get-parameters \
  --names /aws/service/ami-amazon-linux-latest/al2023-ami-kernel-default-x86_64 \
  --query 'Parameters[0].Value' --output text

An AMI is a machine image, the template your server boots from. Image IDs differ per region and change over time, which is why you look it up rather than copy it.

Step 3. Create a firewall that only lets you in.

This is the step that matters most, and it's the one that gets people breached.

MYIP=$(curl -s https://checkip.amazonaws.com)/32

SG=$(aws ec2 create-security-group \
  --group-name neo4j-demo-sg \
  --description "Neo4j demo, locked to my IP" \
  --vpc-id <your-default-vpc-id> \
  --query GroupId --output text)

for port in 22 7474 7687; do
  aws ec2 authorize-security-group-ingress \
    --group-id $SG --protocol tcp --port $port --cidr $MYIP
done

A security group is a firewall attached to the server. Port 22 is SSH, 7474 is Neo4j Browser, 7687 is Bolt. The --cidr $MYIP part restricts every one of them to your own address.

Don't replace that with 0.0.0.0/0. That means "the entire internet". Databases left open on default ports are found by automated scanners within hours, not weeks, and an open Neo4j is a full read and write handle on your data.

Step 4. Boot the server and install Neo4j automatically.

A user-data script is a shell script the server runs once, on first boot, as root.

#!/bin/bash
dnf install -y docker
systemctl enable --now docker

# ask the instance what its own public address is
TOKEN=$(curl -sX PUT "http://169.254.169.254/latest/api/token" \
  -H "X-aws-ec2-metadata-token-ttl-seconds: 300")
PUBIP=$(curl -s -H "X-aws-ec2-metadata-token: $TOKEN" \
  http://169.254.169.254/latest/meta-data/public-ipv4)

docker run -d --name neo4j --restart unless-stopped \
  -p 7474:7474 -p 7687:7687 \
  -e NEO4J_AUTH=neo4j/ChangeThisPassword \
  -e NEO4J_server_default__listen__address=0.0.0.0 \
  -e NEO4J_server_bolt_advertised__address=$PUBIP:7687 \
  -e NEO4J_server_http_advertised__address=$PUBIP:7474 \
  neo4j:5

Three details in there are the whole reason this section exists.

169.254.169.254 is the instance metadata service, a special address every AWS server can reach to ask questions about itself. Here it is asking for its own public IP.

NEO4J_server_default__listen__address=0.0.0.0 tells Neo4j to accept connections from outside the machine. By default it listens only on localhost, and without this your server would be running perfectly and refusing every connection.

The advertised address settings are the subtle one. Neo4j Browser is a web page served by the server, and when it opens a Bolt connection it uses the address the server advertises. If the server advertises localhost, the Browser running in your laptop's browser will try to connect to your laptop. Setting the advertised address to the public IP is what makes a remote Browser work at all.

Note the double underscores. In Neo4j's environment variables, a dot in a config key becomes an underscore and a real underscore becomes a double underscore, so server.default_listen_address becomes NEO4J_server_default__listen__address.

Step 5. Launch it.

aws ec2 run-instances \
  --image-id <ami-from-step-2> \
  --instance-type t3.medium \
  --key-name <your-key-pair> \
  --security-group-ids $SG \
  --associate-public-ip-address \
  --user-data file://userdata.sh \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=neo4j-demo}]'

t3.medium gives 2 CPUs and 4GB of memory, which is comfortable for learning. Neo4j will start on 1GB but you'll fight it.

Boot, package install, and image pull took about 90 seconds. Poll until the Browser answers rather than guessing:

until curl -s -o /dev/null -w "%{http_code}" http://<public-ip>:7474 | grep -q 200; do
  sleep 10
done

Step 6. Delete it when you're finished.

A server you forgot about bills every hour, forever.

aws ec2 terminate-instances --instance-ids <instance-id>
aws ec2 delete-security-group --group-id $SG

I can't stress this enough for anyone learning on their own account: set a billing alarm, and terminate the moment you're done. The instance used for this handbook existed for under an hour and cost a few cents, but only because I deleted it after.

The Driver

pip install neo4j

That installs the official driver. At the time of writing it's version 6.x and supports Python 3.10 and above.

Connecting

The driver object is expensive to create and cheap to reuse. Create one when your program starts, and keep it. Creating a driver per request is a common and costly mistake, because each one builds its own connection pool.

from neo4j import GraphDatabase

URI = "neo4j+s://xxxxxxxx.databases.neo4j.io"
AUTH = ("neo4j", "your-password")

with GraphDatabase.driver(URI, auth=AUTH) as driver:
    driver.verify_connectivity()
    print("Connected")

There are two things worth doing every time:

verify_connectivity() fails immediately with a clear error if the URI or the password is wrong. Without it, your first failure happens inside a query, where the error is less obvious and harder to attribute.

Using the driver as a context manager, with with, closes it cleanly when the block exits. In a long-running service you would instead create the driver at startup and close it during shutdown.

Never put credentials in your source. Read them from the environment:

import os
from neo4j import GraphDatabase

driver = GraphDatabase.driver(
    os.environ["NEO4J_URI"],
    auth=(os.environ["NEO4J_USER"], os.environ["NEO4J_PASSWORD"]),
)

The Modeling Decision That Matters Most

Before you write a single row of data you have to decide what becomes a node, what becomes a property, and what becomes a relationship.

This is the part that decides whether your graph is a pleasure or a problem six months from now. It's also the part that no query optimiser can fix for you later.

Here are the rules:

Make it a node if you'll ever ask a question about it. If you want to know which engineers work on the payments service, then the payments service is a node. If you want to count incidents by severity, severity is a candidate for a node.

Make it a property if it only ever describes something else. The timestamp on an incident is a property. Nobody asks a database to find all the things that happened at 14:32 and then traverse outwards from that moment.

Make it a relationship if it connects two nodes and you want to walk it. Ownership connects an engineer to a service, and the entire point is walking from one to the other, so it's a relationship.

A useful test: can you imagine drawing an arrow to it? If yes, it's probably a node. Nobody draws an arrow to a timestamp.

Another useful test: would you ever want to attach something else to it? Teams have managers, budgets, and charters. That's three arrows waiting to happen, which means a team is a node, not a string.

Relationship Direction

Every relationship in Neo4j has a direction. You store (:Engineer)-[:OWNS]->(:Service) because an engineer owns a service and not the other way round.

Direction matters when you write the data. It matters much less when you query, because you can traverse against the stored direction, and you can ignore direction entirely.

// follow the stored direction
MATCH (e:Engineer)-[:OWNS]->(s:Service) RETURN e, s

// traverse against it: start from the service
MATCH (s:Service)<-[:OWNS]-(e:Engineer) RETURN s, e

// ignore direction entirely
MATCH (e:Engineer)-[:OWNS]-(s:Service) RETURN e, s

Those three return the same pairs. Store the direction that reads naturally as an English sentence, and stop worrying about it.

Three patterns matching identical data: walking the stored direction, walking against it, and dropping the arrowhead to ignore direction. All three return Ada and payments.

That third one is the debugging move. If a query returns nothing and you expected rows, drop the arrowheads. If rows appear, direction was the cause. If not, you've ruled out the likeliest suspect in ten seconds. Direction does matter when you write: MERGE (a)-[:OWNS]->(b) and the reverse create two different facts, and only one is true.

Properties on Relationships

This is the feature people forget exists, and it's often the cleanest answer.

One fact, stored two ways. In tables, since lives on an ownership join table that isn't part of your domain and exists only because rows can't point at each other. In a graph it sits on the connection, and you can query it directly: MATCH (e:Engineer)-[r:OWNS]->(s:Service) WHERE r.since < date() - duration('P1Y') gives you everyone who has owned something for more than a year.

MERGE (e:Engineer {email: 'ada@example.com'})-[r:OWNS]->(s:Service {name: 'payments'})
  SET r.since = date('2026-03-01'), r.primary = true

Now you can ask who has owned a service for longer than a year, without inventing a join table to hold the fact.

Three Modeling Mistakes Almost Everyone Makes

I've watched these three mistakes happen more times than any others, and each one is easy to avoid once you've seen it.

Almost every first graph model makes this one: storing a connection as a property because it looks simpler. It can't be traversed, can't carry facts of its own, and turns into string matching.

Mistake #1: Storing a Connection as a Property

You give each engineer a team property holding the string "platform".

This works right up until you want to know what else the platform team owns. Now you're matching strings scattered across thousands of nodes. Worse, the moment someone writes "Platform" with a capital P, you've silently created a second team, and no error was raised.

The fix is to make the team a node and connect engineers to it. Both problems disappear at once, and you gain somewhere to hang the team's manager and budget later.

The general form of this mistake: anything you want to traverse must be a relationship. A property holding a list of identifiers is a graph database pretending to be a spreadsheet.

Mistake #2: One Generic Relationship Type for Everything

You create a RELATED_TO relationship and put a type property on it to say what kind of relation it is.

This looks flexible. It's the opposite. Neo4j narrows the search by relationship type before it walks anything, so -[:OWNS]-> is fast. Filtering on a property means walking every RELATED_TO relationship first, then discarding most of them, which is exactly the row-scanning behaviour you moved to a graph to avoid.

Name your relationships for what they mean: OWNS, AFFECTS, MEMBER_OF, or DEPENDS_ON. Specific types are both faster and self documenting.

Mistake #3: Making Everything a Node

This is the overcorrection, and it's its own problem.

If a value only ever describes one node, and you never search for it independently, it's a property. Creating a node for every timestamp gives you a much larger graph, slower traversals, and nothing whatsoever in return.

The test remains the same. Will you ask a question about it, or attach something to it? If not, it's a property.

Modeling Backwards From Your Questions

Here's a technique that will save you a rewrite.

Don't start by modeling your domain. Start by writing down the questions the graph has to answer, in plain English, before you draw anything.

For our example:

1. Which services did this incident affect?

2. Who owns those services?

3. Which teams do those owners belong to?

4. Which services depend on the one that broke?

5. Who has been on call for this service in the last month?

Now check your model against the list. Every question should be a path you can trace with your finger. If a question requires a join across two properties, or a scan of every node of some label, the model is wrong for that question.

Question five is a good example of why this matters. "On call in the last month" is a fact about a period of time connecting a person and a service. That's a relationship with properties on it, and if you had modeled on-call as a boolean property on the engineer, you would've discovered the problem after loading your data instead of before.

Relational modeling teaches you to normalise first and query later. Graph modeling works better in the other direction.

Three Modeling Patterns Worth Knowing Early

Once the basics land, three patterns cover most of what you'll hit in real data.

When a Relationship Needs More Than Two Ends

A relationship connects exactly two nodes. Sometimes a fact connects three or more.

"Ada was on call for payments during March" involves a person, a service, and a time window. You can't hang that off a single relationship without losing something.

The pattern is to promote the fact itself to a node:

MERGE (e:Engineer {email: 'ada@example.com'})
MERGE (s:Service {name: 'payments'})
CREATE (r:OnCallRotation {start: date('2026-03-01'), end: date('2026-03-31')})
MERGE (e)-[:SERVED]->(r)
MERGE (r)-[:FOR_SERVICE]->(s)

"Ada was on call for payments during March" has three participants and a relationship has two ends. Forced onto one ON_CALL, it breaks in April, because a second rotation needs a second relationship between the same nodes and nothing can hang off either. Promote the fact to a node and it gets three relationships, so anything can attach. The signal is wanting to put a property on a relationship that describes something other than that exact pair.

OnCallRotation is sometimes called an intermediate node, a reified relationship, or a hyper-edge. The name doesn't matter. What matters is that a fact with three participants becomes a node with three relationships, and now you can attach more to it later, such as who swapped in halfway through.

The signal that you need this: you find yourself wanting to put a property on a relationship that describes something other than that exact pair of nodes.

Versioning, When Facts Change Over Time

Graphs are easy to update in place, which makes it tempting to overwrite. If history matters, don't.

Ownership changes hands, and pointing the relationship at the new person erases that anyone else ever held it. The alternative closes the old relationship with an end date and opens a new one, so history survives. Overwriting is what happens if you don't decide.

The usual pattern is to keep the relationship and mark it closed rather than deleting it:

// close the old ownership rather than deleting it
MATCH (e:Engineer {email: $old})-[r:OWNS]->(s:Service {name: $service})
WHERE r.until IS NULL
SET r.until = date()

// open a new one
MATCH (e:Engineer {email: $new}), (s:Service {name: $service})
MERGE (e)-[r2:OWNS]->(s)
  ON CREATE SET r2.since = date()

Current ownership is then WHERE r.until IS NULL, and history is still there when someone asks who owned this last year. The cost is that every query about "now" needs that filter, so decide deliberately rather than by accident.

Hierarchies, Which Graphs Are Unusually Good At

Trees are painful in SQL and trivial here. An organisation, a category tree, a folder structure, and a dependency chain are all the same shape.

// everyone under a given manager, at any depth
MATCH path = (m:Engineer {email: $email})<-[:REPORTS_TO*1..10]-(report:Engineer)
RETURN report.name AS name, length(path) AS depth
ORDER BY depth, name

Naming the path with path = is what lets you call length() on it, which returns the number of relationships traversed and therefore how far down the tree each person sits.

This is the query that makes people switch. In SQL it's a recursive common table expression that most engineers have to look up every time. Here it's one line, and changing the depth is changing a number.

Loading Data From Python

The modern driver gives you one method for running a query: execute_query. It manages sessions and retries for you, and it's the right default.

Start with a single engineer and a single service.

driver.execute_query(
    """
    MERGE (e:Engineer {email: $email})
      SET e.name = $name
    MERGE (s:Service {name: $service})
    MERGE (e)-[:OWNS]->(s)
    """,
    email="ada@example.com",
    name="Ada",
    service="payments",
    database_="neo4j",
)

Three things in that snippet deserve attention.

MERGE Rather Than CREATE

CREATE always makes a new node. Run your loading script twice and you have two identical engineers, two identical services, and a mess.

MERGE looks for a node matching the pattern and creates one only if nothing matches. That makes the script safe to run again, which you'll want the very first time it fails halfway through a load.

The rule of thumb: CREATE when you know the thing is new, MERGE when you're loading from a source that might contain something you already have.

Merge on Identity, Then Set Everything Else

Look carefully at where the properties are.

MERGE (e:Engineer {email: $email})
  SET e.name = $name

The MERGE is on email alone, and the name is applied afterwards with SET.

If you had merged on both email and name, then the day someone changes their name you would create a second node rather than updating the first. You would end up with two Adas, connected to different things, and no error to tell you.

Merge on the property that identifies the node. Set the rest.

Two scripts that both run without error and both report success. The left merges on email and name together. The right merges on email alone and sets the name afterwards.

Load them once and they look identical. Then Ada marries and changes her name to Ada Okonjo, same email. On the left the pattern no longer matches, because the name differs, so MERGE creates a second node. Her ownerships are now split across both, and every query about her returns part of the truth.

On the right the email still matched, so MERGE found the existing node and SET overwrote the name, and her relationships stay attached to the node they were always on.

The rule: merge on the property that identifies the node and nothing else, and set everything that merely describes it. If a value can change while the thing stays the same thing, it doesn't belong in the key. You can catch this whole class of bug by loading your data twice and asserting the node count is identical, which costs three lines.

There's a matching variant when you want different behaviour on first insert versus update:

MERGE (e:Engineer {email: $email})
  ON CREATE SET e.name = $name, e.created = datetime()
  ON MATCH  SET e.name = $name, e.last_seen = datetime()

Parameters, Never String Formatting

The values are passed separately as $email and $name. Never build a query by concatenating strings.

This protects you from injection, which is the obvious reason. There's a second reason that matters for performance: Neo4j caches query plans keyed on the query text. Parameterised queries have identical text every time, so the plan is compiled once and reused. String-formatted queries produce a new plan for every distinct value, which fills the plan cache with garbage and recompiles constantly.

Loading at Scale with UNWIND

One node at a time means one network round trip per node. Loading ten thousand records that way is slow, and almost all of the time is spent waiting rather than working.

Send a list instead and let Cypher loop inside the database.

rows = [
    {"email": "ada@example.com",   "name": "Ada",   "service": "payments"},
    {"email": "linus@example.com", "name": "Linus", "service": "checkout"},
    {"email": "grace@example.com", "name": "Grace", "service": "payments"},
]

driver.execute_query(
    """
    UNWIND $rows AS row
    MERGE (e:Engineer {email: row.email})
      SET e.name = row.name
    MERGE (s:Service {name: row.service})
    MERGE (e)-[:OWNS]->(s)
    """,
    rows=rows,
    database_="neo4j",
)

UNWIND takes a list and turns it into rows, so everything after it runs once per element, all inside a single transaction and a single round trip.

What makes a bulk load slow isn't the writing, it's the waiting between writes. One statement per row is a network round trip per row. One UNWIND sends the batch in a single trip and lets the database loop internally.

This is not a small optimisation. Writing 1,000 rows to the 75,500 node dataset, one statement per row against a single UNWIND:

Forty-three times faster, on a database running on the same machine as the client, where a round trip costs almost nothing. Run it yourself and you'll get a different multiple, somewhere in the same region: a clean checkout on this machine measured sixty-six.

The gap grows with distance. I ran the same comparison against a managed instance in another city and measured 91,722 ms against 150 ms, which is 613 times. Nothing about the work changed. What changed is that each of the 1,000 round trips now pays for a journey across the country and back. A minute and a half became a seventh of a second.

That's the real lesson: the cost of chattiness isn't fixed. It is however far away your database happens to be, multiplied by how many times you talk to it.

For a real load, batch it. One enormous transaction holds every change in memory until it commits, and a transaction containing a million updates is a good way to exhaust the heap.

def load_in_batches(driver, rows, batch_size=5000):
    query = """
    UNWIND $rows AS row
    MERGE (e:Engineer {email: row.email})
      SET e.name = row.name
    MERGE (s:Service {name: row.service})
    MERGE (e)-[:OWNS]->(s)
    """
    for start in range(0, len(rows), batch_size):
        batch = rows[start:start + batch_size]
        driver.execute_query(query, rows=batch, database_="neo4j")
        print(f"loaded {start + len(batch)} of {len(rows)}")

A few thousand rows per batch is a reasonable starting point. Tune it by watching memory rather than by guessing.

Loading From a CSV File

Most real data starts life in a spreadsheet or an export. There are two ways to get it in, and picking the wrong one is a common source of frustration.

Option #1: Read it in Python, Send it with UNWIND

This is the one to reach for by default. You already know how it works, it runs anywhere, and you can clean the data on the way through.

import csv

def load_csv(driver, path, batch_size=5000):
    with open(path, newline="", encoding="utf-8") as f:
        rows = list(csv.DictReader(f))

    query = """
    UNWIND $rows AS row
    MERGE (e:Engineer {email: row.email})
      SET e.name = row.name
    MERGE (s:Service {name: row.service})
    MERGE (e)-[:OWNS]->(s)
    """
    for start in range(0, len(rows), batch_size):
        driver.execute_query(query, rows=rows[start:start + batch_size], database_="neo4j")

csv.DictReader gives you a dictionary per row keyed by the header names, which is exactly the shape UNWIND wants.

One warning that catches everyone: every value from a CSV is a string. A column of numbers arrives as "42", not 42, and a column of dates arrives as "2026-03-01". If you store them raw you'll later write comparisons that silently do the wrong thing, because "9" > "10" is true when both are strings. Convert as you read:

for row in rows:
    row["headcount"] = int(row["headcount"]) if row["headcount"] else None

Option #3: LOAD CSV, Which Runs Inside the Database

Cypher can read a file itself. This is faster for very large files because the data never travels through your Python process.

LOAD CSV WITH HEADERS FROM 'file:///engineers.csv' AS row
CALL {
  WITH row
  MERGE (e:Engineer {email: row.email})
    SET e.name = row.name
  MERGE (s:Service {name: row.service})
  MERGE (e)-[:OWNS]->(s)
} IN TRANSACTIONS OF 1000 ROWS

CALL { ... } IN TRANSACTIONS OF 1000 ROWS is the important part. Without it the whole file is one transaction, which is how people run a large import and watch it exhaust memory.

There are two constraints on LOAD CSV that surprise people:

First, the file has to be somewhere the database can reach, not somewhere you can reach. file:/// means the import directory on the server. On Docker that means mounting a folder into the container with -v $(pwd)/data:/var/lib/neo4j/import. On Aura you can't use local files at all, so the URL must be a publicly reachable https:// address.

Every CSV value arrives as a string, including numbers. Nothing errors and no warning appears, so "9" > "10" is true and your filter quietly returns the wrong rows. Cast on the way in.

Second, everything is still a string. Cypher has conversion functions for this:

LOAD CSV WITH HEADERS FROM 'https://example.com/services.csv' AS row
MERGE (s:Service {name: row.name})
  SET s.headcount = toInteger(row.headcount),
      s.launched  = date(row.launched)

toInteger, toFloat, date and datetime are the ones you'll use constantly. toInteger returns null rather than throwing on a value it can't parse, which is convenient and also means a column full of typos will quietly become a column full of nulls. Check your data after loading:

MATCH (s:Service) WHERE s.headcount IS NULL RETURN count(*) AS unparsed

Updating and Deleting

Loading is only half of it. Data changes, and the commands that change it have sharp edges.

Changing Properties

SET adds or overwrites a property. REMOVE takes one away entirely, which is different from setting it to null.

MATCH (e:Engineer {email: $email})
SET e.name = $name, e.updated = datetime()
REMOVE e.legacy_id

There's a shorthand that overwrites several properties at once from a map:

MATCH (e:Engineer {email: $email})
SET e += $props

+= merges the map into the node, leaving properties you didn't mention alone. Plain = replaces the entire property set, silently deleting anything not in your map. That difference has cost people real data, so it's worth reading twice.

Deleting

You can't delete a node that still has relationships. Neo4j refuses, because leaving a dangling relationship would corrupt the graph.

// fails if the engineer owns anything
MATCH (e:Engineer {email: $email}) DELETE e

DETACH DELETE removes the relationships and then the node:

MATCH (e:Engineer {email: $email}) DETACH DELETE e

It's handy, and dangerous for exactly the same reason. Run the MATCH on its own with RETURN first and look at what comes back, every time.

To wipe a whole database while experimenting:

MATCH (n) DETACH DELETE n

That's fine on a few thousand nodes and a bad idea on millions, because it builds one enormous transaction. For a large reset, drop the database or delete in batches with CALL { ... } IN TRANSACTIONS.

Working with Neo4j Data Types

Neo4j stores more than strings and numbers, and using the right type saves you from parsing dates out of text later.

A property can't hold a map or a node. If you find yourself wanting nested structure inside a property, that nested thing is usually asking to be a node.

Temporal types are the ones that earn their keep immediately:

MATCH (e:Engineer)-[r:OWNS]->(s:Service)
WHERE r.since < date() - duration('P1Y')
RETURN e.name, s.name, duration.between(r.since, date()).years AS years

Comparing dates as dates, rather than as strings you hope sort correctly, removes a whole category of bug.

On the Python side the driver converts these for you. date and datetime come back as neo4j.time objects, which have .to_native() if you want Python's own datetime:

records, _, _ = driver.execute_query(
    "MATCH (e:Engineer)-[r:OWNS]->(s:Service) WHERE r.since IS NOT NULL RETURN r.since AS since",
    database_="neo4j",
)
for r in records:
    print(r["since"], "->", r["since"].to_native())

Your First Cypher Queries

Cypher looks a little like SQL in places, but its central idea is different. You draw the shape you're looking for, and the database finds every part of the graph matching that shape.

Patterns use parentheses for nodes and arrows for relationships:

(e:Engineer)-[:OWNS]->(s:Service)

Read it aloud: an engineer node, an OWNS relationship pointing out of it, and a service node at the other end. The pattern is the query.

Five conventions on (e:Engineer)-[:OWNS]->(s:Service). Round brackets are a node. e is an optional variable, named only if you want it back. :Engineer is a label, narrowing to that kind first. Square brackets and an arrow are a relationship and its stored direction. :OWNS is the type, and Neo4j narrows by type first, which is why specific types are fast.

Said aloud: "an engineer, who owns a service." The SQL equivalent says how to reconstruct the connection. The Cypher says what the connection is.

Finding Things

records, summary, keys = driver.execute_query(
    """
    MATCH (e:Engineer)-[:OWNS]->(s:Service {name: $service})
    RETURN e.name AS name, e.email AS email
    ORDER BY name
    """,
    service="payments",
    database_="neo4j",
)

for record in records:
    print(record["name"], record["email"])

execute_query returns three things: the records, a summary, and the keys that were returned.

Most of the time you want the records, which is why you'll often see the other two discarded with underscores.

Neo4j Browser running the multi-hop query, with the results as a table. It's the same query you wrote above, with the parameter filled in by hand. That's what you do when you're exploring in the browser rather than calling from Python.

The query starts at incident INC-4471, follows AFFECTS out to the services it touched, then follows OWNS backwards to the engineers who own them. The rows that come back are those engineers' names and email addresses, sorted by name.

The same query, just run in Neo4j Browser. Two columns come back, name and email, one row per engineer.

Filtering

WHERE works much as you would expect.

MATCH (e:Engineer)-[r:OWNS]->(s:Service)
WHERE r.since < date('2026-01-01') AND s.tier = 'critical'
RETURN e.name, s.name, r.since

Note that you can filter on a property of the relationship, r.since, as easily as on a property of a node. That's the payoff for modeling the fact where it belongs.

Counting and Grouping

Cypher has no GROUP BY. Aggregation is implicit: anything you return that's not an aggregate becomes the grouping key.

MATCH (t:Team)<-[:MEMBER_OF]-(e:Engineer)-[:OWNS]->(s:Service)
RETURN t.name AS team, count(DISTINCT s) AS services
ORDER BY services DESC

That returns one row per team, because t.name is the only non-aggregate in the RETURN.

When Something Might Not Be There

MATCH drops rows that don't match the whole pattern. If you want engineers whether or not they own anything, use OPTIONAL MATCH, which is the closest equivalent to a left outer join.

MATCH (e:Engineer)
OPTIONAL MATCH (e)-[:OWNS]->(s:Service)
RETURN e.name AS name, collect(s.name) AS services

Engineers who own nothing come back with an empty list rather than vanishing from the result.

The Multi-Hop Query That Justifies the Whole Thing

Now let's return to the question from the very beginning.

An incident affected some services. Who has context on those services?

records, _, _ = driver.execute_query(
    """
    MATCH (i:Incident {ref: $ref})-[:AFFECTS]->(:Service)<-[:OWNS]-(e:Engineer)
    RETURN DISTINCT e.name AS name, e.email AS email
    """,
    ref="INC-4471",
    database_="neo4j",
)

One incident, two hops, and six nodes read. The work is the small pile standing on each step, not anything proportional to how much data the database holds.

Read the pattern from left to right and it's close to the English sentence.

Here's that query run against a live Neo4j Aura instance from the terminal:

Same query again, this time from cypher-shell against Aura instead of the browser, returning the identical three names: "Ada Okonjo", "Grace Lin" and "Linus Berg".

Start at the incident, follow AFFECTS to the services it hit, then follow OWNS backwards to the engineers who own them.

The arrow pointing left, <-[:OWNS]-, is doing real work. Ownership was stored from engineer to service, so reaching the engineers from the services means traversing against the stored direction.

Getting this backwards is the single most common reason a beginner's query returns nothing at all. If a query returns an empty result and you expected rows, check your arrow directions first.

This is the same result drawn as a graph instead of a table, in Neo4j Browser. The incident sits at one end, the services it affected in the middle, and the engineers who own those services at the other end. The path the query walked is visible as a shape rather than as rows.

Now widen it. Which whole teams are behind the affected services?

Here's the query most people write first. It's wrong, and it fails silently, which is why it's worth showing.

// WRONG: silently drops teams. Explanation below.
MATCH (i:Incident {ref: $ref})-[:AFFECTS]->(:Service)<-[:OWNS]-(:Engineer)
      -[:MEMBER_OF]->(t:Team)<-[:MEMBER_OF]-(e:Engineer)
RETURN DISTINCT t.name AS team, e.name AS name
ORDER BY team, name

Run that against the dataset in this handbook and it returns three rows, all from the Platform team. The Commerce team is missing, even though Linus owns checkout and checkout was affected.

Relationship Uniqueness, the Trap That Hides Answers

We have two versions side by side here. The single pattern looks correct and returns three rows. Split into two patterns joined by WITH, the same question returns four. The drawing traces why: the pattern has to walk out along a MEMBER_OF relationship and back along the same one, and Cypher discards that match rather than reusing the relationship.

Splitting the pattern lifts the restriction because the rule applies within one pattern, not across the query, and WITH DISTINCT keeps the extra rows from duplicating.

Cypher guarantees that a single pattern won't traverse the same relationship twice. This is called relationship isomorphism, and it exists to stop patterns looping back on themselves forever.

Look at what that means for Commerce. Its only member is Linus, and Linus is also the owner. To match, the pattern has to walk out of Linus along his MEMBER_OF relationship to reach the team, and then walk back down the very same relationship to reach a member. That's the same relationship twice, so Cypher discards the row.

There's no error or warning, just a quieter answer than the truth.

The fix is to break the single pattern into two, so the rule no longer spans both halves:

MATCH (i:Incident {ref: $ref})-[:AFFECTS]->(:Service)<-[:OWNS]-(:Engineer)-[:MEMBER_OF]->(t:Team)
WITH DISTINCT t
MATCH (t)<-[:MEMBER_OF]-(e:Engineer)
RETURN t.name AS team, e.name AS name
ORDER BY team, name

WITH ends one pattern and begins another. The second MATCH starts fresh, so the owner's own membership is available again.

That version returns four rows, including Commerce and Linus.

Does it Still Hold at Scale?

A fair objection to everything above is that fourteen nodes proves nothing. So here is the same multi-hop query, unchanged, against the 75,500 node dataset:

33 engineers returned, 150 database accesses, 4.6 ms

The graph is roughly five thousand times larger. The query is identical, and it still touches around a hundred and fifty things.

That's index-free adjacency doing exactly what was promised at the top of this article. The work is proportional to the neighbourhood you walk, not to the size of the database you walk it in. A join across three tables of that size would have to consider vastly more rows to answer the same question.

You can reproduce this yourself. The dataset is committed to the companion repository, and benchmark.py runs this measurement along with the others in this article.

The general lesson: whenever a pattern leaves a node and comes back to the same kind of node, ask whether the two halves could ever be the same relationship. If they could, split the query with WITH. This is the most common source of silently incomplete results in Cypher, and it's very hard to spot by reading, because the query looks correct and returns plausible data.

Four hops, still readable as a sentence. Writing the equivalent in SQL means several joins plus a distinct, and changing "two steps" to "three steps" means rewriting it.

Variable Length Paths and How to Keep Them Safe

Sometimes you don't know how many hops you need. Service dependencies are the classic case: payments depends on auth, auth depends on the user store, and you want everything downstream of a failure.

MATCH (s:Service {name: $name})<-[:DEPENDS_ON*1..4]-(affected:Service)
RETURN DISTINCT affected.name

The *1..4 means follow between one and four DEPENDS_ON relationships.

Always bound a variable length path. Each hop multiplies what the last one reached, so [:DEPENDS_ON*] has nothing to stop it while [:DEPENDS_ON*1..4] does. On a connected graph the unbounded version doesn't return slowly, it stops being a query you can wait for.

Always put an upper bound on it. An unbounded * on a well-connected graph can walk an enormous portion of the database, and the query that was instant on your test data will hang on production data. This is the single most common way people make a graph database look slow.

Here's what each extra pair of hops costs, starting from the most depended-upon service in the 75,500 node dataset, which has 10,039 DEPENDS_ON relationships between services:

Look at what happens between two hops and six. The reach grows more than fifteen fold, and the work grows twenty two fold. Nothing about the query changed except two characters.

That's the shape to keep in your head. Reach grows geometrically, and work grows with it. On a denser graph than this one the multiplier is larger, which is why an unbounded * on a social graph or a dependency graph can go from fast to hopeless with no warning at all, and why the failure arrives in production rather than on your laptop: your test data was not connected enough to hurt you.

I have deliberately not given you timings for these three. At this size they all complete in two to four milliseconds and the differences between them are measurement noise, not signal. The database access counts are the honest comparison, and unlike the timings, they'll be identical on your machine.

You can also ask for the shortest connection between two nodes, which is a genuinely hard query in SQL and a one liner here:

MATCH p = shortestPath(
  (a:Engineer {email: $from})-[:MEMBER_OF|OWNS*..6]-(b:Engineer {email: $to})
)
RETURN [n IN nodes(p) | coalesce(n.name, n.email)] AS hops

That returns the chain of things connecting two people. Recommendation engines, fraud detection, and access analysis are all variations on this one query.

What an Index Actually is

Before we use one, it's worth being clear about what an index is, because almost every performance problem in this article traces back to this one idea.

Think about a textbook of nine hundred pages. You want the part about photosynthesis. You have two options: you can start at page one and read forward until you find it, or you can turn to the index at the back, find "photosynthesis, 412", and go straight to page 412.

Both find the same page. One reads up to nine hundred pages, the other reads two.

A database index is that back-of-the-book index. It's a second, separate structure that the database maintains alongside your data, which maps a property value to the nodes that have it. You don't query the index directly and you don't have to tell Cypher to use it. You create it once, and from then on the planner uses it when it helps.

On the left, AllNodesScan: sixty pages read, one of them useful, and the other fifty-nine still read. On the right, NodeUniqueIndexSeek: two reads, the index entry and then the page.

The figure also carries the number this handbook measures later, on the 75,500 node dataset: 151,002 database accesses became 3. And the part worth remembering is that you never tell Cypher to use an index. You create it once, and from then on the planner reaches for it when it helps.

Here's the same lookup done three ways, against the 75,500 node dataset. All three find exactly one engineer, and all three return the same answer. What changes is how much work the database does to get there.

One: no label, no index.

PROFILE MATCH (n) WHERE n.email = 'eng25000@example.com' RETURN n.name

operator            details                       est     rows   dbHits
ProduceResults      `n.name`                     3775        1        0
  Projection        n.name AS `n.name`           3775        1        1
    Filter          n.email = $autostring_0      3775        1    75500
      AllNodesScan  n                           75500    75500    75501

AllNodesScan is the database reading every node it has. All 75,500 of them, including every service, team, and incident, none of which could possibly have an email. Then Filter checks the email property on every one. Total: 151,002 database accesses to find one node.

Two: with a label, still no index.

PROFILE MATCH (e:Engineer) WHERE e.email = 'eng25000@example.com' RETURN e.name

operator               details                    est     rows   dbHits
ProduceResults         `e.name`                  2500        1        0
  Projection           e.name AS `e.name`        2500        1        1
    Filter             e.email = $autostring_0   2500        1    50000
      NodeByLabelScan  e:Engineer               50000    50000    50001

NodeByLabelScan is better. It reads only the 50,000 engineers instead of all 75,500 nodes. But it still reads every single one. Total: 100,002 accesses. The label narrowed the haystack. It didn't stop us searching it straw by straw.

Three: with an index.

CREATE CONSTRAINT engineer_email IF NOT EXISTS
FOR (e:Engineer) REQUIRE e.email IS UNIQUE

PROFILE MATCH (e:Engineer) WHERE e.email = 'eng25000@example.com' RETURN e.name

operator                 details                                        est   rows   dbHits
ProduceResults           `e.name`                                         1      1        0
  Projection             e.name AS `e.name`                               1      1        1
    NodeUniqueIndexSeek  UNIQUE e:Engineer(email) WHERE email = $auto      1      1        2

The scan and the filter are both gone, replaced by a single NodeUniqueIndexSeek. Total: 3 database accesses.

Here we have one lookup done three ways, finding one engineer among 50,000 in a graph of 75,500 nodes, measured with PROFILE on Neo4j 5.26.29 Community. All three return the identical answer. What changes is the work: reading every node of the label, a scan narrowed by property, or an index seek straight to it.

Three, against a hundred and fifty-one thousand. That's the entire argument for indexes in one table:

On my machine, that was 35.4 ms without the index and 4.0 ms with it, so about nine times faster.

But be careful how you quote numbers like these. The database did 33,334 times less work, but it didn't run 33,334 times faster, because a single query also pays for connection handling, planning and returning the result, none of which the index changes. The work ratio is the durable claim. The speed ratio depends on your hardware, your cache, and what else the server is doing.

You won't get nine. When I ran this same benchmark again from a clean checkout, the same query on the same data measured seventeen times faster rather than nine. The database access counts were identical to the digit: 100,002 and 3, both times.

That contrast is the entire point. Database accesses are a property of your data and your query, so they reproduce exactly. Milliseconds are a property of the machine you happened to run on, so they do not. When you're comparing two ways of writing a query, compare the accesses.

The Index Types Neo4j Gives You

Most tutorials show you one kind of index and stop. Neo4j 5 has six, and picking the wrong one is the same as having none, because the planner will quietly ignore an index that can' t answer your predicate.

The one that catches people is the difference between range and text. A range index handles STARTS WITH perfectly well, because names sharing a prefix sit next to each other in sorted order, the same way "photosynthesis" and "photosphere" are neighbours in a book index. It cannot help with CONTAINS or ENDS WITH, because the thing you are searching for could be anywhere inside the value, and a sorted structure gives you no way to narrow that down. That's what a text index is for.

We have six index types and the question each answers. The wrong type is the same as no index, because the planner quietly ignores an index that can't answer your predicate and nothing tells you it happened.

If you write no type at all, you get a range index, which is the right default for the overwhelming majority of cases:

CREATE INDEX service_tier IF NOT EXISTS FOR (s:Service) ON (s.tier)

You can also index more than one property at once, which is called a composite index:

CREATE INDEX service_tier_name IF NOT EXISTS FOR (s:Service) ON (s.tier, s.name)

A composite index isn't the same as two separate indexes. It's one structure sorted by tier first and then by name inside each tier, like a phone book ordered by city and then surname. It's excellent when you filter on both, and useless if you filter only on the second one, because you can't look up a surname in a phone book that is grouped by city without going through every city.

A composite index covers a combination of properties, and their order decides which queries it serves. Filtering on the first property alone can use it. Filtering only on the second can't.

Relationships can be indexed too, using the same syntax with a relationship pattern:

CREATE INDEX owns_since IF NOT EXISTS FOR ()-[r:OWNS]-() ON (r.since)

To see what you have, ask:

SHOW INDEXES

Why Your Index Isn't Being Used

An index that exists but is never used is the most frustrating case, because everything looks correct. There are four usual reasons, and a PROFILE tells you which one you have.

1. You indexed a different property from the one you filter on. An index on email does nothing for a query filtering on name.

2. Your predicate can't use that index type. CONTAINS against a range index is the classic. The index exists, the planner looks at it, and correctly concludes it can't help.

3. You wrapped the property in a function. WHERE toLower(e.email) = 'x' can't use an index on e.email, because the index stores the original values, not the lowercased ones. Store a normalised copy of the property and index that instead.

4. You didn't give the node a label. Indexes are defined on a label. MATCH (n) WHERE n.email = ... has no label to work with, which is exactly why the first example above scanned every node in the database.

Constraints, and the Trap That Will Catch You

An index makes lookups fast. A constraint makes a rule impossible to break. They're different jobs, and the reason they get discussed together is that in Neo4j one of them quietly does the other.

Every MERGE has to check whether a matching node already exists. Without an index, that check scans every node carrying the label.

On a thousand nodes you won't notice. At a hundred thousand your import will crawl, and the reason won't be obvious because nothing is broken. It's simply doing an enormous amount of unnecessary work.

Create a uniqueness constraint on the property you merge on. It enforces correctness and creates the supporting index at the same time.

Here we have two runs of the same existence check, before and after a constraint. Without one, answering "does this engineer already exist" means reading every Engineer node and comparing the email, keeping one match and discarding the rest, then doing it all again for the next row. The plan shows NodeByLabelScan. With a uniqueness constraint the database creates a supporting index, so it goes straight to the node or straight to nothing and never looks at the others. The plan shows NodeUniqueIndexSeek.

At a thousand nodes you won't notice. At a hundred thousand the import crawls and nothing in the output explains why. The cost is the same either way, so there is no reason to skip it.

To check what yours is doing, put PROFILE in front of the query and look at the bottom operator. NodeByLabelScan on a starting node almost always means a missing index, and it's the single most common finding in a slow Cypher query.

You can prove the second half of that sentence rather than take my word for it:

SHOW INDEXES YIELD name, type, owningConstraint
WHERE owningConstraint IS NOT NULL
RETURN name, type, owningConstraint

name             type     owningConstraint
engineer_email   RANGE    engineer_email
incident_ref     RANGE    incident_ref
service_name     RANGE    service_name
team_name        RANGE    team_name

Four constraints, four range indexes created automatically, each owned by its constraint. This is why the loading script in this article never creates those indexes separately: doing so would be redundant, and Neo4j would reject it as a conflict.

Neo4j offers four kinds of constraint:

Here's the trap: only the first one works on Neo4j Community Edition, which is what you get from the Docker image in this article. The other three are Enterprise features. Aura runs Enterprise, so they work there.

That means the same script can succeed against Aura and fail against your local Docker container, which is a genuinely confusing thing to hit when you are learning. This is what it looks like:

Neo.DatabaseError.Schema.ConstraintCreationFailed
Unable to create Constraint( type='NODE PROPERTY EXISTENCE', schema=(:Engineer {name}) ):
Property existence constraint requires Neo4j Enterprise Edition

That's not your mistake. It's an edition limit, and the message says so if you read to the end of the line.

IS UNIQUE works on Community Edition, which is what the Docker image in this handbook gives you, and it also creates the backing index. The figure lists three others that Community refuses: IS NOT NULL for property existence, IS NODE KEY for unique-and-present across one or more properties, and a property type constraint such as requiring a STRING. All three need Enterprise.

Aura runs Enterprise, so the same script can succeed there and fail on your laptop. That isn't your mistake, and the refusal says so if you read to the end: Neo.DatabaseError.Schema.ConstraintCreationFailed, followed by the words Enterprise Edition.

Everything in this handbook uses only IS UNIQUE, so all of it runs on Community.

CREATE CONSTRAINT engineer_email IF NOT EXISTS
FOR (e:Engineer) REQUIRE e.email IS UNIQUE

Do this before you load, not after.

For properties you filter on frequently but which aren't unique, create a plain index:

CREATE INDEX service_tier IF NOT EXISTS
FOR (s:Service) ON (s.tier)

A sensible starting set for our model:

CREATE CONSTRAINT engineer_email IF NOT EXISTS FOR (e:Engineer) REQUIRE e.email IS UNIQUE;
CREATE CONSTRAINT service_name  IF NOT EXISTS FOR (s:Service)  REQUIRE s.name  IS UNIQUE;
CREATE CONSTRAINT incident_ref  IF NOT EXISTS FOR (i:Incident) REQUIRE i.ref   IS UNIQUE;
CREATE CONSTRAINT team_name     IF NOT EXISTS FOR (t:Team)     REQUIRE t.name  IS UNIQUE;

Run these from Python once at setup time:

CONSTRAINTS = [
    "CREATE CONSTRAINT engineer_email IF NOT EXISTS FOR (e:Engineer) REQUIRE e.email IS UNIQUE",
    "CREATE CONSTRAINT service_name  IF NOT EXISTS FOR (s:Service)  REQUIRE s.name  IS UNIQUE",
    "CREATE CONSTRAINT incident_ref  IF NOT EXISTS FOR (i:Incident) REQUIRE i.ref   IS UNIQUE",
    "CREATE CONSTRAINT team_name     IF NOT EXISTS FOR (t:Team)     REQUIRE t.name  IS UNIQUE",
]

for statement in CONSTRAINTS:
    driver.execute_query(statement, database_="neo4j")

IF NOT EXISTS makes that block safe to run on every startup.

What the Planner Does With Your Query

Cypher is a declarative language. You describe the shape of the answer you want, and you never say how to find it. That's a real convenience, and it has one consequence worth understanding: something has to decide how.

That something is the query planner.

When you send a query, Neo4j parses it, then considers the different ways it could be executed. For our multi-hop query it could start from the incident and walk out to the engineers, or start from all the engineers and walk in towards the incident. Both produce identical results. One touches a handful of nodes and the other touches fifty thousand.

The planner picks between them using statistics it keeps about your data: how many nodes carry each label, how many relationships of each type exist, and how many distinct values a given indexed property has. From those it estimates how many rows each possible step would produce, and chooses the plan with the lowest estimated cost. This is why it is called a cost-based planner, and why the header of every plan says Planner COST.

Cypher is declarative, so you never say how to find anything. Something still chooses, and that choice is where fast and slow are decided. A query plan is that decision, written down.

The important consequence for you: the planner is guessing. Educated guessing, from real statistics, but guessing. When its guess is badly wrong, you get a slow query, and the plan is where you can see that happening.

EXPLAIN and PROFILE

Two keywords let you see the plan, and the difference between them matters.

EXPLAIN plans the query without running it. You get the operators the planner chose and its row estimates. Nothing is executed, nothing is read, and no data is changed. It costs essentially nothing, so you can use it on a query you suspect might run for an hour.

PROFILE plans the query and then runs it. You get everything EXPLAIN gives you plus what actually happened: real row counts and real database hits per operator.

Here's the same query both ways.

EXPLAIN MATCH (e:Engineer)-[:OWNS]->(s:Service {tier:'critical'}) RETURN count(e) AS c

operator               details                       est   rows   dbHits
ProduceResults         c                               1      ?        ?
  EagerAggregation     count(e) AS c                   1      ?        ?
    Filter             e:Engineer                   2401      ?        ?
      Expand(All)      (s)<-[anon_0:OWNS]-(e)       2401      ?        ?
        Filter         s.tier = $autostring_0        250      ?        ?
          NodeByLabelScan  s:Service                5000      ?        ?

Every rows and dbHits value is a question mark, because nothing ran. Now with PROFILE:

operator               details                       est   rows   dbHits
ProduceResults         c                               1      1        0
  EagerAggregation     count(e) AS c                   1      1        0
    Filter             e:Engineer                   2401   7573     7573
      Expand(All)      (s)<-[anon_0:OWNS]-(e)       2401   7573    17871
        Filter         s.tier = $autostring_0        250    786     5000
          NodeByLabelScan  s:Service                5000   5000     5001

EXPLAIN plans it, PROFILE runs it. Operators and estimates are identical because the planner decided the same either way. What EXPLAIN can't give you is what actually happened, which is the number you need when the estimate was wrong.

Use EXPLAIN when you want to know what the database intends to do, or when running the query would be expensive or destructive. Use PROFILE when you want to know what it actually did.

EXPLAIN has a second use that's worth more than it sounds: it parses and plans without touching data, so it is the fastest possible check that a query is even valid. You can run every Cypher string in your codebase through EXPLAIN as a test, and catch typos and renamed properties before they reach production.

That's exactly what the check_cypher.py script in the companion repository does: it pulls every Cypher block out of this article, 39 of them, runs each through EXPLAIN, and fails if a single one is invalid.

Reading a Plan: Start at the Bottom

This is the single thing that makes plans readable, and it's the opposite of what most people assume.

A query plan is read from the bottom up. The bottom row is the leaf operator, where data enters. Each row above it receives rows from the row below, does something to them, and passes the result upward. The top row, always ProduceResults, is where the answer leaves the database.

So in the plan above, reading it the right way round:

1. NodeByLabelScan reads all 5,000 services. This is the leaf: it's where rows come from.

2. Filter keeps only the critical ones, 786 of the 5,000.

3. Expand(All) follows OWNS backwards from each of those to the engineers, producing 7,573 rows.

4. Filter checks that each is really an Engineer.

5. EagerAggregation counts them.

6. ProduceResults hands back the single number.

A plan is read from the bottom up. The bottom row is where rows enter, and each row above receives them, changes them and passes them on, up to ProduceResults. Reading it top down is why plans look like noise at first.

Indentation shows the parent and child relationship. An operator's children sit one level deeper than it does. Most operators have exactly one child. A few, like joins, have two, and their right-hand input is shown first and indented deeper.

What the Columns Mean

Two of these are misread often enough to be worth spelling out.

DB hits aren't rows. A database hit counts low-level accesses in the storage engine: reading a node, reading a property, or reading an index entry. A single returned row can cost many hits. Look again at the Expand(All) line above: 7,573 rows, 17,871 hits. The row count is your result size, the hit count is the price you paid for it.

Page cache hits and misses show whether the data was in memory. A miss means the database had to go to disk. On a first run against cold data you'll see mostly misses, and on a second run mostly hits, which is why comparing timings between a cold and a warm run tells you nothing useful. This column is an Enterprise Edition feature, so on the Community Docker image in this article it reads 0/0 throughout. That's not a bug and it doesn't mean your cache is empty.

The Most Useful Thing in the Whole Plan

Compare Estimated Rows against Rows.

The estimate is what the planner believed when it chose this plan. The row count is the truth. When they're close, the planner made its decision with a good picture of your data. When they diverge badly, it chose a plan for a dataset that doesn't exist, and that's very often the real reason a query is slow.

Look at the numbers from the profile above:

The planner guessed that filtering services down to the critical ones would leave 250 of 5,000. In our data it leaves 786, because roughly 15% of services are critical rather than the 5% its default assumption implies. That error then flows upward: because it expected 250 services it expected about 2,401 engineers, and got 7,573.

Here the consequence is harmless. On a bigger query, a three-fold underestimate at the bottom of a plan is exactly how the planner talks itself into a strategy that falls apart, because it believed it was joining a small thing to a big thing when it was really joining two big things.

Estimated Rows is what the planner believed when it chose this plan. Rows is what happened. Where they diverge is usually where a slow query is explained, because the planner optimised for a shape the data didn't have.

If estimates are consistently wrong across your queries, the statistics behind them may be stale.

So the habit worth building is: run PROFILE, read from the bottom, and check the estimate against the truth at every step. You aren't looking for a big number. You're looking for the first place the planner was surprised.

Three Tells Worth Recognising

This is PROFILE output in Neo4j Browser, showing the operator chain with estimated and actual row counts beside each step. This is the real output the NodeUniqueIndexSeek explanation refers to.

Beyond the estimate check, three specific things in a plan should catch your eye.

What specific operators tell you when you see them. NodeByLabelScan on a starting node means no index is being used. Each entry pairs the symptom with the cause and the fix.

NodeByLabelScan means the database read every node with that label. On a starting node this almost always means a missing index. It's the single most common finding.

A row count that explodes and then collapses: if one step produces two hundred thousand rows and the next reduces it to forty, you're generating work and throwing it away. Usually the pattern can be reordered so the selective part happens first.

CartesianProduct means two parts of your pattern aren't connected, so the database is combining every row on the left with every row on the right. It's nearly always an accident, and it's nearly always the reason a query went from milliseconds to minutes.

All three have the same shape as a fix: give the planner a cheaper way in. An index turns a scan into a seek, a reordered pattern makes the selective step happen first, and a missing relationship in the pattern removes the cartesian product.

Six Problems You'll Actually Hit

These are the ones that cost people an afternoon. None of them produce an obvious error message, which is exactly why they're worth listing.

The Query Returns Nothing and You Expected Rows

Check your arrow directions first. (a)-[:OWNS]->(b) and (a)<-[:OWNS]-(b) are different questions, and the second one is what you want when you're starting from the thing that's owned. If you're unsure, drop the arrowheads entirely and use -[:OWNS]-, which matches either direction. If rows appear, direction was the problem.

The Query Returns Fewer Rows Than the Truth

This is the relationship uniqueness trap from earlier in this handbook. If a pattern leaves a node and comes back to the same kind of node, and both halves could be the same relationship, Cypher discards those matches without a word. Split the pattern with WITH.

A Query That Was Instant is Suddenly Slow

Look for CartesianProduct in PROFILE. It means two parts of your pattern aren't connected to each other, so every row on the left is being combined with every row on the right. Usually a variable was forgotten, or two MATCH clauses were written where one pattern was meant.

MERGE Created a Duplicate

You merged on more than the identifying property. MERGE (e:Engineer {email: $email, name: $name}) treats a changed name as a different node. Merge on identity, then SET the rest.

MERGE is Unbearably Slow

You have no index on the property you merge on, so every merge scans every node with that label. Create the constraint before loading, not after.

The Whole Import Ran Out of Memory

You put everything in one transaction. Batch it. A few thousand rows per transaction is a sane default, and CALL { ... } IN TRANSACTIONS lets Cypher do the batching for you inside a single query.

Here's a short checklist worth keeping next to you:

Transactions and What Happens When Things Fail

execute_query wraps each call in its own transaction and retries it automatically if it hits a transient error such as a leader election in a cluster. For the majority of work, that's exactly what you want and you don't need to think about it.

Here's what actually happens across the driver, the session and the database, including the case everyone worries about: a write that fails halfway.

From your code through the driver and session to Neo4j. One driver per application with GraphDatabase.driver(uri, auth), then a session per unit of work. The session is cheap and short-lived, the driver expensive and long-lived, and swapping those round is a common cause of slow applications.

The important part is the middle. Once a transaction begins, nothing it has written is visible or durable until it commits. A failure at step nine doesn't leave you with half a graph, it leaves you with the graph you started with.

When you need several statements to succeed or fail together, manage the transaction yourself:

def reassign_service(tx, service, from_email, to_email):
    tx.run(
        """
        MATCH (:Engineer {email: $from_email})-[r:OWNS]->(s:Service {name: $service})
        DELETE r
        """,
        from_email=from_email, service=service,
    )
    tx.run(
        """
        MATCH (e:Engineer {email: $to_email}), (s:Service {name: $service})
        MERGE (e)-[:OWNS {since: date()}]->(s)
        """,
        to_email=to_email, service=service,
    )

with driver.session(database="neo4j") as session:
    session.execute_write(reassign_service, "payments", "ada@example.com", "grace@example.com")

execute_write runs your function inside one transaction. If any statement raises, the whole thing rolls back and the graph is left as it was. It also retries the function on transient failures, which is why the work goes in a function rather than inline: it may be executed more than once, so it must be safe to repeat.

That last point is worth saying plainly: any function you hand to execute_write must be idempotent, which means running it twice has the same effect as running it once. A retry starts your function again from the top, so anything that increments a counter or appends to a list will do it twice. This is another reason to reach for MERGE rather than CREATE inside one.

Testing Code That Talks to a Graph

Graph code is easy to write and easy to get subtly wrong, as the relationship uniqueness trap earlier in this handbook showed. Tests are how you find that class of bug once rather than repeatedly.

Don't Mock the Database

The temptation is to mock the driver and assert that your function called it with a particular string. Resist it. That test passes when your Cypher is wrong, which is precisely the failure you need to catch. The bugs in graph code are almost never in the Python around the query. They're in the query.

Run tests against a real Neo4j. It starts in seconds in Docker, and the whole point is to exercise the query engine.

Give Each Test a Clean Graph

import os
import pytest
from neo4j import GraphDatabase

@pytest.fixture(scope="session")
def driver():
    d = GraphDatabase.driver(
        os.environ.get("NEO4J_TEST_URI", "bolt://localhost:7687"),
        auth=("neo4j", os.environ["NEO4J_TEST_PASSWORD"]),
    )
    d.verify_connectivity()
    yield d
    d.close()

@pytest.fixture(autouse=True)
def clean(driver):
    """Wipe before every test so tests cannot leak into each other."""
    driver.execute_query("MATCH (n) DETACH DELETE n", database_="neo4j")

The driver is created once for the whole session, because it's expensive. The wipe runs before every test, because a test that depends on another test's leftovers will pass alone and fail in a suite.

Test the Thing That Actually Broke

A useful test is one that would have caught a real bug. Here's the one for the trap from earlier:

def test_teams_includes_a_team_whose_only_member_is_the_owner(driver):
    driver.execute_query(
        """
        MERGE (e:Engineer {email: 'linus@example.com'}) SET e.name = 'Linus'
        MERGE (s:Service {name: 'checkout'})
        MERGE (t:Team {name: 'Commerce'})
        MERGE (i:Incident {ref: 'INC-1'})
        MERGE (e)-[:OWNS]->(s)
        MERGE (e)-[:MEMBER_OF]->(t)
        MERGE (i)-[:AFFECTS]->(s)
        """,
        database_="neo4j",
    )

    teams = teams_involved(driver, "INC-1")

    # The single-pattern version returns [] here, with no error at all.
    assert [t["team"] for t in teams] == ["Commerce"]

That test is worth more than a dozen tests of your Python. It encodes a specific, silent, hard-to-spot failure, and it will fail loudly if anyone ever "simplifies" the query back into one pattern.

Assert on Counts as Well as Contents

Silent under-fetching is the characteristic graph bug, so assert how many rows you got, not only that the ones you got look right:

def test_load_is_idempotent(driver):
    load(driver)
    _, summary, _ = driver.execute_query(
        "MATCH (e:Engineer) RETURN count(e) AS c", database_="neo4j"
    )
    first = driver.execute_query("MATCH (e:Engineer) RETURN count(e) AS c", database_="neo4j")[0][0]["c"]

    load(driver)   # run it again
    second = driver.execute_query("MATCH (e:Engineer) RETURN count(e) AS c", database_="neo4j")[0][0]["c"]

    assert first == second, "loading twice created duplicates, so a MERGE key is wrong"

That single assertion catches the most expensive loading mistake there is, which is merging on more than the identifying property.

The same graph, seen as a data model in Neo4j Browser against the live Aura instance:

CALL db.schema.visualization() running in the Aura console, which draws the shape of whatever is currently in the database. It shows four node labels, Engineer, Incident, Service and Team, joined by four relationship types: an incident AFFECTS a service, a service DEPENDS_ON another service, an engineer OWNS a service, and an engineer is a MEMBER_OF a team. The property keys in use are email, name, ref and summary.

This is the same model you built locally, running on the managed service, and it's a quick way to check that a load did what you expected.

From Graph to Knowledge Graph

Everything so far has been a graph database. A knowledge graph is what you get when the nodes represent real entities from your domain and the relationships represent meaningful facts about them, so that the graph itself is a model of what you know.

The step up from one to the other is mostly about where the data comes from. Instead of loading rows from a table, you extract entities and relationships from documents, tickets, wikis, code, or conversations.

The mechanics you've already learned don't change:

def add_fact(driver, subject, predicate_service, source_doc):
    driver.execute_query(
        """
        MERGE (e:Engineer {email: $subject})
        MERGE (s:Service {name: $service})
        MERGE (e)-[r:OWNS]->(s)
          ON CREATE SET r.source = $source, r.extracted = datetime()
        """,
        subject=subject, service=predicate_service, source=source_doc,
        database_="neo4j",
    )

Notice r.source. When facts are extracted rather than entered, recording where each fact came from isn't optional. You'll need it the first time somebody asks why the graph believes something, and you'll need it when a source document is corrected and you have to find everything derived from it.

Two habits make extracted graphs survivable:

• Store provenance on the relationship. Which document, which version, when.

• Keep extraction idempotent. Re-running over the same document must not duplicate facts, which is exactly what MERGE on an identifying property gives you.

Why AI Systems Keep Rediscovering Graphs

This is the part that makes graphs suddenly relevant to people who have never touched one.

The standard way to give a language model access to your data is to embed your documents as vectors and retrieve the chunks most similar to the question. This works well, and it fails in a specific and predictable way.

Similarity retrieval can tell you that two things are related. It can't tell you how.

This is why neither retrieval method is enough alone, and what order to combine them in.

Vector search alone finds four documents that are each related to the question and none of which contain the answer. The chain from incident to service to owner to team spans all four, so no single chunk holds it and nothing scores highly enough to be retrieved together.

Graph traversal alone is exact once it starts: hop one goes from the incident to payments and checkout, hop two to Ada and Grace, hop three to the Platform team. The problem is starting, because "last night's payments incident" is a phrase, not a node, and the graph has never seen that wording.

Used together, in order: embed the question and find which entities it's about, which handles wording the graph has never seen. Traverse out from those entities, where relationships are stored so the chain is read rather than inferred. Hand back a small, precise set of facts with their provenance instead of five paragraphs of loosely related prose.

Similarity search can tell you that two things are related. It can't tell you how, which is why these answers degrade into confident guesses exactly when the reasoning gets interesting.

Ask "who should I talk to about last night's payments incident" and a vector store returns the chunks that look most like that sentence. It has no representation of the fact that the incident affected a service, that the service is owned by an engineer, and that the engineer is on a team. Each of those facts might live in a different document, and no single chunk contains the chain.

A graph stores the chain explicitly. Multi-hop questions become traversals, and the answer is derived rather than guessed.

The two aren't rivals, and treating them as rivals is a mistake. The pattern that works in practice is to use both:

In practice the pattern is: embed the text, use similarity to work out which entities the question is about, then traverse the graph from those entities to assemble the context you hand to the model.

Neo4j can hold the vectors too, which keeps both halves in one place. You create a vector index over a property holding the embedding:

CREATE VECTOR INDEX service_notes IF NOT EXISTS
FOR (s:Service) ON (s.embedding)
OPTIONS {indexConfig: {
  `vector.dimensions`: 1536,
  `vector.similarity_function`: 'cosine'
}}

Then the hybrid query becomes one round trip: similarity finds the entry points, and the traversal does the rest.

def context_for_question(driver, question_embedding, k=3):
    records, _, _ = driver.execute_query(
        """
        // 1. vector search finds the services the question is about
        CALL db.index.vector.queryNodes('service_notes', $k, $embedding)
        YIELD node AS s, score

        // 2. the graph supplies what similarity cannot: how things connect
        OPTIONAL MATCH (s)<-[:OWNS]-(owner:Engineer)-[:MEMBER_OF]->(t:Team)
        OPTIONAL MATCH (s)<-[:AFFECTS]-(i:Incident)
        RETURN s.name AS service, score,
               collect(DISTINCT owner.name) AS owners,
               collect(DISTINCT t.name)     AS teams,
               collect(DISTINCT i.ref)      AS incidents
        ORDER BY score DESC
        """,
        embedding=question_embedding, k=k, database_="neo4j",
    )
    return [dict(r) for r in records]

Read what each half contributes. The vector index answers "which services does this question seem to be about", which a graph alone can't do because the user's wording won't match your node names.

The traversal then answers "who owns them, which teams, what broke recently", which similarity alone can't do because those facts live in different documents and no single chunk contains the chain.

The result you hand the model is a small, precise set of connected facts rather than five paragraphs of loosely related prose. That's usually the difference between an answer and a plausible guess.

A note on honesty in the output: because every fact came out of the graph, you can cite it. Passing the relationship provenance along with the facts lets the model say where each claim came from, and lets you check it when it gets one wrong.

The same argument explains why durable memory for AI agents keeps ending up shaped like a graph.

The example is three notes. note-03 says "We decided to use Mongo for payments", note-09 says "Mira moved payments onto Postgres", note-14 says "Payments storage reviewed, no action". Ask "what database does payments use" and, as loose text, all three look equally relevant, so the agent picks one.

Drawn as a graph, the newer Decision node use Postgres has a SUPERSEDES edge pointing at the Mongo decision and an APPLIES_TO edge pointing at the payments Service. The ordering that was invisible in prose is now a stored fact the agent can follow.

An agent that remembers needs to know that a decision was made, who made it, what it superseded, and what depends on it. Those are relationships with direction and properties. Storing them as loose text and hoping similarity search reconstructs them is how agents end up confidently contradicting themselves.

None of this requires new skills. It's the same modeling discipline from earlier in this handbook, applied to facts extracted from text instead of rows from a table. Which is why the modeling section is the one worth re-reading.

Building a Knowledge Graph from Text

So far every fact arrived as a tidy Python dictionary. Real knowledge graphs are usually built from prose: incident write-ups, wiki pages, tickets, commit messages, and support threads.

The extraction step is where people either build something durable or build a mess. Three rules keep it durable, and here's where each of them sits in the pipeline:

Raw text goes to an extractor, which produces candidate entities and relationships, which are merged into the graph. The stages are separable, which matters because the extractor is the part you'll swap and re-run.

Notice where the gate is. The schema check happens before anything is written, not after. Once an invented relationship type is in the graph it's indistinguishable from a real one, and you'll be cleaning it up by hand.

Rule #1: Extract into a Fixed Schema, Not a Free-for-All

If you let an extractor invent relationship types, you'll end up with OWNS, owns, IS_OWNER_OF and RESPONSIBLE_FOR all meaning the same thing, and no query will ever find all four.

Decide your vocabulary first, and make the extractor choose from it:

NODE_LABELS = ["Engineer", "Service", "Incident", "Team"]
REL_TYPES = ["OWNS", "AFFECTS", "MEMBER_OF", "DEPENDS_ON"]

Whatever does the extraction (a language model, a regex, or a human), its job is to emit triples that use only those names. Anything else gets rejected rather than written.

Rule #2: Every Extracted Fact Carries its Source

Three stages, left to right: documents go in, extraction emits triples using a fixed vocabulary, and the merge records where each fact came from.

The detail the drawing turns on is the split between ON CREATE and ON MATCH. The source is written once, when the fact is first created, while the freshness timestamp updates every time the same fact is seen again. That way re-running over the same document doesn't overwrite the original provenance.

It pays off when a document turns out to be wrong, because matching on the source property lets you retract every fact that came from it in one query. The step people skip is the confidence score: store it, then actually use it downstream, because a guess at 0.4 must not read as a confirmed fact.

When a human types data in, you can ask them. When a machine extracts it, you can't, and someone will eventually ask "why does the graph think Ada owns checkout?"

def write_triple(driver, subject_email, rel_type, object_name, source_doc, confidence):
    if rel_type not in REL_TYPES:
        raise ValueError(f"refusing unknown relationship type: {rel_type}")

    driver.execute_query(
        f"""
        MERGE (e:Engineer {{email: $subject}})
        MERGE (s:Service {{name: $object}})
        MERGE (e)-[r:{rel_type}]->(s)
          ON CREATE SET r.source = $source,
                        r.confidence = $confidence,
                        r.extracted_at = datetime()
          ON MATCH  SET r.last_seen = datetime()
        """,
        subject=subject_email, object=object_name,
        source=source_doc, confidence=confidence,
        database_="neo4j",
    )

Two things about that snippet deserve a warning.

The relationship type is the one thing in Cypher you can't pass as a parameter. -[r:$type]-> isn't valid, which is why it's interpolated into the string.

That's exactly the pattern that causes injection bugs, so the if rel_type not in REL_TYPES check above it is not decoration. It's the only thing making the interpolation safe. Never build that string from raw model output without checking it against a fixed list first.

ON CREATE and ON MATCH let you record provenance once and freshness every time, which means re-running extraction over the same document does not overwrite the original source.

Rule #3: make Re-extraction Safe

You will re-run extraction. Documents get corrected, your prompt improves, or a bug gets fixed. If a second run duplicates everything, the graph is worthless.

Because every write above is a MERGE on an identifying property, re-running is safe by construction. That's the same idempotency property from the loading section, and it matters far more here.

To retract facts from a document that has changed:

MATCH ()-[r]->()
WHERE r.source = $source_doc
DELETE r

Then re-extract. Deleting by source is only possible because you stored the source, which is the whole argument for rule two.

A Caution on Confidence

If your extractor emits a confidence score, store it, and then actually use it. A graph that mixes facts a human confirmed with facts a model guessed at 0.4 confidence, and treats them identically at query time, will produce confident wrong answers.

MATCH (e:Engineer)-[r:OWNS]->(s:Service)
WHERE r.confidence IS NULL OR r.confidence > 0.8
RETURN e.name, s.name

r.confidence IS NULL keeps the hand-entered facts, which have no score because nobody guessed them.

The Complete Script

Here's everything from this handbook as one runnable file. It creates the constraints, loads the data, and answers the question from the introduction. If you've followed along, this is the whole thing in one place.

"""A minimal knowledge graph, end to end."""

import os
from neo4j import GraphDatabase

URI = os.environ.get("NEO4J_URI", "bolt://localhost:7687")
AUTH = (
    os.environ.get("NEO4J_USER", "neo4j"),
    os.environ["NEO4J_PASSWORD"],
)

CONSTRAINTS = [
    "CREATE CONSTRAINT engineer_email IF NOT EXISTS FOR (e:Engineer) REQUIRE e.email IS UNIQUE",
    "CREATE CONSTRAINT service_name  IF NOT EXISTS FOR (s:Service)  REQUIRE s.name  IS UNIQUE",
    "CREATE CONSTRAINT incident_ref  IF NOT EXISTS FOR (i:Incident) REQUIRE i.ref   IS UNIQUE",
    "CREATE CONSTRAINT team_name     IF NOT EXISTS FOR (t:Team)     REQUIRE t.name  IS UNIQUE",
]

PEOPLE = [
    {"email": "ada@example.com",   "name": "Ada Okonjo",   "service": "payments", "team": "Platform"},
    {"email": "grace@example.com", "name": "Grace Lin",    "service": "payments", "team": "Platform"},
    {"email": "linus@example.com", "name": "Linus Berg",   "service": "checkout", "team": "Commerce"},
    {"email": "mira@example.com",  "name": "Mira Haddad",  "service": "auth",     "team": "Platform"},
    {"email": "tom@example.com",   "name": "Tom Ferreira", "service": "search",   "team": "Discovery"},
]

# One engineer who owns nothing, so the OPTIONAL MATCH example has something to
# show. Without her, that query looks identical to a plain MATCH.
UNASSIGNED = {"email": "nadia@example.com", "name": "Nadia Rossi"}

# Service dependencies, which the variable length path example walks.
DEPENDENCIES = [
    {"upstream": "auth",     "downstream": "payments"},
    {"upstream": "auth",     "downstream": "checkout"},
    {"upstream": "payments", "downstream": "checkout"},
    {"upstream": "search",   "downstream": "checkout"},
]

INCIDENT = {"ref": "INC-4471", "summary": "Elevated 5xx on card capture",
            "services": ["payments", "checkout"]}


def setup(driver):
    """Constraints first. They enforce correctness and create the indexes
    that stop MERGE from scanning every node."""
    for statement in CONSTRAINTS:
        driver.execute_query(statement, database_="neo4j")


def load(driver):
    """People and teams, then the unassigned engineer, then dependencies,
    then the incident. Four round trips for the whole dataset."""
    driver.execute_query(
        """
        UNWIND $rows AS row
        MERGE (e:Engineer {email: row.email})
          SET e.name = row.name
        MERGE (s:Service {name: row.service})
        MERGE (t:Team {name: row.team})
        MERGE (e)-[:OWNS]->(s)
        MERGE (e)-[:MEMBER_OF]->(t)
        """,
        rows=PEOPLE, database_="neo4j",
    )
    driver.execute_query(
        "MERGE (e:Engineer {email: $email}) SET e.name = $name",
        **UNASSIGNED, database_="neo4j",
    )
    driver.execute_query(
        """
        UNWIND $rows AS row
        MATCH (u:Service {name: row.upstream}), (d:Service {name: row.downstream})
        MERGE (d)-[:DEPENDS_ON]->(u)
        """,
        rows=DEPENDENCIES, database_="neo4j",
    )
    driver.execute_query(
        """
        MERGE (i:Incident {ref: $ref}) SET i.summary = $summary
        WITH i
        UNWIND $services AS svc
        MATCH (s:Service {name: svc})
        MERGE (i)-[:AFFECTS]->(s)
        """,
        **INCIDENT, database_="neo4j",
    )


def who_has_context(driver, ref):
    """The question from the introduction, in one pattern."""
    records, _, _ = driver.execute_query(
        """
        MATCH (i:Incident {ref: $ref})-[:AFFECTS]->(:Service)<-[:OWNS]-(e:Engineer)
        RETURN DISTINCT e.name AS name, e.email AS email
        ORDER BY name
        """,
        ref=ref, database_="neo4j",
    )
    return [dict(r) for r in records]


def teams_involved(driver, ref):
    """Split into two patterns on purpose. A single pattern would hit the
    relationship uniqueness rule and silently drop any team whose only
    member is also the owner."""
    records, _, _ = driver.execute_query(
        """
        MATCH (i:Incident {ref: $ref})-[:AFFECTS]->(:Service)<-[:OWNS]-(:Engineer)-[:MEMBER_OF]->(t:Team)
        WITH DISTINCT t
        MATCH (t)<-[:MEMBER_OF]-(e:Engineer)
        RETURN t.name AS team, collect(e.name) AS members
        ORDER BY team
        """,
        ref=ref, database_="neo4j",
    )
    return [dict(r) for r in records]


def main():
    with GraphDatabase.driver(URI, auth=AUTH) as driver:
        driver.verify_connectivity()
        setup(driver)
        load(driver)

        print("Engineers with context on INC-4471:")
        for row in who_has_context(driver, "INC-4471"):
            print(f"  {row['name']:<14} {row['email']}")

        print("\nTeams involved:")
        for row in teams_involved(driver, "INC-4471"):
            print(f"  {row['team']:<10} {', '.join(row['members'])}")


if __name__ == "__main__":
    main()

Run it with your password in the environment rather than in the file:

export NEO4J_PASSWORD='your-password'
python3 knowledge_graph.py

Note os.environ["NEO4J_PASSWORD"] with square brackets rather than .get(). That's deliberate. It fails loudly at startup if the variable is missing, instead of quietly trying to connect with None and giving you a confusing authentication error.

Where to Go Next

You now have the pieces that matter: a data model you can defend, a loading script that's safe to re-run, queries that traverse instead of joining, indexes that keep them fast, and a way to find out why something is slow.

Here are three suggestions for what to do with that:

Start with a domain you already understand. Modeling is the hard part, and it's far easier to judge whether a model is right when you already know what questions the data should answer. Your own codebase, your team's services, or your reading list are all better first projects than a dataset you downloaded.

Write the questions before the model. It takes ten minutes and it will save you a rewrite. This remains the single highest-leverage habit in this whole handbook.

Then point something at it that's not a person. Once your data is modeled properly, wiring a language model to traverse it is a much smaller step than it sounds, because the hard part was never the model. It was knowing what the things are and how they connect.

The companion repository is github.com/ronidas39/knowledge-graph-python-neo4j. It has the complete script, the 75,500 node dataset as committed CSVs, the benchmark behind every number in this article, and a checker that runs all 39 Cypher blocks through EXPLAIN. Clone it, run verify_dataset.py, and you'll know your data matches mine before you trust a single measurement.

If you want to go deeper, I write about system design at systemdesign.academy and publish longer engineering tutorials on my YouTube channel.

So I built a benchmark suite that pits vanilla PostgreSQL against a feature-optimized PostgreSQL instance — measuring caching, message queues, full-text search, and pub/sub under controlled conditions.

In this article, you'll learn how to use PostgreSQL's built-in features for caching, job queues, full-text search, and pub/sub. You'll see actual benchmark results (latency percentiles, throughput, and error rates) comparing naive PostgreSQL patterns against optimized ones, and understand where PostgreSQL's limits are so you can decide whether you really need that extra service in your stack.

Table of Contents

• Prerequisites

• The Setup

• Benchmark 1: Caching with UNLOGGED Tables

• Benchmark 2: Job Queues with SKIP LOCKED

• Benchmark 3: Full-Text Search with tsvector

• Benchmark 4: Pub/Sub with LISTEN/NOTIFY

• The Combined Workload: The Honest Test

• What I Learned

Prerequisites

To follow along or reproduce the benchmarks, you'll need:

• Docker and Docker Compose

• Node.js 20+ (for the Express TypeScript API layer)

• k6 for load testing

• Basic familiarity with SQL and PostgreSQL

The full benchmark project is open source on GitHub — you can clone it and run every test yourself.

The Setup

The benchmark uses two identical PostgreSQL 17 instances running in Docker containers, each with fixed resource constraints (2 CPUs, 2 GB RAM). Both share the same Express TypeScript API layer — the only difference is which PostgreSQL features are enabled.

┌─────────┐     ┌──────────────────┐     ┌─────────────────┐
│   k6    │────>│  Express API     │────>│  PG Baseline    │
│  (load  │     │  (TypeScript)    │     │  (vanilla PG17) │
│  test)  │────>│  Port 3001/3002  │────>│  PG Modded      │
└─────────┘     └──────────────────┘     │  (features on)  │
                                         └─────────────────┘

The baseline instance uses naïve approaches (regular tables, ILIKE search, polling). The modded instance uses PostgreSQL's built-in features (UNLOGGED tables, tsvector with GIN indexes, LISTEN/NOTIFY, partial indexes). Same hardware, same API code, same data. Only the database features differ.

Both instances share this tuned postgresql.conf:

# Memory allocation
shared_buffers = 512MB           # 25% of available RAM
effective_cache_size = 1536MB    # 75% of RAM — helps the query planner
work_mem = 16MB                  # per-sort/hash operation memory

# SSD-optimized planner settings
random_page_cost = 1.1           # default 4.0 assumes spinning disks
effective_io_concurrency = 200   # allow parallel I/O on SSDs

These settings matter. The defaults assume spinning disks from the early 2000s. Setting random_page_cost = 1.1 tells the query planner that random reads are nearly as fast as sequential reads on SSDs, which encourages index usage over sequential scans.

Benchmark 1: Caching with UNLOGGED Tables

The idea: Use an UNLOGGED table as an in-database cache. UNLOGGED tables skip PostgreSQL's Write-Ahead Log (WAL) — the mechanism that guarantees durability. Since cache data is ephemeral by nature, losing it on a crash is acceptable, and skipping WAL removes the biggest write bottleneck.

-- Modded: UNLOGGED table for cache entries
CREATE UNLOGGED TABLE cache_entries (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    expires_at TIMESTAMPTZ
);

-- Baseline: same schema, but a regular (logged) table
CREATE TABLE cache_entries (
    key TEXT PRIMARY KEY,
    value JSONB NOT NULL,
    expires_at TIMESTAMPTZ
);

Results (200 Virtual Users)

A consistent 13% improvement across all percentiles. Not dramatic, but free — you change one keyword in your CREATE TABLE statement.

Under Stress (1,000 Virtual Users, No Sleep)

The relative improvement stays locked at 12-13% regardless of load level. The UNLOGGED advantage is a per-write optimization — it saves the same amount of I/O whether you are doing 100 or 10,000 writes per second. The modded instance served 37,000 more requests in the same time window.

The Verdict

UNLOGGED tables won't match Redis for sub-millisecond hot-path caching (real-time bidding, gaming leaderboards). But for web applications where the difference between 2ms and 5ms is invisible to users, they eliminate an entire infrastructure dependency for zero additional complexity.

You do give up Redis data structures (sorted sets, HyperLogLog, streams). If you need those, a dedicated cache is still the right call.

Benchmark 2: Job Queues with SKIP LOCKED

The idea: Use PostgreSQL as a job queue with SELECT ... FOR UPDATE SKIP LOCKED. Multiple workers poll the same table, and SKIP LOCKED ensures each worker gets a different row — no duplicates, no contention.

-- Queue table with a partial index on pending jobs only
CREATE TABLE job_queue (
    id SERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Partial index: only indexes pending jobs
-- As jobs complete, they leave the index — it stays small forever
CREATE INDEX idx_pending_jobs ON job_queue (created_at)
    WHERE status = 'pending';

The dequeue pattern:

-- Atomic dequeue: select + update in one statement
UPDATE job_queue SET status = 'processing'
WHERE id = (
    SELECT id FROM job_queue
    WHERE status = 'pending'
    ORDER BY created_at
    LIMIT 1
    FOR UPDATE SKIP LOCKED  -- skip rows locked by other workers
) RETURNING *;

How SKIP LOCKED works: Worker A locks row 1. Worker B tries row 1, sees the lock, skips it, and takes row 2 instead. No blocking, no duplicates. If a worker crashes, the transaction rolls back and the row becomes available again.

Results (100 Producers + 50 Consumers)

They're virtually identical. The partial index doesn't show its value in a 60-second benchmark because the table doesn't accumulate enough completed rows for the index size difference to matter. In a production system with millions of completed jobs, the partial index keeps the index at kilobytes while a full index grows to gigabytes.

The Verdict

SKIP LOCKED is production-ready for job queues. Libraries like pg-boss (Node.js) and river (Go) build on this exact pattern.

You do give up exchange/routing patterns (fan-out, topic-based routing) and consumer groups with message replay. If you need those, a dedicated message broker is still the right tool. For simple "process this job once" workloads, PostgreSQL handles it.

Benchmark 3: Full-Text Search with tsvector

The idea: Use PostgreSQL's built-in full-text search instead of a separate search service. A tsvector column stores pre-processed search tokens, and a GIN (Generalized Inverted Index) enables fast lookups using the same inverted index concept that powers Elasticsearch.

-- Search-optimized article table
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    search_vector tsvector  -- pre-computed search tokens
);

-- GIN index for full-text search
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- Auto-update search_vector on insert/update
CREATE OR REPLACE FUNCTION update_search_vector() RETURNS trigger AS $$
BEGIN
    NEW.search_vector := to_tsvector('english',
        COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.body, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_search
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

The baseline uses ILIKE with a leading wildcard — the approach most developers reach for first:

-- Baseline: sequential scan on every query
SELECT * FROM articles
WHERE title ILIKE '%postgresql%' OR body ILIKE '%postgresql%';

-- Modded: GIN index lookup with relevance ranking
SELECT id, title,
    ts_rank(search_vector, plainto_tsquery('english', 'postgresql')) AS rank
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'postgresql')
ORDER BY rank DESC LIMIT 20;

Results (500 Virtual Users)

This is the standout result. The baseline's p95 of 101ms versus the modded's 10ms is a 10x improvement.

Why the baseline's p50 (1.96ms) is slightly better than the modded's (2.76ms): simple ILIKE queries on small result sets can be fast when the data fits in shared_buffers. But as load increases and the buffer cache is contested, sequential scans degrade dramatically. The GIN index stays stable.

Under Stress (500 Virtual Users, No Sleep)

ILIKE collapses to 1-second p95 latencies. Each query forces a sequential scan of all 10,000 articles, blocking shared buffers and starving concurrent queries. The tsvector approach serves 2.6x more requests in the same time window because the GIN index lookup is O(log n) regardless of concurrency.

The Verdict

This is the strongest argument in the entire benchmark. The fix requires zero extensions — to_tsvector(), plainto_tsquery(), and CREATE INDEX USING GIN are all built into core PostgreSQL. If you're doing WHERE column ILIKE '%term%' on any table with more than a few thousand rows, you're leaving massive performance on the table.

You do give up distributed search across shards, complex analyzers for CJK languages, and aggregation/faceted search pipelines. For a product search bar, blog search, or internal tool — PostgreSQL is enough.

Benchmark 4: Pub/Sub with LISTEN/NOTIFY

The idea: Use PostgreSQL's native LISTEN/NOTIFY for pub/sub messaging, triggered automatically on INSERT via a database trigger.

-- Trigger that fires pg_notify on every new message
CREATE OR REPLACE FUNCTION notify_message() RETURNS trigger AS $$
BEGIN
    PERFORM pg_notify(NEW.channel, NEW.payload::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_notify
    AFTER INSERT ON messages
    FOR EACH ROW EXECUTE FUNCTION notify_message();

Results (200 Virtual Users)

Here we have a 20% improvement at p95. The trigger-based approach does more work per INSERT (INSERT + NOTIFY), but the reduced round trips and better connection reuse patterns offset the overhead.

The Verdict

LISTEN/NOTIFY works for real-time features where you would otherwise reach for Redis pub/sub. The main limitation is payload size (8,000 bytes maximum) and the requirement for dedicated connections (incompatible with PgBouncer in transaction mode).

The Combined Workload: The Honest Test

Individual benchmarks are flattering. The real question: can one PostgreSQL instance handle caching, queues, search, and pub/sub simultaneously without degrading?

Results (All Four Workloads Running Together)

Under combined load, the baseline marginally outperforms the modded setup. The modded PostgreSQL does more work per operation — maintaining GIN indexes, firing triggers, running pg_cron in the background. When all these features are active simultaneously, the overhead is measurable: about 15% higher p95 latency.

But both setups stay comfortably under 10ms at p95. For most web applications, that's more than good enough.

What I Learned

After running all these benchmarks, here's what I would tell a team evaluating whether to "just use Postgres":

1. Do it for full-text search: Switching from ILIKE to tsvector with a GIN index is a 10x improvement that requires zero extensions. This is the single highest-ROI change in the entire PostgreSQL ecosystem, and most developers don't know it exists.

2. Do it for job queues: SKIP LOCKED is production-ready and eliminates RabbitMQ for simple "process this job" workloads. Use a library like pg-boss or river rather than rolling your own.

3. Consider it for caching: UNLOGGED tables give a steady 13% improvement over regular tables. If sub-millisecond latency is not a hard requirement (and for most web apps, it is not), you can drop Redis entirely.

4. Be honest about the overhead: Running all four roles simultaneously adds about 15% latency compared to running any single role. Whether that matters depends on your latency budget.

5. Know where to stop: PostgreSQL won't match Redis for sub-millisecond caching, Kafka for millions of messages per second, or Elasticsearch for distributed multi-node search with complex analyzers. The line is at extreme throughput or extreme specialization.

The honest conclusion is not "PostgreSQL does everything." It is: for most applications, a single well-configured PostgreSQL instance handles 80% of what you would otherwise need three to five additional services for. That is less infrastructure to deploy, monitor, and maintain — and fewer things to break at 3 AM.

Enterprise-scale applications processing millions of messages per second, serving sub-millisecond cache hits to millions of concurrent users, or running distributed search across terabytes of documents will still need specialized tools. Those tools exist for a reason, and at that scale the operational cost of running them is justified by the performance you get back.

But most of us aren't building at that scale — and may never need to. Starting with PostgreSQL for these roles means you ship faster with fewer moving parts. If and when you outgrow what PostgreSQL can handle, your benchmarks will tell you exactly which role needs to be extracted into a dedicated service. That is a much better position than starting with five services on day one because you assumed you would need them.

The benchmark project is open source if you want to reproduce these results or adapt the tests for your own workload.

You can find more of my writing at site.aaronhsyong.com.

In this article, you’lll learn how to integrate Isar into a Flutter project, set up a data model, and perform the full range of CRUD (Create, Read, Update, Delete) operations. To make this practical, you’ll build a simple to-do app that allows users to create, view, update, and delete tasks.

Table of Contents

• Prerequisites

• What We Are Building

• How to Set Up Isar in a Flutter Project

• How to Create the Task Model

• How to Build the Repository for CRUD Operations

• How to Integrate CRUD into the Flutter UI

• Beyond CRUD: Advanced Features of Isar

• Conclusion

Prerequisites

Before starting, ensure you have the following:

1. Flutter SDK installed (version 3.0 or above recommended).
   Check your version with:

    flutter --version
   

2. Dart knowledge: Familiarity with Dart syntax, classes, and async programming.

3. Flutter basics: You should know how to set up a Flutter project, build widgets, and use FutureBuilder or setState for state management.

4. Code editor: VS Code or Android Studio is recommended.

If these are in place, we are ready to begin.

What We Are Building

We will create a Task Manager App that lets users:

• Add new tasks.

• View all tasks in a list.

• Update existing tasks.

• Delete tasks.

By the end, you will have a fully functioning CRUD app built with Flutter and Isar.

How to Set Up Isar in a Flutter Project

Step 1: Add dependencies

Open your pubspec.yaml file and add the following:

dependencies:
  flutter:
    sdk: flutter
  isar: ^3.1.0
  isar_flutter_libs: ^3.1.0

dev_dependencies:
  isar_generator: ^3.1.0
  build_runner: any

• isar: The core Isar package.

• isar_flutter_libs: Required for Flutter integration.

• isar_generator: Used to generate code for your models.

• build_runner: Runs the code generator.

Run:

flutter pub get

Step 2: Create and initialize Isar

Create a file named isar_setup.dart. This will handle the opening of the Isar database.

import 'package:isar/isar.dart';
import 'package:path_provider/path_provider.dart';
import 'task.dart'; // we will create this model soon

late final Isar isar;

Future<void> initializeIsar() async {
  final dir = await getApplicationDocumentsDirectory();
  isar = await Isar.open(
    [TaskSchema],
    directory: dir.path,
  );
}

Explanation:

• getApplicationDocumentsDirectory() provides a storage location for the database file.

• Isar.open() initializes the database and registers our Task schema.

• late final Isar isar; ensures we can access the database instance globally after initialization.

How to Create the Task Model

Now let’s define our data model for tasks. Create a file named task.dart.

import 'package:isar/isar.dart';

part 'task.g.dart';

@Collection()
class Task {
  Id id = Isar.autoIncrement; // auto-incrementing primary key

  late String name;

  late DateTime createdAt;

  Task(this.name) : createdAt = DateTime.now();
}

Explanation:

• @Collection() tells Isar this class represents a database collection.

• Id id = Isar.autoIncrement; creates a unique identifier automatically.

• late String name; stores the task name.

• late DateTime createdAt; stores the creation timestamp.

• part 'task.g.dart'; links to the generated code, which will be created after running the code generator.

Generate the code with:

flutter pub run build_runner build

This generates task.g.dart, which contains the necessary schema code.

How to Build the Repository for CRUD Operations

Create a new file called task_repository.dart. This will house the methods for interacting with the database.

import 'package:isar/isar.dart';
import 'task.dart';
import 'isar_setup.dart';

class TaskRepository {
  Future<void> addTask(String name) async {
    final task = Task(name);
    await isar.writeTxn(() async {
      await isar.tasks.put(task);
    });
  }

  Future<List<Task>> getAllTasks() async {
    return await isar.tasks.where().findAll();
  }

  Future<void> updateTask(Task task) async {
    await isar.writeTxn(() async {
      await isar.tasks.put(task);
    });
  }

  Future<void> deleteTask(Task task) async {
    await isar.writeTxn(() async {
      await isar.tasks.delete(task.id);
    });
  }
}

Explanation:

• addTask: Creates a new task and saves it.

• getAllTasks: Reads all tasks from the database.

• updateTask: Updates an existing task by calling .put() again.

• deleteTask: Removes a task by its id.

• isar.writeTxn: Ensures operations run inside a transaction for safety and consistency.

How to Integrate CRUD into the Flutter UI

Now, let’s connect everything inside main.dart.

import 'package:flutter/material.dart';
import 'isar_setup.dart';
import 'task_repository.dart';
import 'task.dart';

void main() async {
  WidgetsFlutterBinding.ensureInitialized();
  await initializeIsar(); // initialize Isar before runApp
  runApp(MyApp());
}

class MyApp extends StatelessWidget {
  @override
  Widget build(BuildContext context) {
    return MaterialApp(
      home: TaskListScreen(),
    );
  }
}

class TaskListScreen extends StatefulWidget {
  @override
  _TaskListScreenState createState() => _TaskListScreenState();
}

class _TaskListScreenState extends State<TaskListScreen> {
  final TaskRepository _taskRepository = TaskRepository();
  late Future<List<Task>> _tasksFuture;

  @override
  void initState() {
    super.initState();
    _tasksFuture = _taskRepository.getAllTasks();
  }

  Future<void> _addTask() async {
    await _taskRepository.addTask('New Task');
    setState(() {
      _tasksFuture = _taskRepository.getAllTasks();
    });
  }

  Future<void> _deleteTask(Task task) async {
    await _taskRepository.deleteTask(task);
    setState(() {
      _tasksFuture = _taskRepository.getAllTasks();
    });
  }

  @override
  Widget build(BuildContext context) {
    return Scaffold(
      appBar: AppBar(title: Text('Isar CRUD Example')),
      body: FutureBuilder<List<Task>>(
        future: _tasksFuture,
        builder: (context, snapshot) {
          if (snapshot.connectionState == ConnectionState.waiting) {
            return Center(child: CircularProgressIndicator());
          } else if (snapshot.hasError) {
            return Center(child: Text('Error: ${snapshot.error}'));
          } else {
            final tasks = snapshot.data ?? [];
            if (tasks.isEmpty) {
              return Center(child: Text('No tasks yet.'));
            }
            return ListView.builder(
              itemCount: tasks.length,
              itemBuilder: (context, index) {
                final task = tasks[index];
                return ListTile(
                  title: Text(task.name),
                  subtitle: Text('Created at: ${task.createdAt}'),
                  trailing: IconButton(
                    icon: Icon(Icons.delete),
                    onPressed: () => _deleteTask(task),
                  ),
                );
              },
            );
          }
        },
      ),
      floatingActionButton: FloatingActionButton(
        onPressed: _addTask,
        child: Icon(Icons.add),
      ),
    );
  }
}

Explanation:

• initializeIsar(): Ensures the database is ready before the app runs.

• _tasksFuture: Holds a future of the list of tasks.

• _addTask: Adds a new task and refreshes the list.

• _deleteTask: Deletes a task and refreshes the list.

• FutureBuilder: Automatically rebuilds the UI when the future completes.

• ListView.builder: Displays all tasks dynamically.

This gives you a simple yet complete CRUD app using Isar.

Beyond CRUD: Advanced Features of Isar

Once you are comfortable with CRUD, Isar provides advanced tools to optimize and extend your application:

1. Reactive Queries:
   Instead of using FutureBuilder, you can listen for changes directly.

    final stream = isar.tasks.where().watch(fireImmediately: true);
   

2. Indexes:
   Improve query performance by indexing fields.

    @Collection()
    class Task {
      Id id = Isar.autoIncrement;
   
      @Index()
      late String name;
    }
   

3. Relations:
   Link one collection to another (for example, Project with many Tasks).

4. Custom Queries:
   Perform complex filtering, sorting, and pagination.

5. Migrations:
   Safely evolve your schema as the app grows.

6. Batch Operations:
   Insert or update many records in one transaction.

Conclusion

We built a simple Flutter to-do app with Isar that supports creating, reading, updating, and deleting tasks. Along the way, we learned how to:

1. Add Isar dependencies.

2. Define a model with annotations.

3. Generate schema code.

4. Implement CRUD operations in a repository.

5. Connect Isar to the Flutter UI.

With its performance, developer-friendly API, and advanced features, Isar is an excellent choice for local persistence in Flutter applications.

For further learning, consult the official docs:

1. Isar on pub.dev

2. Isar documentation

Database migrations are usually run before an application starts and do not run successfully more than once for the same database. Database migration tools save a history of migrations that have run in a database so that they can be tracked for future purposes.

In this article, you’ll learn how to set up and run database migrations in a minimal Node.js API application. We will use ts-migrate-mongoose and an npm script to create a migration and seed data into a MongoDB database. ts-migrate-mongoose supports running migration scripts from TypeScript code as well as CommonJS code.

ts-migrate-mongoose is a migration framework for Node.js projects that use mongoose as the object-data mapper. It provides a template for writing migration scripts. It also provides a configuration to run the scripts programmatically and from the CLI.

Table of Contents

• How to Set Up the Project

• How to Configure ts-migrate-mongoose for the Project

• How to Seed User Data with ts-migrate-mongoose

• How to Build an API Endpoint to Fetch Seeded Data

• Conclusion

How to Set Up the Project

To use ts-migrate-mongoose for database migrations, you need to have the following:

1. A Node.js project with mongoose installed as a dependency.

2. A MongoDB database connected to the project.

3. MongoDB Compass (Optional – to enable us view the changes in the database).

A starter repository which can be cloned from ts-migrate-mongoose-starter-repo has been created for ease. Clone the repository, fill the environment variables and start the application by running the npm start command.

Visit http://localhost:8000 with a browser or an API client such as Postman and the server will return a "Hello there!" text to show that the starter application runs as expected.

How to Configure ts-migrate-mongoose for the Project

To configure ts-migrate-mongoose for the project, install ts-migrate-mongoose with this command:

npm install ts-migrate-mongoose

ts-migrate-mongoose allows configuration with a JSON file, a TypeScript file, a .env file or via the CLI. It is advisable to use a .env file because the content of the configuration may contain a database password and it is not proper to have that exposed to the public. .env files are usually hidden via .gitignore files so they are more secure to use. This project will use a .env file for the ts-migrate-mongoose configuration.

The file should contain the following keys and their values:

• MIGRATE_MONGO_URI - the URI of the Mongo database. It is the same as the database URL.

• MIGRATE_MONGO_COLLECTION - the name of the collection (or table) which migrations should be saved in. The default value is migrations which is what is used in this project. ts-migrate-mongoose saves migrations to MongoDB.

• MIGRATE_MIGRATIONS_PATH - the path to the folder for storing and reading migration scripts. The default value is ./migrations which is what is used in this project.

How to Seed User Data with ts-migrate-mongoose

We have been able to create a project and connect it successfully to a Mongo database. At this point, we want to seed user data into the database. We need to:

1. Create a users collection (or table)

2. Use ts-migrate-mongoose to create a migration script to seed data

3. Use ts-migrate-mongoose to run the migration to seed the user data into the database before the application starts

1. Create a users Collection using Mongoose

Mongoose schema can be used to create a user collection (or table). User documents (or records) will have the following fields (or columns): email, favouriteEmoji and yearOfBirth.

To create a Mongoose schema for the user collection, create a user.model.js file in the root of the project containing the following code snippet:

const mongoose = require("mongoose");

const userSchema = new mongoose.Schema(
  {
    email: {
      type: String,
      lowercase: true,
      required: true,
    },
    favouriteEmoji: {
      type: String,
      required: true,
    },
    yearOfBirth: {
      type: Number,
      required: true,
    },
  },
  {
    timestamps: true,
  }
);

module.exports.UserModel = mongoose.model("User", userSchema);

2. Create a Migration Script with ts-migrate-mongoose

ts-migrate-mongoose provides CLI commands which can be used to create migration scripts.

Running npx migrate create <name-of-script> in the root folder of the project will create a script in the MIGRATE_MIGRATIONS_PATH folder (./migrations in our case). <name-of-script> is the name we want the migration script file to have when it is created.

To create a migration script to seed user data, run:

npx migrate create seed-users

The command will create a file in the ./migrations folder with a name in the form -<timestamp>-seed-users.ts. The file will have the following code snippet content:

// Import your models here

export async function up (): Promise<void> {
  // Write migration here
}

export async function down (): Promise<void> {
  // Write migration here
}

The up function is used to run the migration. The down function is used to reverse whatever the up function executes, if need be. In our case, we are trying to seed users into the database. The up function will contain code to seed users into the database and the down function will contain code to delete users created in the up function.

If the database is inspected with MongoDB Compass, the migrations collection will have a document that looks like this:

{
  "_id": ObjectId("6744740465519c3bd9c1a7d1"),
  "name": "seed-users",
  "state": "down",
  "createdAt": 2024-11-25T12:56:36.316+00:00,
  "updatedAt": 2024-11-25T12:56:36.316+00:00,
  "__v": 0
}

The state field of the migration document is set to down. After it runs successfully, it changes to up.

You can update the code in ./migrations/<timestamp>-seed-users.ts to the one in the snippet below:

require("dotenv").config() // load env variables
const db = require("../db.js")
const { UserModel } = require("../user.model.js");

const seedUsers = [
  { email: "john@email.com", favouriteEmoji: "🏃", yearOfBirth: 1997 },
  { email: "jane@email.com", favouriteEmoji: "🍏", yearOfBirth: 1998 },
];

export async function up (): Promise<void> {
  await db.connect(process.env.MONGO_URI)
  await UserModel.create(seedUsers);}

export async function down (): Promise<void> {
  await db.connect(process.env.MONGO_URI)
  await UserModel.delete({
    email: {
      $in: seedUsers.map((u) => u.email),
    },
  });
}

3. Run the Migration Before the Application Starts

ts-migrate-mongoose provides us with CLI commands to run the up and down function of migration scripts.

With npx migrate up <name-of-script> we can run the up function of a specific script. With npx migrate up we can run the up function of all scripts in the ./migrations folder with a state of down in the database.

To run the migration before the application starts, we make use of npm scripts. npm scripts with a prefix of pre will run before a script without the pre prefix. For example, if there is a dev script and a predev script, whenever the dev script is run with npm run dev, the predev script will automatically run before the dev script is run.

We will use this feature of npm scripts to place the ts-migrate-mongoose command in a prestart script so that the migration will run before the start script.

Update the package.json file to have a prestart script that runs the ts-migrate-mongoose command for running the up function of migration scripts in the project.

  "scripts": {
    "prestart": "npx migrate up",
    "start": "node index.js"
  },

With this setup, when npm run start is executed to start the application, the prestart script will run to execute the migration using ts-migrate-mongoose and seed the database before the application starts.

You should have something similar to the snippet below after running npm run start:

Synchronizing database with file system migrations...
MongoDB connection successful
up: 1732543529744-seed-users.ts 
All migrations finished successfully

> ts-migrate-mongoose-starter-repo@1.0.0 start
> node index.js

MongoDB connection successful                      
Server listening on port 8000

Check out the seed-users branch of the repository to see the current status of the codebase at this point in the article.

How to Build an API Endpoint to Fetch Seeded Data

We can build an API endpoint to fetch the seeded users data in our database. In the server.js file, update the code to the one in the snippet below:

const { UserModel } = require("./user.model.js")

module.exports = async function (req, res) {
  const users = await UserModel.find({}) // fetch all the users in the database

  res.writeHead(200, { "Content-Type": "application/json" });
  return res.end(JSON.stringify({ // return a JSON representation of the fetched users data
    users: users.map((u) => ({
      email: u.email,
      favouriteEmoji: u.favouriteEmoji,
      yearOfBirth: u.yearOfBirth,
      createdAt: u.createdAt
    }))
  }, null, 2));
};

If we start the application and visit http://localhost:8000 using Postman or a browser, we get a JSON response similar to the one below:

{
  "users": [
    {
      "email": "john@email.com",
      "favouriteEmoji": "🏃",
      "yearOfBirth": 1997,
      "createdAt": "2024-11-25T14:18:55.416Z"
    },
    {
      "email": "jane@email.com",
      "favouriteEmoji": "🍏",
      "yearOfBirth": 1998,
      "createdAt": "2024-11-25T14:18:55.416Z"
    }
  ]
}

Notice that if the application is run again, the migration script does not run anymore because the state of the migration will now be up after it has run successfully.

Check out the fetch-users branch of the repository to see the current status of the codebase at this point in the article.

Conclusion

Migrations are useful when building applications and there is need to seed initial data for testing, seeding administrative users, updating database schema by adding or removing columns and updating the values of columns in many records at once.

ts-migrate-mongoose can help provide a framework for running migrations for your Node.js applications if you use Mongoose with MongoDB.

Object-relational mapping, as a programming concept, is an efficient standard protocol for facilitating seamless connection with databases. But what does it really mean, and how do you set it up as a developer? We’ll answer these questions and highlight more about object-relational mapping.

Here are the prerequisites:

• Knowledge of Node.js

• Use the Express framework

• An installed MySQL database

Table of Contents

• Table of Contents

• What is an ORM?

• How to Set Up Your Node.js Server

• How to Integrate Relevant Packages

• Demo Project

• Additional Information

What is an ORM?

Object Relational Mapping (ORM) is a database communication concept in programming that involves the abstraction of data types as compatible object-oriented programming variables. It simply eliminates the use of database-defined queries and storage types to allow ease of creating databases via the programming languages.

Its use has been widely adopted in the tech space as has more advantages than conventional database query methods. Here are some of them:

• It reduces the risk of data manipulation: SQL and non-SQL injections involve inputting malicious SQL syntaxes and queries into the database, which can compromise database security. Having an ORM in place adds an input validation scheme feature, and details the expected input variable syntax and processes it accordingly.

• Ease of database communication: ORM serves to simplify the use of databases as a data tool without undergoing the process of learning a different database query language. The ORM schema can be highlighted in an object-oriented fashion in the application language and can be configured to automatically translate the code to queries compatible with the database.

• This feature also allows easy code portability, achieving maintenance of a single database integration code base while changing the database without any adverse outcome. It is highly flexible and can be used in any database of choice.

• It also has additional features included to allow database interactions. Database migration features and version control processes are provided. With these, we have seen some of its benefits, we will then highlight popular ORM tools used globally.

Here are the popular ORM tools:

• SQLAlchemy

• Prisma ORM

• Sequelize

• ActiveRecord

• TypeORM

• Waterline

For this article, we’ll be streamlining our ORM use cases to a basic Node.js project linked to a MySQL database. We’ll use the Sequelize ORM as the tool of choice.

With an average package download of 8.5 million monthly and an active development community, Sequelize boasts robust features that seamlessly integrate databases with backend applications. It also provides a user oriented documentation which helps guide the user on setting up and using the tool.

Here is a link to the documentation. It also offers support for MySQL, DB2, and SQLite Microsoft SQL server, and it offers features such as read replication, lazy loading, and efficient database transaction properties.

Next, we’ll set up our web application and install Sequelize to connect us to a MySQL database hosted locally.

How to Set Up Your Node.js Server

In this section you’ll set up our Node server. Navigate to the command line and execute npm init. This command creates a new Node project structure for you.

Next, install the Express package – this will serve as the backend framework. You can do this by running the npm i express command.

How to Integrate Relevant Packages

For the purpose of this tutorial, we’ll install the Sequelize Node package manager in our Node application in order to set up the ORM communication to the database.

To set this up, execute npm i sequelize.

We’ll use a locally hosted MySQL database. To do this, we’ll install an npm package database driver. In this case, we will be installing mysql2. Here is a link to the package

Run npm i mysql2 to install it.

Let’s move on to configuring the connection to the database and building our demo project.

Demo Project

In this section we’ll build a simple backend server that performs Create-Read-Update-Delete operations, with the Sequelize library serving as the connection pipeline.

In order to begin the project, we’ll have to set up the database connection for our application. We’ll create a database connection file and set up our database credentials. You can name the file SequelizeConfig.

module.exports = {

    HOST: "localhost",

    USER: "root",

    PASSWORD: "",

    DB: "sequel",

    dialect: "mysql"

}

In the code above, the database credentials were specified, along with the host address. In our case, the database is locally hosted, so localhost is the default host.

The database login details were also provided. The user here is the root, while the password was set to an empty string. This should be tweaked to ensure database security. I also created a defunct database named “sequel”.

The dialect refers to the type of database the user intends to use. In our case, the dialect is MySQL. Note that this can also be replicated on a cloud hosted database with the credentials obtained. With that, let's integrate the connection file with the application.

const SequelConfig = require('../config/sequelize');

const Sequelize = require('sequelize');

const sequelize = new Sequelize(SequelCOnfig.DB, SequelCOnfig.USER, SequelCOnfig.PASSWORD, {

    host: SequelCOnfig.HOST,

    dialect: SequelCOnfig.dialect

});

In order to facilitate a connection to the database, the variables in the config file were imported and initialized in the Sequelize setup file.

const db = {};

db.Sequelize = Sequelize;

db.sequelize = sequelize;

db.user = require('../model/user.model')(sequelize, Sequelize);

db.token = require('../model/token.model')(sequelize, Sequelize)

module.exports= db;

This file above imports the config file created previously and initializes the Sequelize library. The code then fetches the database details inputted in the config file and, when executed, creates the database.

Furthermore, the various database models which will be discussed subsequently are then integrated with the defunct database and generates a SQL database table .

To get this up and running, the database file created is invoked using the sequelize.sync() method. Any error encountered is logged and the database connection gets terminated.

db.sequelize.sync().then(() => {

  console.log('user created ');

}).catch(err => {

  console.error(err)

})

We’ll go on to discuss the database models.

Models

const Sequelize = require("sequelize");

module.exports = (sequelize) => {

sequelize.define(

"user", {

firstName: {

type : Sequelize.DataTypes.STRING,

allowNull : false

},

lastName: {

type : Sequelize.DataTypes.STRING,

allowNull : false

},

email : {

type : Sequelize.DataTypes.STRING,

allowNull : false, unique: true

},

password: {

type : Sequelize.DataTypes.STRING,

allowNull : false

},

role:  {

type : Sequelize.DataTypes.STRING,

allowNull : false

}

}

)

}

In the code above, the user model was initialized in Sequelize ORM and the field details were specified: email, role, lastName, and password. The type of data to be received was also specified.

It also provides an option to ensure the uniqueness of the user details, and the option to prevent the user from leaving some fields empty via the use of allowNull = false.

On execution of the application, the Sequelize ORM creates an SQL equivalent of the model as a data table.

Next, we’ll work on the CRUD functions in Node.js.

Create Operation

const createUser = async (userInfo) => {

try {

// Check if the email already exists in the database

const ifEmailExists = await User.findOne({ where: { email: userInfo.email } });

if (ifEmailExists) {

throw new ApiError('Email has already been registered');

}

// Create the new user

const newUser = await User.create(userInfo);

return newUser; // Return the created user object

} catch (error) {

// Handle errors such as validation or uniqueness constraint

throw error;

}

};

The function above highlights the controller function for creating user entries in the Express server.

The function is asynchronous, which allows for execution of some commands before eventual execution. The code ensures that the user email doesn’t exist in the database before cresting a new user.

In addition, we also ensured that each email field is unique. If the user details are entered into the database successfully, a “successful” response is sent back to the server. Additionally, any error encountered leads to termination of the function and the error gets sent back to the server.

Read Operation

const FetchUser = async (userId) => {

let userDets;

if (userId) {

// Fetch a single user by ID if userId is provided

userDets = await User.findOne({ where: { id: userId } });

// Check if the user exists

if (!userDets) {

throw new ApiError(httpStatus.NOT_FOUND, 'User not found');

}

} else {

// Fetch all users if no userId is provided

userDets = await User.findAll();

// Check if any users were found

if (userDets.length === 0) {

throw new ApiError(httpStatus.NOT_FOUND, 'No users found');

}

}

The read operation fetches the desired query and sends it back to the user without modification. The user ID, which should be unique, is used to search for a specific user. In this scenario, we want access to all the users created in the database.

In case the requested query is not found, an appropriate error code is generated.

Update Operation

const updateUser = async (userId, userDetails) => {

// First, find the user by their ID

const user = await User.findOne({ where: { id: userId } });

if (!user) {

throw new ApiError(httpStatus.BAD_REQUEST, "User doesn't exist");

}

// Update the user with the new details

await User.update(userDetails, { where: { id: userId } });

// Fetch the updated user to return it

const updatedUser = await User.findOne({ where: { id: userId } });

console.log('Updated user:', updatedUser); // Log the updated user

return updatedUser; // Return the updated user object

};

The update operation aims to modify the data entered in previous operations. That is, to update some data fields.

In the case of Sequelize, the update method is invoked. To succeed with this, the particular user to be edited must be identified. The code above then generates the updated data field and sends it as the output of a successful request.

Delete Operation

const deleteUser = async (userId) => {

const user = await User.findOne({ where: { id: userId } });

if (!user) {

throw new ApiError(httpStatus.BAD_REQUEST, "User doesn't exist");

}

// Delete the user

await user.destroy();

console.log('Deleted user:', user); // Log the deleted user

return user; // Return the deleted user object (useful for confirmation)

};

The delete operation is invoked when data in the database table needs to be deleted. Sequelize makes provision for this via the use of the destroy method. This method deletes a specific user. When executed, a success response code is displayed.

Additional Information

So far, we have integrated an ORM library to serve as a connection between our backend application and our relational database. We also explored advanced concepts such as database migrations and CRUD operations. To learn more about this, you can explore the documentation and utilize it in building more complex projects, as hands-on learning is much encouraged.

Feel free to reach out to me on my blog and check out my other articles here. Till next time, keep on coding!

Instead, it stores everything in a simple file on your computer. It also requires zero configuration, so there’s no complicated setup process, making it perfect for beginners and small projects.

SQLite is a great choice for small to medium applications because it’s easy to use, fast, and can handle most tasks that bigger databases can do, but without the hassle of managing extra software. Whether you're building a personal project or prototyping a new app, SQLite is a solid option to get things up and running quickly.

In this tutorial, you'll learn how to work with SQLite using Python. Here’s what we’re going to cover in this tutorial:

• How to Set Up Your Python Environment

• How to Create an SQLite Database

• How to Create Database Tables

• How to Insert Data into a Table

• How to Query Data

• How to Update and Delete Data

• How to Use Transactions

• How to Optimize SQLite Query Performance with Indexing

• How to Handle Errors and Exceptions

• How to Export and Import Data [Bonus Section]

• Wrapping Up

This tutorial is perfect for anyone who wants to get started with databases without diving into complex setups.

How to Set Up Your Python Environment

Before working with SQLite, let’s ensure your Python environment is ready. Here’s how to set everything up.

Installing Python

If you don’t have Python installed on your system yet, you can download it from the official Python website. Follow the installation instructions for your operating system (Windows, macOS, or Linux).

To check if Python is installed, open your terminal (or command prompt) and type:

python --version

This should show the current version of Python installed. If it’s not installed, follow the instructions on the Python website.

Installing SQLite3 Module

The good news is that SQLite3 comes built-in with Python! You don’t need to install it separately because it’s included in the standard Python library. This means you can start using it right away without any additional setup.

How to Create a Virtual Environment (Optional but Recommended)

It’s a good idea to create a virtual environment for each project to keep your dependencies organized. A virtual environment is like a clean slate where you can install packages without affecting your global Python installation.

To create a virtual environment, follow these steps:

1. First, open your terminal or command prompt and navigate to the directory where you want to create your project.

2. Run the following command to create a virtual environment:

python -m venv env

Here, env is the name of the virtual environment. You can name it anything you like.

3. Activate the virtual environment:

# Use the command for Windows
env\Scripts\activate

# Use the command for macOS/Linux:
env/bin/activate

After activating the virtual environment, you’ll notice that your terminal prompt changes, showing the name of the virtual environment. This means you’re now working inside it.

Installing Necessary Libraries

We’ll need a few additional libraries for this project. Specifically, we’ll use:

• pandas: This is an optional library for handling and displaying data in tabular format, useful for advanced use cases.

• faker: This library will help us generate fake data, like random names and addresses, which we can insert into our database for testing.

To install pandas and faker, simply run the following commands:

pip install pandas faker

This installs both pandas and faker into your virtual environment. With this, your environment is set up, and you’re ready to start creating and managing your SQLite database in Python!

How to Create an SQLite Database

A database is a structured way to store and manage data so that it can be easily accessed, updated, and organized. It’s like a digital filing system that allows you to efficiently store large amounts of data, whether it’s for a simple app or a more complex system. Databases use tables to organize data, with rows and columns representing individual records and their attributes.

How SQLite Databases Work

Unlike most other database systems, SQLite is a serverless database. This means that it doesn’t require setting up or managing a server, making it lightweight and easy to use. All the data is stored in a single file on your computer, which you can easily move, share, or back up. Despite its simplicity, SQLite is powerful enough to handle many common database tasks and is widely used in mobile apps, embedded systems, and small to medium-sized projects.

How to Create a New SQLite Database

Let’s create a new SQLite database and learn how to interact with it using Python’s sqlite3 library.

Connecting to the Database

Since sqlite3 is pre-installed, you just need to import it in your Python script. To create a new database or connect to an existing one, we use the sqlite3.connect() method. This method takes the name of the database file as an argument. If the file doesn’t exist, SQLite will automatically create it.

import sqlite3

# Connect to the SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('my_database.db')

In this example, a file named my_database.db is created in the same directory as your script. If the file already exists, SQLite will just open the connection to it.

Creating a Cursor

Once you have a connection, the next step is to create a cursor object. The cursor is responsible for executing SQL commands and queries on the database.

# Create a cursor object
cursor = connection.cursor()

Closing the Connection

After you’ve finished working with the database, it’s important to close the connection to free up any resources. You can close the connection with the following command:

# Close the database connection
connection.close()

However, you should only close the connection once you’re done with all your operations.

When you run your Python script, a file named my_database.db will be created in your current working directory. You’ve now successfully created your first SQLite database!

How to Use Context Manager to Open and Close Connections

Python provides a more efficient and cleaner way to handle database connections using the with statement, also known as a context manager. The with statement automatically opens and closes the connection, ensuring that the connection is properly closed even if an error occurs during the database operations. This eliminates the need to manually call connection.close().

Here’s how you can use the with statement to handle database connections:

import sqlite3

# Step 1: Use 'with' to connect to the database (or create one) and automatically close it when done
with sqlite3.connect('my_database.db') as connection:

    # Step 2: Create a cursor object to interact with the database
    cursor = connection.cursor()

    print("Database created and connected successfully!")

# No need to call connection.close(); it's done automatically!

From now on, we’ll use the with statement in our upcoming code examples to manage database connections efficiently. This will make the code more concise and easier to maintain.

How to Create Database Tables

Now that we’ve created an SQLite database and connected to it, the next step is to create tables inside the database. A table is where we’ll store our data, organized in rows (records) and columns (attributes). For this example, we’ll create a table called Students to store information about students, which we’ll reuse in upcoming sections.

To create a table, we use SQL's CREATE TABLE statement. This command defines the table structure, including the column names and the data types for each column.

Here’s a simple SQL command to create a Students table with the following fields:

• id: A unique identifier for each student (an integer).

• name: The student's name (text).

• age: The student's age (an integer).

• email: The student's email address (text).

The SQL command to create this table would look like this:

CREATE TABLE Students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);

We can execute this CREATE TABLE SQL command in Python using the sqlite3 library. Let’s see how to do that.

import sqlite3

# Use 'with' to connect to the SQLite database and automatically close the connection when done
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to create the Students table
    create_table_query = '''
    CREATE TABLE IF NOT EXISTS Students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    );
    '''

    # Execute the SQL command
    cursor.execute(create_table_query)

    # Commit the changes
    connection.commit()

    # Print a confirmation message
    print("Table 'Students' created successfully!")

• IF NOT EXISTS: This ensures that the table is only created if it doesn’t already exist, preventing errors if the table has been created before.

• connection.commit(): This saves (commits) the changes to the database.

When you run the Python code above, it will create the Students table in the my_database.db database file. You’ll also see a message in the terminal confirming that the table has been created successfully.

If you’re using Visual Studio Code, you can install the SQLite Viewer extension to view SQLite databases.

Data Types in SQLite and Their Mapping to Python

SQLite supports several data types, which we need to understand when defining our tables. Here’s a quick overview of common SQLite data types and how they map to Python types:

In our Students table:

• id is of type INTEGER, which maps to Python’s int.

• name and email are of type TEXT, which map to Python’s str.

• age is also of type INTEGER, mapping to Python’s int.

How to Insert Data into a Table

Now that we have our Students table created, it’s time to start inserting data into the database. In this section, we’ll cover how to insert both single and multiple records using Python and SQLite, and how to avoid common security issues like SQL injection by using parameterized queries.

How to Insert a Single Record

To insert data into the database, we use the INSERT INTO SQL command. Let’s start by inserting a single record into our Students table.

Here’s the basic SQL syntax for inserting a single record:

INSERT INTO Students (name, age, email) 
VALUES ('John Doe', 20, 'johndoe@example.com');

However, instead of writing SQL directly in our Python script with hardcoded values, we’ll use parameterized queries to make our code more secure and flexible. Parameterized queries help prevent SQL injection, a common attack where malicious users can manipulate the SQL query by passing harmful input.

Here’s how we can insert a single record into the Students table using a parameterized query:

import sqlite3

# Use 'with' to open and close the connection automatically
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Insert a record into the Students table
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    student_data = ('Jane Doe', 23, 'jane@example.com')

    cursor.execute(insert_query, student_data)

    # Commit the changes automatically
    connection.commit()

    # No need to call connection.close(); it's done automatically!
    print("Record inserted successfully!")

The ? placeholders represent the values to be inserted into the table. The actual values are passed as a tuple (student_data) in the cursor.execute() method.

How to Insert Multiple Records

If you want to insert multiple records at once, you can use the executemany() method in Python. This method takes a list of tuples, where each tuple represents one record.

To make our example more dynamic, we can use the Faker library to generate random student data. This is useful for testing and simulating real-world scenarios.

from faker import Faker
import sqlite3

# Initialize Faker
fake = Faker(['en_IN'])

# Use 'with' to open and close the connection automatically
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Insert a record into the Students table
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    students_data = [(fake.name(), fake.random_int(
        min=18, max=25), fake.email()) for _ in range(5)]

    # Execute the query for multiple records
    cursor.executemany(insert_query, students_data)

    # Commit the changes
    connection.commit()

    # Print confirmation message
    print("Fake student records inserted successfully!")

In this code:

• Faker() generates random names, ages, and emails for students. Passing the locale([‘en_IN’]) is optional.

• cursor.executemany(): This method allows us to insert multiple records at once, making the code more efficient.

• students_data: A list of tuples where each tuple represents one student’s data.

How to Handle Common Issues: SQL Injection

SQL injection is a security vulnerability where attackers can insert or manipulate SQL queries by providing harmful input. For example, an attacker might try to inject code like '; DROP TABLE Students; -- to delete the table.

By using parameterized queries (as demonstrated above), we avoid this issue. The ? placeholders in parameterized queries ensure that input values are treated as data, not as part of the SQL command. This makes it impossible for malicious code to be executed.

How to Query Data

Now that we’ve inserted some data into our Students table, let’s learn how to retrieve the data from the table. We'll explore different methods for fetching data in Python, including fetchone(), fetchall(), and fetchmany().

To query data from a table, we use the SELECT statement. Here’s a simple SQL command to select all columns from the Students table:

SELECT * FROM Students;

This command retrieves all records and columns from the Students table. We can execute this SELECT query in Python and fetch the results.

How to Fetch All Records

Here’s how we can fetch all records from the Students table:

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Execute the SQL command
    cursor.execute(select_query)

    # Fetch all records
    all_students = cursor.fetchall()

    # Display results in the terminal
    print("All Students:")
    for student in all_students:
        print(student)

In this example, the fetchall() method retrieves all rows returned by the query as a list of tuples.

All Students:
(1, 'Jane Doe', 23, 'jane@example.com')
(2, 'Bahadurjit Sabharwal', 18, 'tristanupadhyay@example.net')
(3, 'Zayyan Arya', 20, 'yashawinibhakta@example.org')
(4, 'Hemani Shukla', 18, 'gaurikanarula@example.com')
(5, 'Warda Kara', 20, 'npatil@example.net')
(6, 'Mitali Nazareth', 19, 'sparekh@example.org')

How to Fetch a Single Record

If you want to retrieve only one record, you can use the fetchone() method:

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Execute the SQL command
    cursor.execute(select_query)

    # Fetch one record
    student = cursor.fetchone()

    # Display the result
    print("First Student:")
    print(student)

Output:

First Student:
(1, 'Jane Doe', 23, 'jane@example.com')

How to Fetch Multiple Records

To fetch a specific number of records, you can use fetchmany(size):

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Create a cursor object
    cursor = connection.cursor()

    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Execute the SQL command
    cursor.execute(select_query)

    # Fetch three records
    three_students = cursor.fetchmany(3)

    # Display results
    print("Three Students:")
    for student in three_students:
        print(student)

Output:

Three Students:
(1, 'Jane Doe', 23, 'jane@example.com')
(2, 'Bahadurjit Sabharwal', 18, 'tristanupadhyay@example.net')
(3, 'Zayyan Arya', 20, 'yashawinibhakta@example.org')

How to Use pandas for Better Data Presentation

For better data presentation, we can use the pandas library to create a DataFrame from our query results. This makes it easier to manipulate and visualize the data.

Here’s how to fetch all records and display them as a pandas DataFrame:

import sqlite3
import pandas as pd

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:
    # Write the SQL command to select all records from the Students table
    select_query = "SELECT * FROM Students;"

    # Use pandas to read SQL query directly into a DataFrame
    df = pd.read_sql_query(select_query, connection)

# Display the DataFrame
print("All Students as DataFrame:")
print(df)

Output:

All Students as DataFrame:
   id                  name  age                        email
0   1              Jane Doe   23             jane@example.com
1   2  Bahadurjit Sabharwal   18  tristanupadhyay@example.net
2   3           Zayyan Arya   20  yashawinibhakta@example.org
3   4         Hemani Shukla   18    gaurikanarula@example.com
4   5            Warda Kara   20           npatil@example.net
5   6       Mitali Nazareth   19          sparekh@example.org

The pd.read_sql_query() function executes the SQL query and directly returns the results as a pandas DataFrame.

How to Update and Delete Data

In this section, we’ll learn how to update existing records and delete records from our Students table using SQL commands in Python. This is essential for managing and maintaining your data effectively.

Updating Existing Records

To modify existing records in a database, we use the SQL UPDATE command. This command allows us to change the values of specific columns in one or more rows based on a specified condition.

For example, if we want to update a student's age, the SQL command would look like this:

UPDATE Students 
SET age = 21 
WHERE name = 'Jane Doe';

Now, let’s write Python code to update a specific student's age in our Students table.

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL command to update a student's age
    update_query = '''
    UPDATE Students 
    SET age = ? 
    WHERE name = ?;
    '''

    # Data for the update
    new_age = 21
    student_name = 'Jane Doe'

    # Execute the SQL command with the data
    cursor.execute(update_query, (new_age, student_name))

    # Commit the changes to save the update
    connection.commit()

    # Print a confirmation message
    print(f"Updated age for {student_name} to {new_age}.")

In this example, we used parameterized queries to prevent SQL injection.

How to Delete Records from the Table

To remove records from a database, we use the SQL DELETE command. This command allows us to delete one or more rows based on a specified condition.

For example, if we want to delete a student named 'Jane Doe', the SQL command would look like this:

DELETE FROM Students 
WHERE name = 'Jane Doe';

Let’s write Python code to delete a specific student from our Students table using the with statement.

import sqlite3

# Use 'with' to connect to the SQLite database
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL command to delete a student
    delete_query = '''
    DELETE FROM Students 
    WHERE name = ?;
    '''

    # Name of the student to be deleted
    student_name = 'Jane Doe'

    # Execute the SQL command with the data
    cursor.execute(delete_query, (student_name,))

    # Commit the changes to save the deletion
    connection.commit()

    # Print a confirmation message
    print(f"Deleted student record for {student_name}.")

Important Considerations

• Conditions: Always use the WHERE clause when updating or deleting records to avoid modifying or removing all rows in the table. Without a WHERE clause, the command affects every row in the table.

  

• Backup: It’s good practice to back up your database before performing updates or deletions, especially in production environments.

How to Use Transactions

A transaction is a sequence of one or more SQL operations that are treated as a single unit of work. In the context of a database, a transaction allows you to perform multiple operations that either all succeed or none at all. This ensures that your database remains in a consistent state, even in the face of errors or unexpected issues.

For example, if you are transferring money between two bank accounts, you would want both the debit from one account and the credit to the other to succeed or fail together. If one operation fails, the other should not be executed to maintain consistency.

Why Use Transactions?

1. Atomicity: Transactions ensure that a series of operations are treated as a single unit. If one operation fails, none of the operations will be applied to the database.

2. Consistency: Transactions help maintain the integrity of the database by ensuring that all rules and constraints are followed.

3. Isolation: Each transaction operates independently of others, preventing unintended interference.

4. Durability: Once a transaction is committed, the changes are permanent, even in the event of a system failure.

When to Use Transactions?

You should use transactions when:

• Performing multiple related operations that must succeed or fail together.

• Modifying critical data that requires consistency and integrity.

• Working with operations that can potentially fail, such as financial transactions or data migrations.

How to Manage Transactions in Python

In SQLite, transactions are managed using the BEGIN, COMMIT, and ROLLBACK commands. However, when using the sqlite3 module in Python, you typically manage transactions through the connection object.

Starting a Transaction

A transaction begins implicitly when you execute any SQL statement. To start a transaction explicitly, you can use the BEGIN command:

cursor.execute("BEGIN;")

However, it’s usually unnecessary to start a transaction manually, as SQLite starts a transaction automatically when you execute an SQL statement.

How to Commit a Transaction

To save all changes made during a transaction, you use the commit() method. This makes all modifications permanent in the database.

connection.commit()

We have already used the commit() method in the above provided examples.

Rolling Back a Transaction

If something goes wrong and you want to revert the changes made during a transaction, you can use the rollback() method. This will undo all changes made since the transaction started.

connection.rollback()

Example of Using Transactions in Python

To illustrate the use of transactions in a real-world scenario, we’ll create a new table called Customers to manage customer accounts. In this example, we’ll assume each customer has a balance. We will add two customers to this table and perform a funds transfer operation between them.

First, let's create the Customers table and insert two customers:

import sqlite3

# Create the Customers table and add two customers
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Create Customers table
    create_customers_table = '''
    CREATE TABLE IF NOT EXISTS Customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        balance REAL NOT NULL
    );
    '''
    cursor.execute(create_customers_table)

    # Insert two customers
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))

    connection.commit()

Now, let’s perform the funds transfer operation between Ashutosh and Krishna:

import sqlite3


def transfer_funds(from_customer, to_customer, amount):
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        try:
            # Start a transaction
            cursor.execute("BEGIN;")

            # Deduct amount from the sender
            cursor.execute(
                "UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
            # Add amount to the receiver
            cursor.execute(
                "UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))

            # Commit the changes
            connection.commit()
            print(
                f"Transferred {amount} from {from_customer} to {to_customer}.")

        except Exception as e:
            # If an error occurs, rollback the transaction
            connection.rollback()
            print(f"Transaction failed: {e}")


# Example usage
transfer_funds('Ashutosh', 'Krishna', 80.0)

In this example, we first created a Customers table and inserted two customers, Ashutosh with a balance of ₹100, and Krishna with a balance of ₹50. We then performed a funds transfer of ₹80 from Ashutosh to Krishna. By using transactions, we ensure that both the debit from Ashutosh's account and the credit to Krishna's account are executed as a single atomic operation, maintaining data integrity in the event of any errors. If the transfer fails (for example, due to insufficient funds), the transaction will roll back, leaving both accounts unchanged.

How to Optimize SQLite Query Performance with Indexing

Indexing is a powerful technique used in databases to improve query performance. An index is essentially a data structure that stores the location of rows based on specific column values, much like an index at the back of a book helps you quickly locate a topic.

Without an index, SQLite has to scan the entire table row by row to find the relevant data, which becomes inefficient as the dataset grows. By using an index, SQLite can jump directly to the rows you need, significantly speeding up query execution.

How to Populate the Database with Fake Data

To effectively test the impact of indexing, we need a sizable dataset. Instead of manually adding records, we can use the faker library to quickly generate fake data. In this section, we’ll generate 10,000 fake records and insert them into our Students table. This will simulate a real-world scenario where databases grow large, and query performance becomes important.

We will use the executemany() method to insert the records as below:

import sqlite3
from faker import Faker

# Initialize the Faker library
fake = Faker(['en_IN'])


def insert_fake_students(num_records):
    """Generate and insert fake student data into the Students table."""
    fake_data = [(fake.name(), fake.random_int(min=18, max=25),
                  fake.email()) for _ in range(num_records)]

    # Use 'with' to handle the database connection
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Insert fake data into the Students table
        cursor.executemany('''
        INSERT INTO Students (name, age, email) 
        VALUES (?, ?, ?);
        ''', fake_data)

        connection.commit()

    print(f"{num_records} fake student records inserted successfully.")


# Insert 10,000 fake records into the Students table
insert_fake_students(10000)

By running this script, 10,000 fake student records will be added to the Students table. In the next section, we'll query the database and compare the performance of queries with and without indexing.

How to Query Without Indexes

In this section, we’ll query the Students table without any indexes to observe how SQLite performs when there are no optimizations in place. This will serve as a baseline to compare the performance when we add indexes later.

Without indexes, SQLite performs a full table scan, which means that it must check every row in the table to find matching results. For small datasets, this is manageable, but as the number of records grows, the time taken to search increases dramatically. Let’s see this in action by running a basic SELECT query to search for a specific student by name and measure how long it takes.

First, we’ll query the Students table by looking for a student with a specific name. We’ll log the time taken to execute the query using Python’s time module to measure the performance.

import sqlite3
import time


def query_without_index(search_name):
    """Query the Students table by name without an index and measure the time taken."""

    # Connect to the database using 'with'
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Measure the start time
        start_time = time.perf_counter_ns()

        # Perform a SELECT query to find a student by name
        cursor.execute('''
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Fetch all results (there should be only one or a few in practice)
        results = cursor.fetchall()

        # Measure the end time
        end_time = time.perf_counter_ns()

        # Calculate the total time taken
        elapsed_time = (end_time - start_time) / 1000

        # Display the results and the time taken
        print(f"Query completed in {elapsed_time:.5f} microseconds.")
        print("Results:", results)


# Example: Searching for a student by name
query_without_index('Ojasvi Dhawan')

Here’s the output:

Query completed in 1578.10000 microseconds.
Results: [(104, 'Ojasvi Dhawan', 21, 'lavanya26@example.com')]

By running the above script, you'll see how long it takes to search the Students table without any indexes. For example, if there are 10,000 records in the table, the query might take 1000-2000 microseconds depending on the size of the table and your hardware. This may not seem too slow for a small dataset, but the performance will degrade as more records are added.

We use time.perf_counter_ns() to measure the time taken for the query execution in nanoseconds. This method is highly accurate for benchmarking small time intervals. We convert the time to microseconds(us) for easier readability.

Introducing the Query Plan

When working with databases, understanding how queries are executed can help you identify performance bottlenecks and optimize your code. SQLite provides a helpful tool for this called EXPLAIN QUERY PLAN, which allows you to analyze the steps SQLite takes to retrieve data.

In this section, we’ll introduce how to use EXPLAIN QUERY PLAN to visualize and understand the inner workings of a query—specifically, how SQLite performs a full table scan when no index is present.

Let’s use EXPLAIN QUERY PLAN to see how SQLite retrieves data from the Students table without any indexes. We’ll search for a student by name, and the query plan will reveal the steps SQLite takes to find the matching rows.

import sqlite3


def explain_query(search_name):
    """Explain the query execution plan for a SELECT query without an index."""

    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Use EXPLAIN QUERY PLAN to analyze how the query is executed
        cursor.execute('''
        EXPLAIN QUERY PLAN
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Fetch and display the query plan
        query_plan = cursor.fetchall()

        print("Query Plan:")
        for step in query_plan:
            print(step)


# Example: Analyzing the query plan for searching by name
explain_query('Ojasvi Dhawan')

When you run this code, SQLite will return a breakdown of how it plans to execute the query. Here’s an example of what the output might look like:

Query Plan:
(2, 0, 0, 'SCAN Students')

This indicates that SQLite is scanning the entire Students table (a full table scan) to find the rows where the name column matches the provided value (Ojasvi Dhawan). Since there is no index on the name column, SQLite must examine each row in the table.

How to Create an Index

Creating an index on a column allows SQLite to find rows more quickly during query operations. Instead of scanning the entire table, SQLite can use the index to jump directly to the relevant rows, significantly speeding up queries—especially those involving large datasets.

To create an index, use the following SQL command:

CREATE INDEX IF NOT EXISTS index-name ON table (column(s));

In this example, we will create an index on the name column of the Students table. Here’s how you can do it using Python:

import sqlite3
import time


def create_index():
    """Create an index on the name column of the Students table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQL command to create an index on the name column
        create_index_query = '''
        CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
        '''

        # Measure the start time
        start_time = time.perf_counter_ns()

        # Execute the SQL command to create the index
        cursor.execute(create_index_query)

        # Measure the start time
        end_time = time.perf_counter_ns()

        # Commit the changes
        connection.commit()

        print("Index on 'name' column created successfully!")

        # Calculate the total time taken
        elapsed_time = (end_time - start_time) / 1000

        # Display the results and the time taken
        print(f"Query completed in {elapsed_time:.5f} microseconds.")


# Call the function to create the index
create_index()

Output:

Index on 'name' column created successfully!
Query completed in 102768.60000 microseconds.

Even though creating the index takes this long (102768.6 microseconds), it's a one-time operation. You will still get substantial speed-up when running multiple queries. In the following sections, we will query the database again to observe the performance improvements made possible by this index.

How to Query with Indexes

In this section, we will perform the same SELECT query we executed earlier, but this time we will take advantage of the index we created on the name column of the Students table. We'll measure and log the execution time to observe the performance improvements provided by the index.

import sqlite3
import time


def query_with_index(student_name):
    """Query the Students table using an index on the name column."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQL command to select a student by name
        select_query = 'SELECT * FROM Students WHERE name = ?;'

        # Measure the execution time
        start_time = time.perf_counter_ns()  # Start the timer

        # Execute the query with the provided student name
        cursor.execute(select_query, (student_name,))
        result = cursor.fetchall()  # Fetch all results

        end_time = time.perf_counter_ns()  # End the timer

        # Calculate the elapsed time in microseconds
        execution_time = (end_time - start_time) / 1000

        # Display results and execution time
        print(f"Query result: {result}")
        print(f"Execution time with index: {execution_time:.5f} microseconds")


# Example: Searching for a student by name
query_with_index('Ojasvi Dhawan')

Here’s what we get in the output:

Query result: [(104, 'Ojasvi Dhawan', 21, 'lavanya26@example.com')]
Execution time with index: 390.70000 microseconds

We can observe a significant reduction in execution time compared to when the query was performed without an index.

Let’s analyze the query execution plan for the query with the index on the name column of the Students table. If you execute the same script again to explain the query, you’ll get the below output:

Query Plan:
(3, 0, 0, 'SEARCH Students USING INDEX idx_name (name=?)')

The plan now shows that the query uses the index idx_name, significantly reducing the number of rows that need to be scanned, which leads to faster query execution.

Comparing Performance Results

Now, let's summarize the performance results we obtained when querying with and without indexes.

Execution Time Comparison

Performance Improvement Summary

• The query with the index is approximately 4.04 times faster than the query without the index.

• The execution time improved by about 75.24% after adding the index.

Best Practices for Using Indexes

Indexes can significantly enhance the performance of your SQLite database, but they should be used judiciously. Here are some best practices to consider when working with indexes:

When and Why to Use Indexes

1. Frequent Query Columns: Use indexes on columns that are frequently used in SELECT queries, especially those used in WHERE, JOIN, and ORDER BY clauses. This is because indexing these columns can drastically reduce query execution time.

2. Uniqueness Constraints: When you have columns that must hold unique values (like usernames or email addresses), creating an index can enforce this constraint efficiently.

3. Large Datasets: For tables with a large number of records, indexes become increasingly beneficial. They enable quick lookups, which is essential for maintaining performance as your data grows.

4. Composite Indexes: Consider creating composite indexes for queries that filter or sort by multiple columns. For example, if you often search for students by both name and age, an index on both columns can optimize such queries.

Potential Downsides of Indexes

While indexes provide significant advantages, there are some potential downsides:

1. Slower Insert/Update Operations: When you insert or update records in a table with indexes, SQLite must also update the index, which can slow down these operations. This is because each insert or update requires additional overhead to maintain the index structure.

2. Increased Storage Requirements: Indexes consume additional disk space. For large tables, the storage cost can be substantial. Consider this when designing your database schema, especially for systems with limited storage resources.

3. Complex Index Management: Having too many indexes can complicate database management. It may lead to situations where you have redundant indexes, which can degrade performance rather than enhance it. Regularly reviewing and optimizing your indexes is a good practice.

Indexes are powerful tools for optimizing database queries, but they require careful consideration. Striking a balance between improved read performance and the potential overhead on write operations is key. Here are some strategies for achieving this balance:

• Monitor Query Performance: Use SQLite’s EXPLAIN QUERY PLAN to analyze how your queries perform with and without indexes. This can help identify which indexes are beneficial and which may be unnecessary.

• Regular Maintenance: Periodically review your indexes and assess whether they are still needed. Remove redundant or rarely used indexes to streamline your database operations.

• Test and Evaluate: Before implementing indexes in a production environment, conduct thorough testing to understand their impact on both read and write operations.

By following these best practices, you can leverage the benefits of indexing while minimizing potential drawbacks, ultimately enhancing the performance and efficiency of your SQLite database.

How to Handle Errors and Exceptions

In this section, we’ll discuss how to handle errors and exceptions when working with SQLite in Python. Proper error handling is crucial for maintaining the integrity of your database and ensuring that your application behaves predictably.

Common Errors in SQLite Operations

When interacting with an SQLite database, several common errors may arise:

1. Constraint Violations: This occurs when you try to insert or update data that violates a database constraint, such as primary key uniqueness or foreign key constraints. For example, trying to insert a duplicate primary key will trigger an error.

2. Data Type Mismatches: Attempting to insert data of the wrong type (for example, inserting a string where a number is expected) can lead to an error.

3. Database Locked Errors: If a database is being written to by another process or connection, trying to access it can result in a "database is locked" error.

4. Syntax Errors: Mistakes in your SQL syntax will result in errors when you try to execute your commands.

How to Use Python's Exception Handling

Python’s built-in exception handling mechanisms (try and except) are essential for managing errors in SQLite operations. By using these constructs, you can catch exceptions and respond appropriately without crashing your program.

Here’s a basic example of how to handle errors when inserting data into the database:

import sqlite3


def add_customer_with_error_handling(name, balance):
    """Add a new customer with error handling."""
    try:
        with sqlite3.connect('my_database.db') as connection:
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO Customers (name, balance) VALUES (?, ?);", (name, balance))
            connection.commit()
            print(f"Added customer: {name} with balance: {balance}")

    except sqlite3.IntegrityError as e:
        print(f"Error: Integrity constraint violated - {e}")

    except sqlite3.OperationalError as e:
        print(f"Error: Operational issue - {e}")

    except Exception as e:
        print(f"An unexpected error occurred: {e}")


# Example usage
add_customer_with_error_handling('Vishakha', 100.0)  # Valid
add_customer_with_error_handling('Vishakha', 150.0)  # Duplicate entry

In this example:

• We catch IntegrityError, which is raised for violations like unique constraints.

• We catch OperationalError for general database-related issues (like database locked errors).

• We also have a generic except block to handle any unexpected exceptions.

Output:

Added customer: Vishakha with balance: 100.0
Error: Integrity constraint violated - UNIQUE constraint failed: Customers.name

Best Practices for Ensuring Database Integrity

1. Use Transactions: Always use transactions (as discussed in the previous section) when performing multiple related operations. This helps ensure that either all operations succeed or none do, maintaining consistency.

2. Validate Input Data: Before executing SQL commands, validate the input data to ensure it meets the expected criteria (for example, correct types, within allowable ranges).

3. Catch Specific Exceptions: Always catch specific exceptions to handle different types of errors appropriately. This allows for clearer error handling and debugging.

4. Log Errors: Instead of just printing errors to the console, consider logging them to a file or monitoring system. This will help you track issues in production.

5. Graceful Degradation: Design your application to handle errors gracefully. If an operation fails, provide meaningful feedback to the user rather than crashing the application.

6. Regularly Backup Data: Regularly back up your database to prevent data loss in case of critical failures or corruption.

7. Use Prepared Statements: Prepared statements help prevent SQL injection attacks and can also provide better performance for repeated queries.

How to Export and Import Data [Bonus Section]

In this section, we will learn how to export data from an SQLite database to common formats like CSV and JSON, as well as how to import data into SQLite from these formats using Python. This is useful for data sharing, backup, and integration with other applications.

Exporting Data from SQLite to CSV

Exporting data to a CSV (Comma-Separated Values) file is straightforward with Python’s built-in libraries. CSV files are widely used for data storage and exchange, making them a convenient format for exporting data.

Here’s how to export data from an SQLite table to a CSV file:

import sqlite3
import csv

def export_to_csv(file_name):
    """Export data from the Customers table to a CSV file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Execute a query to fetch all customer data
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Write data to CSV
        with open(file_name, 'w', newline='') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(['ID', 'Name', 'Balance'])  # Writing header
            csv_writer.writerows(customers)  # Writing data rows

        print(f"Data exported successfully to {file_name}.")

# Example usage
export_to_csv('customers.csv')

How to Export Data to JSON

Similarly, you can export data to a JSON (JavaScript Object Notation) file, which is a popular format for data interchange, especially in web applications.

Here’s an example of how to export data to JSON:

import json
import sqlite3


def export_to_json(file_name):
    """Export data from the Customers table to a JSON file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Execute a query to fetch all customer data
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Convert data to a list of dictionaries
        customers_list = [{'ID': customer[0], 'Name': customer[1],
                           'Balance': customer[2]} for customer in customers]

        # Write data to JSON
        with open(file_name, 'w') as json_file:
            json.dump(customers_list, json_file, indent=4)

        print(f"Data exported successfully to {file_name}.")


# Example usage
export_to_json('customers.json')

How to Import Data into SQLite from CSV

You can also import data from a CSV file into an SQLite database. This is useful for populating your database with existing datasets.

Here's how to import data from a CSV file:

import csv
import sqlite3


def import_from_csv(file_name):
    """Import data from a CSV file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Open the CSV file for reading
        with open(file_name, 'r') as csv_file:
            csv_reader = csv.reader(csv_file)
            next(csv_reader)  # Skip the header row

            # Insert each row into the Customers table
            for row in csv_reader:
                cursor.execute(
                    "INSERT INTO Customers (name, balance) VALUES (?, ?);", (row[1], row[2]))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# Example usage
import_from_csv('customer_data.csv')

How to Import Data into SQLite from JSON

Similarly, importing data from a JSON file is simple. You can read the JSON file and insert the data into your SQLite table.

Here's how to do it:

import json
import sqlite3


def import_from_json(file_name):
    """Import data from a JSON file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Open the JSON file for reading
        with open(file_name, 'r') as json_file:
            customers_list = json.load(json_file)

            # Insert each customer into the Customers table
            for customer in customers_list:
                cursor.execute("INSERT INTO Customers (name, balance) VALUES (?, ?);", (customer['Name'], customer['Balance']))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# Example usage
import_from_json('customer_data.json')

Wrapping Up

And that’s a wrap! This guide has introduced you to the fundamentals of working with SQLite in Python, covering everything from setting up your environment to querying and manipulating data, as well as exporting and importing information. I hope you found it helpful and that it has sparked your interest in using SQLite for your projects.

Now it's time to put your newfound knowledge into practice! I encourage you to create your project using SQLite and Python. Whether it’s a simple application for managing your library, a budgeting tool, or something unique, the possibilities are endless.

Once you’ve completed your project, share it on Twitter and tag me! I’d love to see what you’ve created and celebrate your accomplishments.

You can find all the code from this tutorial on GitHub. Thank you for following along, and happy coding!

Generate Table of Contents for your freeCodeCamp articles for free using the TOC Generator tool.

In this tutorial, I’ll walk you through how to build highly performant web applications with Neon – a serverless PostgreSQL database designed for the cloud. You'll also learn how to perform CRUD (Create, Read, Update, and Delete) operations with Neon.

By the end of this tutorial, you will have the basic knowledge required to start building advanced and scalable web applications with Neon.

Table of Contents:

• What is Neon?
• Why Neon?
• How to add Neon to a Next.js app
• How to set up Neon Serverless Driver with Drizzle ORM in Next.js
• How to Build the Application Interface with Next.js
• How to Authenticate Users with Clerk
• CRUD Operations with the Neon Database
• Conclusion
• Next Steps

What is Neon?

Neon is an open-source, scalable, and efficient Postgres DB that separates compute from storage. This means that database computation processes (queries, transactions, and so on) are handled by one set of resources (compute), while the data itself is stored on a separate set of resources (storage).

This architecture allows for greater scalability and performance, making Neon a solid choice for modern web applications.

Neon - a serverless Postgres database

3 Things to Remember About Neon:

• 🐘 Postgres: Neon is built on the foundation of Postgres. It supports the same extensions, drivers, and SQL syntax as Postgres, ensuring familiarity and ease of use.
• ☁️ Serverless: Neon operates on a serverless model. Your database is represented as a simple URL, and Neon automatically scales up and down based on workload demands. Say goodbye to over-provisioning.
• 🌱 Branching: Just like version control for code, Neon allows you to create instant, isolated copies of your data. This feature is invaluable for development, testing, and maintaining separate environments.

Why Neon?

Neon brings the serverless experience to Postgres. Developers can build faster and scale their products effortlessly, without the need to dedicate big teams or big budgets to the database.

Neon supports multiple languages and frameworks – but what are the unique features that make Neon stand out?

Instant branching and auto-scaling

Neon allows you to create database branches instantly for testing, development, and staging environments. This lets you experiment without affecting the production database.

It also provides an auto-scaling capability that automatically adjusts resources based on the application's workload, ensuring optimal performance and cost-efficiency.

Neon DB Main Dashboard

Support for AI applications

Neon supports AI and machine learning applications by providing a high-performance and scalable infrastructure. It enables you to perform semantic and similarity searches in Postgres and handles complex queries and large datasets efficiently, making it ideal for AI or LLM applications.

Open-source

Neon is backed by a vibrant community of Postgres hackers, systems engineers, and cloud engineers who are all huge fans of Postgres.

As an open-source platform, Neon offers transparency and flexibility. You can also reach out to the team and contributors to ask questions, contribute, and help improve the software.

Serverless Architecture

Neon eliminates the need for manual server management, allowing you to focus on building applications rather than maintaining infrastructure. Its serverless nature provides on-demand scalability, ensuring that your application can handle varying loads without manual intervention.

Built upon Postgres

Postgres is one of the most reliable open-source relational database systems. Neon inherits all the advanced features, stability, and performance optimizations of Postgres, including support for ACID transactions, advanced SQL, and NoSQL/JSON, to create a cheaper and more efficient database for cloud environments.

How to Add Neon to a Next.js App

Neon supports multiple frameworks and libraries and provides clear and detailed documentation on adding Neon to them. The Neon serverless driver enables us to connect and interact with Neon in a Next.js application.

Before we proceed, let’s create a Neon account and project.

Neon DB Projects Overview: View and manage all your projects in one place.

Within your project dashboard, you'll find a database connection string. You'll use this to interact with your Neon database.

Neon DB Project Dashboard: Manage database settings with ease from the project dashboard.

Create a TypeScript Next.js project by running the following code snippet in your terminal:

Next, install the Neon Serverless package:

Create a .env.local file and copy your database connection string into the file:

Create a 'db' folder containing an index.ts file within the Next.js app directory and copy the code snippet below into the file:

import { neon } from '@neondatabase/serverless';

if (!process.env.NEON_DATABASE_URL) {
  throw new Error('NEON_DATABASE_URL must be a Neon postgres connection string')
}

export const getDBVersion = async() => {
    const sql = neon(process.env.NEON_DATABASE_URL!);
    const response = await sql`SELECT version()`;
    return { version: response[0].version }
}

Convert the app/page.tsx file to a server component and execute the getDBVersion() function:

import { getDBVersion } from "./db";

export default async function Home() {
    const { version } = await getDBVersion();
    console.log({version})

   return (<div>{/** — UI elements — */}</div>)

}

The getDBVersion() function establishes a connection with the Neon database and allows us to run SQL queries using the Postgres client. This function returns the database version, which is then logged to the console.

Congratulations – you’ve successfully added Neon to your Next.js application.

But interacting with the Neon database by writing SQL queries directly can require extra learning or introduce complexities for developers who are not familiar with SQL. It can also lead to errors or performance issues when performing complex queries.

This is why Neon supports database ORMs such as Drizzle ORM, which provide a higher-level interface for interacting with the database. Drizzle ORM enables you to write complex query functions and interact with the database easily using TypeScript.

How to Set Up Neon Serverless Driver with Drizzle ORM in Next.js

Drizzle ORM lets you query data and perform various operations on the database using simple TypeScript query commands. It is lightweight, typesafe, and easy to use.

First, you'll need to install the Drizzle Kit and the Drizzle ORM package.

Drizzle Kit lets you manage the database schema and migrations.

npm i drizzle-orm
npm i -D drizzle-kit

Inside the db folder, add an actions.ts, and schema.ts file:

cd db
touch actions.ts schema.ts

Add the code snippet below into the db/schema.ts file. It contains the database schema.

import {  text, serial, pgTable, timestamp } from "drizzle-orm/pg-core";

export const postsTable = pgTable("posts", {
    id: serial("id").primaryKey().notNull(),
    content: text("content").notNull(),
    author: text("author").notNull(),
    author_id: text("author_id").notNull(),
    title: text("title").notNull(),
    created_at: timestamp("created_at").defaultNow(),
    slug: text("slug").notNull(),
});

Update the db/index.ts file to connect to the Neon database and export the Drizzle instance (db). This will be used to execute typesafe SQL queries against your Postgres database hosted by Neon.

import { neon } from '@neondatabase/serverless';
import { drizzle } from 'drizzle-orm/neon-http';
import { postsTable } from './schema';


if (!process.env.NEON_DATABASE_URL) {
  throw new Error('DATABASE_URL must be a Neon postgres connection string')
}
const sql = neon(process.env.NEON_DATABASE_URL!);

export const db = drizzle(sql, {
  schema: { postsTable }
});

Next, create a drizzle.config.ts file at the root of the Next.js folder and add the following configuration:

import type { Config } from 'drizzle-kit';
import * as dotenv from "dotenv";

dotenv.config();

if (!process.env.NEON_DATABASE_URL) throw new Error('NEON DATABASE_URL not found in environment');

export default {
  schema: './src/app/db/schema.ts',
  out: './src/app/db/migrations',
 dialect: "postgresql",
  dbCredentials: {
    url: process.env.NEON_DATABASE_URL,
 },
  strict: true,
} satisfies Config;

The drizzle.config.ts file contains all the information about your database connection, migration folder, and schema files.

Finally, update the package.json file to include the Drizzle Kit commands for generating database migrations and updating the tables.

Neon DB Tables Dashboard: Effortlessly manage your database tables and view all data.

How to Build the Application Interface with Next.js

In this section, you’ll learn how to build a blog application that allows users to read posts and authenticate authors, enabling them to create and delete posts from the Neon database.

The application is divided into 3 pages:

• Home Page: displays all the available blog posts.
• Post Details Page (/posts/[slug]): displays the content of a particular blog post.
• Create Post Page (/posts/create): allows authors to create new blog posts.

Install the following packages:

npm install date-fns react-simplemde-editor easymde react-markdown remark-gfm dotenv

The Date Fns package allows us to convert the posts' timestamps to human-readable forms for display within the application. The React SimpleMDE Editor provides a WYSIWYG editor for creating content in markdown formats using an interactive editor, and the React Markdown package converts the markdown texts to their corresponding plain formats.

Next, create a utils.ts file within the Next.js app folder and copy the code snippet below into the file:

import { format } from "date-fns";

export const formatDateString = (dateString: Date | null): string => {
    if (!dateString) return "";
    const date = new Date(dateString);
    const formattedDate = format(date, "MMMM do yyyy, h:mma");
    return formattedDate;
};

export const slugifySentences = (sentence: string): string => {
    const slug = sentence
 .toLowerCase()
 .replace(/[^a-z0-9\s-]/g, "")
 .replace(/\s+/g, "-");

    // Generate 5 random letters
    const randomLetters = Array.from({ length: 5 }, () =>
        String.fromCharCode(97 + Math.floor(Math.random() * 26))
 ).join("");

    return `${slug}-${randomLetters}`;
};

The formatDateString function accepts a Date object and returns the date and time in a human-readable format using the date-fns package. The slugifySentences function creates a slug for each post using the post's title, which is useful for implementing the routes for each post.

Copy the code snippet below into the app/page.tsx file:

import Link from "next/link";
import { formatDateString, slugifySentences } from "./utils";

interface Post {
    author_id: string;
    title: string;
    content: string;
    author: string;
    slug: string;
    id: number | null;
    created_at: Date | null;
}

export default async function Home() {
    // dummy posts
    const posts: Post[] = [
        {
            author_id: "1",
            title: "Welcome to Neon Tutorial",
            content: "This is a test post",
            author: "John Doe",
            slug: slugifySentences("Welcome to Neon Tutorial"),
            id: 1,
            created_at: new Date(),
        },
        {
            author_id: "1",
            title: "Hello World",
            content: "This is a test post",
            author: "Jane Doe",
            slug: slugifySentences("Hello World"),
            id: 2,
            created_at: new Date(),
        },
    ];

    // shorten posts with longer title
    const shortenText = (text: string): string => {
        return text.length <= 55 ? text : text.slice(0, 55) + "...";
    };

    return (
        <div>
            <main className='md:px-8 py-8 px-4 w-full bg-white'>
                {posts?.map((post) => (
                    <Link
                        href={`/posts/${post.slug}`}
                        className='rounded w-full border-[1px] p-4 text-blue-500 hover:bg-blue-50 hover:drop-shadow-md transition-all duration-200 ease-in-out flex items-center justify-between gap-4 mb-4'
                        key={post.id}
                    >
                        <h3 className='text-lg font-semibold'>{shortenText(post.title)}</h3>
                        <div className='flex items-center justify-between'>
                            <p className='text-xs text-gray-500'>
                                {formatDateString(post?.created_at)}
                            </p>
                        </div>
                    </Link>
                ))}
            </main>
        </div>
    );
}

The app/page.tsx file represents the home page of the application and displays all the available posts.

It's live - see the power of serverless PostgreSQL and Next.js

Next, add the routes for creating posts and reading the contents of each post. Within the Next.js app folder, create a posts directory containing /posts/create and /posts/[slug] subdirectories.

Create a page.tsx file within the /posts/create folder and copy the code snippet below into the file:

use client";
import { useState, useCallback } from "react";
import { useRouter } from "next/navigation";
import SimpleMDE from "react-simplemde-editor";
import "easymde/dist/easymde.min.css";
import { slugifySentences } from "@/app/utils";

export default function PostCreate() {
    const [publishing, setPublishing] = useState<boolean>(false);
    const [content, setContent] = useState<string>("");
    const [title, setTitle] = useState<string>("");
    const router = useRouter();

    const onChangeContent = useCallback((value: string) => {
        setContent(value);
    }, []);

    const handleCreatePost = async (e: React.FormEvent<HTMLFormElement>) => {
        e.preventDefault();
        console.log({ title, content });
        router.push("/");
    };

    return (
        <div className='min-h-[100vh]'>
            <main className='md:px-8 py-8 px-4 w-full'>
                <form className='flex flex-col w-full' onSubmit={handleCreatePost}>
                    <label htmlFor='title' className='text-sm text-blue-600'>
                        Title
                    </label>
                    <input
                        type='text'
                        name='title'
                        id='title'
                        value={title}
                        required
                        onChange={(e) => setTitle(e.target.value)}
                        className='px-4 py-3 border-2 rounded-md text-lg mb-4'
                    />

                    <label htmlFor='content' className='text-sm text-blue-600'>
                        Content
                    </label>
                    <SimpleMDE value={content} onChange={onChangeContent} id='content' />

                    <button
                        type='submit'
                        disabled={publishing}
                        className='bg-blue-600 mt-2 text-white py-3 rounded-md'
                    >
                        {publishing ? "Publishing....please wait" : "Publish Post"}
                    </button>
                </form>
            </main>
        </div>
    );
}

The /posts/create page renders a form that accepts the title and content of the post, allowing authors to create new blog posts.

Create your next blog post with ease

Finally, update the /posts/[slug] page to display each post's content and include a button that allows only the posts' authors to delete posts. (You'll learn how to implement this later in the tutorial.)

use client";
import { useRouter, useParams } from "next/navigation";
import ReactMarkdown from "react-markdown";
import { useEffect, useState, useCallback } from "react";
import remarkGfm from "remark-gfm";
import { formatDateString } from "@/app/utils";

export default function Post() {
    const router = useRouter();
    const [loading, setLoading] = useState<boolean>(true);
    const [post, setPost] = useState<Post | null>(null);
    const params = useParams<{ slug: string }>();

    const deletePost = async () => {
        if (confirm("Are you sure you want to delete this post?")) {
            alert(`Delete ${params.slug}`);
            router.push("/");
        }
    };

    return (
        <div>
            <main className='w-full md:px-8 px-4'>
                <header className='mb-6 py-4'>
                    <div className='flex items-center justify-between mb-2'>
                        <h2 className='text-3xl text-blue-700 font-bold'>{post?.title}</h2>

                        <div className='flex items-center'>
                            <button
                                className='px-4 py-2 rounded text-xs bg-red-200 hover:bg-red-40 mr-3'
                                onClick={() => deletePost()}
                            >
                                Delete
                            </button>
                        </div>
                    </div>

                    <div className='flex'>
                        <p className='text-red-500 mr-8 text-sm'>
                            Author: <span className='text-gray-700'>{post?.author}</span>
                        </p>
                        <p className='text-red-500 mr-6 text-sm'>
                            Posted on:{" "}
                            <span className='text-gray-700'>
                                {formatDateString(post?.created_at!)}
                            </span>
                        </p>
                    </div>
                </header>

                <div className='text-sm text-justify'>
                    <ReactMarkdown remarkPlugins={[remarkGfm]}>
                        {post?.content!}
                    </ReactMarkdown>
                </div>
            </main>
        </div>
    );
}

The /posts/[slug] page accepts the unique slug for each blog post, fetches the post's content, and allows post authors to delete their own posts.

Blog Post

Congratulations! You've completed the user interface for the application.

How to Authenticate Users with Clerk

Clerk is a complete user management platform that enables you to add various forms of authentication to your software applications. It provides easy-to-use, flexible UI components and APIs that can be integrated seamlessly into your application.

Install the Clerk Next.js SDK by running the following code snippet in your terminal.

Create a middleware.ts file within the Next.js src folder and copy the code snippet below into the file:

import { clerkMiddleware, createRouteMatcher } from "@clerk/nextjs/server";


// the createRouteMatcher function accepts an array of routes to be protected
const protectedRoutes = createRouteMatcher(["/posts/create"]);

// protects the route
export default clerkMiddleware((auth, req) => {
    if (protectedRoutes(req)) {
        auth().protect();
 }
});

export const config = {
    matcher: ["/((?!.*\\..*|_next).*)", "/", "/(api|trpc)(.*)"],
};

The createRouteMatcher function accepts an array containing routes to be protected from unauthenticated users and the clerkMiddleware() function ensures the routes are protected.

Next, import the following Clerk components into the app/layout.tsx file and update the RootLayout function as shown below:

import {
    ClerkProvider,
    SignInButton,
    SignedIn,
    SignedOut,
    UserButton,
} from "@clerk/nextjs";
import Link from "next/link";

export default function RootLayout({
    children,
}: {
    children: React.ReactNode;
}) {
    return (
        <ClerkProvider>
            <html lang='en'>
                <body className={inter.className}>
                    <nav className='w-full py-4 border-b-[1px] md:px-8 px-4 text-center flex items-center justify-between sticky top-0 bg-white z-10 '>
                        <Link href='/' className='text-xl font-extrabold text-blue-700'>
                            Neon Blog
                        </Link>

                        <div className='flex items-center gap-5'>
                            {/*-- if user is signed out --*/}
                            <SignedOut>
                                <SignInButton mode='modal' />
                            </SignedOut>
                            {/*-- if user is signed in --*/}
                            <SignedIn>
                                <Link href='/posts/create' className=''>
                                    Create Post
                                </Link>
                                <UserButton showName />
                            </SignedIn>
                        </div>
                    </nav>

                    {children}
                </body>
            </html>
        </ClerkProvider>
    );
}

When a user is not signed in, the Sign in button component is rendered.

Seamless sign-ups redefined with Clerk UI

Then, after signing into the application, the Clerk User Button component and a link to create a new post are displayed.

After sign-in: Use Clerk's User Button to create a new post

Next, create a Clerk account and add a new application project.

Clerk's sleek UI dashboard

Select username as the authentication method and create the Clerk project.

Clerk's sleek UI dashboard

Finally, add your Clerk publishable and secret keys into the .env.local file.

Clerk provides various ways to read user's data on the client and the server, which is essential for identifying users within the application.

CRUD Operations with the Neon Database

In this section, you’ll learn how to perform CRUD (Create, Read, Update, Delete) operations with the Neon database. These fundamental operations are essential for interacting with and managing data within any application.

The db/actions.ts file will contain the CRUD operations. Add the following code snippet to the file:

import { db } from ".";
import { postsTable } from './schema';
import { desc, eq } from "drizzle-orm";

// add a new row to the posts table
export const createPost = async (post: Post) => {
    await db.insert(postsTable).values({
        content: post.content,
        author: post.author,
        author_id: post.author_id,
        title: post.title,
        slug: post.slug,
    });
};

// get all the posts
export const getAllPosts = async () => {
    return await db.select().from(postsTable).orderBy(desc(postsTable.created_at));
};

// get a post using its slug
export const getSinglePost = async (slug: string) => {
    return await db.query.postsTable.findFirst({
        where: (post, { eq }) => eq(post.slug, slug)
    });
};

// delete a post
export const deletePost = async (id: number) => {
    await db.delete(postsTable).where(eq(postsTable.id, id));
};

// update a post's content
export const updatePost = async (content: string, id: number) => {
    await db.update(postsTable)
        .set({ content: content })
        .where(eq(postsTable.id, id));
};

From the code snippet above:

• This createPost function takes a post object as an argument and inserts a new row into the postsTable with the specified post content, author, author ID, title, and slug.
• The getAllPosts function retrieves all the posts from the postsTable and sorts them in descending order by their creation date (created_at).
• This getSinglePost function takes a slug as an argument and retrieves the first post that matches the given slug from the postsTable. The slug is unique, so it will return a single object.
• This deletePost function takes an id as an argument and deletes the post with the matching ID from the postsTable.
• This updatePost function accepts content and a post's id as arguments and updates the post's content with the matching ID in the postsTable.

Finally, you can execute the CRUD functions on the server via API endpoints or Next.js server fetch requests.

For instance, you can fetch all the existing blog posts within the Neon database and display them within the application using the Next.js server data fetching method:

import { getAllPosts } from "./db/actions";

const getPosts = async () => await getAllPosts()

export default async function Home() {
    const posts = await getPosts()

    return (<div>{/** -- UI elements --*/}</div>)
}

You can also create a Next.js API endpoint that returns all the available blog posts. Create a /api/posts/all endpoint that returns the posts:

import { getPosts } from "@/app/db/actions";
import { NextRequest, NextResponse } from "next/server";

export async function POST() {


    try {
        const data = await getPosts()
        return NextResponse.json({ message: "Post fetched", data }, { status: 200 });
 } catch (err) {
        return NextResponse.json(
 { message: "Post not available", err },
 { status: 400 }
 );
 }
}

Congratulations! You’ve completed the project for this tutorial.

You can find the code for the app we built here.

Conclusion

In this tutorial, you’ve learned what a Neon database is, how to create one, and how to perform CRUD operations with Neon and Drizzle ORM in a Next.js application.

Neon's serverless architecture, combined with its scalability and performance optimizations, makes it an excellent choice for modern web applications. Neon also provides a smooth developer experience and a community of passionate individuals ready to help you achieve your application goals. Thank you for reading.

Next Steps

By now, you should have a good understanding of how to build full-stack applications with Neon and Next.js.

If you'd like to learn more about how you can leverage Neon to build advanced and scalable applications, you can check out the following resources:

• Neon documentation
• Neon example projects
• How to integrate Neon with Vercel
• How to import your data from a Postgres database to Neon

Thanks for Reading!

That's it for this tutorial. I hope you learned something new today.

If you did, please share so that it reaches others as well.

You can connect with me on Twitter or subscribe to my newsletter.

Want to read more interesting blog posts?

You can read more tutorials like this one on my blog.

Database management is a critical skill a developer must possess in building scalable applications that have a high level of efficiency. If not handled properly, it can result in data loss and mismanagement on the part of the database developer.

Hence, databases must be structured and built with the users in mind and built utilizing the best practices available.

This article aims to highlight general principles of database best practices and also explain each peculiarity. But before we discuss that in detail, let’s review what database transactions are all about.

What are Database Transactions?

Database transactions are simply groups of operations which can be termed as a unit of a work process performed on a database within a database management system.

It encompasses basic operations such as CRUD operations to more advanced operations such as database indexing, caching, and normalization.

With so many users performing many transactions at the same time, it’s important to ensure that the database is concurrency-enabled to prevent data interference between two or more users accessing the same resource.

Hence, there is the need for the ACID principle. What then does ACID represent?

• Atomicity
• Consistency
• Isolation
• Durability

Subsequently, we will be discussing each point in detail. First on our list is atomicity.

What is the Database Atomicity Principle?

What does database atomicity entail? The atomicity of a database simply means that a database operation can’t be broken down further as a unit. This means that the database operation or transactions gets executed completely, and in case any error comes up during the execution process, the entire operation gets completely cancelled, preventing room for partial operation execution.

If the database isn’t atomic, this can result in the provision of misleading incomplete data and ultimately result in entire system chaos. How does the database ensure atomicity? It does this by creating a copy of the existing database before the operation gets executed and then initiates a crash recovery and backup restoration operation in the event of an operation failure.

It is also important to note that other database principles such as consistency and durability rely on the need for the database to be atomic to be truly fulfilled.

Having discussed this, let’s move on to the database consistency principle.

What is the Database Consistency Principle?

This principle entails that the database has certain constraints, cascades, triggers and other requirements in place, which needs to be fulfilled while making changes to an established database. Failure to fulfill this requirement will lead to consistency errors, returning the database to its previous stable state.

Also, consistency as a principle ensures that the data updated by a user is made available as the latest version of the data in the database to all users who desire to read the database. Having this in place eliminates the occurrence of inconsistencies and aids faster information retrieval.

Understanding what it means for a database to be consistent involves ensuring the operation performed on the database passes the integrity check before being successfully executed. Having exhausted this in detail, let's discuss the database isolation principle.

What is the Database Isolation Principle?

Why should we isolate a database and how does one make a database operation independent from other database operations?

Isolation is necessary in a database management system to ensure that the user's access to information on the database is not interfered with by other concurrent transactions undertaken by other users on the database. To enforce this, the use of isolation levels in each database operation helps to preserve information integrity.

To effectively guarantee the database integrity, specific database isolation levels must be used. Here are some of the isolation levels ranked in order of hierarchy:

• Read uncommitted
• Read committed
• Repeatable read
• Serializability

Read Uncommitted Isolation Level

The read uncommitted database isolation level allows other users to have access to read current database transactions which has not yet been completely or successfully executed. It allows access to read what is being referred to as dirty read, which is one of the data inconsistencies that can be seen. This level of data isolation isn’t advised.

Read Committed Isolation Level

This database isolation level disallows other users to read or have access to a database transaction that has not yet been committed. Hence it prevents other users from seeing, updating or overwriting it until it has been completely executed.

Repeatable Read Isolation Level

This isolation level exclusively isolates a transaction from other transactions occurring concurrently, preventing other users access to read and update the transactions.

Serializability Isolation Level

This is the highest level of data isolation and is referred to as the strictest level. It isolates the multiple transactions performed concurrently and executes them efficiently as they are executed serially. It also prevents database inconsistencies.

Without these levels in place, inconsistent database mishaps such as dirty reads, non-repeatable reads, phantom reads and many others may be experienced. With this, let's move on to the last point about database durability and discuss it in detail.

What is the Database Durability Principle?

What does it imply when we describe a database as durable and how do we ensure the durability of a database? Durability as it sounds is a principle which ensures that databases have a high level of immortality.

Irrespective of any adverse outcomes that the database management system might face such as outages and crashes, there shouldn't be any loss of database information.

How do databases try to achieve this? The database creates a transactional log that contains the recorded data before any new operation gets executed. In the event of any of these adverse events, the transaction log serves as the backup store, ensuring that the database info is well preserved up to the point before the operation occurred, thereby mitigating against data breaches and loss.

We'll also highlight other helpful database operations best practices that can also be implemented.

Other Database Operations Best Practices

The BASE principle, which is more suited for NoSQL databases such as MongoDB, Redis, and Cassandra, and so on. It entails a database to be:

• Basically available
• Existing in a soft state
• And be eventually consistent.

Basically Available

This entails that the database prioritizes the availability of the database operations over consistency and concurrency. This is quite applicable to distributed systems which rely on a high level of efficiency to function effectively.

Soft State

This ensures easy flexibility of the database, allowing for size scaling, operations and increased concurrency for optimal database performance at all times. This allows the data to maintain resiliency.

Eventually Consistent

This entails that, irrespective of how the transactions get executed in the sequences, it eventually achieves efficient consistency. This is achieved by conflict resolution and reconciliation. This eventually contributes to the building of a resilient data system.

Conclusion

With this, we have come to the end of the tutorial. We hope you’ve learned essentially about optimizing database operations and their efficiency using the ACID principle and other best practices available.

Feel free to drop comments and questions in the box below, and also check out my other articles here. Till next time, keep on coding!

In this fast-paced development landscape, deploying via containers, particularly with Kubernetes, is becoming increasingly popular. Some companies perform numerous deployments daily, so it's crucial for you to learn these technologies.

Kubernetes is a popular choice to deploy containerized applications like web servers, databases, and APIs. You can set up Kubernetes either locally or in the cloud. In this tutorial, we'll explore setting up Kubernetes on a cloud platform, specifically Azure.

I'll walk you through the process of setting up Kubernetes using Azure Kubernetes Service (AKS). You'll configure your YAML file using StatefulSet, Persistent Volume, and Services to deploy a PostgreSQL database on Azure Kubernetes. Then, you'll obtain the PostgreSQL database credentials running inside the AKS and use them to establish a connection with a Node.js application.

We'll cover key concepts such as deployment, stateful sets, persistent volumes, and services, preparing you to deploy a Postgres container effectively on AKS. I'll also help you connect your Node.js Express app to the Postgres container within the AKS cluster.

So find a comfortable seat and get ready, as we're about to dive in.

Prerequisites

Before you begin, it's important to understand some basic concepts in Kubernetes like pods, deployments, services, and nodes.

If you're new to this, I recommend checking out the Stashchuk freeCodeCamp video for a beginner-friendly tutorial.

You'll also need an active Azure account and subscription to follow along.

Table of Contents

• Challenges We're Trying to Solve
  – Deployments
  – StatefulSets
  – Persistent Volumes

• Azure Kubernetes Service (AKS)
  – Sign in to Your Azure Portal
  – Create a Resource
  – Create a new container
  – Create a new Azure Kubernetes Service(AKS)
  – Create a new resource group
  – Give your Kubernetes cluster a name
  – Navigate to the node pool page
  – Enable container logs and set up alerts
  – Advanced Section
  – Tags

• Connect to Your AKS Cluster
  – Download Azure CLI and kubectl
  – Verify if Azure CLI is installed
  – Verify if kubectl is installed
  – Login to Azure account
  – Configure kubectl

• How to Create Resources with YAML
  – Clone the Repository
  – Open the Repository
  – Install Dependencies

• YAML Configuration
  – StorageClass
  – PersistentVolumeClaim
  – ConfigMap
  – StatefulSet
  – Service

• How to Deploy YAML Resource to Azure
  – Deploy the YAML resource

• Node.js Application
  – Configure Nodejs
  – Run Nodejs Application
  – Test the Application
  – Open Postman
  – Confirm the Data
  – Delete Pod
  – Data Persistence

• Conclusion

Challenges We're Trying to Solve

Firstly, what is Kubernetes? Well, it's like a manager for your software containers. It helps you run and manage lots of containers like web servers, databases, microservices, and APIs which are like little packages holding your applications.

Kubernetes takes care of things like starting, stopping, and scaling these containers, so your apps run smoothly even when there is more load on your application. It's popular because it makes running software in the cloud easier and more reliable.

Now, let's talk about how to tackle some challenges you might face with a real-world application running Postgres in a Kubernetes production cluster.

Imagine that the infrastructure hosting your Postgres crashes, causing you to lose all the services and data stored in the database. Or, picture a scenario where the Postgres database becomes corrupted, leading to data loss.

In both cases, you need a way to back up your application so you can restore it to a working state if disaster strikes.

So, how do you capture a comprehensive application backup that includes all the necessary data? This backup should allow you to restore the entire application, including the database, if you lose your cluster or encounter data loss.

In Kubernetes, think of a Pod as the tiniest unit that you can deploy. It's like a small box that holds one thing, like a web server or a database. So, if your Pod isn't running, your web server or database isn't either.

This means that if the cluster where your Pod runs gets destroyed, all the data in the Pod disappears too. All the nodes (virtual machines that run your application over the network) will also be wiped out.

How can you make a pod stay on one specific node where the data is and never move? And how can you make sure that each pod can be found separately when you're using a load balancer?

One solution is to consider how you deploy your application on Kubernetes. Typically, you create a deployment and expose it using a service, specifying the service type as either Cluster type, NodePort, or LoadBalancer.

But not all applications are the same when it comes to state. Some applications, known as stateless applications, don't rely on storing data locally, so losing their state isn't a big issue.

But for applications like databases or caches, maintaining state is crucial because they rely on storage. In Kubernetes, deploying stateful applications like databases using just deployment isn't ideal. You need a solution that ensures your application's data is safely stored and can be recovered in case of failure.

Deployments

You might be wondering why we can't just use a Kubernetes deployment to deploy Postgres in the Kubernetes cluster? Well, the thing is, many people aren't aware of the difference between a deployment and a stateful set.

Let's imagine you have a pod running in your cluster that you created using a deployment. Then you scaled up to two pods, so you now have Pod A and Pod B.

The problem arises because, by default, pods created as part of the same deployment share the same persistent volume (PV) across the cluster. So, when you scaled up, both instances of Postgres would write to the same storage, which could lead to data corruption.

Another issue arises from a networking perspective. Pods A and B don't have a dependable way to communicate with each other over the network. By default, Kubernetes pods don't have their own DNS names. Instead, you rely on services to expose ports to other applications in the cluster.

If you take a closer look at pod names, you'll notice that pods are assigned a random hash at the end of their names. Because of this, pods lack a consistent network identity. Every time a pod is destroyed and recreated, it receives a new randomized name. This inconsistency isn't ideal for reliable networking.

Postgres isn't naturally made for Kubernetes, and Kubernetes can be tough when handling stateful tasks. To set up a Postgres instance, you've got to know the right Kubernetes setup. You can't just throw it in a pod, because if the pod goes down, so does your data. But, for a quick integration, a pod could work fine.

Deployments aren't ideal either, since you don't want your pod randomly placed on a node. But for testing, deployments are handy if you just need a Postgres instance to run temporarily.

What you really want is a pod that sticks to a particular node where your data resides, and stays put. Plus, you also want your pod to be individually addressable. for this we need what we called a statefulSet.

StatefulSets

When you update your deployment to become a StatefulSet, Kubernetes introduces some improvements for deploying stateful workloads. One major change is how it handles scaling.

If you specify that you want three replicas of your StatefulSet, Kubernetes won't create all three pods at once. Instead, it creates them one by one. Each pod gets its own unique DNS name, starting with the pod's name followed by an ordinal number starting from zero. So, when you scale up, the ordinal number increases for each new pod.

Here's the cool part: if a pod like Pod-0 is destroyed and needs to be remade, it will return with the same name. This means each pod has a specific address, even if it's replaced.

And here's another cool feature: each pod in a StatefulSet gets its own persistent volume (PV). This lets you keep the same storage even if you scale up or down. This brings us to another concept called persistent volumes.

Persistent Volumes

Let's forget about pods, deployment, and containers for a moment. What exactly is "state"? In simple terms, state is the data that your applications need to work properly.

Now, when we talk about processes, there are two types: stateless and stateful. Stateless processes don't rely on any data to work. They just do their thing without needing any specific information. On the other hand, stateful processes need data or state to function properly.

Now, where do you store this state? There are two main places: memory and disk. Memory allows for quick access to data, which is great for applications like Redis, MongoDB, Postgres, or MySQL. They store their state on memory for quick access. But for persistent, they store it on disk on the file system (for more permanent storage).

Why the file system? Because it's the only way to keep the state persistent even when the system reboots. So, when a process dies and gets recreated, it can read its state from the file system.

I like breaking things down because I used to teach tech stuff. Now, let's get into setting up Kubernetes in Azure.

Azure Kubernetes Service (AKS)

In this section, I'll guide you through setting up a Kubernetes cluster on Azure.

Step 1: Sign in to your Azure portal

To begin, you will have to sign into your Azure portal. Once logged in, you should see a dashboard similar to this:

Azure portal homepage

Step 2: Create a resource

Click on "create a resource" to create a resource.

Resources are the various services, components, and assets that you can create and manage within the Azure cloud platform. These resources can include virtual machines, databases, storage accounts, networking components, web applications, and more.

Creating a resource in Azure portal

Step 3: Create a new container

Next, navigate to the "Containers" category from the options available on the left pane. Click on Containers as shown by the arrow in the screenshot.

Again, Kubernetes is a container orchestration platform. It manages and orchestrates the deployment, scaling, and operation of application containers across clusters of machines. Kubernetes provides a framework for automating the deployment, scaling, and management of containerized applications.

Creating new container (Kubernetes) in Azure

Step 4: Create a new Azure Kubernetes Service (AKS)

Select "Azure Kubernetes Service (AKS)" from the list of available container services and click Create. This will take you to the AKS creation page.

Creating a new Azure Kubernetes service

Step 5: Create a new resource group

In the "Resource group" section, click on "Create new" to create a new resource group for your Azure Kubernetes Service (AKS) deployment.

In Azure, a "resource group" is a logical container used to group together related Azure resources. It serves as a way to organize and manage these resources collectively, rather than individually.

When you create resources such as virtual machines, databases, storage accounts, or any other Azure service, you typically associate them with a resource group.

Creating a new resource group in azure portal

Let's name the resource group "AZURE-POSTGRES-RG" as shown below. You can name it anything you like. Then click ok.

Inputting name for the resource group

Step 6: Give your Kubernetes cluster a name

Now let's name the session for configuring the Kubernetes cluster "Kubernetes Cluster Name".

In Azure, a Kubernetes cluster is a managed container orchestration service provided by Azure Kubernetes Service (AKS). It allows you to deploy, manage, and scale containerized applications using Kubernetes without having to manage the underlying infrastructure.

Give it a name like "AZURE-POSTGRES-KC" and and select a region that's close to you. In my case I select (Asia Pacific) East Asia and click next.

Naming the Kubernetes cluster name

Step 7: Navigate to the node pool page

Now it's time to configure the node pool session by clicking on the agentpool.

In Azure, a node pool is a group of virtual machines (VMs) that are provisioned and managed together within an Azure Kubernetes Service (AKS) cluster. Each node pool runs a specific version of Kubernetes and has its own set of configurations, such as VM size, OS image, and node count.

Editing agentpool

Set the minimum node count to 1, maximum node count to 2, and the maximum pods per node to 30 to minimise cost. Then click update.

These parameters help control the size and behavior of the node pool in an Azure Kubernetes Service (AKS) cluster:

1. Minimum Node Count: Ensures a minimum number of nodes are always available for consistent performance and availability, even during low-demand periods.

2. Maximum Node Count: Sets an upper limit on the number of nodes in the node pool to manage costs and prevent over-provisioning.

3. Maximum Pods per Node: Defines the maximum number of pods that can run on each node, optimizing resource utilization and preventing overcrowding.

Updating agentpool details

Once you've clicked "Update," you'll be directed to the "Networking" section as shown below. Keep the page as is and proceed by clicking "Next." This will take you to Integration session.

Navigating to Next Page

Azure Container Registry (ACR) is a fully managed private Docker registry service provided by Microsoft Azure. It enables developers to store, manage, and deploy Docker container images securely within their Azure environment.

You will need a place to store the Docker image that's pulled.

To begin, select "Create New" to set up a new container registry. This action will bring up a page where you can input the necessary details, as illustrated on the right side of the image below. Enter the details as indicated by the arrows and then click "Okay." Once you're done, proceed by clicking "Next."

Naming and editing Azure Container Registry details

Step 8: Enable container logs and set up alerts

The Enable Container Logs option allows you to turn on logging for your containers. Logging records important information about what's happening inside your containers, like errors, warnings, and other events. It's useful for troubleshooting and monitoring your applications.

Choosing container logs

Step 9: Advanced section

Keep the Monitoring section unchanged and proceed by clicking "Next."

Navigating to Next Page

Step 10: Tags

Keep the Tags section unchanged and proceed by clicking "Next."

Navigating to Next Page

Step 11: Click "Review + create" to finalize the deployment

Once completed, your resource group, Azure Kubernetes Service (AKS), Azure Container Registry, and Kubernetes cluster will be created.

Completing Azure Kubernetes Setup

The screenshot below shows that the deployment was successful.

Successful Deployment

You've just successfully created an Azure Kubernetes Service from the Azure portal. Congrats!

How to Connect to Your AKS Cluster Using the Command Line

After successfully creating a new AKS in the Azure portal, the next step is to establish a connection to that cluster.

In this section, I'll guide you through Azure login, configuring kubectl to use the current context, and creating the YAML file for our Postgres container. This file will include StatefulSet, persistent volume, persistent volume claim, config map, and using Azure File for data storage.

I'll also show you how to run a Node.js Express application locally, use Postman to test the endpoints, and receive a response confirming that data was sent to the database successfully.

Download Azure CLI and kubectl

To start, you'll need to download the Azure CLI and kubectl.

• Azure CLI (Command-Line Interface): a command-line tool provided by Microsoft for managing Azure resources. It allows users to interact with Azure services and resources directly from the command line, making it easy to automate tasks, create scripts, and manage Azure resources programmatically.

• kubectl: a command-line tool for managing Kubernetes clusters, used to deploy, scale, and manage containerized applications. It allows users to perform operations like deploying applications, managing pods, services, and deployments, inspecting cluster resources, scaling applications, and debugging issues, simplifying management of containerized workloads in a Kubernetes environment.

I'm using the warp terminal. Warp is the terminal reimagined with AI and collaborative tools for better productivity. You can run the command using PowerShell on Windows or Terminal on Mac. I'm using a MacBook.

Verify if the Azure CLI is installed by typing the command az --version

Once the download finishes, verify whether Azure CLI is installed on your computer by running the command az --version. If the installation is successful, you should see an output similar to this:

Verifying Azure CLI Installation

Verify if kubectl is installed

To check if kubectl is installed, just type kubectl version in the command line.

Verifying Kubectl Installation

Login to your Azure account

Enter az login in the command line. This will open your browser and prompt you to sign in to your Azure account.

Logging into Azure

After signing in, it shows details about your Azure subscription, including the subscription name, ID, and user information.

Select an Azure subscription

Azure subscriptions are logical containers used to provision resources in Azure. You'll need to locate the subscription ID that you plan to use in this module. Use the command to list your Azure subscriptions:

az account list --output table

Use the following command to ensure you're using an Azure subscription that allows you to create resources for the purpose of this module, substituting your subscription ID (SubscriptionId):

az account set --subscription "Name of the subscription"

Configure kubectl to connect to your Azure Kubernetes

Replace Your_Azure_Resource_groups_name in the code below with the name you chose when creating a resource group. Also, replace your_azure_kubernetes_service_name with the name of your Kubernetes cluster. Then, execute the following command:

az aks get-credentials --resource-group [Your_Azure_Resource_groups_name] --name [your_azure_kubernetes_service_name]

The output should look like this:

Merging kubectl with Azure Kubernetes Service

Verify if kubectl has been merged successfully

Run the following command kubectl get nodes:

Verifying if merged is successful

When you run this command, Kubernetes communicates with the cluster's control plane to fetch a list of all the nodes that are part of the cluster you created. As you can see, this is the node that was running in the Kubernetes cluster we created inside Azure.

Virtual Node running in AKS cluster

Run the command kubectl get pods

Displaying Pod Information

When you run the command kubectl get pods, Kubernetes attempts to retrieve information about all pods within the default namespace of your cluster. But in this case, the output indicates that there are no resources (pods) found within the default namespace, implying that no pods currently exist in that namespace.

A namespace in Kubernetes is a virtual cluster environment within which resources like pods, services, and deployments are organized and isolated. It's a way to divide cluster resources between multiple users, teams, or projects. Namespaces provide a scope for names and make it easier to manage and control access to resources.

By default, Kubernetes starts with a "default" namespace, but you can create additional namespaces to organize and manage resources more effectively. Namespaces help prevent naming conflicts and provide a logical separation of resources, allowing different teams or projects to work independently within the same Kubernetes cluster.

Create a namespace

Creating Namespace

When you run the command kubectl create namespace database, Kubernetes creates a new namespace named "database." The output "namespace/database created" confirms that the namespace has been successfully created.

You can now use this namespace to organize and manage resources related to databases within the Kubernetes cluster.

Confirm the namespace

The command kubectl get namespace lists all namespaces in the Kubernetes cluster including the database namespace we just created, showing their names, status (active), and age.

Confirming namespace

Get pod information in database namespace

Displaying Pod Information related to database namespace

This command, kubectl get pods -n database, attempts to fetch information about pods specifically within the "database" namespace. But the output No resources found in database namespace indicates that there are currently no pods deployed in the "database" namespace.

How to Create Resources with YAML

Let's explore creating resources with YAML to provision our PostgreSQL database running in an Azure Kubernetes cluster. But first, what exactly is YAML?

Kubernetes YAML is a configuration file written in YAML (YAML Ain't Markup Language). They define how Kubernetes resources like pods, deployments, and services should be set up within a cluster. These files are easy to read and specify details like resource names, types, specifications, labels, and annotations. They're crucial for deploying applications and infrastructure on Kubernetes clusters.

YAML is what you will use to create Kubernetes resources that will run Postgres.

First, you need to clone this GitHub repository. Inside, you'll find a Node.js Express application and a YAML file. The Node.js app allows users to register with their email, password, and full name, and also enables them to log in by verifying their details in the database. If their details are found, it displays a success message.

Clone the repository

Create a new folder on your computer and then clone this repository into it.

Open your terminal or PowerShell, go to the folder you want, and use the command below to clone the repository into your computer in that location.

git clone https://github.com/ayowilfred95/Azure-k8s-postgres.git

Open the cloned repository in any text editor

I'm using Visual Studio Code, but feel free to use any text editor you prefer. Here's the structure of the project:

Project folder structure

Install project dependencies

Open the terminal in VS Code and go to the main directory of the project. Next, execute the command npm install to install all the required packages and dependencies for the project:

npm install

Since the backend application is a Node.js Express app, you use npm to install dependencies (similar to how we use maven clean install in Java).

After the dependencies are installed, open the file named "postgres.yaml". It holds all the YAML configurations required to set up your PostgreSQL database that will run in the Kubernetes cluster.

YAML Configuration

In the postgres.yaml file, there are five configurations separated by ---. It's important to use this "---" symbol when declaring different types of Kubernetes resources. If you forget to do this, you'll encounter an error.

StorageClass

The first one is the StorageClass. This YAML configuration defines a StorageClass in Kubernetes for managing storage resources.

kind: StorageClass
apiVersion: storage.k8s.io/v1
metadata:
  name: azuredisk-premium-retain
provisioner: kubernetes.io/azure-disk
reclaimPolicy: Retain   # Retain or Delete
volumeBindingMode: WaitForFirstConsumer   # WaitForFirstConsumer or Immediate
allowVolumeExpansion: true    # true or false
parameters:
  storageaccounttype: Premium_LRS   # Premium or Standard
  kind: Managed

Let's break down what each part means:

• kind: StorageClass: Indicates the type of Kubernetes resource being defined, which is a StorageClass. A StorageClass defines the class of storage offered by a cluster.

• apiVersion: storage.k8s.io/v1: Specifies the Kubernetes API version being used for this resource.

• metadata: name: azuredisk-premium-retain: Provides metadata for the StorageClass, including its name, which in this case is "azuredisk-premium-retain".

• provisioner: kubernetes.io/azure-disk: Specifies the provisioner responsible for provisioning storage. In this case, it's "kubernetes.io/azure-disk", indicating that Azure Disk will be used as the storage provisioner.

• reclaimPolicy: Retain: Defines the reclaim policy for the storage resources. It specifies what action should be taken when the associated persistent volume is released. Here, it's set to "Retain", meaning the volume is retained even after it's no longer used by a pod.

• volumeBindingMode: WaitForFirstConsumer: Specifies the volume binding mode, which determines when volume binding should occur. In this case, it's set to "WaitForFirstConsumer", meaning the volume will be bound when the first pod using it is created.

• allowVolumeExpansion: true: Indicates whether volume expansion is allowed. Setting it to "true" means that the size of the volume can be increased if needed.

• parameters: Contains additional parameters specific to the provisioner. Here, it specifies the storage account type as "Premium_LRS" and the kind of storage as "Managed".

Overall, this configuration sets up a StorageClass named "azuredisk-premium-retain" using Azure Disk as the provisioner, with specific policies and parameters tailored for Azure storage.

PersistentVolumeClaim

The second configuration in the postgres.yaml file is the persistent volume claim.

This YAML configuration defines a PersistentVolumeClaim (PVC) in Kubernetes, which is used to request storage resources.

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: azure-managed-disk-pvc
spec:
  accessModes:
  - ReadWriteOnce   # ReadWriteOnce, ReadOnlyMany or ReadWriteMany
  storageClassName: azuredisk-premium-retain
  resources:
    requests:
      storage: 4Gi

Let's break down what each part means:

• apiVersion: v1: Specifies the Kubernetes API version being used for this resource.

• kind: PersistentVolumeClaim: Indicates the type of Kubernetes resource being defined, which is a PersistentVolumeClaim. A PVC is used by pods to request storage resources.

• metadata: name: azure-managed-disk-pvc: Provides metadata for the PersistentVolumeClaim, including its name, which is "azure-managed-disk-pvc".

• spec: Describes the desired state of the PersistentVolumeClaim.

• accessModes: - ReadWriteOnce: Specifies the access mode for the volume. Here, it's set to "ReadWriteOnce", meaning the volume can be mounted as read-write by a single node at a time.

• storageClassName: azuredisk-premium-retain: Specifies the StorageClass to use for provisioning the volume. This PVC will use the StorageClass named "azuredisk-premium-retain" defined previously.

• resources: requests: storage: 4Gi: Specifies the desired storage capacity for the volume. Here, it requests 4 gigabytes (Gi) of storage.

Overall, this configuration sets up a PersistentVolumeClaim named "azure-managed-disk-pvc" requesting storage resources with specific access modes, storage class, and storage capacity.

ConfigMap

The third configuration in the postgres.yaml file is the config map. This YAML configuration defines a ConfigMap in Kubernetes, which is used to store configuration data in key-value pairs.

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-config
  labels:
    app: postgres
data:
  POSTGRES_DB: freecodecamp
  POSTGRES_USER: freecodecamp1
  POSTGRES_PASSWORD: freecodecamp@
  PGDATA: /var/lib/postgresql/data/pgdata

Let's break down what each part means:

• apiVersion: v1: Specifies the Kubernetes API version being used for this resource.

• kind: ConfigMap: Indicates the type of Kubernetes resource being defined, which is a ConfigMap. A ConfigMap is used to store non-confidential data in key-value pairs.

• metadata: name: postgres-config: Provides metadata for the ConfigMap, including its name, which is "postgres-config".

• labels: app: postgres: Labels are key-value pairs used to organize and select resources. Here, a label "app" with the value "postgres" is applied to the ConfigMap.

• data: Contains the key-value pairs of configuration data.

• POSTGRES_DB: pisonitsha: Specifies the name of the PostgreSQL database as "pisonitsha".

• POSTGRES_USER: pisonitsha1: Specifies the username for accessing the PostgreSQL database as "pisonitsha1".

• POSTGRES_PASSWORD: pisonitsha@: Specifies the password for accessing the PostgreSQL database as "pisonitsha@".

• PGDATA: /var/lib/postgresql/data/pgdata: Specifies the location of PostgreSQL data directory as "/var/lib/postgresql/data/pgdata".

Overall, this configuration sets up a ConfigMap named "postgres-config" containing key-value pairs of configuration data, such as database name, username, password, and data directory location, which can be used by other Kubernetes resources.

Note: It's recommended to avoid hardcoding secret variables such as POSTGRES_DB, POSTGRES_PASSWORD,PGDATA and instead store them in secret files, for the sake of simplicity in this tutorial, we'll keep them hardcoded.

StatefulSet

The fourth configuration is the stateful set.This YAML configuration defines a StatefulSet in Kubernetes, which is used to manage stateful applications like databases.

apiVersion: apps/v1
kind: StatefulSet
metadata:
  name: postgres
spec:
  serviceName: postgres
  selector:
    matchLabels:
      app: postgres
  replicas: 1
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres
          image: postgres:10.4
          imagePullPolicy: "IfNotPresent"
          ports:
          - containerPort: 5432
          envFrom:
          - configMapRef:
              name: postgres-config
          volumeMounts:
          - name: azure-managed-disk-pvc
            mountPath: /var/lib/postgresql/data
      volumes:
      - name: azure-managed-disk-pvc
        persistentVolumeClaim:
          claimName: azure-managed-disk-pvc
    ```

Let's break down what each part means:

* `apiVersion: apps/v1`: Specifies the Kubernetes API version being used for this resource.
* `kind: StatefulSet`: Indicates the type of Kubernetes resource being defined, which is a `StatefulSet`. `StatefulSets` are used to manage stateful applications by providing unique **identities** and stable **network** identities to each pod.
* `metadata: name: postgres`: Provides metadata for the `StatefulSet`, including its name, which is "postgres".
* `spec`: Describes the desired state of the `StatefulSet`.
* `serviceName: postgres`: Specifies the name of the Kubernetes service that will be used to access the `StatefulSet` pods.
* `selector: matchLabels: app: postgres`: Selects the pods controlled by this `StatefulSet` based on the label "app: postgres".
* `replicas: 1`: Specifies the desired number of replicas (instances) of the StatefulSet, which is 1 in this case.
* `template`: Defines the pod template used to create pods managed by the `StatefulSet`.
* `metadata: labels: app: postgres`: Labels applied to the pods created from this template.
* `spec`: Describes the specification of the containers within the pod.
* `containers`: Specifies the containers running in the pod.
* `name: postgres`: Defines the name of the container as "postgres".
* `image: postgres:10.4`: Specifies the Docker image used for the container, which is "postgres:10.4".
* `imagePullPolicy: "IfNotPresent"`: Specifies the policy for pulling the container image, which is "IfNotPresent", meaning it will only pull the image if it's not already present on the node.
* `ports: containerPort: 5432`: Specifies the port that the PostgreSQL service inside the container is listening on.
* `envFrom: configMapRef: name: postgres-config`: Injects environment variables from a ConfigMap named "**postgres-config**" that you defined earlier.
* `volumeMounts: name: azure-managed-disk-pvc mountPath: /var/lib/postgresql/data`: Mounts a persistent volume claim named "azure-managed-disk-pvc" to the container at the specified path.
* `volumes: name: azure-managed-disk-pvc persistentVolumeClaim: claimName: azure-managed-disk-pvc`: Defines the persistent volume claim named "azure-managed-disk-pvc" to be used by the pod.

Overall, this configuration sets up a StatefulSet named "postgres" with one replica, running a PostgreSQL container with specific settings and mounted persistent storage.

### Service

The fifth configuration is the **service**. This YAML configuration defines a **Service** in Kubernetes, which is used to expose the `StatefulSet` we declared earlier as a network service.

```bash
apiVersion: v1
kind: Service
metadata:
  name: postgres
  labels:
    app: postgres
spec:
  type: LoadBalancer
  selector:
    app: postgres
  ports:
    - protocol: TCP
      name: https
      port: 5432
      targetPort: 5432

Let's break down what each part means:

• apiVersion: v1: Specifies the Kubernetes API version being used for this resource.

• kind: Service: Indicates the type of Kubernetes resource being defined, which is a Service. Services allow pods to be accessed by other pods or external users.

• metadata: name: postgres: Provides metadata for the Service, including its name, which is "postgres".

• labels: app: postgres: Labels are key-value pairs used to organize and select resources. Here, a label "app" with the value "postgres" is applied to the Service.

• spec: Describes the desired state of the Service.

• type: LoadBalancer: Specifies the type of Service, which is "LoadBalancer". This type allows the Service to be exposed externally with a cloud provider's load balancer.

• selector: app: postgres: Selects the pods controlled by the Service based on the label "app: postgres".

• ports: Specifies the ports that the Service will listen on.

• protocol: TCP: Specifies the protocol used for the port, which is TCP.

• name:https : Specifies a name for the port, which is "https".

• port: 5432: Specifies the port number on which the Service will listen, which is 5432.

• targetPort: 5432: Specifies the target port on the pods to which traffic will be forwarded, which is also 5432. This means that traffic received on port 5432 of the Service will be forwarded to port 5432 on the pods.

Overall, this configuration sets up a Service named "postgres" with a LoadBalancer type, forwarding traffic on port 5432 to pods labeled with "app: postgres".

How to Deploy YAML Resource to Azure Kubernetes Service (AKS)

You've previously connected "kubectl" with the Azure Kubernetes Service (AKS) you set up. Let's double-check it.

In your VS Code terminal, rerun the command kubectl get nodes. You'll see an output like this, though your node's value will be different.

Displaying node information running in Azure Kubernetes cluster

Next, verify the namespace you previously created by executing the command: kubectl get namespace database. Your output should resemble this:

Retrieving Namespace Information

Deploy the YAML resource

Once you've confirmed everything is set, you can deploy the YAML resource. This will establish your PostgreSQL database in the Azure Kubernetes cluster you've configured.

Run the below command in the main directory where the configuration file is located. Currently, I'm in the project's root directory (azure-k8s-postgres). To deploy the database, just execute this command below:

kubectl apply -n database -f postgres.yaml

Your output should look like this. This output confirms that all these components have been successfully created in Kubernetes.

Applying Configuration to Namespace

Execute the command below to verify that the pod is running:

kubectl get pods -n database

Your output should look like this:

Fetching Pods in Namespace

This output confirms that a pod name "postgres-0" is running in your Azure Kubernetes Cluster. But it was not the only pod you created. As I said earlier, to connect to a pod, you need what is called service. And you have declared a service resource in our configuration file which has also been deployed into your Kubernetes.

To get the status of the service, run this command:

kubectl get services -n database

Your output should look like this:

Retrieving Services in Namespace

This output displays the services in the "database" namespace, including a service named "postgres" with the type "LoadBalancer," its internal cluster IP, external IP, and port mappings. You'll utilize the external IP along with the Postgres port "5432" to connect your database with the Node.js application. Note that your external IP will differ from mine.

Node.js Application

In this section, I'll guide you through setting up your Node.js app to connect to a PostgreSQL database in your Azure Kubernetes Service.

We'll cover sending data into the database and retrieving it using Postman. Also, I'll demonstrate how to check if the data remains in the database even if the pod running PostgreSQL in the cluster is deleted.

Configure your Node.js application

Go to the database folder and open the database.js file. Replace the host with your EXTERNAL-IP obtained from the service, and leave the rest unchanged since you've already defined those variables in your config map.

Your database.js file should resemble the CodeSnap below:

CodeSnap of database.js Configuration

Run your Node.js application

In your VS Code terminal, execute this command to start the Node.js application locally:

npm start

Your output should look like this if the connection is established successfully.

Server Listening on Port 4000

If your output looks the same as mine, it indicates that you've successfully connected your Node.js application to the PostgreSQL database running in your Azure Kubernetes cluster. Congratulations! 🎉

Test the application

Testing is a fundamental principle in DevOps operations. It helps us understand the state of the application we've built before releasing it to users. Any application that doesn't pass the testing stage will not be deployed. This is a rule in DevOps.

For this tutorial, you'll be using Postman. You can download Postman here. Postman enables you to test API endpoints by receiving status responses.

Check out this post on how to use Postman to test APIs. If you want to learn more, here's a full course on the subject.

Open your Postman application

To begin using Postman, start by creating a new API request in your preferred workspace. Choose POST. POST requests add new data to the database or server. Then, paste the endpoint URL (localhost:4000/api/v1/admin/register) for your Postman test.

The below screenshot illustrates how you will create a POST request.

Postman URL Endpoint Configuration

In the body, paste the JSON data shown below inside it as shown below:

{   
    "fullName":"Azure postgres freecodecamp",
    "email":"freecodecamp@gmail.com",
    "password":"freecodecamp"
}

Postman Request Body

Once you've set up the request, just click the "Send" button to send it. Postman will then show you status codes, and the response payload as shown below.

Postman API Response

Confirm the data

To confirm that the data you sent into the database exists, make a GET request to this endpoint URL: localhost:4000/api/v1/admin/freecodecamp@gmail.com

Postman GET Request URL Endpoint

When you click send, Postman will then show you status codes and the response payload as shown below. Notice that we didn't put anything in the body because this is a GET request.

Postman GET Request by Email Retrieval Response

Delete the pod to confirm data persistence

We chose to create our PostgreSQL database using a StatefulSet to ensure that data persists even if the pod is destroyed. Let's test this by deleting the pod and checking if the data remains intact.

In your VS Code terminal, execute the command: kubectl delete pod -n database postgres-0.

This command deletes a pod named "postgres-0" in the "database" namespace from your Kubernetes cluster. Your output should look like this.

Deleting Pod in Namespace:

Pod recreation

Kubernetes has a built-in feature called replication controllers or replica sets that ensure a specified number of pod replicas are running at any given time. If a pod is deleted, Kubernetes will automatically recreate it to maintain the desired number of replicas, ensuring high availability

If you run kubectl get pods -n database, you'll notice that Kubernetes has created a new pod with the same name, "postgres-0", to replace the one that was deleted. This ensures that the application remains available and continues to function as expected.

Pod Recreated back in Namespace

Data persistence

Navigate back to Postman and make a GET request to the endpoint URL localhost:4000/api/v1/admin/freecodecamp@gmail.com.

You should get the same response as before. So under the hood, when we delete the pod, the storage disk was not deleted. The storage disk is inside the Azure disk. How do we know that? If you run this command:

kubectl get pvc -n database

you should get this output:

Persistennce Volume Claim details in namespace

This shows details about a storage called "azure-managed-disk-pvc" in your Kubernetes. It's currently in use and has 4 gigabytes of space available. It's set up to be read and written to by one system at a time. This storage is provided by a service called "azuredisk-premium-retain" that we configured earlier.

Clean up resources

In this tutorial, you created Azure resources in a resource group. If you won't need these resources later, delete the resource group from the Azure portal or run the following command in your terminal:

az group delete --name AZURE-POSTGRES-RG --yes

This command might take a minute to run.

Conclusion

We've gone on quite a journey here! You've learned how to deploy a Postgres container in Azure Kubernetes Service (AKS) and integrate it with a Node.js application.

In this tutorial, I guided you through the process of configuring Kubernetes using Azure Kubernetes Service (AKS). You learned to customize YAML files utilizing StatefulSet, Persistent Volume, and Services to deploy a PostgreSQL database on Azure Kubernetes. You also acquired PostgreSQL database credentials running within AKS to establish connectivity with a Node.js application. I then provided detailed instructions on connecting your Node.js Express app to the Postgres container within the AKS cluster.

Thank you for reading!

In today's digital age, managing who can access what resources is more critical than ever. That's where ReBAC comes in. It's a fresh take on authorization, focusing on the relationships between different entities rather than just assigning static roles or attributes.

Traditional access control methods, like Role-Based Access Control (RBAC), assign specific roles to users. While this works in many cases, it can become difficult, especially in dynamic environments where roles and permissions need to adapt quickly. On the other hand, Attribute-Based Access Control (ABAC) offers flexibility based on user attributes, but it can get complex to manage.

Now, ReBAC is all about understanding the intricate web of relationships between entities. Whether it's within an organization, a social media platform, or a project management tool, ReBAC ensures that access control remains dynamic and context-aware.

By the end of this tutorial, you'll have a clear understanding of ReBAC and be able to model a ReBAC scenario.

Key Takeaways

• ReBAC Principles: Understand how ReBAC uses relationships between entities for access control, differing from traditional models.
• Policy Visualization: Learn about representing policies as graphs for clearer management.
• Real-World Examples: Explore ReBAC's application in scenarios like social media platforms and project management tools.
• Benefits of ReBAC: Discover the advantages like granular control and dynamic policy adaptation.
• Permission Models: Get familiar with ReBAC's common models such as Ownership and Hierarchical Models.
• Permify Implementation: Step-by-step guide to implement ReBAC in Permify, including entity definition, relationship establishment, and permissions setup.

What is Relationship-Based Access Control (ReBAC)?

Traditional access control methods, like Role-Based Access Control (RBAC), assign specific roles to users, like giving someone a badge that says "manager" or "employee". But what if the roles aren't so clear-cut, and relationships between people and resources matter more?

That's where Relationship-Based Access Control (ReBAC) steps in. Instead of relying solely on predefined roles or attributes, ReBAC considers the intricate web of connections between users, resources, and other entities. It's like saying, "You can access this because you're connected to it in this specific way", rather than just based on a generic label.

But how does ReBAC actually do this? ReBAC examines the relationships between entities, such as users and resources, and uses these connections to determine access.

Let's break it down further. In our everyday lives, we have relationships that matter. Think about social media – you can see certain posts because you're friends with someone or because someone you follow liked it. ReBAC takes this idea and applies it to access control in systems.

Policy as a Graph

At the core of ReBAC lies the concept of "Policy as a Graph". This idea shows the importance of visualizing access policies through relationships.

Imagine that you have a detailed map of a bustling city. It doesn't just show buildings but also the connections between them – the roads, bridges, and pathways that link everything together.

Now, picture this map as a representation of your organization. Instead of buildings, it represents team members, departments, and their roles. The connections between them symbolize the relationships that dictate access.

This is what we mean by "Policy as a Graph" in ReBAC.

In simpler terms, access policies are like interconnected dots on a graph. Each dot represents an entity, and the lines between them signify the relationships influencing authorization. It's a visual representation that helps us understand the complex web of connections that govern access.

How is ReBAC Different from Other Control Models?

Now, let's explore how ReBAC sets itself apart from other access control models, such as Role-Based Access Control (RBAC).

Unlike traditional models, ReBAC doesn't rely solely on rigid roles or attributes. Instead, it works on deriving permissions from existing relationships. Here's how it stands out:

• Role Derivation:ReBAC allows the creation of authorization policies based on pre-existing relationships. This means that assigning a user a certain role in one context might automatically extend that role to related entities, saving the need for manual assignment.
• Resource Roles:Unlike global roles in traditional models, ReBAC introduces the concept of resource-specific roles (for example: Folder#Owner). These roles are exclusive to the context of a particular resource, ensuring that permissions are relevant and tailored to that specific entity.

Real-World Examples

To better understand how Relationship-Based Access Control (ReBAC) functions in the real world, let's explore two scenarios that mimic everyday complexities.

These examples will help illustrate how ReBAC excels in managing intricate access dynamics.

Instagram-like Social Platform

Consider an Instagram-inspired platform where users hold individual accounts. Each account consists of user-generated content, namely pictures (Pic 1 and Pic 2), chat interactions with different users, and project collaboration.

The user account possesses a list of blocked users who are restricted from viewing pictures. Here's a detailed breakdown of the entities and permissions:

1. Account Entities

• User Account: Represents individual user accounts on the platform.
• Pictures (Pic 1 and Pic 2): Depict user-generated visual content.
• Chats: Captures interaction histories with different users.
• Blocked Users List: Maintains a list of users who are blocked from viewing pictures.

2. Permissions Dynamics

Account Access Permissions:

• "Account#Owner" grants ownership, allowing the user account holder to manage all aspects.
• "Account#Viewer" enables others to view the user's account.

Picture Management Permissions:

• "Picture#Owner" designates ownership at the picture level, allowing the user to edit, delete, and upload pictures.
• "Picture#Viewer" permits normal viewers to only view pictures.
• "BlockedUser#CannotView" ensures that blocked users cannot view pictures.

Chat Interaction Permissions:

• "Chat#Participant" allows users to participate in chat interactions.
• "Chat#BlockedUser" restricts certain users from participating in chats.

Account Editing Permissions:

• "Account#Edit" grants the ability to update account details and preferences.

Instagram-like Social Platform Entities

In this scenario, the "#" symbol represents the relationship between entities when defining permissions. For example, "Account#Owner" signifies ownership of the user account, allowing the account holder to manage all aspects of their account.

Project Management Tool

Imagine a project management tool where teams collaborate on various projects. Entities like "Teams", "Projects", and "Tasks" play central roles, showcasing ReBAC's adaptability:

1. Team Entities:

• Teams: Represent collaborative groups within the project management tool.
• Projects: Encompass various ongoing initiatives.
• Tasks: Break down project activities into manageable tasks.

2. Permissions Dynamics:

Team Leadership Permissions:

• "Team#Lead" designates team leadership, allowing leaders to manage team-related activities.

Project Ownership Permissions:

• "Project#Owner" signifies ownership at the project level, granting comprehensive control over project-related actions.

Task Assignment Permissions:

• "Task#Assignee" designates individuals responsible for specific tasks.

Project Management Tool Entities

These real-world scenarios demonstrate ReBAC's versatility and effectiveness in managing access control in different settings.

Advantages of ReBAC

Now, let's understand why Relationship-Based Access Control (ReBAC) stands out from traditional methods like Role-Based Access Control (RBAC) and Attribute-Based Access Control (ABAC). ReBAC brings a host of benefits to the table, enhancing scalability, flexibility, and adaptability in complex organizational setups. Let's take a closer look at its key advantages:

Granular and Contextual Control

ReBAC allows organizations to define granular access controls tailored to the specific relationships between users, resources, and entities. This ensures that permissions are contextually relevant, providing a nuanced level of control.

Efficient Management of Hierarchies

In scenarios with hierarchical structures, ReBAC simplifies the management of access control. By allowing permissions to be inherited based on relationships, it reduces the need for manual role assignments.

This simplifies the creation of natural connections between different business units, resources, and entities, making it easier to navigate complex hierarchies.

Scalability and Adaptability

ReBAC is designed to scale with organizational growth and changes in relationships. It easily accommodates the introduction of new entities or connections. However, it's crucial to address the challenge of role explosion, where the number of roles grows exponentially alongside asset growth.

Without proper management, this can lead to security risks and administrative overhead. Yet, ReBAC's scalability ensures that access controls remain effective, mitigating these challenges and avoiding the need for extensive modifications.

With these advantages in mind, ReBAC offers a robust framework for access control that meets the evolving needs of modern organizations. Now, let's delve into the common Relationship Type Permission Models to further understand how ReBAC operates.

Common Relationship Type Permission Models

Let’s look at the some of the permission models:

Ownership Model

The Ownership Model in ReBAC is a fundamental concept where ownership relationships streamline authorization within hierarchical structures.

In this model, the act of owning a higher-level entity automatically extends ownership over its subordinate entities.

Imagine a scenario in a cloud storage platform where users create folders to organize their files. In the Ownership Model, the user who creates a folder is designated as the owner.

Consequently, this ownership relation automatically grants the user ownership permissions for all files within that folder.

This hierarchical ownership structure simplifies permission management and mirrors real-world ownership dynamics.

Parent-Child & Hierarchical Model

The Parent-Child & Hierarchical Model is a powerful tool for managing access control in hierarchical structures such as organizational frameworks or file systems.

In this model, permissions granted at the parent level down to its child entities, ensuring a cohesive and efficient authorization system.

Consider a corporate environment where organizations have multiple departments. Using ReBAC's Parent-Child & Hierarchical Model, permissions granted at the organization level, such as admin privileges, seamlessly extend to the organization's departments and their respective members.

This hierarchical flow of permissions reflects the organizational structure, making it easy to manage access control across different levels.

User Groups & Teams Model

The User Groups & Teams Model allows for efficient permission management by grouping users based on shared attributes or project affiliations.

In this model, permissions assigned to a group leader, for instance, can be effortlessly applied to all members of that group.

In a collaborative project management tool, teams serve as user groups. Applying ReBAC's User Groups & Teams Model, the team lead's permissions, like editing or deleting project tasks, can be automatically inherited by all team members.

This streamlined approach simplifies access control in collaborative environments, where team-based permissions are crucial for project efficiency.

These three Relationship-Based Access Control models demonstrate the flexibility and adaptability of ReBAC in handling diverse organizational structures and application domains.

By aligning permissions with inherent relationships among entities, ReBAC provides an intuitive and powerful access control framework.

How to Implement ReBAC with Permify

Now, let's practically implement ReBAC using Permify.

Permify is an open-source authorization as a service platform that allows developers to model, manage, and enforce access control in applications. It provides tools for defining complex authorization rules and relationships between entities, such as users, organizations, and resources.

Permify uses a domain-specific language for creating authorization models and offers a Playground environment for testing these models.

It also supports the creation of relational tuples and attributes for managing dynamic access control scenarios, streamlining the process of implementing robust and flexible authorization systems in software applications.

We'll create a scenario covering both Ownership and Parent-Child & Hierarchical models.

We'll use the Permify Playground for modeling.

Modeling

Modeling in Permify involves creating a schema that defines the relationships and permissions between different entities in your system.

Here's a simplified process:

1. Define Entities: Start by creating entities that represent the resources in your system (for example: users, organizations, teams).
2. Define Relations: Establish relationships between these entities. For example, an organization can have members and admins, or a team can be part of an organization.
3. Define Actions and Permissions: Specify the actions that can be performed on each entity and the conditions under which they are allowed. For example, only admins can delete an organization.

Permify uses its own language for modeling authorization logic, allowing for complex structures using set-algebraic operators. The modeling process includes defining entities, relations, actions, permissions, and, if needed, attributes for more advanced scenarios like ABAC (Attribute-Based Access Control).

Modeling in Permify is about creating a clear blueprint of your organization's structure and defining who gets to do what.

Let's break down how to model a schema in Permify.

Step 1: Define Entities

Entities are the core objects in your model. In this case, we have user, organization, department, project, file, and task.

entity user {}
entity organization {}
entity department {}
entity project {}
entity file {}
entity task {}

Step 2: Establish Relationships

Next, we specify relationships between these entities. This defines how they are connected.

Organization:

• Has admin who are users.

entity organization {
    relation admin @user
}

Department:

• Belongs to an organization (parent).
• Has head, manager, and employee roles, all of which are users.

entity department {
    relation parent @organization
    relation head @user
    relation manager @user
    relation employee @user
}

Project:

• Belongs to a department (parent).

entity project {
    relation parent @department
}

File:

• Belongs to a department (parent).
• Has an owner who is a user.

entity file {
    relation parent @department
    relation owner @user
}

Task:

• Belongs to a project (parent).
• Has an assignee who is a user.

entity task {
    relation parent @project
    relation assignee @user
}

Step 3: Define Permissions

Permissions determine what actions specific roles can perform on each entity.

Project:

• contribute_to_project permission is granted to employee or manager of the parent department.

entity project {
    // ... (existing relations)
    permission contribute_to_project = parent.employee or parent.manager
}

File:

• read, edit, and delete permissions are controlled based on the manager of the parent department and the owner.

entity file {
    // ... (existing relations)
    permission read   = parent.manager or owner
    permission edit   = parent.manager or owner
    permission delete = owner
}

Task:

• view_task permission is given to the assignee.

entity task {
    // ... (existing relations)
    permission view_task = assignee
}

Full Schema:

// Define entities
entity user {}

entity organization {
    // Organizational roles
    relation admin @user
}

entity department {
    // Department roles
    relation parent @organization
    relation head @user
    relation manager @user
    relation employee @user
}

entity project {
    // Project roles
    relation parent @department

    // Permissions
    permission contribute_to_project = parent.employee or parent.manager
}

entity file {
    // Represents files' parent entity (department)
    relation parent @department

    // Represents the owner of the file
    relation owner @user

    // Permissions
    permission read   = parent.manager or owner
    permission edit   = parent.manager or owner
    permission delete = owner
}

entity task {
    // Represents tasks' parent entity (project)
    relation parent @project

    // Represents the assignee of the task
    relation assignee @user

    // Permissions
    permission view_task   = assignee
}

Relation Tuples

The creation of relation tuples for the organization schema can be accomplished through the Permify Playground and API.

Here's how the relationship tuples would be structured according to the schema:

User and Organization Relationships:

• For assigning a user as an admin in an organization, the tuple would be: organization:ID#admin@user:ID.
• To denote a user as a member of an organization: organization:ID#member@user:ID.

User and Department Relationships:

• Assigning a head to a department: department:ID#head@user:ID.
• Assigning a manager to a department: department:ID#manager@user:ID.
• Associating an employee with a department: department:ID#employee@user:ID.
• To set a department's parent organization: department:ID#parent@organization:ID.

Project and Department Relationships:

• To define the parent department of a project: project:ID#parent@department:ID.

File Management:

• Associating a file with its parent department: file:ID#parent@department:ID.
• Defining the owner of a file: file:ID#owner@user:ID.

Task Management:

• Linking a task to its parent project: task:ID#parent@project:ID.
• Assigning a user as the assignee of a task: task:ID#assignee@user:ID.

In each of these tuples, ID is a placeholder that should be replaced with the actual identifier of the entity or user in your system.

For instance, if you have an organization with an ID of 1 and a user with an ID of 3, and you want to assign this user as an admin of that organization, the tuple would be organization:1#admin@user:3.

Relationships dashboard

These tuples are created and managed using the Permify API. The API allows for creating, updating, and deleting these tuples as needed, reflecting the dynamic nature of relationships and permissions in an organization. This flexibility ensures that your authorization data is always up-to-date and consistent with the current state of your system's entities and their relationships.

Enforcement

To enforce access control in your schema using Permify, you can create scenarios in the Permify Playground's Enforcement section. This is done using YAML to define various test scenarios.

Here's an example based on your schema and the created relation tuples:

Check if a user (for example: user:2) can contribute to a project:

• Entity: project:1
• Subject: user:2
• Assertion: contribute_to_project: true or false (depending on whether user:2 is an employee or manager in the parent department of project:1).

- name: user_access_test
  checks:
    - entity: project:1
      subject: user:2
      context: null
      assertions:
        contribute_to_project: false
  entity_filters: []
  subject_filters: []

Check if a user (for example: user:4) can view a task:

• Entity: task:1
• Subject: user:4
• Assertion: view_task: true or false (true if user:4 is the assignee of the task).

- name: user_access_test
  checks:
    - entity: task:1
      subject: user:4
      context: null
      assertions:
        view_task: false
  entity_filters: []
  subject_filters: []

These scenarios will help you validate the permissions as per your schema in a controlled environment.

Each assertion in the YAML scenario will define the expected outcome (true or false) for a particular action or permission based on your schema and data tuples.

YAML representation

For detailed steps and examples, refer to the Permify Modeling Documentation.

Conclusion

By following these steps, you can effectively implement a sophisticated ReBAC system using Permify.

This implementation will provide a robust, flexible, and secure access control framework tailored to the unique needs and relationships within your organization.

Isolation is a guarantee that concurrently running transactions should not interfere with each other. This is arguably the most important ACID property, because different DBMS can often have different default isolation levels. And you may need to change this based on what is needed for your application.

In a previous article, I explained the two main isolation levels used by most DBMS. These are the read committed and repeatable read isolation levels.

Pessimistic and optimistic concurrency controls essentially explain some of the ways a database is able to achieve these two isolation guarantees.

Table of Contents

1. Pessimistic Concurrency Control

2. Pessimistic Concurrency Control Analogy

3. Real-World Example of Pessimistic Concurrency Control

4. Pros and Cons of Pessimistic Concurrency Control

5. How it Guarantees the Read Committed Isolation Level

6. Optimistic Concurrency Control

7. Real-World Example of Optimistic Concurrency Control

8. Pros and Cons of Optimistic Concurrency Control

9. How it Guarantees the Repeatable Read Isolation Level

10. Bringing it Together

Pessimistic Concurrency Control

With pessimistic concurrency control, the DBMS assumes that conflicts between transactions are likely to occur. It is pessimistic – that is, it assumes that if something can go wrong, it will go wrong. This pessimism prevents conflicts from occurring by blocking them before they get a chance to start.

To prevent these conflicts, it locks the data that a transaction is using until the transaction is completed. This approach is 'pessimistic' because it assumes the worst-case scenario – that every transaction might lead to a conflict. The data is therefore locked in order to prevent conflicts from happening.

I've mentioned two technical terms here that need clarification: locks and conflict.

What are locks?

A lock is a mechanism used to control access to a database item, like a row or table. Locks ensure data integrity, if multiple transactions are occurring at the same time.

In very simple terms, a lock is analogous to a reservation on the database item. A reservation, be it a restaurant, hotel, or a train, prevents other people from using the resource you reserved for a fixed duration of time. Locks work in a similar way.

There are two types of locks: a read lock and a write lock.

A read lock can be shared by multiple transactions trying to read the same database item. But it blocks other transactions from updating that database item.

A write lock is exclusive – that is, it can only be held by a single transaction. A transaction with a write lock on a database item blocks every other transaction from reading or updating that database item.

What are conflicts?

A conflict refers to a situation where multiple transactions are attempting to access and modify the same data concurrently, in a way that could lead to inconsistencies or errors in the database.

A Library Analogy for Pessimistic Concurrency Control

First, let us describe an analogy for a write lock.

Imagine you're at a library, and you want to borrow a hard copy of a popular book, say, The Great Gatsby by F. Scott Fitzgerald.

Write locks are analogous to borrowing a physical book from the library

With a write lock, the librarian assumes that there can be conflicts over who gets to borrow the book. So, they implement a strict rule to avoid conflicts: only one person can hold the reservation for a physical book at a time.

When you reserve the book, no one else can borrow it. The book is available to be reserved again only once it is returned. This is similar to how a write lock works.

Write locks are exclusive. This means that they can only he held by a single transaction at any time. Similarly, reserving a physical book from the library means no one else has access to it. Only the person with the reservation can read the book, or write in it (although writing in a library book is bad form).

Read locks work a bit differently.

A read lock is analogous to someone making a reservation to borrow an e-book. Borrowing an e-book is not a very popular thing to do, but some libraries do have such a service.

Many people can make the same reservation for the same e-book without any conflict. One person borrowing an e-book version of The Great Gatsby does not stop others from doing the same. But no one who borrows an e-book can update it, by scribbling notes in it that can be seen by others, for example.

Read locks are analogous to borrowing an e-book from the library

Pessimistic concurrency control is very safe because it prevents conflicts from occurring by blocking them before they get a chance to start. A write lock on a database item prevents other transactions from reading or updating that item while that lock is held, similar to to how a library stops more than one person from trying to borrow the same physical book at the same time.

A read lock on a database item allows other transactions to also obtain a read lock for that item, but prevents transactions from updating that item. This is analogous to borrowing an e-book, where multiple people can borrow the same e-book at the same time, but can’t make any updates to it.

A Simple Real-World Example of Pessimistic Concurrency Control in Action

Let's illustrate how pessimistic concurrency control works using a simple example involving a bank balance database table. Assume we have a table named Accounts with the following columns: AccountID and Balance.

Database columns for AccountID and Balance

Two transactions, T1 and T2, intend to update the balance of account 12345. T1 wants to withdraw $300, and T2 wants to deposit $400. At the end of these two transactions, the account balance should read $1600

Here are the steps of how this will work using write locks:

1. Start of T1 (Withdrawal): T1 requests to update the balance of AccountID 12345. The database system places an exclusive write lock on the row for AccountID 12345, preventing other transactions from reading or writing to this row until T1 is completed. T1 reads the balance ($1500).

2. T1 Processing: T1 calculates the new balance as $1200 ($1500 - $300).

3. Commit T1: T1 writes the new balance ($1200) back to the database. Upon successful commit, T1 releases the exclusive lock on AccountID 12345.

4. Start of T2 (Deposit) After T1 Completes: Now that T1 has completed and the lock is released, T2 can start. T2 attempts to read and update the balance for AccountID 12345. The database system places an exclusive lock on the row for AccountID 12345 for T2, ensuring no other transactions can interfere. T2 reads the updated balance ($1200).

5. T2 Processing: T2 calculates the new balance as $1600 ($1200 + $400).

6. Commit T2: T2 writes the new balance ($1600) back to the database. Upon successful commit, T2 releases the exclusive lock on AccountID 12345.

7. Result: The Accounts table is updated using locks After T1: $1200 After T2: $1600

Without a write lock in this example, T1 and T2 could read the original balance of $1500 at the same time. So, instead of a balance of $1200 after T1 has committed, T2 still reads the original balance of $1500 and adds $400. This would cause the final balance to be $1500 + $400 = $1900 (instead of $1600).

Absence of locking has created free money, which is never a bad thing for a customer. But, if money can be conjured out of thin air because of these conflicts, it can also vanish, and accidentally shrinking bank balances are a quick way to make customers unhappy.

Benefits and Challenges of Pessimistic Concurrency Control

Just like reserving a book ensures that it's set aside for one person, pessimistic concurrency control locks data for a single transaction. Other transactions cannot access or modify this data until the lock is released.

This method prevents two people from trying to take out the same popular book at the same time, thereby avoiding disputes. Similarly, in databases, it stops conflicts due to concurrent transactions before they get a chance to start.

But this approach can be inefficient. The reserved book might sit on the reserved shelf for a while, stopping other people from reading it.

In databases, this locking mechanism can lead to underutilisation of resources and a slowdown in the speed transactions take to complete, since a subset of the data is locked and inaccessible to other transactions.

How Pessimistic Concurrency Controls Guarantee the Read Committed Isolation Level

So, how exactly does pessimistic concurrency control work in ensuring the isolation guarantee, that is the “I” in ACID? The implementation details can vary across different DBMS. But the explanation here shows the general approach.

Recall that the read committed isolation level prevents dirty writes and dirty reads.

Preventing Dirty Writes

Overwriting data that has already been written by another transaction but not yet committed is called a dirty write. A common approach to preventing dirty writes is to use pessimistic concurrency control. For example, by using a write lock at the row level.

When a transaction wants to modify a row, it acquires a lock on that row and holds it until the transaction is complete. Recall that write locks can only be held by a single transaction. This prevents another transaction from acquiring a lock to modify that row.

Preventing Dirty Reads

Reading data from another transaction that has not yet been committed is called a dirty read. Dirty reads are prevented using either a read or write lock. Once a transaction acquires a read lock on a database item, it will prevent updates to that item.

But what happens if you are trying to read something that is already being updated but the transaction has not yet committed? In this instance, the write lock saves the day again.

Since write locks are exclusive (can’t be shared with other transactions), any transaction wanting to read the same database item will have to wait until the transaction with the write lock is committed (or aborted, if it fails). This prevents other transactions from reading uncommitted changes.

Optimistic Concurrency Control

With optimistic concurrency control, transactions do not obtain locks on data when they read or write. The "Optimistic" in the name comes from assuming that conflicts are unlikely to occur, so locks are not needed. If something does go wrong though, conflicts will still be prevented and everything will be OK.

Unlike pessimistic concurrency control – which prevents conflicts from occurring by blocking them before they get a chance to start – optimistic concurrency control checks for conflicts at the end of a transaction.

With optimistic concurrency control, multiple transactions can read or update the same database item without acquiring locks. How exactly does this work?

Every time a transaction wants to update a database item, say a row, it will also read two additional columns added to every table by the DBMS – the timestamp and the version number. Before that transaction is committed, it checks if another transaction has made any change(s) to that row by confirming if the version number and timestamp are the same.

If they have changed, that means another transaction has updated that row, so the initial transaction will have to be retried.

A Simple Real-World Example of Optimistic Concurrency Control in Action

Let's illustrate how optimistic concurrency control works using a simple example involving a bank balance database table. Assume we have a table named Accounts with the following columns: AccountID, Balance, VersionNumber, and Timestamp.

Table showing AccountID, Balance, VersionNumber, and Timestamp columns

Two transactions, T1 and T2, intend to update the balance of account 12345 at the same time. T1 wants to withdraw $200, and T2 wants to deposit $300. At the end of these two transactions, the account balance should read $1100

Here are the steps of how this will work:

1. Start of Transactions: T1 reads the balance, version number, and timestamp for AccountID 12345. Simultaneously, T2 reads the same row with the same balance, version number, and timestamp.

2. Processing: T1 calculates the new balance as $800 ($1000 - $200) but does not write it back immediately. T2 calculates the new balance as $1300 ($1000 + $300) but also waits to commit.

3. Attempt to Commit T1: Before committing, T1 checks the current VersionNumber and Timestamp of AccountID 12345 in the database. Since no other transaction has modified the row, T1 updates the balance to $800, increments the VersionNumber to 2, updates the Timestamp, and commits successfully.

4. Attempt to Commit T2: T2 attempts to commit by first verifying the VersionNumber and Timestamp. T2 finds that the VersionNumber and Timestamp have changed (now VersionNumber is 2, and Timestamp is updated), indicating another transaction (T1) has updated the row. Since the version number and timestamp have changed, T2 realises there was a conflict.

5. Resolution for T2: T2 must restart its transaction. It re-reads the updated balance of $800, the new VersionNumber 2, and the updated Timestamp. T2 recalculates the new balance as $1100 ($800 + $300), updates the VersionNumber to 3, updates the Timestamp, and commits successfully.

Result: The Accounts table is updated sequentially and safely without any locks: After T1: $800, VersionNumber: 2. After T2: $1100, VersionNumber: 3.

Benefits and Challenges of Optimistic Concurrency Control

On the positive side, avoiding locks allows for high levels of concurrency. This is particularly beneficial in read-heavy workloads where transactions are less likely to conflict, allowing the system to handle more transactions in a given period. For example, database backups and analytical queries typically used in a data warehouse.

But in scenarios where conflicts are frequent, the cost of repeatedly rolling back and retrying transactions can outweigh the benefits of avoiding locks, making optimistic concurrency control less efficient

How Optimistic Concurrency Controls Guarantee the Repeatable Read Isolation level

The repeatable read is more strict isolation level in that it has the same guarantees as read committed isolation, plus it guarantees that reads are repeatable.

A repeatable read guarantees that if a transaction reads a row of data, any subsequent reads of that same row of data within the same transaction will yield the same result, regardless of changes made by other transactions. This consistency is maintained throughout the duration of the transaction.

How can a repeatable read be achieved? Pessimistic control using a read lock can help with this, since a transaction with a read lock on a database item will prevent that item from being updated. But this can be inefficient, since a long running read transaction can block updates from happening to that database item.

Multi-Version Concurrency Control (MVCC) is a concurrency control method used by some DBMS to allow multiple transactions to access the same data simultaneously without locking the data. This makes it a popular choice for reducing lock contention and improving the scalability of databases.

MVCC achieves this by keeping multiple versions of data objects, which helps to manage different visibility levels for transactions depending on their timestamps or version numbers.

Bringing it Together

A lock is a mechanism used to control access to a database item, like a row or table. In very simple terms, it is analogous to a reservation on a database item.

Pessimistic concurrency control assumes the worst. It assumes that conflicts are likely to happen, so locks are used to block transactions that can cause conflicts before they even get a chance to start.

In situations where conflicts are common, such as a write heavy application, this approach can prevent the overhead associated with frequent rollbacks and retries (which happens in optimistic concurrency control) by ensuring exclusive access to database items during transactions.

Optimistic concurrency control assumes the best. It assumes that conflicts are unlikely to occur, so locks are not needed to stop transactions before they start. Instead, potential conflicts are checked at the end of a transaction and if any are found, the transaction is aborted or retried.

Optimistic concurrency control is useful for read heavy transactions with infrequent writes, as it allows multiple transactions to proceed without the need to use a lock, which can be inefficient.

But you'll need to have a way to back up your data, as it's crucial to ensuring the functionality of the production site and the stability of everyday workflows. So creating Azure SQL backups can help you and your team avoid data loss emergencies and have the shortest possible downtime while maintaining control over the infrastructure.

Another reason to have a current Azure database backup is Microsoft’s policy. Microsoft uses the shared responsibility model, which makes the user responsible for data integrity and recovery while Microsoft only ensures the availability of its services. Microsoft directly recommends using third-party solutions to create database backups.

In case you run a local SQL Server, you'll need to prepare for the possibility of hardware failures that may result in data loss and downtime. An SQL database on Azure helps mitigate that risk, although it's still prone to human errors or cloud-specific threats like malware.

These and other threats make enabling Azure SQL database backups necessary for any organization using Microsoft’s service to manage and process data.

In this tutorial, you'll learn about backing up Azure databases and restoring your data on demand with native instruments provided by Microsoft, including methods like:

• Built-in Azure database backup functionality
• Cloud archiving
• Secondary database and table management
• Linked server
• Stretch Database

Why Backup Your SQL Azure Database?

Although I covered this briefly in the intro, there are many reasons to back up your SQL Azure database data.

Disaster Recovery

Data centers can be damaged or destroyed by planned cyberattacks, random malware infiltration (check out this article to discover more on ransomware protection), and natural disasters like floods or hurricanes, among others. Backups can be used to swiftly recover data and restore operations after various disaster cases.

Data Loss Prevention

Data corruption, hardware failure, and accidental or malicious deletion lead to data loss and can threaten an organization. Backup workflows set up to run regularly mean you can quickly recover the data that was lost or corrupted.

Compliance and Regulations

Compliance requirements and legislative regulations can be severe regardless of your organization’s industry. Mostly, laws require you to keep up with security and perform regular backups for compliance.

Testing and Development

You can use backups to create Azure database copies for development, troubleshooting, or testing. Thus, you can fix, develop, or improve your organization’s workflows without involving the production environment.

How to Back Up Your Azure SQL Database

Backing up your Azure SQL database can be challenging if you go through the process without preparation. So that's why I wrote this guide – to help you be prepared. Here's what we'll cover in the following sections:

• Requirements for SQL Azure database backup
• How to configure database backups in Azure with native tools
• Cloud archiving
• Backup verification and data restoration

SQL Azure Database Backup Requirements

Before backing up your SQL Azure databases, you need to create and configure Azure storage. Before you do that, you'll need to go through the following steps:

First, open the Azure management portal and find Create a Resource.

Then, go to Storage > Storage account. Provide the information, including the location and names of a storage account and resource group according to your preferences. After you enter the information, hit Next.

Storage account config

Then go to the advanced section for additional settings. The optimal choice is to set _"_Secure transfer required" as Enabled and "Allow access" from All networks. For more resilience in case of human error, you can set "Blob soft delete" as Enabled. With that setting, you can quickly correct accidental deletions in the storage account.

After that, specify the tags you need to simplify navigating through your infrastructure.

Azure backup storage tags

Check the settings once more. If everything is configured correctly, hit Create. Your new storage account is now created.

Once the storage volume is created, it's time to configure a backup data storage container.

Go to the storage account, find Containers, then hit the + Container tab there. After that, specify a name for the new container and switch Public access level to Private (no anonymous access).

Container Azure storage account

You can then use the container as a backup storage (.bak files will be stored there in that case).

Azure Database Backup Configuration

Now, everything is set up for you to back up your SQL Azure database. Do the following to create a database backup:

First, go to SQL Management Studio, and establish a connection with the SQL server. After that, right-click the database that should be backed up. The context menu appears, so go to Tasks there. Then hit Back Up….

SQL server tasks backup

Then find the Destination tab, and set Back up to line to URL there. After that, hit New container.

Next, sign in to Azure. Pick the container you created before. Provide your credentials, then hit OK.

You’ll see a message asking you to sign in to Azure subscription. Then, choose the container and hit OK.

Now, you'll see the configured backup destination URL listed. To start the workflow to back up your Azure data, hit OK once again.

When your SQL Azure database backup is completed, the message shows up: "The backup of database ‘your database name’ completed successfully."

The backup file in the target container should now be visible from the Azure portal.

Keep in mind that, when uploading backups to any cloud storage, you may face issues if your network connection is not fast enough.

In case that’s true for you, you can reorganize your backup workflows: send backup data to a physical storage drive first, and then send another copy to the cloud. Thus, you can prevent operational challenges that might appear due to network bandwidth deficiency.

Cloud Archiving for Azure Database Backups

Databases tend to grow in volume as the organization grows. This means that the storage space required to fit the data and that data's backup increases significantly. Also, the original data volume prolongs the duration of full backup workflows, posing another challenge.

Of course, the first way to get more storage space is to revise your data regularly and erase records that are irrelevant, outdated, or unnecessary otherwise. Still, it's sometimes difficult to determine if data will be or become unnecessary or irrelevant, especially when dealing with issues of compliance.

To keep your organization compliant in any case, data archiving can help you solve two problems at once: you can ensure data accessibility on one hand, and save storage space on the other hand.

To archive your SQL database in the cloud, you should first save that database copy to an Azure blob container. Then, to move a newly created blob to the archive tier in the Azure portal, do the following:

1. Go to the required container where the SQL database is stored.
2. Choose the blob that you need to move.
3. Hit Change tier.

Azure blob container change tier

4. In the Access tier dropdown menu, choose Archive.

Azure blob change tier

5. Hit Save.

Additionally, the Archive storage tier is the most affordable one in Azure, meaning that you can reduce your database data TCO with it.

Secondary Database and Table Management

There exist several workflows that can help you set up Azure database backup archiving for your organization. When you need the data to stay in the initial database, for instance, creating a separate table and moving that data there can be your choice. However, the filegroup of that table should stay apart from the main database and be moved to a separate disk whenever possible.

Most probably, you’ll want to let users access the data you send to a separate table. To make that happen, you can create a view merging the relevant tables and redirect the requests to that view, not to the original table. Doing things that way, you can keep the data accessible while dealing with maintenance faster.

SQL Server Linking

If you can’t move the data to another database for internal reasons such as special Azure backup policies, you can consider maintaining your primary database accordingly.

Here, the outcome is likely to be that of the previous case, but you need to link the SQL servers or configure apps so they can send direct requests to your second server.

The downside here is that your SQL database, which was supposed to be a backup one, becomes a production database and gains appropriate importance for an organization.

There are two ways to create linked servers via SQL Server Management Studio (SSMS):

• sp_addlinkedserver (Transact-SQL) system stored procedure that creates a linked server
• SSMS GUI

After you've ensured that you have appropriate access rights on both server instances you need to link, the network is configured appropriately to access them, and SSMS is installed, you'll need to go through the following steps:

First, open SSMS.

Microsoft SSMS

Connect to the instance where you need to establish a linked server. Then find Object Explorer > Server Objects, then right-click Linked Servers.

Pick New Linked Server from the dropdown:

New linked server SSMS

Then configure the server properties, including name, server type, provider and product name:

Linked server configuration SSMS

Then you'll just need to complete the security configuration, set up the server options, and complete connection testing.

Original Data Deletion

When you don’t need 24/7 data availability but need the data stored due to internal policies or compliance requirements, you can choose what's probably the simplest solution to increase storage space efficiency. Just back up the data that can stay unavailable and then delete the originals from the main database. Accessing any records you may need will still be possible via the backup.

Stretch Database

Aiming to make data management of organizations’ databases simpler, Microsoft implemented a Stretch Database feature in SQL Server 2016. With this feature, you can get an SQL backup to Azure after you send the data from the hosted database to an Azure SQL database. The method enables you to increase overall infrastructure cost-efficiency by simplifying backup workflows.

To enable this workflow in your environment, develop the policy specifying the data on a hosted server to send to Azure. You don’t need to introduce any changes in applications that use the production database: SQL Server can independently get the records from the Azure SQL Database.

Azure Database Backups Verification and Restoration

During an SQL Azure database backup, you can choose to create such backups WITH CHECKSUMS or without them. When the workflow is complete, I recommend you use the following command: RESTORE VERIFYONLY. This command enables you to check the recoverability of backup files.

To access the data, you can restore records from a backup to a different database. With Azure Automation scripts on backups, you can accelerate the restoration process, thus minimizing downtime and increasing the overall resilience of your Azure infrastructure.

You need to follow only a few steps to restore an Azure SQL database to a required recovery point from a backup. Still, keep in mind that your subscription can define the available retention period which can vary from 7 to 35 days. A native tool for backup restoration to SQL servers is Server Management Studio.

To Conclude

The critical nature of Azure SQL database data makes Azure SQL backups obligatory for any organization that uses this Microsoft solution. In this guide, we reviewed the process of creating SQL Azure database backup using native Microsoft tools.

These tools provide data backup, backup verification, and recovery functionality along with some automation.

You can also implement a specialized all-in-one data protection solution, such as NAKIVO, the company where I work. It can help you make your data backup workflows more efficient.

Most popular DBMS like MySQL, PostgresSQL and Oracle have ACID guarantees out of the box. Others have partial ACID guarantees like Redis, DynamoDB, and Cassandra. The trend, however, seems to be that more and more DBMS are offering ACID compliance.

It is important to note that while a lot of DBMS may say they are ACID compliant, the implementation of this compliance can vary.

So, for example, if isolation is a key property that you need for an application you are building, you need to understand how exactly your chosen DBMS implements isolation.

This article will explain what transactions are, and go through, in detail, what atomicity, consistency, isolation and durability mean, using analogies and real world examples.

Table of Contents:

1. What are Transactions?

2. What Does Atomicity Mean?

3. What Does Consistency Mean?

4. What Does Isolation Mean?

5. What Does Durability Mean?

6. Bringing it Together

What are Transactions?

Lots of things can go wrong when using a database:

• the database hardware or software can fail

• the application calling the database can fail mid-operation

• the network can be flooded with more traffic can it can handle (rendering it inoperable)

• several clients can make writes at the same time that overwrite the other’s changes

• clients can read phantom data that should not be in the database

And so on – this is in no way an exhaustive list of things that can go wrong.

Since things can fail in more ways than we can possibly anticipate, trying to prevent every possible failure can become unnecessarily expensive and complicated. Instead, it is better to design a system that can continue to operate in spite of a failure. Transactions allow us to do this.

Transactions serve a single purpose: they make sure a system is fault tolerant. If a failure in a system occurs, can the system continue to operate without complete catastrophe? Phrased differently, can the system tolerate faults? An answer of ‘yes’ to this question means that such a system is fault tolerant.

So, what exactly is a transaction?

Not this kind of transaction

A transaction is an abstraction. It is a collection of operations (reads and writes) that are treated as a single logical operation.

Imagine you want to buy a single book from an online store, say amazon.com. The steps below show a simplified view of what needs to happen:

1. First, you select the book, which adds the item to your basket.

2. The inventory quantity of the book is checked to ensure it is valid (that is, the inventory value for the title you are buying needs to be greater than 0).

3. You click ‘buy’, which updates Amazon’s inventory for the book and decreases it by 1 (since you are buying a single book).

4. Also, your bank account balance is updated to account for the cost of the book.

A transaction ensures that all operations related to the purchase are treated as a single operation. If any part of the transaction fails, the entire transaction is rolled back, leaving the database in a state as if the customer had never attempted the purchase, thus maintaining the integrity of the data.

The transaction is committed when all the operations within the transaction are successfully completed and their results are permanently recorded. This permanence is typically achieved by writing the changes to the database's storage, which could be on disk for traditional databases or in memory for in-memory databases like Redis.

By treating all of these different operations as a single logical operation, the database is able to offer some guarantees as to how it can be fault tolerant. These guarantees are atomicity, consistency, isolation and durability.

What Does Atomicity Mean?

Atomicity simply means that all queries in a transaction must succeed for the transaction to succeed. If one query fails, the entire transaction fails.

An Atomic Restaurant

Imagine using a self-service machine at a fast-food restaurant. The transaction in this case is ordering food, and consists of two separate operations:

1. Select food

2. Make payment

Both of these must succeed for the transaction to succeed. If either fails, the transaction fails.

Customer making an order in an "atomic" restaurant

You select your burger, fries, and a drink from the touchscreen menu. The machine prompts you to pay, and only after your payment is processed successfully, it sends your order to the kitchen. Moments later, your entire order is ready, and you pick it up from the counter.

This is an atomic operation: the transaction (ordering food) is either entirely completed (if you select your food item and make a payment) or not completed at all.

Either part of the transaction failing means the entire transaction will fail. If your payment fails, the machine won't process any part of the order, so the transaction fails. If you make a payment without selecting a food item, the transaction also fails, as there is nothing for the kitchen to prepare.

A Non-Atomic Restaurant

Now consider the alternative, a traditional sit-down restaurant where you order several dishes. As each dish is prepared, it is brought to your table.

customer making an order in a "non-atomic" restaurant

Again, the transaction is ordering food, and consists of two separate operations:

1. Select food

2. Make payment

In this non-atomic restaurant, failure to make a payment does not stop the transaction from completing, since you pay after you have finished your meal. Partial failures do not cause a transaction to fail.

This creates a risk for the restaurant. Customers that choose to dine and dash can order food to their heart’s delight and then simply leave without paying, causing a financial loss for the restaurant.

non-atomic restaurants are at risk of customers doing a dine and dash

Atomic Transactions

If several SQL queries are grouped together in a transaction, atomicity is a guarantee that, should any of the queries fail for any reason (hardware, application or networking problems) then the transaction is aborted and the database returns to its previous state, as if nothing had happened.

Without atomicity, if a failure occurs while some queries are running, it is difficult to know which queries have been committed (that is, completed) and which have not. Running the queries again after a failure can compound the problem, since you risk introducing incorrect data to the database by re-running queries that previously succeeded.

Atomic transactions prevent such uncertainty, since you know that if the previous transaction failed, it failed in its entirety, and you can simply retry without worrying about introducing inconsistent data.

What Does Consistency Mean?

Consistency can mean different things in cloud/software engineering, depending on the context. In the case of ACID, the “C” was most likely added to make the acronym work.

Consistency in the context of ACID means consistency in data, which is defined by the creator of the database. The technical term for consistency in data is called referential integrity. Referential integrity is a method of ensuring that relationships between tables remain consistent. It's usually enforced through the use of foreign keys.

To understand referential integrity, consider the following.

Imagine a library system with two types of cards: a book card and a borrower's card.

• The book card lists all the books available in the library.

• The borrower's card tracks which books are borrowed by which members.

A book card and borrower card for a library

The rule of the library is that a book can only be listed on a borrower's card if it exists on a book card. This is referential integrity. If someone tries to list a book on a borrower's card that isn't on the book card (that is a book that doesn’t exist in the library), the system will not allow it.

While atomicity, isolation and durability are properties intrinsic to the database itself, consistency in data, or referential integrity, is not a property intrinsic to the database.

Consistency is defined by the creator of the database. The application calling the database relies on the atomicity and isolation properties of the database to maintain that consistency.

What Does Isolation Mean?

Isolation is a guarantee that concurrently running transactions should not interfere with each other. Concurrency here refers to two or more transactions trying to modify or read the same database record(s) at the same time.

There are three levels of transaction isolation. I'll just explain the two main ones below, arranged in order from the least strict to most strict.

Read Committed

This gives two guarantees. It prevents dirty reads and dirty writes.

No Dirty Reads: Reading data from another transaction that has not yet been committed is called a dirty read. With the read committed isolation level, you will only see data that has been committed by another transaction.

No Dirty Writes: Overwriting data that has already been written by another transaction but not yet committed is called a dirty write.

To understand how read committed isolation works, consider the following example.

Imagine a fast-food restaurant with only one last special burger available, and two hungry customers, Marie and Marko, are trying to buy it simultaneously.

Two customers ordering a burger at the same time

1. Marie checks the availability of burgers and sees the last one available. Unknown to her, Marko’s order is being processed but hasn't been finalised in the system, as he has not paid. Since his order has not yet been finalised, Marie is not aware that his order conflicts with her own. This is similar to a transaction reading the most recently committed data, where it does not see uncommitted changes (like Marko's pending order).

2. Marie places an order based on this incomplete information, thinking a burger is available.

3. Once Marko pays, the system updates to show that there are no burgers left. This is similar to a transaction being committed

4. Marie’s order will have to be aborted since there are no burgers left.

The key point here is step #3. What if Marko’s payment failed at this stage? Then the transaction will not be committed and there would still be a burger available for Marie.

In this example, read committed isolation ensures that Marie is not prematurely excluded from buying the burger just because someone else said they wanted it. Only committed transactions can be read. Therefore, the burger is available to be ordered as long as no one has paid for it.

Repeatable Read

The repeatable read is a more strict isolation level, in that it has the same guarantees as read committed isolation – plus it guarantees that reads are repeatable.

A repeatable read guarantees that if a transaction reads a row of data, any subsequent reads of that same row of data within the same transaction will yield the same result, regardless of changes made by other transactions. This consistency is maintained throughout the duration of the transaction.

When a transaction reads the same data twice, but sees a different value in each read because a committed transaction has updated the value between the two reads, this is called a fuzzy read. The repeatable read isolation level prevents fuzzy reads.

Fuzzy reads are neither inherently good nor bad. It all depends on what you are trying to achieve.

Fuzzy reads are bad for long-running, read-only transactions, since new writes are likely to occur during the transaction and this can cause inconsistencies in the data. Examples of long running, read-only transactions are a database backup and analytical queries typically used in a data warehouse.

Repeatable reads are usually implemented by the DBMS by reading from a snapshot of the database which remains unchanged for the duration of the transaction, thereby ignoring any new committed writes in that period.

What Does Durability Mean?

Durability is a guarantee that changes made by a committed transaction must not be lost. All committed transactions must be persisted on durable, non-volatile storage, that is on disk. This ensures that any committed transactions are protected even if the database crashes.

Naturally, durability cannot protect against destruction of the disk which stores the data. Additional redundancy can be added by having backups of your database stored separately from the original.

Bringing it Together

ACID (Atomicity, Consistency, Isolation, and Durability) provides a set of guarantees when working with a DBMS. While most relational DBMS are ACID compliant, the implementation of this compliance can vary.

Atomicity ensures that all parts of a transaction are completed or none at all. Partial failures are not allowed.

Consistency, or referential integrity, ensures that data remains accurate and reliable, adhering to predefined rules. Unlike the other priorities, consistency is not intrinsic to the DBMS itself. Instead, the application calling the database relies on the atomicity and isolation properties of the database to maintain consistency.

Isolation is a guarantee that concurrently running transactions should not interfere with each other. This is arguably the most important property because a DBMS can often have different default isolation levels, which may need to be changed based on what is needed for your application.

Finally, durability is a guarantee that changes made by a committed transaction must not be lost.

This a free and open text-based handbook. If you want to get started, just scroll down and start reading. That said, there are two other options for following along:

1. Try the interactive version of this SQL course on Boot.dev, complete with coding challenges and projects
2. Watch the video walkthrough of this course on FreeCodeCamp's YouTube channel (embedded below):

Table of Contents

• Chapter 1: Introduction
• Chapter 2: SQL Tables
• Chapter 3: Constraints
• Chapter 4: CRUD Operations
• Chapter 5: Basic SQL Queries
• Chapter 6: How to Structure Return Data in SQL
• Chapter 7: How to Perform Aggregations in SQL
• Chapter 8: SQL Subqueries
• Chapter 9: Database Normalization
• Chapter 10: How to Join Tables in SQL
• Chapter 11: Database Performance

Chapter 1: Introduction

Structured Query Language, or SQL, is the primary programming language used to manage and interact with relational databases. SQL can perform various operations such as creating, updating, reading, and deleting records within a database.

What is a SQL Select Statement?

Let's write our own SQL statement from scratch. A SELECT statement is the most common operation in SQL – often called a "query". SELECT retrieves data from one or more tables. Standard SELECT statements do not alter the state of the database.

SELECT id from users;

How to select a single field

A SELECT statement begins with the keyword SELECT followed by the fields you want to retrieve.

SELECT id from users;

How to select multiple fields

If you want to select more than one field, you can specify multiple fields separated by commas like this:

SELECT id, name from users;

How to select all fields

If you want to select every field in a record, you can use the shorthand * syntax.

SELECT * from users;

After specifying fields, you need to indicate which table you want to pull the records from using the from statement followed by the name of the table.

We'll talk more about tables later, but for now, you can think about them like structs or objects. For example, the users table might have 3 fields:

• id
• name
• balance

And finally, all statements end with a semi-colon ;.

Which Databases Use SQL?

SQL is just a query language. You typically use it to interact with a specific database technology. For example:

• SQLite
• PostgreSQL
• MySQL
• CockroachDB
• Oracle

And others.

Although many different databases use the SQL language, most of them will have their own dialect. It's critical to understand that not all databases are created equal. Just because one SQL-compatible database does things a certain way, doesn't mean every SQL-compatible database will follow those exact same patterns.

We're using SQLite

In this course, we'll be using SQLite specifically. SQLite is great for embedded projects, web browsers, and toy projects. It's lightweight, but has limited functionality compared to the likes of PostgreSQL or MySQL – two of the more common production SQL technologies.

And I'll make sure to point out to you whenever some functionality we're working with is unique to SQLite.

NoSQL vs SQL

When talking about SQL databases, we also have to mention the elephant in the room: NoSQL.

To put it simply, a NoSQL database is a database that does not use SQL (Structured Query Language). Each NoSQL typically has its own way of writing and executing queries. For example, MongoDB uses MQL (MongoDB Query Language) and ElasticSearch simply has a JSON API.

While most relational databases are fairly similar, NoSQL databases tend to be fairly unique and are used for more niche purposes. Some of the main differences between a SQL and NoSQL database are:

1. NoSQL databases are usually non-relational, SQL databases are usually relational (we'll talk more about what this means later).
2. SQL databases usually have a defined schema, NoSQL databases usually have dynamic schema.
3. SQL databases are table-based, NoSQL databases have a variety of different storage methods, such as document, key-value, graph, wide-column, and more.

Types of NoSQL databases

• Document Database
• Key-Value Store
• Wide-Column
• Graph

A few of the most popular NoSQL databases are:

• MongoDB
• Cassandra
• CouchDB
• DynamoDB
• ElasticSearch

Comparing SQL Databases

Let's dive deeper and talk about some of the popular SQL Databases and what makes them different from one another. Some of the most popular SQL Databases right now are:

• PostgreSQL
• MySQL
• Microsoft SQL Server
• SQLite
• And many others

Source: db-engines.com

While all of these Databases use SQL, each database defines specific rules, practices, and strategies that separate them from their competitors.

SQLite vs PostgreSQL

Personally, SQLite and PostgreSQL are my favorites from the list above. Postgres is a very powerful, open-source, production-ready SQL database. SQLite is a lightweight, embeddable, open-source database. I usually choose one of these technologies if I'm doing SQL work.

SQLite is a serverless database management system (DBMS) that has the ability to run within applications, whereas PostgreSQL uses a Client-Server model and requires a server to be installed and listening on a network, similar to an HTTP server.

See a full comparison here.

Again, in this course we will be working with SQLite, a lightweight and simple database. For most backend web servers, PostgreSQL is a more production-ready option, but SQLite is great for learning and for small systems.

Chapter 2: SQL Tables

The CREATE TABLE statement is used to create a new table in a database.

How to use the CREATE TABLE statement

To create a table, use the CREATE TABLE statement followed by the name of the table and the fields you want in the table.

CREATE TABLE employees (id INTEGER, name TEXT, age INTEGER, is_manager BOOLEAN, salary INTEGER);

Each field name is followed by its datatype. We'll get to data types in a minute.

It's also acceptable and common to break up the CREATE TABLE statement with some whitespace like this:

CREATE TABLE employees(
    id INTEGER,
    name TEXT,
    age INTEGER,
    is_manager BOOLEAN,
    salary INTEGER
);

How to Alter Tables

We often need to alter our database schema without deleting it and re-creating it. Imagine if Twitter deleted its database each time it needed to add a feature, that would be a disaster! Your account and all your tweets would be wiped out on a daily basis.

Instead, we can use use the ALTER TABLE statement to make changes in place without deleting any data.

How to use ALTER TABLE

With SQLite an ALTER TABLE statement allows you to:

1. Rename a table or column, which you can do like this:

ALTER TABLE employees
RENAME TO contractors;

ALTER TABLE contractors
RENAME COLUMN salary TO invoice;

2. ADD or DROP a column, which you can do like this:

ALTER TABLE contractors
ADD COLUMN job_title TEXT;

ALTER TABLE contractors
DROP COLUMN is_manager;

Intro to Migrations

A database migration is a set of changes to a relational database. In fact, the ALTER TABLE statements we did in the last exercise were examples of migrations.

Migrations are helpful when transitioning from one state to another, fixing mistakes, or adapting a database to changes.

Good migrations are small, incremental and ideally reversible changes to a database. As you can imagine, when working with large databases, making changes can be scary. We have to be careful when writing database migrations so that we don't break any systems that depend on the old database schema.

Example of a bad migration

If a backend server periodically runs a query like SELECT * FROM people, and we execute a database migration that alters the table name from people to users without updating the code, the application will break. It will try to grab data from a table that no longer exists.

A simple solution to this problem would be to deploy new code that uses a new query:

SELECT * FROM users;

And we would deploy that code to production immediately following the migration.

SQL Data Types

SQL as a language can support many different data types. But the datatypes that your database management system (DBMS) supports will vary depending on the specific database you're using.

SQLite only supports the most basic types, and we're using SQLite in this course.

SQLite Data Types

Let's go over the data types supported by SQLite: and how they are stored.

1. NULL - Null value.
2. INTEGER - A signed integer stored in 0,1,2,3,4,6, or 8 bytes.
3. REAL - Floating point value stored as an 64-bit IEEE floating point number.
4. TEXT - Text string stored using database encoding such as UTF-8
5. BLOB - Short for Binary large object and typically used for images, audio or other multimedia.

For example:

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    age INTEGER,
    is_manager BOOLEAN,
    salary INTEGER
);

Boolean values

It's important to note that SQLite does not have a separate BOOLEAN storage class. Instead, boolean values are stored as integers:

• 0 = false
• 1 = true

It's not actually all that weird – boolean values are just binary bits after all!

SQLite will still let you write your queries using boolean expressions and true/false keywords, but it will convert the booleans to integers under-the-hood.

Chapter 3: Constraints

A constraint is a rule we create on a database that enforces some specific behavior. For example, setting a NOT NULL constraint on a column ensures that the column will not accept NULL values.

If we try to insert a NULL value into a column with the NOT NULL constraint, the insert will fail with an error message. Constraints are extremely useful when we need to ensure that certain kinds of data exist within our database.

NOT NULL constraint

The NOT NULL constraint can be added directly to the CREATE TABLE statement.

CREATE TABLE employees(
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE,
    title TEXT NOT NULL
);

SQLite limitation

In other dialects of SQL you can ADD CONSTRAINT within an ALTER TABLE statement. SQLite does not support this feature, so when we create our tables we need to make sure we specify all the constraints we want.

Here's a list of SQL Features SQLite does not implement in case you're curious.

Primary Key Constraints

A key defines and protects relationships between tables. A primary key is a special column that uniquely identifies records within a table. Each table can have one, and only one primary key.

Your primary key will almost always be the "id" column

It's very common to have a column named id on each table in a database, and that id is the primary key for that table. No two rows in that table can share an id.

A PRIMARY KEY constraint can be explicitly specified on a column to ensure uniqueness, rejecting any inserts where you attempt to create a duplicate ID.

Foreign Key Constraints

Foreign keys are what makes relational databases relational! Foreign keys define the relationships between tables. Simply put, a FOREIGN KEY is a field in one table that references another table's PRIMARY KEY.

Creating a Foreign Key in SQLite

Creating a FOREIGN KEY in SQLite happens at table creation! After we define the table fields and constraints we add an additional CONSTRAINT where we define the FOREIGN KEY and its REFERENCES.

Here's an example:

CREATE TABLE departments (
    id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    department_id INTEGER,
    CONSTRAINT fk_departments
    FOREIGN KEY (department_id)
    REFERENCES departments(id)
);

In this example, an employee has a department_id. The department_id must be the same as the id field of a record from the departments table.

Schema

We've used the word schema a few times now, let's talk about what that word means. A database's schema describes how data is organized within it.

Data types, table names, field names, constraints, and the relationships between all of those entities are part of a database's schema.

There is no perfect way to architect a database schema

When designing a database schema there typically isn't a "correct" solution. We do our best to choose a sane set of tables, fields, constraints, etc that will accomplish our project's goals. Like many things in programming, different schema designs come with different tradeoffs.

How do we decide on a sane schema architecture?

One very important decision that needs to be made is to decide which table will store a user's balance! As you can imagine, ensuring our data is accurate when dealing with money is super important. We want to be able to:

• Keep track of a user's current balance
• See the historical balance at any point in the past
• See a log of which transactions changed the balance over time

There are many ways to approach this problem. For our first attempt, let's try the simplest schema that fulfills our project's needs.

Chapter 4: CRUD Operations in SQL

What is CRUD?

CRUD is an acronym that stands for CREATE, READ, UPDATE, and DELETE. These four operations are the bread and butter of nearly every database you will create.

HTTP and CRUD

The CRUD operations correlate nicely with the HTTP methods you may have already learned:

• HTTP POST - CREATE
• HTTP GET - READ
• HTTP PUT - UPDATE
• HTTP DELETE - DELETE

SQL Insert Statement

Tables are pretty useless without data in them. In SQL we can add records to a table using an INSERT INTO statement. When using an INSERT statement we must first specify the table we are inserting the record into, followed by the fields within that table we want to add VALUES to.

Here's an example of an INSERT INTO statement:

INSERT INTO employees(id, name, title)
VALUES (1, 'Allan', 'Engineer');

HTTP CRUD Database lifecycle

It's important to understand how data flows through a typical web application.

1. The front-end processes some data from user input - maybe a form is submitted.
2. The front-end sends that data to the server through an HTTP request - maybe a POST.
3. The server makes a SQL query to it's database to create an associated record - Probably using an INSERT statement.
4. Once the server has processed that the database query was successful, it responds to the front-end with a status code! Hopefully a 200-level code (success)!

Manual Entry

Manually INSERTing every single record in a database would be an extremely time-consuming task! Working with raw SQL as we are now is not super common when designing backend systems.

When working with SQL within a software system, like a backend web application, you'll typically have access to a programming language such as Go or Python.

For example, a backend server written in Go can use string concatenation to dynamically create SQL statements, and that's usually how it's done.

sqlQuery := fmt.Sprintf(`
INSERT INTO users(name, age, country_code)
VALUES ('%s', %v, %s);
`, user.Name, user.Age, user.CountryCode)

SQL Injection

The example above is an oversimplification of what really happens when you access a database using Go code. In essence, it's correct. String interpolation is how production systems access databases. That said, it must be done carefully to not be a security vulnerability. We'll talk more about that later!

Count

We can use a SELECT statement to get a count of the records within a table. This can be very useful when we need to know how many records there are, but we don't particularly care what's in them.

Here's an example in SQLite:

SELECT count(*) from employees;

The * in this case refers to a column name. We don't care about the count of a specific column - we want to know the number of total records so we can use the wildcard (*).

HTTP CRUD database lifecycle

We talked about how a "create" operation flows through a web application. Let's talk about a "read".

Let's talk through an example. Our product manager wants to show profile data on a user's settings page. Here's how we could engineer that feature request:

1. First, the front-end webpage loads.
2. The front-end sends an HTTP GET request to a /users endpoint on the back-end server.
3. The server receives the request.
4. The server uses a SELECT statement to retrieve the user's record from the users table in the database.
5. The server converts the row of SQL data into a JSON object and sends it back to the front-end.

WHERE clause

In order to keep learning about CRUD operations in SQL, we need to learn how to make the instructions we send to the database more specific. SQL accepts a WHERE statement within a query that allows us to be very specific with our instructions.

If we were unable to specify the specific record we wanted to READ, UPDATE, or DELETE making queries to a database would be very frustrating, and very inefficient.

Using a WHERE clause

Say we had over 9000 records in our users table. We often want to look at specific user data within that table without retrieving all the other records in the table. We can use a SELECT statement followed by a WHERE clause to specify which records to retrieve. The SELECT statement stays the same, we just add the WHERE clause to the end of the SELECT.

Here's an example:

SELECT name FROM users WHERE power_level >= 9000;

This will select only the name field of any user within the users table WHERE the power_level field is greater than or equal to 9000.

Finding NULL values

You can use a WHERE clause to filter values by whether or not they're NULL.

IS NULL

SELECT name FROM users WHERE first_name IS NULL;

IS NOT NULL

SELECT name FROM users WHERE first_name IS NOT NULL;

DELETE

When a user deletes their account on Twitter, or deletes a comment on a YouTube video, that data needs to be removed from its respective database.

DELETE statement

A DELETE statement removes a record from a table that match the WHERE clause. As an example:

DELETE from employees
    WHERE id = 251;

This DELETE statement removes all records from the employees table that have an id of 251!

The danger of deleting data

Deleting data can be a dangerous operation. Once removed, data can be really hard if not impossible to restore! Let's talk about a couple of common ways back-end engineers protect against losing valuable customer data.

Strategy 1 - Backups

If you're using a cloud-service like GCP's Cloud SQL or AWS's RDS you should always turn on automated backups. They take an automatic snapshot of your entire database on some interval, and keep it around for some length of time.

For example, the Boot.dev database has a backup snapshot taken daily and we retain those backups for 30 days. If I ever accidentally run a query that deletes valuable data, I can restore it from the backup.

You should have a backup strategy for production databases.

Strategy 2 - Soft deletes

A "soft delete" is when you don't actually delete data from your database, but instead just "mark" the data as deleted.

For example, you might set a deleted_at date on the row you want to delete. Then, in your queries you ignore anything that has a deleted_at date set. The idea is that this allows your application to behave as if it's deleting data, but you can always go back and restore any data that's been removed.

You should probably only soft-delete if you have a specific reason to do so. Automated backups should be "good enough" for most applications that are just interested in protecting against developer mistakes.

Update query in SQL

Whenever you update your profile picture or change your password online, you are changing the data in a field on a table in a database. Imagine if every time you accidentally messed up a Tweet on Twitter you had to delete the entire tweet and post a new one instead of just editing it...

...Well, that's a bad example.

Update statement

The UPDATE statement in SQL allows us to update the fields of a record. We can even update many records depending on how we write the statement.

An UPDATE statement specifies the table that needs to be updated, followed by the fields and their new values by using the SET keyword. Lastly a WHERE clause indicates the record(s) to update.

UPDATE employees
SET job_title = 'Backend Engineer', salary = 150000
WHERE id = 251;

Object-Relational Mapping (ORMs)

An Object-Relational Mapping or an ORM for short, is a tool that allows you to perform CRUD operations on a database using a traditional programming language. These typically come in the form of a library or framework that you would use in your backend code.

The primary benefit an ORM provides is that it maps your database records to in-memory objects. For example, in Go we might have a struct that we use in our code:

type User struct {
    ID int
    Name string
    IsAdmin bool
}

This struct definition conveniently represents a database table called users, and an instance of the struct represents a row in the table.

Example: Using an ORM

Using an ORM we might be able to write simple code like this:

user := User{
    ID: 10,
    Name: "Lane",
    IsAdmin: false,
}

// generates a SQL statement and runs it,
// creating a new record in the users table
db.Create(user)

Example: Using straight SQL

Using straight SQL we might have to do something a bit more manual:

user := User{
    ID: 10,
    Name: "Lane",
    IsAdmin: false,
}

db.Exec("INSERT INTO users (id, name, is_admin) VALUES (?, ?, ?);",
    user.ID, user.Name, user.IsAdmin)

Should you use an ORM?

That depends – an ORM typically trades simplicity for control.

Using straight SQL you can take full advantage of the power of the SQL language. Using an ORM, you're limited by whatever functionality the ORM has.

If you run into issues with a specific query, it can be harder to debug with an ORM because you have to dig through the framework's code and documentation to figure out how the underlying queries are being generated.

I recommend doing projects both ways so that you can learn about the tradeoffs. At the end of the day, when you're working on a team of developers, it will be a team decision.

Chapter 5: Basic SQL Queries

How to use the AS Clause in SQL

Sometimes we need to structure the data we return from our queries in a specific way. An AS clause allows us to "alias" a piece of data in our query. The alias only exists for the duration of the query.

AS keyword

The following queries return the same data:

SELECT employee_id AS id, employee_name AS name
FROM employees;

and:

SELECT employee_id, employee_name
FROM employees;

The difference is that the results from the aliased query would have column names id and name instead of employee_id and employee_name.

SQL Functions

At the end of the day, SQL is a programming language, and it's one that supports functions. We can use functions and aliases to calculate new columns in a query. This is similar to how you might use formulas in Excel.

IIF function

In SQLite, the IIF function works like a ternary. For example:

IIF(carA > carB, "Car a is bigger", "Car b is bigger")

If a is greater than b, this statement evaluates to the string "Car a is bigger". Otherwise, it evaluates to "Car b is bigger".

Here's how we can use IIF() and a directive alias to add a new calculated column to our result set:

SELECT quantity,
    IIF(quantity < 10, "Order more", "In Stock") AS directive
    from products

How to Use BETWEEN with WHERE

We can check if certain values are between two numbers using the WHERE clause in an intuitive way. The WHERE clause doesn't always have to be used to specify specific id's or values. We can also use it to help narrow down our result set. Here's an example:

SELECT employee_name, salary
FROM employees
WHERE salary BETWEEN 30000 and 60000;

This query returns all the employees name and salary fields for any rows where the salary is BETWEEN 30,000 and 60,000. We can also query results that are NOT BETWEEN two specified values.

SELECT product_name, quantity
FROM products
WHERE quantity NOT BETWEEN 20 and 100;

This query returns all the product names where the quantity was not between 20 and 100. We can use conditionals to make the results of our query as specific as we need them to be.

How to return distinct values

Sometimes we want to retrieve records from a table without getting back any duplicates.

For example, we may want to know all the different companies our employees have worked at previously, but we don't want to see the same company multiple times in the report.

SELECT DISTINCT

SQL offers us the DISTINCT keyword that removes duplicate records from the resulting query.

SELECT DISTINCT previous_company
    FROM employees;

This only returns one row for each unique previous_company value.

Logical Operators

We often need to use multiple conditions to retrieve the exact information we want. We can begin to structure much more complex queries by using multiple conditions together to narrow down the search results of our query.

The logical AND operator can be used to narrow down our result sets even more.

AND operator

SELECT product_name, quantity, shipment_status
    FROM products
    WHERE shipment_status = 'pending'
    AND quantity BETWEEN 0 and 10;

This only retrieves records where both the shipment_status is "pending" AND the quantity is between 0 and 10.

Equality operators

All of the following operators are supported in SQL. The = is the main one to watch out for, it's not == like in many other languages.

• =
• <
• >
• <=
• >=

For example, in Python you might compare two values like this:

if name == "age"

Whereas in SQL you would do:

WHERE name = "age"

OR operator

As you've probably guessed, if the logical AND operator is supported, the OR operator is probably supported as well.

SELECT product_name, quantity, shipment_status
    FROM products
    WHERE shipment_status = 'out of stock'
    OR quantity BETWEEN 10 and 100;