slimtoolkit / slim

Database Management - slimtoolkit/slim

What is the Database Schema?

The Slim database schema is defined in the database package. This package contains the definitions for the tables and their corresponding columns.

Tables

The Slim database uses the following tables:

  • projects: This table stores information about each project.
  • images: This table stores information about each image.
  • builds: This table stores information about each build.
  • layers: This table stores information about each layer.
  • manifests: This table stores information about each manifest.
  • tags: This table stores information about each tag.
  • metadata: This table stores metadata about each project, image, build, layer, manifest, and tag.

Database Schema in Code

Here are some code examples demonstrating how to access the database schema.

Example 1: Retrieving the schema for the projects table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// ProjectsSchema represents the schema for the projects table.
type ProjectsSchema struct {
    ID       int    `db:"id"`
    Name     string `db:"name"`
    Repo     string `db:"repo"`
    Owner    string `db:"owner"`
    Created  int64  `db:"created"`
    Updated  int64  `db:"updated"`
    Deleted  int64  `db:"deleted"`
    Metadata string `db:"metadata"`
}

// GetProjectsSchema retrieves the schema for the projects table.
func GetProjectsSchema(db *sqlx.DB) ([]ProjectsSchema, error) {
    var projects []ProjectsSchema
    err := db.Select(&projects, `SELECT * FROM projects`)
    if err != nil {
        return nil, err
    }
    return projects, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the projects table.
    projects, err := GetProjectsSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, project := range projects {
        fmt.Printf("ID: %d, Name: %s, Repo: %s, Owner: %s, Created: %d, Updated: %d, Deleted: %d, Metadata: %s\n",
            project.ID, project.Name, project.Repo, project.Owner, project.Created, project.Updated, project.Deleted, project.Metadata)
    }
}

Example 2: Retrieving the schema for the images table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// ImagesSchema represents the schema for the images table.
type ImagesSchema struct {
    ID       int    `db:"id"`
    ProjectID int    `db:"project_id"`
    Digest    string `db:"digest"`
    Name     string `db:"name"`
    Tag      string `db:"tag"`
    Created  int64  `db:"created"`
    Updated  int64  `db:"updated"`
    Deleted  int64  `db:"deleted"`
    Metadata string `db:"metadata"`
}

// GetImagesSchema retrieves the schema for the images table.
func GetImagesSchema(db *sqlx.DB) ([]ImagesSchema, error) {
    var images []ImagesSchema
    err := db.Select(&images, `SELECT * FROM images`)
    if err != nil {
        return nil, err
    }
    return images, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the images table.
    images, err := GetImagesSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, image := range images {
        fmt.Printf("ID: %d, ProjectID: %d, Digest: %s, Name: %s, Tag: %s, Created: %d, Updated: %d, Deleted: %d, Metadata: %s\n",
            image.ID, image.ProjectID, image.Digest, image.Name, image.Tag, image.Created, image.Updated, image.Deleted, image.Metadata)
    }
}

Example 3: Retrieving the schema for the builds table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// BuildsSchema represents the schema for the builds table.
type BuildsSchema struct {
    ID       int    `db:"id"`
    ProjectID int    `db:"project_id"`
    Digest    string `db:"digest"`
    Name     string `db:"name"`
    Tag      string `db:"tag"`
    Created  int64  `db:"created"`
    Updated  int64  `db:"updated"`
    Deleted  int64  `db:"deleted"`
    Metadata string `db:"metadata"`
}

// GetBuildsSchema retrieves the schema for the builds table.
func GetBuildsSchema(db *sqlx.DB) ([]BuildsSchema, error) {
    var builds []BuildsSchema
    err := db.Select(&builds, `SELECT * FROM builds`)
    if err != nil {
        return nil, err
    }
    return builds, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the builds table.
    builds, err := GetBuildsSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, build := range builds {
        fmt.Printf("ID: %d, ProjectID: %d, Digest: %s, Name: %s, Tag: %s, Created: %d, Updated: %d, Deleted: %d, Metadata: %s\n",
            build.ID, build.ProjectID, build.Digest, build.Name, build.Tag, build.Created, build.Updated, build.Deleted, build.Metadata)
    }
}

Example 4: Retrieving the schema for the layers table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// LayersSchema represents the schema for the layers table.
type LayersSchema struct {
    ID        int    `db:"id"`
    ImageID   int    `db:"image_id"`
    BuildID   int    `db:"build_id"`
    Digest    string `db:"digest"`
    Size      int64  `db:"size"`
    MediaType string `db:"media_type"`
    Created   int64  `db:"created"`
    Updated   int64  `db:"updated"`
    Deleted   int64  `db:"deleted"`
    Metadata  string `db:"metadata"`
}

