DEV Community

Colin Fay
Colin Fay

Posted on • Originally published at colinfay.me on

3 3

clientsdb - A docker image with clients comments

Have you ever been looking for a ready to use database while doingtraining? Search no more, this docker is an image with a client review database dropped inside a postgre, to be used for teaching.

About the dataset

The titles and comments are extracted from this GoogleDrivelink that contains “amazon_review_full_csv.tar.gz”, which I discovered on the Amazon reviewdatabase Kaggle page. Then the two columns date and name being randomly generated in R.

Here is the coded used to generate the full table:

library(data.table)
dataset <- fread("data/train.csv", header = FALSE, sep = ",")
names(dataset) <- c("score", "title", "comment")

nms <- paste(
  sample(charlatan:::person_en_us$first_names, nrow(dataset), TRUE), 
  sample(charlatan:::person_en_us$last_names, nrow(dataset), TRUE)
)

date <- sample(0:as.numeric(as.POSIXct("2010-01-01")), nrow(dataset), TRUE)
date <- as.POSIXct(date, origin = "1970-01-01")

dataset[
  , `:=`(
    score = NULL,
    name = nms, 
    date = date
  )
]   

data.table::fwrite(dataset, "datasetwithusers.csv")

Enter fullscreen mode Exit fullscreen mode

Launch and use

The main purpose of this image is to provide a “real life” tool for teaching databases use.

Info:

  • the POSTGRES_DB used is clients
  • the POSTGRES_PASSWORD isverysecretwow
  • the POSTGRES_USER is superduperuser

To launch the db, do:

# Might take some time to warm up
docker run --rm -d -p 5432:5432 --name clientsdb colinfay/clientsdb:latest

Enter fullscreen mode Exit fullscreen mode

Then, for example from R:

library(DBI)

con <- dbConnect(
  RPostgres::Postgres(),
  dbname = 'clients', 
  host = 'localhost',
  port = 5432, 
  user = 'superduperuser',
  password = 'verysecretwow'
)

dbListTables(con)


[1] "clients"


res <- dbSendQuery(con, "SELECT score, title, name, date FROM clients LIMIT 5")
dbFetch(res)


  score title name date
1 5 Fantastic Item Karolyn Wunsch 2003-01-02
2 4 Easy to Use, Great Value Wayland Langosh 2008-06-06
3 4 It works well! Fed Oberbrunner 1989-07-22
4 3 Meets a need Lora Yost 1976-04-13
5 4 Ion TTUSB Turntable with USB Record Valentina Harvey 1986-01-28


dbClearResult(res)

res <- dbSendQuery(con, "SELECT title, name, date FROM clients WHERE date = '1998-05-12' LIMIT 10")
dbFetch(res)


                                title name date
1 underrated art Burgess Kuhlman 1998-05-12
2 Great product! Madelyn Bailey 1998-05-12
3 Disappointing, not correct. Scott Walsh 1998-05-12
4 The turtle says it all! Magdalen Strosin 1998-05-12
5 I took the TEAS and NLN. Rosie Bradtke 1998-05-12
6 Terrible. Lita Marquardt 1998-05-12
7 Harlan County History Clemon Effertz 1998-05-12
8 A flawed book, but a good subject. Amey Rutherford 1998-05-12
9 Worth every penny Debrah Keebler 1998-05-12
10 A light enjoyable read Dwaine Schneider 1998-05-12


dbClearResult(res)

dbDisconnect(con)

Enter fullscreen mode Exit fullscreen mode

And then stop the db.

docker stop clientsdb 
Enter fullscreen mode Exit fullscreen mode

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Postmark Image

Speedy emails, satisfied customers

Are delayed transactional emails costing you user satisfaction? Postmark delivers your emails almost instantly, keeping your customers happy and connected.

Sign up