Incoming links: Sequelize
Demo under: nodejs/sequelize/raw/many_to_many.js.
NO way in the SQL standard apparently, but you'd hope that implementation status would be similar to UPDATE with JOIN, but not even!
- PostgreSQL: possible with
DELETE FROM USING: stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join - SQLite: not possible without subqueries as of 3.35 far: stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite, Does not appear to have any relevant features at: www.sqlite.org/lang_delete.html
ORM
- Sequelize: no support of course: stackoverflow.com/questions/40890131/sequelize-destroy-record-with-join
The default feathers-chat app runs on NeDB (local filesystem JSON database).
Ciro Santilli managed to port it to Sequelize for PostgreSQL as shown at: github.com/cirosantilli/feathers-chat/tree/sequelize-pg
Looks interesting.
It seems to abstract the part about the client messaging the backend, which focuses on being able to easily plug in a number of Front-end web framework to manage client state.
Has the "main web API is the same as the REST API" focus, which is fundamental 2020-nowadays.
Uses Socket.IO, which allows the client Javascript to register callbacks when data is updated to achieve Socket.IO, e.g. their default chat app does:so that message appear immediately as they are sent.
client.service('messages').on('created', addMessage);Their standard template from which looks promising! They don't have a default template for a Front-end web framework however unfortunately: docs.feathersjs.com/guides/frameworks.html#the-feathers-chat lists a few chat app versions, which is their hello world:But it is in itself a completely boring app with a single splash page, and no database interaction, so not a good showcase. The actual showcase app is feathersjs/feathers-chat.
feathers generate app on @feathersjs/cli@4.5.0 includes:- several authentication methods, including OAuth
- testing
- backend database with one of several object-relational mapping! However, they don't abstract across them. E.g., the default Chat example uses NeDB, but a real app will likely use Sequelize, and a port is needed
- Front-end web framework: not built-in on generator, but there are some sample repos pointed from the documentation, and they did work out-of-box:
And there is no official example of the chat app that is immediately deployable to Heroku: FeathersJS Heroku deployment, all setups require thinking.
Global source entry point: determine on
package.json as usual, defaults to src/index.js. @cirosantilli/_file/nodejs/sequelize/nodejs/sequelize/parallel_select_and_update.js Updated 2024-12-15 +Created 1970-01-01
This example is the same as nodejs/sequelize/raw/parallel_select_and_update.js, but going through Sequelize rather than with Sequelize raw queries.
NONE is not supported for now to not have a transaction at all because lazy.The examples illustrates: stackoverflow.com/questions/55452441/for-share-and-for-update-statements-in-sequelize
Sample invocation:where:
node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATEREAD_COMMITTED: one of the keys documented at: sequelize.org/master/class/lib/transaction.js~Transaction.html which correspond to the standard sQL isolation levels. It not given, don't set one, defaulting to the database's/sequelize's default level.UPDATE: one of the keys documented at: sequelize.org/master/class/lib/transaction.js~Transaction.html#static-get-LOCK. Update generates aSELECT FOR UPDATEin PostgreSQL for example. If not given, don't use anyFOR xxxexplicit locking.
Other examples:
node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATE
Then, the outcome is exactly as described at: nodejs/sequelize/raw/parallel_select_and_update.js:
READ_COMMITTED: failsREAD_COMMITTED UPDATE: worksREPEATABLE_READ: works, but is a bit slower, as it does rollbacksThis case also illustrates Sequelize transaction retries, since in this transaction isolation level transactions may fail:
How to decide if an ORM is decent? Just try to replicate every SQL query from nodejs/sequelize/raw/many_to_many.js on PostgreSQL and SQLite.
There is only a very finite number of possible reasonable queries on a two table many to many relationship with a join table. A decent ORM has to be able to do them all.
If it can do all those queries, then the ORM can actually do a good subset of SQL and is decent. If not, it can't, and this will make you suffer. E.g. Sequelize v5 is such an ORM that makes you suffer.
The next thing to check are transactions.
Basically, all of those come up if you try to implement a blog hello world world such as gothinkster/realworld correctly, i.e. without unnecessary inefficiencies due to your ORM on top of underlying SQL, and dealing with concurrency.
Mission: to live in a world where you can learn university-level mathematics, physics, chemistry, biology and engineering from perfect free books that anyone can write to get famous.
Live website: ourbigbook.com
Further information and rationale: Section "OurBigBook.com"
The project's mission is one of, or perhaps the most important, life objective of Ciro Santilli. Reproductive goals aside. These two types of goal are incommensurable. This is one of the great challenges of life.
This is ongoing project.
Ciro's goals in advertising this half done project are is partly to obtain some feedback, and partly to give the idea to someone else who might help push it further, be it in this stack or not.
Better editing support is a must, likely WYSIWYG.
But besides that, it is already in broad strokes the best approach Ciro Santilli can come up with to try and reach the mission statement only with technical advances, i.e. without large amounts of money or political influence which Ciro Santilli does not have.
Maybe that website isn't enough of a technical advance to reach its mission. Maybe there is some further not yet imagined technical insight that would push it into viability. Maybe not. But one must try. Only God can know the answer to these questions.
As of 2022, Ciro has spent about 2.5 years full time working on this project. First he spent about 1 year in 2014 on the first iteration: github.com/booktree/booktree, a GitLab fork, but then decided it was not the way to go.
Then around 2021 he put in some more 1.5 year of full time work, now with a possibly overly complicated (or perhaps just insane/immature) Next.js/Sequelize from scratch website stack.
It makes Ciro a bit ashamed to see that "so little user visible stuff was achieved in so much time". It is partly because he and many people underestimate the difficulty of web development. Perhaps there were some bad stack/useless feature choices issues. And a good dose of indulging in studying the natural sciences to bootstrap content and have fun. But really trying is the only way to learn.
Appears to be a port of gothinkster/node-express-realworld-example-app to Sequelize.
Seemed to just work at 68bbadfd77f679f0df0fcd0de5bceb9c37b1144a Ubuntu 20.10, was forked from parent project in 2018.
github.com/cirosantilli/node-express-sequelize-nextjs-realworld-example-app contains the same baseline tech as OurBigBook, and I have been use to quickly test/benchmark new concepts for the website base.
I'm almost proud about that project, as a reasonable template for a Next.js project. It is not perfect, notably see issues on the issue tracker, but it it quite reasonable.
The side effects of ambitious goals are often the most valuable thing achieved once again? I to actually make the project be more important thatn the side effects this time, but we'll see.
Since the last update, I've made some major improvements to the baseline tech of the website, which I'll move little by little into OurBigBook. Some of the improvements actually started in OurBigBook.com. The improvements were:
- got a satisfactorily comprehensive linting working at: this commit. Nothing is easy, not even that! Part of the wisdom extracted to: stackoverflow.com/questions/58233482/next-js-setting-up-eslint-for-nextjs/70519682#70519682.
- fully rationalized directory structure to avoid nasty errors that show up in Next.js when accidentally requiring backend stuff on the client. Commit. A detailed explanation of this was extracted to: stackoverflow.com/questions/64926174/module-not-found-cant-resolve-fs-in-next-js-application/70363153#70363153.
- create an extremely clean and rationalized way to do API tests from a simple
npm test. These now actually start a clean server, and make full HTTP requests to that server. Commit. Wisdom extracted to: stackoverflow.com/questions/63762896/whats-the-best-way-to-test-express-js-api/70479940#70479940. - greatly reduce the number of SQL queries, fully understood every problem
- more intelligently using JOINs where I have managed to get Sequelize to do what I fucking want. This also led to several sequelize Stack Overflow answers as usual: stackoverflow.com/search?tab=newest&q=user%3a895245%20%5bsequelize%5dEverything that I didn't manage to do because of crappy sequelize is documented at: github.com/cirosantilli/node-express-sequelize-nextjs-realworld-example-app/issues/5
- better understanding Next.js/React/useSWR do avoid doing double API queries
These examples are written in the Sequelize library using raw queries.
Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard JavaScript. The queries themselves are all written by hand.
By default the examples run on SQLite. Just like the examples from sequelize example, you can set the database at runtime as:
./index.jsor./index.js l: SQLite./index.js p: PostgreSQL. You must manually create a database calledtmpand ensure that peer authentication works for it
Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations:
- nodejs/sequelize/raw/index.js: basic hello world to demonstrate the setup and very simple functionality
- nodejs/sequelize/raw/many_to_many.js: illustrates many-to-many relations with JOIN. Contains:
- SQL transaction examples:
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and
COMMITsimply does aROLLBACK. Let's check. Yup, true for Postgres, but false for SQLite, SQLite just happily runs anything it can, you really needROLLBACKfor it. - SQL isolation level example
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and
- GROUP BY and SQL aggregate functions:
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems,
sqlite3Node.js package allows it:- github.com/sequelize/sequelize/issues/5481#issuecomment-964387232
- dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in SQL:1999 when there are no ambiguities due to constraints, e.g. when grouping by unique columns
- github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that PostgreSQL wants it to work for
UNIQUE NOT NULL, but they just haven't implemented it as of 13.5, where it only works if you group byPRIMARY KEY - dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that
UNIQUE NOT NULLdoesn't work. Dan Lenski then points to rationale mailing list thread:
- nodejs/sequelize/raw/group_by_max_full_row.js: here we try to get the full row of each group at which a given column reaches the max of the group
- Postgres: has
SELECT DISCINTCT ONwhich works perfectly if you only want one row in case of multiple rows attaining the max.ONis an extension to the standard unfortunately: www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT Docs specify that it always respectsORDER BYwhen selecting the row.- stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
- stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice OUTER JOIN only solution! Incredible, very elegant.
- dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max
- stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
- SQLite:
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 Ciro Santilli uses the magic of
ROW_NUMBER
- stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify
- code.djangoproject.com/ticket/22696 WONTFIXed
DISTINCT ON- stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494
DISTINCTvsDISTINCT ON, somewhat related question
- stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494
- stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
- stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres
- Postgres: has
- nodejs/sequelize/raw/most_frequent.js: illustrates a few variants of findind the mode, including across GROUP
- nodejs/sequelize/raw/group_by_max_n.js: get the top N in each group
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems,
- order results in the same order as
IN: - LIMIT by a running total: TODO links
SQL's implementation of database triggers.
This feature is really cool, as it allows you to keep caches up to date!
In particular, everything that happens in a trigger happens as if it were in a transaction. This way, you can do less explicit transactions when you use triggers. It is a bit like the advantages of SQL CASCADE.
DBMS:
Example: nodejs/sequelize/trigger_count.js
There is of course no built-in support for SQL TRIGGERs in Sequelize, but we can add our own: stackoverflow.com/questions/29716346/how-to-create-a-trigger-in-sequelize-nodejs/76215728#76215728
Dumping examples under nodejs/sequelize/raw/many_to_many.js.
Not possible without subqueries in the standard syntax, a huge shame: stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server
Webpack is like a magic hydra that can eat any type of file and bundle it into a single output: .js, .ts, .ccs, .scss, .jsx, .tsx,
require, import, import css from .js, it doesn't matter at all, it just digests all into the same dump.When it works, you are just left in awe and with a single Js file. When it doesn't, you're fucked and have to debug for several hours.
Demos under: webpack/. To run all of them by default:To easily make changes and reload the .js output live let this run on a terminal:
cd webpack/min
npm install
npm run build
xdg-open index.htmlnpx webpack watchExamples:
- webpack/min: minimal hello world. Doesn't do much, just copies
index.jstodist/index.js. - webpack/require:
requireandimportdemo. Both work from the same file.dist/index.jsnow contains all of:notindex.jsnotindex2.js- Lodash, a common third-party helper library specified in the package.json and installed with npm
- webpack/node: produce Node.js output, as opposed to the default web output. To test it run:Achieved simply with:
npm run build node dist/index.jsas documented at: webpack.js.org/concepts/targets/target: 'node'Fatman in Robin, - webpack/sequelize: attempts at getting Sequelize to work with webpack. It's just not supported by Sequelize: