Railway•9mo ago

Sqlite DB

Is there a way I can add an sqlite database to a project in railway and have its data persist across deployments in my project?

Solution:

database.LoadAndMigrate("db/altera.db") then mount the volume to /app/db...

Jump to solution

37 Replies

Percy•9mo ago

Project ID: dfc8d88b-459f-4ec8-9e35-824c0bc7fbc0

Crspy•9mo ago

dfc8d88b-459f-4ec8-9e35-824c0bc7fbc0

Fragly•9mo ago

It is possible through the use of volumes

Crspy•9mo ago

currently I have a volume added to a deployment in my project, but when the deployment is redeployed, the volume is reset. Maybe I am not adding the sqlite db to the volume correctly? the mount path for the volume is set as /db, and in my code, I store the sqlite database file as /db/sqlite.db

Fragly•9mo ago

volumes are mounted to the root of your container and your app is in the /app directory in your container, to make /db persist, you'll want to mount the volume to /app/db

Crspy•9mo ago

ahh ok crap didnt mean to delete does the project ID allow you to see that about my project?

Fragly•9mo ago

Conductors can't see anything inside your project, only team members can when your app is built via nixpacks then your app is deployed into the /app directory by default

Crspy•9mo ago

my app is deployed via docker

Crspy•9mo ago

Fragly•9mo ago

ooh i see, just make sure the volume is mounted on the global path of your db then

Crspy•9mo ago

so in my dockerfile, if I set the WORKDIR to /app the volume should mount to /app/db like you suggested

Fragly•9mo ago

exactly right

Crspy•9mo ago

sorry im kinda new to this.

Fragly•9mo ago

no worries 🙂

Crspy•9mo ago

ok thanks for your help! I will go and try this out

Fragly•9mo ago

happy to help

Crspy•9mo ago

doesn't seem like it is working. When I set the mount path to /app, I get errors saying that /app/backend is not found. When I set it to /app/server the error disappears, but the database is not persistent as this directory is overwritten on each deployment. Do you have any idea what is causing this? Here is my dockerfile:

FROM golang:latest as builder

WORKDIR /app

COPY go.mod go.sum ./
RUN go mod download
COPY . .

RUN GOOS=linux go build -v -o backend ./server

FROM ubuntu:latest
RUN apt-get update && \
    apt-get install -y ca-certificates && \
    update-ca-certificates && \
    rm -rf /var/lib/apt/lists/*

COPY --from=builder /app/backend /app/backend
RUN chmod +x /app/backend
CMD ["/app/backend"]

FROM golang:latest as builder

WORKDIR /app

COPY go.mod go.sum ./
RUN go mod download
COPY . .

RUN GOOS=linux go build -v -o backend ./server

FROM ubuntu:latest
RUN apt-get update && \
    apt-get install -y ca-certificates && \
    update-ca-certificates && \
    rm -rf /var/lib/apt/lists/*

COPY --from=builder /app/backend /app/backend
RUN chmod +x /app/backend
CMD ["/app/backend"]

Brody•9mo ago

fragly asked me to take over, it was already very late for him. please show me the code that opens the sqlite database

Crspy•9mo ago

here is the code:

err := godotenv.Load()

    dbPath, ok := os.LookupEnv("RAILWAY_VOLUME_MOUNT_PATH")
    fmt.Println("ENV path: ", dbPath)
    if !ok {
        dbPath = "./"
    } 
    
    fmt.Println("Database path: ", dbPath)

    err = database.LoadAndMigrate(dbPath + "altera.db")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Database loaded and migrated")

err := godotenv.Load()

    dbPath, ok := os.LookupEnv("RAILWAY_VOLUME_MOUNT_PATH")
    fmt.Println("ENV path: ", dbPath)
    if !ok {
        dbPath = "./"
    } 
    
    fmt.Println("Database path: ", dbPath)

    err = database.LoadAndMigrate(dbPath + "altera.db")
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Database loaded and migrated")

Solution

Brody•9mo ago

database.LoadAndMigrate("db/altera.db") then mount the volume to /app/db

Crspy•9mo ago

I'll give it a try

Crspy•9mo ago

It cant create the file db file in the directory

Brody•9mo ago

if the file doesnt exist your code needs to create it, its an empty volume after all

Crspy•9mo ago

In my github repo, there is an empty folder called db so it should make it in there maybe I need to add a chmod -R 777 /db to my dockerfile

Brody•9mo ago

I'm talking about the database file itself

Crspy•9mo ago

my code dos create it when I run it locally at least. I think that its a linux permission issue, where the program doesnt have permission to create the file.

Brody•9mo ago

show me the updated code please

Crspy•9mo ago

The code didn't change. The LoadAndMigrate function creates the file (or the gorm.Open function inside that function does:

func LoadAndMigrate(sqliteFile string) error {
    if Instance != nil {
        return errors.New("database already initialized")
    }

    db, err := gorm.Open(sqlite.Open(sqliteFile), &gorm.Config{})
    if err != nil {
        return err
    }

    err = db.AutoMigrate(Models...)
    if err != nil {
        return err
    }

    Instance = db

    return nil
}

func LoadAndMigrate(sqliteFile string) error {
    if Instance != nil {
        return errors.New("database already initialized")
    }

    db, err := gorm.Open(sqlite.Open(sqliteFile), &gorm.Config{})
    if err != nil {
        return err
    }

    err = db.AutoMigrate(Models...)
    if err != nil {
        return err
    }

    Instance = db

    return nil
}

Brody•9mo ago

I had suggested code changes though

Crspy•9mo ago

ohh ye I did these my bad i just wasnt thinking

Brody•9mo ago

please show me the updated code

Crspy•9mo ago

    err := godotenv.Load()

    err = database.LoadAndMigrate("db/altera.db")
    if err != nil {
        log.Fatal(err)
    }

    err := godotenv.Load()

    err = database.LoadAndMigrate("db/altera.db")
    if err != nil {
        log.Fatal(err)
    }

removed the prints, changed the volume mount location and string in LoadAndMigrate function to "db/altera.db" as you suggested I removed the environment variable stuff since it wasn't really serving any use I believe you said to change the string passed to LoadAndMigrate which is what I did and that removed the necessity to have the previous code

Brody•9mo ago

I'd have to do some testing myself currently making dinner though

Crspy•9mo ago

ok take ur time. Imma just mess around with stuff on my side and see if smth makes it work got it to work. I misstyped smth in railways 😭

Brody•9mo ago

what did you miss type

Crspy•9mo ago

db was dv just wondering, is there a way to retrieve (or download) data from inside volumes (via ssh or smth)? or is that not supported?

Brody•9mo ago

that sure would do it theres noting native, but there are these two templates https://railway.app/template/guiCBv https://railway.app/template/Nan7Bs

Gaming

Programming

Sqlite DB