Skip to main content

Data Layer

In OID application, the data storage layer is designed to be agnostic, ensuring that business logic is completely separated from the underlying storage mechanism. This architecture provides flexibility and scalability, allowing us to adapt to various database systems based on requirements.

PostgreSQL​

We have chosen PostgreSQL as our primary database system due to its numerous advantages:

  • Reliability and Stability: PostgreSQL is known for its robustness and reliability, ensuring data integrity and consistent performance over time.
  • Advanced Features: It offers advanced features such as full ACID compliance, complex queries, foreign keys, triggers, views, and stored procedures.
  • Extensibility: PostgreSQL supports a wide range of extensions, including PostGIS for spatial data, which allows us to extend its capabilities as needed.
  • Performance: With features like indexing, parallel query processing, and advanced optimization, PostgreSQL delivers high performance for both read and write operations.
  • Open Source: As an open-source database, PostgreSQL benefits from a large community of contributors, ensuring continuous improvements and support without licensing costs.
  • Security: PostgreSQL offers robust security features, including SSL support, authentication methods, and granular access control, ensuring our data is well-protected.
SQLite as an alternative to Postgres

Depending on specific requirements, OID application can also work with SQLite, a file-based database system. SQLite is particularly suitable for smaller applications, embedded systems, or scenarios where a lightweight database is sufficient.

  • Simplicity: SQLite is simple to set up and requires minimal configuration, making it easy to integrate into various environments.
  • Portability: Being a file-based database, SQLite databases can be easily moved between different machines and platforms.
  • Low Overhead: SQLite has a small footprint, making it ideal for applications with limited resources.

Query Builder: Kysely​

To maintain our agnostic approach to the data storage layer, we use Kysely, a flexible query builder that supports multiple SQL dialects. Kysely allows us to abstract the underlying database logic, making it easy to switch between different databases without changing the storage mechanism implementation.

  • Dialect Flexibility: Kysely supports various SQL dialects, enabling seamless transitions between PostgreSQL, SQLite, and other databases.
  • Type Safety: It provides strong type safety, reducing the likelihood of runtime errors and improving code quality.
  • Ease of Use: With its intuitive syntax and comprehensive documentation, Kysely simplifies the process of building and executing complex queries.
Drizzle as an alternative

As an alternative to Kysely, the OID application can also utilize the Drizzle framework. Drizzle is another robust query builder designed to support various SQL dialects while offering unique advantages.

  • Modern TypeScript Support: Drizzle is built with modern TypeScript support, ensuring excellent type safety and developer experience.
  • Advanced Migrations: It offers a comprehensive migrations system that simplifies database schema changes, making it easier to manage evolving data structures.
  • Modular Design: Drizzle's modular design allows for the integration of additional functionalities and extensions, providing flexibility in customizing the query builder to specific needs.
  • Performance Optimization: With built-in performance optimizations, Drizzle ensures efficient query execution, which is crucial for high-load applications.
  • Extensive Documentation: Drizzle provides extensive documentation and community support, making it accessible for developers to learn and implement effectively.