The database is the heart of any web application. And choosing the right database is a decision that affects everything in the project: performance, security, scalability, and even development speed. In this article, I'll share my journey with databases from SQLite to PostgreSQL, and how Prisma changed the way I handle data.
I started with SQLite in my first projects. SQLite is excellent for small projects and prototypes — there's no server to run, the database file sits in the project itself, and everything works instantly. In a simple project like Eleqbal Form initially, SQLite was sufficient. But as the project grew and user numbers increased, problems emerged: SQLite can't handle many concurrent connections, and lacks advanced features like Full-Text Search or JSONB.
Moving to PostgreSQL was a turning point. In Esma3 Radio, we needed a database that could handle thousands of stations and hundreds of users simultaneously. PostgreSQL provided that and more. JSONB support made it easy to store variable station data without a complex schema. Native Full-Text Search helped me implement fast searching across station names without external libraries.
Prisma ORM was the link that tied everything together. Instead of writing SQL manually and dealing with connections and migrations, Prisma provided a clear, readable schema, automatic TypeScript types, and a migration system that manages database changes. In Tammeny, the schema was simple and clear: User model, LocationShare model, FamilyCircle model. Every relationship is clearly defined in the schema.prisma file.
One of Prisma's biggest benefits: Type Safety. When writing your code, everything has a clear type. If you want to query users, Prisma returns an exact type with all the fields in the model. This prevents many runtime errors and makes autocomplete work perfectly. In Battle of Questions, the Types helped me handle player and round data without any runtime errors.
But Prisma isn't perfect. One of the most frustrating things: the N+1 Query Problem. When querying a model with relationships, sometimes Prisma makes a separate query for each record instead of a single JOIN. The solution is to use include wisely or enable _previewFeatures = "relationJoinMode" to force Prisma to use JOINs. Also, Prisma can't do some complex queries that SQL handles easily, like Window Functions or CTEs. In those cases, you have to use $queryRaw and write SQL manually.
Schema design is an art in itself. One of the most important patterns I learned: use Many-to-Many relationships through intermediate tables instead of trying to shortcut them. In Esma3 Radio, the relationship between stations and users (favorites) was Many-to-Many, and I created an intermediate Favorite table with dates and additional data. I also learned that Indexes aren't a luxury — every field you search frequently should have an Index. After adding an Index on station name, search became 10 times faster.
The Migration strategy with Prisma was a strong point. Every schema change creates a new migration with only the changes, not the entire database. This makes rolling back changes easy and preserves modification history. But be careful: migrations that have been applied aren't easily reversible if data exists. That's why I always make a backup before any major migration.
My advice for beginners: start with SQLite for development speed, but plan the transition to PostgreSQL from the beginning. Use Prisma for Type Safety and Migrations, but learn SQL well so you can write complex queries when needed. And always test database performance with real data, not small test datasets.
Top comments (0)