Antonio Mika
·
2025-03-12
postgres.go
1package pgsdb
2
3import (
4 "fmt"
5 "log/slog"
6 "time"
7
8 "github.com/jmoiron/sqlx"
9 _ "github.com/lib/pq"
10 "github.com/picosh/pico/pkg/db"
11 "github.com/picosh/utils"
12)
13
14type PgsPsqlDB struct {
15 Logger *slog.Logger
16 Db *sqlx.DB
17}
18
19var _ PgsDB = (*PgsPsqlDB)(nil)
20
21func NewDB(databaseUrl string, logger *slog.Logger) (*PgsPsqlDB, error) {
22 var err error
23 d := &PgsPsqlDB{
24 Logger: logger,
25 }
26 d.Logger.Info("connecting to postgres", "databaseUrl", databaseUrl)
27
28 db, err := sqlx.Connect("postgres", databaseUrl)
29 if err != nil {
30 return nil, err
31 }
32
33 d.Db = db
34 return d, nil
35}
36
37func (me *PgsPsqlDB) Close() error {
38 return me.Db.Close()
39}
40
41func (me *PgsPsqlDB) FindUsers() ([]*db.User, error) {
42 users := []*db.User{}
43 err := me.Db.Select(&users, "SELECT * FROM app_users")
44 return users, err
45}
46
47func (me *PgsPsqlDB) FindUserByPubkey(key string) (*db.User, error) {
48 pk := []db.PublicKey{}
49 err := me.Db.Select(&pk, "SELECT * FROM public_keys WHERE public_key=$1", key)
50 if err != nil {
51 return nil, err
52 }
53 if len(pk) == 0 {
54 return nil, fmt.Errorf("pubkey not found in our database: [%s]", key)
55 }
56 // When we run PublicKeyForKey and there are multiple public keys returned from the database
57 // that should mean that we don't have the correct username for this public key.
58 // When that happens we need to reject the authentication and ask the user to provide the correct
59 // username when using ssh. So instead of `ssh <domain>` it should be `ssh user@<domain>`
60 if len(pk) > 1 {
61 return nil, &db.ErrMultiplePublicKeys{}
62 }
63
64 return me.FindUser(pk[0].UserID)
65}
66
67func (me *PgsPsqlDB) FindUser(userID string) (*db.User, error) {
68 user := db.User{}
69 err := me.Db.Get(&user, "SELECT * FROM app_users WHERE id=$1", userID)
70 return &user, err
71}
72
73func (me *PgsPsqlDB) FindUserByName(name string) (*db.User, error) {
74 user := db.User{}
75 err := me.Db.Get(&user, "SELECT * FROM app_users WHERE name=$1", name)
76 return &user, err
77}
78
79func (me *PgsPsqlDB) FindFeature(userID, name string) (*db.FeatureFlag, error) {
80 ff := db.FeatureFlag{}
81 err := me.Db.Get(&ff, "SELECT * FROM feature_flags WHERE user_id=$1 AND name=$2 ORDER BY expires_at DESC LIMIT 1", userID, name)
82 return &ff, err
83}
84
85func (me *PgsPsqlDB) InsertProject(userID, name, projectDir string) (string, error) {
86 if !utils.IsValidSubdomain(name) {
87 return "", fmt.Errorf("'%s' is not a valid project name, must match /^[a-z0-9-]+$/", name)
88 }
89
90 var projectID string
91 row := me.Db.QueryRow(
92 "INSERT INTO projects (user_id, name, project_dir) VALUES ($1, $2, $3) RETURNING id",
93 userID,
94 name,
95 projectDir,
96 )
97 err := row.Scan(&projectID)
98 return projectID, err
99}
100
101func (me *PgsPsqlDB) UpdateProject(userID, name string) error {
102 _, err := me.Db.Exec("UPDATE projects SET updated_at=$1 WHERE user_id=$2 AND name=$3", time.Now(), userID, name)
103 return err
104}
105
106func (me *PgsPsqlDB) UpsertProject(userID, projectName, projectDir string) (*db.Project, error) {
107 project, err := me.FindProjectByName(userID, projectName)
108 if err == nil {
109 // this just updates the `createdAt` timestamp, useful for book-keeping
110 err = me.UpdateProject(userID, projectName)
111 if err != nil {
112 me.Logger.Error("could not update project", "err", err)
113 return nil, err
114 }
115 return project, nil
116 }
117
118 _, err = me.InsertProject(userID, projectName, projectName)
119 if err != nil {
120 me.Logger.Error("could not create project", "err", err)
121 return nil, err
122 }
123 return me.FindProjectByName(userID, projectName)
124}
125
126func (me *PgsPsqlDB) LinkToProject(userID, projectID, projectDir string, commit bool) error {
127 linkToProject, err := me.FindProjectByName(userID, projectDir)
128 if err != nil {
129 return err
130 }
131 isAlreadyLinked := linkToProject.Name != linkToProject.ProjectDir
132 sameProject := linkToProject.ID == projectID
133
134 /*
135 A project linked to another project which is also linked to a
136 project is forbidden. CI/CD Example:
137 - ProjectProd links to ProjectStaging
138 - ProjectStaging links to ProjectMain
139 - We merge `main` and trigger a deploy which uploads to ProjectMain
140 - All three get updated immediately
141 This scenario was not the intent of our CI/CD. What we actually
142 wanted was to create a snapshot of ProjectMain and have ProjectStaging
143 link to the snapshot, but that's not the intended design of pgs.
144
145 So we want to close that gap here.
146
147 We ensure that `project.Name` and `project.ProjectDir` are identical
148 when there is no aliasing.
149 */
150 if !sameProject && isAlreadyLinked {
151 return fmt.Errorf(
152 "cannot link (%s) to (%s) because it is also a link to (%s)",
153 projectID,
154 projectDir,
155 linkToProject.ProjectDir,
156 )
157 }
158
159 if commit {
160 _, err = me.Db.Exec(
161 "UPDATE projects SET project_dir=$1, updated_at=$2 WHERE id=$3",
162 projectDir,
163 time.Now(),
164 projectID,
165 )
166 }
167 return err
168}
169
170func (me *PgsPsqlDB) RemoveProject(projectID string) error {
171 _, err := me.Db.Exec("DELETE FROM projects WHERE id=$1", projectID)
172 return err
173}
174
175func (me *PgsPsqlDB) FindProjectByName(userID, name string) (*db.Project, error) {
176 project := db.Project{}
177 err := me.Db.Get(&project, "SELECT * FROM projects WHERE user_id=$1 AND name=$2", userID, name)
178 return &project, err
179}
180
181func (me *PgsPsqlDB) FindProjectLinks(userID, name string) ([]*db.Project, error) {
182 projects := []*db.Project{}
183 err := me.Db.Select(
184 &projects,
185 "SELECT * FROM projects WHERE user_id=$1 AND name != project_dir AND project_dir=$2 ORDER BY name ASC",
186 userID,
187 name,
188 )
189 return projects, err
190}
191
192func (me *PgsPsqlDB) FindProjectsByPrefix(userID, prefix string) ([]*db.Project, error) {
193 projects := []*db.Project{}
194 err := me.Db.Select(
195 &projects,
196 "SELECT * FROM projects WHERE user_id=$1 AND name=project_dir AND name ILIKE $2 ORDER BY updated_at ASC, name ASC",
197 userID,
198 prefix+"%",
199 )
200 return projects, err
201}
202
203func (me *PgsPsqlDB) FindProjectsByUser(userID string) ([]*db.Project, error) {
204 projects := []*db.Project{}
205 err := me.Db.Select(
206 &projects,
207 "SELECT * FROM projects WHERE user_id=$1 ORDER BY name ASC",
208 userID,
209 )
210 return projects, err
211}
212
213func (me *PgsPsqlDB) FindProjects(by string) ([]*db.Project, error) {
214 projects := []*db.Project{}
215 err := me.Db.Select(
216 &projects,
217 `SELECT p.id, p.user_id, u.name as username, p.name, p.project_dir, p.acl, p.blocked, p.created_at, p.updated_at
218 FROM projects AS p
219 LEFT JOIN app_users AS u ON u.id = p.user_id
220 ORDER BY $1 DESC`,
221 by,
222 )
223 return projects, err
224}
225
226func (me *PgsPsqlDB) UpdateProjectAcl(userID, name string, acl db.ProjectAcl) error {
227 _, err := me.Db.Exec(
228 "UPDATE projects SET acl=$3, updated_at=$4 WHERE user_id=$1 AND name=$2",
229 userID, name, acl, time.Now(),
230 )
231 return err
232}