Databases and ORMs

[lastmjs]

This issue is meant to encompass all of the work around getting good database along with ORM support in Azle.

• Database benchmarks: Database benchmarks #1778
• Rust errors don't show with the new exec sync pretty...you have to add azle_verbose=true
• Should we somehow automatically do the database importing and exporting with sqlite? It would be very slick
• TypeORM
• TypeORM migrations
• TypeORM logger
• remove all candid from stablebtreemap
• What about adding authentication to the sqlite examples??
• Database stuff before hardening
  • SQLite sugar on top
  • SQLite excellent teaching-based example
  • SQLite benchmarks
  • SQLite documentation
• Use a very simple abstraction on top of SQL, I think a tagged template literal might be nice, even if custom, it will allow nice types and let you do a simple transformation on top of SQL.js for now, and hopefully in the future it will work for other databases...let's go forward with that strategy and not choose for our users
• Create SQL test framework that we can run on each database and ORM to make sure that they all work
  • This might just have to be an agreed-upon schema with an agreed-upon set of endpoints that will do some operations
  • User table
  • Blog post table
  • 10s, 100s, 1_000s, 10_000s of queries
  • CRUD
  • Joins
  • Indexes
  • Migrations
• SQLite
  • asm
  • heap
  • stable pre/post upgrade
• PGLite (we have decided to not implement this for now, checking off without implementing)
  • wasm
  • heap
  • stable pre/post upgrade
  • stable real-time
• Drizzle
• Kysely (not going to implement now)
• Mikro ORM (not going to implement now)
• Prisma (this might be difficult because of the engine architecture that runs in a separate process using the Node API or an OS-specific binary) (not going to implement now)
• Documentation
  • Show how to do pre and post upgrade stuff with SQLite
  • Explain roughly how many records are possible
  • Explain roughly the max size of a DB
  • Explain indexing
  • Explain migrations
  • Explain all limits
  • Current estimates show ~13 million records possible across entire database because of instruction limit
  • Current estimates show
• create tests using the northwinds database...
• going across upgrades will reset internal state I guess? I don't understand, SELECT last_insert_rowid() isn't working across upgrades
• it would be quite neat to get a Northwinds database uploaded with some kind of GUI on top to showcase
• northwind sqlite: https://github.com/jpwhite3/northwind-SQLite3
• Record the 1 and 10 million record tests
• We should be able to reach a capacity of ~4 GiB, ~2 GiB just to be safe
• instructions limit on pre and post upgrade doesn't seem to be a problem at all, just the size of the heap seems to be the problem...so eventually, bigger heap will mean a bigger db
• We might be able to use this to get even more ORM support: https://www.npmjs.com/package/sql.js-as-sqlite3
• Other ideas for things to test
  • Transactions
  • Rolling back?
  • Indexes
  • Subqueries
  • Aggregate functions like SUM, AVG, MIN, MAX
  • Group by
  • Applying multiple migrations