When embarking on a new project, selecting the right database is crucial. Personally, I always start with SQLite to flesh out a new project before transitioning to a more robust database. SQLite is a popular choice due to its simplicity and ease of use. It's lightweight, requires no server setup, and is perfect for small-scale applications or prototypes. However, as your project grows, you might need to transition to a more scalable solution like PostgreSQL or another database such as DynamoDB, depending on your project requirements. For my AWS Lambda projects, I particularly like using DynamoDB due to its seamless integration and scalability. This blog post will demonstrate how to use SQLite as an initial database for your Go projects while implementing an abstraction layer that facilitates easy database swapping later.

Why Start with SQLite?

• Ease of Use: SQLite requires minimal setup and is embedded within your application, making it ideal for quick development.
• Portability: The database is stored in a single file, which makes it easy to share and move.
• Performance: Suitable for small to medium-sized data loads, SQLite is efficient and fast.

Creating an Abstraction Layer in Go

To ensure you can switch databases smoothly, it's essential to create an abstraction layer. This layer will define a common interface for database operations, allowing you to replace SQLite with another database like DynamoDB or PostgreSQL without rewriting major parts of your code.

Step 1: Define a Common Interface

First, define an interface in Go that outlines the database operations your application will perform.

type User struct {
    ID   int
    Name string
    Age  int
}

type Database interface {
    CreateUser(user User) error
    GetUser(id int) (User, error)
    UpdateUser(user User) error
    DeleteUser(id int) error
}

Step 2: Implement the Interface for SQLite

Using the "github.com/mattn/go-sqlite3" package, you can implement this interface for SQLite. Let's create a basic user management system.

type SQLiteDB struct {
    db *sql.DB
}

func NewSQLiteDB(dataSourceName string) (*SQLiteDB, error) {
    db, err := sql.Open("sqlite3", dataSourceName)
    if err != nil {
        return nil, err
    }
    return &SQLiteDB{db: db}, nil
}

func (s *SQLiteDB) CreateUser(user User) error {
    stmt, err := s.db.Prepare("INSERT INTO users(name, age) VALUES(?, ?)")
    if err != nil {
        return err
    }
    _, err = stmt.Exec(user.Name, user.Age)
    return err
}

func (s *SQLiteDB) GetUser(id int) (User, error) {
    row := s.db.QueryRow("SELECT id, name, age FROM users WHERE id = ?", id)
    var user User
    err := row.Scan(&user.ID, &user.Name, &user.Age)
    return user, err
}

// Implement other methods...

Step 3: Implement the Interface for DynamoDB

To scale, implement the same interface for DynamoDB using the AWS SDK for Go.

type DynamoDB struct {
    svc *dynamodb.DynamoDB
}

func NewDynamoDB() *DynamoDB {
    sess := session.Must(session.NewSession())
    svc := dynamodb.New(sess)
    return &DynamoDB{svc: svc}
}

func (d *DynamoDB) CreateUser(user User) error {
    input := &dynamodb.PutItemInput{
        TableName: aws.String("Users"),
        Item: map[string]*dynamodb.AttributeValue{
            "ID":   {N: aws.String(strconv.Itoa(user.ID))},
            "Name": {S: aws.String(user.Name)},
            "Age":  {N: aws.String(strconv.Itoa(user.Age))},
        },
    }
    _, err := d.svc.PutItem(input)
    return err
}

func (d *DynamoDB) GetUser(id int) (User, error) {
    input := &dynamodb.GetItemInput{
        TableName: aws.String("Users"),
        Key: map[string]*dynamodb.AttributeValue{
            "ID": {N: aws.String(strconv.Itoa(id))},
        },
    }
    result, err := d.svc.GetItem(input)
    if err != nil {
        return User{}, err
    }
    if result.Item == nil {
        return User{}, fmt.Errorf("User not found")
    }
    user := User{
        ID:   id,
        Name: *result.Item["Name"].S,
        Age:  *result.Item["Age"].N,
    }
    return user, nil
}

// Implement other methods...

Switching Between Databases

With the interface in place, switching databases becomes a simple matter of configuration. In Go, it's also easy to have multiple entry points, allowing you to start with a server using SQLite and then add a Lambda entry point that uses the DynamoDB implementation. This flexibility means you can adapt your application to different environments without changing your core logic.

func main() {
    var db Database
    if useDynamoDB {
        db = NewDynamoDB()
    } else {
        db, _ = NewSQLiteDB("mydb.sqlite")
    }

    // Example usage
    user := User{ID: 1, Name: "John Doe", Age: 30}
    db.CreateUser(user)
    fetchedUser, _ := db.GetUser(1)
    fmt.Println(fetchedUser)
}

Conclusion

Starting your project with SQLite and a well-designed abstraction layer in Go ensures that you can scale your application efficiently. This approach allows you to begin development quickly and switch to a more powerful database like DynamoDB or PostgreSQL when your project requires it. By keeping your application adaptable and scalable, you set it up for long-term success.