// GetLayersSchema retrieves the schema for the layers table.
func GetLayersSchema(db *sqlx.DB) ([]LayersSchema, error) {
    var layers []LayersSchema
    err := db.Select(&layers, `SELECT * FROM layers`)
    if err != nil {
        return nil, err
    }
    return layers, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the layers table.
    layers, err := GetLayersSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, layer := range layers {
        fmt.Printf("ID: %d, ImageID: %d, BuildID: %d, Digest: %s, Size: %d, MediaType: %s, Created: %d, Updated: %d, Deleted: %d, Metadata: %s\n",
            layer.ID, layer.ImageID, layer.BuildID, layer.Digest, layer.Size, layer.MediaType, layer.Created, layer.Updated, layer.Deleted, layer.Metadata)
    }
}

Example 5: Retrieving the schema for the manifests table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// ManifestsSchema represents the schema for the manifests table.
type ManifestsSchema struct {
    ID       int    `db:"id"`
    ImageID   int    `db:"image_id"`
    Digest    string `db:"digest"`
    MediaType string `db:"media_type"`
    Created  int64  `db:"created"`
    Updated  int64  `db:"updated"`
    Deleted  int64  `db:"deleted"`
    Metadata string `db:"metadata"`
}

// GetManifestsSchema retrieves the schema for the manifests table.
func GetManifestsSchema(db *sqlx.DB) ([]ManifestsSchema, error) {
    var manifests []ManifestsSchema
    err := db.Select(&manifests, `SELECT * FROM manifests`)
    if err != nil {
        return nil, err
    }
    return manifests, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the manifests table.
    manifests, err := GetManifestsSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, manifest := range manifests {
        fmt.Printf("ID: %d, ImageID: %d, Digest: %s, MediaType: %s, Created: %d, Updated: %d, Deleted: %d, Metadata: %s\n",
            manifest.ID, manifest.ImageID, manifest.Digest, manifest.MediaType, manifest.Created, manifest.Updated, manifest.Deleted, manifest.Metadata)
    }
}

Example 6: Retrieving the schema for the tags table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// TagsSchema represents the schema for the tags table.
type TagsSchema struct {
    ID       int    `db:"id"`
    ImageID   int    `db:"image_id"`
    Name     string `db:"name"`
    Created  int64  `db:"created"`
    Updated  int64  `db:"updated"`
    Deleted  int64  `db:"deleted"`
    Metadata string `db:"metadata"`
}

// GetTagsSchema retrieves the schema for the tags table.
func GetTagsSchema(db *sqlx.DB) ([]TagsSchema, error) {
    var tags []TagsSchema
    err := db.Select(&tags, `SELECT * FROM tags`)
    if err != nil {
        return nil, err
    }
    return tags, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the tags table.
    tags, err := GetTagsSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, tag := range tags {
        fmt.Printf("ID: %d, ImageID: %d, Name: %s, Created: %d, Updated: %d, Deleted: %d, Metadata: %s\n",
            tag.ID, tag.ImageID, tag.Name, tag.Created, tag.Updated, tag.Deleted, tag.Metadata)
    }
}

Example 7: Retrieving the schema for the metadata table.

package database

import (
    "context"
    "database/sql"

    "github.com/jmoiron/sqlx"
)

// MetadataSchema represents the schema for the metadata table.
type MetadataSchema struct {
    ID       int    `db:"id"`
    ProjectID int    `db:"project_id"`
    ImageID   int    `db:"image_id"`
    BuildID   int    `db:"build_id"`
    LayerID   int    `db:"layer_id"`
    ManifestID int    `db:"manifest_id"`
    TagID    int    `db:"tag_id"`
    Key       string `db:"key"`
    Value     string `db:"value"`
    Created  int64  `db:"created"`
    Updated  int64  `db:"updated"`
    Deleted  int64  `db:"deleted"`
}

// GetMetadataSchema retrieves the schema for the metadata table.
func GetMetadataSchema(db *sqlx.DB) ([]MetadataSchema, error) {
    var metadata []MetadataSchema
    err := db.Select(&metadata, `SELECT * FROM metadata`)
    if err != nil {
        return nil, err
    }
    return metadata, nil
}

// Example usage:
func main() {
    // Connect to the database.
    db, err := sqlx.Connect("postgres", "user=postgres password=postgres dbname=slim")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Retrieve the schema for the metadata table.
    metadata, err := GetMetadataSchema(db)
    if err != nil {
        panic(err)
    }

    // Print the schema.
    for _, metadatum := range metadata {
        fmt.Printf("ID: %d, ProjectID: %d, ImageID: %d, BuildID: %d, LayerID: %d, ManifestID: %d, TagID: %d, Key: %s, Value: %s, Created: %d, Updated: %d, Deleted: %d\n",
            metadatum.ID, metadatum.ProjectID, metadatum.ImageID, metadatum.BuildID, metadatum.LayerID, metadatum.ManifestID, metadatum.TagID, metadatum.Key, metadatum.Value, metadatum.Created, metadatum.Updated, metadatum.Deleted)
    }
}

Database Schema Documentation

The database schema is designed to store information about projects, images, builds, layers, manifests, and tags. It also includes a metadata table to store additional information.

