Introducing seed support for additional databases, starting with SQLite

At Snaplet, we're always exploring ways to improve our functionality and versatility, while maintaining the simplicity and efficiency that our users love. Because Snaplet has exclusively supported PostgreSQL until now, one of our most requested features has been support for additional databases. With the latest 0.90 update to @snaplet/seed, we're excited to announce alpha support for SQLite and give you a peek under the hood at the architectural choices that make Snaplet so powerful and flexible.

Architectural design: a foundation for flexibility

At its core, Snaplet seed was architecturally engineered with a dual-layer design: a "core" responsible for the primary logic of data handling and transformation, and an "adapter" layer that interfaces directly with the database.

  • Core Logic: This is where the Snaplet seed data transformation magic happens. It deals with the abstractions of the actual data in the database, focusing on understanding and then simplifying data relationships and data transformations. As such, our core logic is database-agnostic.
  • Adapter Logic: This layer acts as the bridge between Snaplet's core and the specific database in use, like PostgreSQL and now SQLite. By sharing information through a dataModel interface abstraction, we've created a clear interface between Snaplet and various databases, ensuring that our tool can easily adapt to support new SQL dialects without compromising on the user experience or the quality of the data transformations.

This architecture was intentionally designed to facilitate the expansion of Snaplet's support to a wide range of databases by simply switching out or adding new adapters.

Why SQLite?

Choosing SQLite as our first additionally supported database was a strategic decision aimed at testing our adaptable architecture. SQLite's inherently lightweight nature, relative simplicity, and widespread usage and active community made it a good option to demonstrate a proof of concept of Snaplet's capability to support additional databases.

Despite being a good first candidate, there are a few challenges we’ve had to resolve to get SQLite supported, and some ongoing issues which impact the developer experience, such as dealing with SQLite's "typeless" nature. On this point in particular for example, Postgres types mean we’re easily able to ascertain whether a "created_at" column is a number of seconds (number, timestamp) or a string like "01/01/2020 13H48:28". In SQLite, we can’t rely on this, and we’re still actively working on improving datatype reliance for value determination in columns.

What this means for you

For existing seed PostgreSQL users, this update introduces some breaking changes. Some of these are specific to changes necessary to support new databases such as SQLite, and some are related to ongoing improvements to build seed out into more of a standalone application from Snaplet Snapshot.

These changes are detailed in full in our release notes, but most importantly:

  • Enhanced API Methods: The $resetDatabase method now supports selective table resets, offering more control.
  • New Configuration File: seed users must change from snaplet.config.ts to seed.config.ts for a @snaplet/seed specific setup.
  • Initialization Command Update: Use npx @snaplet/seed init instead of npx snaplet setup.
  • Simplified Syncing: A single npx @snaplet/seed sync command now replaces the previous generate and introspect commands, streamlining your workflow.

Looking Ahead

As we look to the future, our eyes are set on supporting more SQL communities, starting with MySQL. We're eager to bring Snaplet's capabilities to an even broader audience and to continue refining our tool based on your feedback. As mentioned, our support for SQLite is in an alpha stage, so if you’re using SQLite and seed we’d especially like to hear your feedback.

Your experiences, challenges, and insights are invaluable to us as we strive to make Snaplet the most developer-friendly tool for working with production-realistic data. We’d love to have you on our Discord server, where you can share your thoughts, and help us shape the future of Snaplet. PS: We give out swag to fans!

Jian Reis
January 24, 2022