MongoDB VS SQLAlchemy

MongoDB — a database service

MongoDB is an open source NoSQL Document database which horizontally scales utilizing clustered servers in replica-sets. A great option for testing and performing local development with MongoDB is using Docker and Docker Compose. The following docker compose spec will create a local docker container running MongoDB with the data persisted to a docker volume.

MongoDB (Mongoose)

Databases Backend developers use databases like MySQL, PostgreSQL, MongoDB (NoSQL), or Redis (an in-memory database) to store and manage data. It is important for persistent data storage and retrieval in web applications.

MongoDB{:target="_blank"} is a document-oriented database with high scalability and flexibility for handling unstructured or semi-structured data. It offers rich querying capabilities, indexing options, and support for distributed data storage with a technique called “sharding”.

NoSQL databases are non-relational databases with flexible schema designed for high performance at a massive scale. Unlike traditional relational databases, which use tables and predefined schemas, NoSQL databases use a variety of data models. There are 4 main types of NoSQL databases - document, graph, key-value, and column-oriented databases. NoSQL databases generally are well-suited for unstructured data, large-scale applications, and agile development processes. The most popular examples of NoSQL databases are MongoDB (document), Memgraph (graph), Redis (key-value store) and Apache HBase (column-oriented).

Our example application will be based on the ME*N stack where M is a MongoDB (but it could be MySQL, or any other) database. E is for Express, which is a backend framework that sits on top of Node.JS. There are others but Express is extremely common and will meet our needs well. N is for the JavaScript runtime Node.js but Deno and Bun are possible alternatives. Finally the * is for the frontend framework such as Angular (MEAN), React (MERN) or Vue (MEVN), to name a few. However, in our example we will not be using a framework (to keep it agnostic and besides it is not that complicated), instead we will be using the native web technologies (HTML, CSS and JS).

MongoDB is a highly scalable document-type database. Its flexible schema approach allows developers to adapt and modify the data structure as needed, providing agility in application development.

MongoDB is an open-source, document-oriented database, meaning it stores data in a semi-structured format similar to JSON (JavaScript Object Notation), known as BSON (Binary JSON).

To simplify this process, Cerbos developers have come up with adapters for popular Object-Relational Mapping (ORM) frameworks. You can check out for more details on the query plan repo - which also contains adapters for Prisma and SQLAlchemy - as well as a fully functioning application using Mongoose as its ORM.

That above pattern is one I've seen people do even recently, using the "select().c" attribute which from very early versions of SQLAlchemy is defined as "the columns from a subquery of the SELECT" ; this usage began raising deprecation warnings in 1.4 and is fully removed in 2.0 as it was a remnant of a much earlier version of SQLAlchemy. it will do exactly as you say, "make a subquery for each filter condition".

the moment you see SQLAlchemy doing something you see that seems "asinine", send an example to https://github.com/sqlalchemy/sqlalchemy/discussions and I will clarify what's going on, correct the usage so that the query you have is what you expect, and quite often we will add new warnings or documentation when we see people doing things we didn't anticipate.

The creator and lead maintainer of SQLAlchemy, one of the most popular and most used Python library for accessing databases (who doesn't?) gave a rather interesting response to PEP703.

If this doesn't ring any alarm bells I don't know what will.

> Basically for the moment the GIL-less idea would likely be burdensome for us and the fact that it's only an "option" seems to strongly imply major compatibility issues that we would not prefer.

https://github.com/sqlalchemy/sqlalchemy/discussions/10002#d...

Recently I discovered BigQuery public datasets - just over 200 datasets available for directly querying via SQL. I think this is a great thing! I can connect these direct to an analytics platform (we use Apache Superset which uses Python SQLAlchemy under the hood) for example and just start dashboarding.

Our tool we're going to be using for interfacing with the SQLite database is SQLAlchemy, a SQL toolkit that provides a unified API for various relational databases. If you installed FastAPI with pip install "fastapi[all]", SQLAlchemy is already part of your setup. but if you opted for FastAPI alone, you would need to install SQLAlchemy separately with pip install sqlalchemy.

This feature is yet to be released, but it will allow you to seamlessly use data modelled with for example Pydantic, SQLAlchemy, msgspec or dataclasses in your route handlers, without the need for an intermediary model; The conversion will be handled by the specific DTO "backend" implementation. This new paradigm also makes it trivial to add support for any such modelling library, by simply implementing an appropriate backend.

The second problem was caused by the pgBouncer setup. pgBouncer is an impostor that owns several dozen of real PostgreSQL connections, but pretends that it has thousands of them available for the backend services. Similar to fractional-reserve banking. So, it needs a way to find out when the real DB connection becomes free and can be used by another service. Our pgBouncer was configured as pool_mode=transaction. I.e., it detected when the current transaction was over, and returned the PostgreSQL connection into the pool, making it available to other users. However, this mode was found to not work well with the code that was using SQLAlchemy: committing the current transaction immediately started a new one. So, the expensive connection between pgBouncer and PostgreSQL remained checked out as long as the connection from service to pgBouncer remained open (forever, or close to that).

In this blog post, we will explore the power of SQLAlchemy, a popular ORM library in Python, to model our domain objects.