My experience of working with database

My journey is from 'MongoDB' to 'Prisma' and to 'Supabase'.

First in-production database with 'MongoDB'

The first in-production database I built for a localization project manage platform was with 'MongoDB'. To be honest, this was because I thought no-sql was much easier and quicker (I was the only engineer of the project during that time) to let the features run and help the team and there was just the company's corresponding cloud infrastructure. The basics were I was using 'MongoDB Node.js Driver' without any ORM to do the CRUDs according to the platform's business logics and there were collections like requests (localization requests from other parties internally and externally) or projects (localization projects based on the requests with timelines and tasks with their assignees).

One thing I remember quite clear is that I put tasks just as a property of project, also because for no-sql, everything is more like an object and you can have nested and nested (and nested) data structures. When the platform was only focused on working with projects part, everything ran smoothly. However, each task (same task with two different language pairs was considered two tasks) of a project had one corresponding finance record (can be seen as the task's payroll) and when building the platform's finance features, I had to extract all tasks from all projects (according to various conditions, e.g. who's the project manager of a task or task settlement status). This extraction already took some effort and when a project changed, its affected tasks should be changed as well, so I need to do the extraction for the project changing scenario as well, making the finance features updated. If at the very beginning when building the project table and related project features, I already knew to have a separate task table, things world be much easier for the future finance feature development works. With this tasks as a property of project structure, I had to do a big database refactor later. I want to discuss:

• Like the discussion fromHow to build shared components, is it possible that our initial design of an app can always support the future development as much as possible? For example, you always know the smallest unit of your team is task instead of project? Or refactoring or even coding a new app with the new design is unavoidable?
• If not, the extraction mentioned above is like a (temporary) patch to the codebase and needs to be changed every time the code that it patches to changes. So the codebase is doomed to be more and more messy? Even though the actual frontend users of the platform might never care about or even know the messiness? How to deal with this dilemma?

When I was going to leave the team, each big collection (requests, projects or emails) had around 5000 rows, which was because localization is something that each project can be very detailed, time consuming and tedious so that the total volume of each table was not that huge, meaning though I did not take considerations of always improving the overall database architecture performance, everything ran smoothly to facilitate the team's everyday project management works during that time.

Changing to 'Prisma'

After quitting the project of the localization management platform, I found much more time for my own projects and started trying many different things that I had never touched before. I learned the basics of how to use 'Prisma' with 'Next.js' fromthis video (the production part of the demo uses 'Neon' on 'Vercel') and implemented it in my 'I Earvin' project. I changed to 'PostgreSQL' (probably the most typical usage of 'Prisma') and back to sql from of no-sql. TheORM idea from using 'Prisma' is not only very convenient and systematical but fun. Definitely 'Prisma' supports 'MongoDB' as well.

One thing I did with 'Prisma' was to rename a column of PostComments table from postId to postSlug. Normally adding new things to an existing schema only needs prisma db push but changing things is a different story. After googling for a while (here'sa 'Reddit' discussion about the issue), these're what I did:

• I created a new postSlug column and copied all existing postId data to postSlug (direct sql operations on both dev and prod and can/should be done by'Prisma' migration),
• updated 'Prisma' schema (prisma db pull from changed database schema),
• changed the code to work with postSlug instead of postId (and imagine there are 30 places that work with postId),
• pushed to origin, deployed to production and checked everything still runs OK on production,
• deleted old postId column and data,
• and finally updated 'Prisma' schema again,

which is a 'expand and shrink' practice (and also applies to other database changes or component API changes). And besides the above example, if you designed something wrong, it'd take your time and effort to fix things later. I personally consider the fixes worth the effort and one important aspect of the robustness of an app which has a lot to do with its scalability and overall performance. Will AI do this kind of refactoring much easier than us so that all future codebases can be in the next level?

Mobile world and Supabase

Mobile app developing is a very different world compared with web app developing, e.g. integrating 'Prisma' needs extra settings. I haven't coded with 'Expo' for kind of long time, so I choseSupabase as a quick-to-start method. Just with its JavaScript SDK, normal CRUD operations can be fulfilled quite easily. I like 'Supabase''s UI and am considering transferring from 'Neon' to 'Supabase' as well.

Other things

For localization, some practices put translated texts in the database instead of using CMS.

I tried to learn thisonline database course from Cornell but very unfortunately failed. Will there be new ways or ideologies of learning new things in this AI age?

（this is a continuously updated article...）