Projects table:

  • id: Unique identifier for the project.
  • name: Name of the project.
  • repo: Repository name of the project.
  • owner: Owner of the project.
  • created: Timestamp when the project was created.
  • updated: Timestamp when the project was last updated.
  • deleted: Timestamp when the project was deleted.
  • metadata: JSON string containing metadata about the project.

Images table:

  • id: Unique identifier for the image.
  • project_id: Foreign key to the projects table, referencing the project that the image belongs to.
  • digest: Image digest.
  • name: Name of the image.
  • tag: Tag of the image.
  • created: Timestamp when the image was created.
  • updated: Timestamp when the image was last updated.
  • deleted: Timestamp when the image was deleted.
  • metadata: JSON string containing metadata about the image.

Builds table:

  • id: Unique identifier for the build.
  • project_id: Foreign key to the projects table, referencing the project that the build belongs to.
  • digest: Build digest.
  • name: Name of the build.
  • tag: Tag of the build.
  • created: Timestamp when the build was created.
  • updated: Timestamp when the build was last updated.
  • deleted: Timestamp when the build was deleted.
  • metadata: JSON string containing metadata about the build.

Layers table:

  • id: Unique identifier for the layer.
  • image_id: Foreign key to the images table, referencing the image that the layer belongs to.
  • build_id: Foreign key to the builds table, referencing the build that the layer belongs to.
  • digest: Layer digest.
  • size: Size of the layer in bytes.
  • media_type: Media type of the layer.
  • `created

Querying the Database

The github.com/slimtoolkit/slim package provides a powerful and flexible interface for interacting with databases. This document outlines how to query the database using the slim package.

Example

package main

import (
    "context"
    "fmt"

    "github.com/slimtoolkit/slim"
)

func main() {
    // Create a new database connection.
    db, err := slim.NewDatabase("mysql", "user:password@tcp(host:port)/database")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Execute a query.
    rows, err := db.Query(context.Background(), "SELECT * FROM users")
    if err != nil {
        panic(err)
    }
    defer rows.Close()

    // Iterate over the results.
    for rows.Next() {
        var id int
        var name string
        var email string

        // Scan the values into the variables.
        err := rows.Scan(&id, &name, &email)
        if err != nil {
            panic(err)
        }

        // Print the results.
        fmt.Printf("ID: %d, Name: %s, Email: %s\n", id, name, email)
    }

    // Check for errors during iteration.
    if err := rows.Err(); err != nil {
        panic(err)
    }
}

This example demonstrates how to connect to a database, execute a query, and iterate over the results. The slim package supports a variety of database drivers, allowing you to interact with different database systems.

Database Drivers

The slim package provides support for the following database drivers:

  • MySQL: github.com/go-sql-driver/mysql
  • PostgreSQL: github.com/lib/pq
  • SQLite: github.com/mattn/go-sqlite3
  • MongoDB: go.mongodb.org/mongo-driver/mongo

Query Parameters

To prevent SQL injection, it’s crucial to use parameterized queries. The slim package supports parameterized queries using the slim.Query() and slim.Exec() functions.

// Example using Query() with parameters
rows, err := db.Query(context.Background(), "SELECT * FROM users WHERE id = ?", 1)
if err != nil {
    panic(err)
}
// ...

// Example using Exec() with parameters
result, err := db.Exec(context.Background(), "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "[email protected]")
if err != nil {
    panic(err)
}

These examples show how to pass parameters to queries and prevent SQL injection vulnerabilities.

Transactions

The slim package allows you to perform transactions using the slim.BeginTx() function.

tx, err := db.BeginTx(context.Background())
if err != nil {
    panic(err)
}
defer func() {
    if r := recover(); r != nil {
        _ = tx.Rollback()
        panic(r)
    } else if err := tx.Commit(); err != nil {
        panic(err)
    }
}()

// Execute multiple queries within the transaction.
_, err = tx.Exec(context.Background(), "INSERT INTO users (name, email) VALUES (?, ?)", "Jane Doe", "[email protected]")
if err != nil {
    panic(err)
}

// ... more queries within the transaction

// Commit the transaction.
if err := tx.Commit(); err != nil {
    panic(err)
}

This example demonstrates how to begin a transaction, execute multiple queries, and commit the transaction. You can also rollback the transaction if an error occurs.

Error Handling

The slim package returns an error if a query fails. You should always handle errors gracefully to prevent application crashes.

// Example of error handling
result, err := db.Exec(context.Background(), "INSERT INTO users (name, email) VALUES (?, ?)", "John Doe", "[email protected]")
if err != nil {
    // Handle the error, e.g., log it and return an error response.
    fmt.Println("Error executing query:", err)
    return err
}

This example demonstrates how to check for errors after executing a query and handle them accordingly.

Conclusion

The slim package provides a powerful and flexible interface for querying databases in Go. By using the provided functions and best practices for SQL injection prevention, you can efficiently and securely interact with databases in your applications. Remember to always handle errors gracefully to ensure your application’s stability.