shivansh-yadav13/sqlite-ui

Web 🌐 based Admin Interface for SQLite 🗂️ Database.

🌐 SQLite UI

SQLite UI is a web user interface for SQLite database built with Golang. It is built in Golang using the Fiber package. Fiber is an Express inspired web framework You can read more about it here. Project is right now at it's very intial stage and under developement. Our plan is to make this a Good Open Source project, where people can easily start contributing, fix bugs and add more features 🙂.

🔧 Usage

Step 1:

Pull Docker Image.

docker pull ghcr.io/shivansh-yadav13/sqlite-ui:latest

Step 2:

Start Docker container using:

docker run -p 3000:3000 -v { location of db file }:/app -e SQLITE_NAME=/app/{ db file name } shivansh-yadav13/sqlite-ui:latest

Optional: You can also use the --name and -d for the name of the container and for running in detached mode.

💻 Set up Project Locally

1.) Fork the repository by click the Fork button at the top right of the page.

2.) Navigate to a directory, open the terminal and paste the following command and replace your-github-username with your GitHub username:

git clone https://github.com/your-github-username/SQLite-UI.git

3.) Build the TailwindCSS bundle, see https://tailwindcss.com/docs/installation for instruction to install the cli.

./tailwindcss -i ./static/css/app.css -o ./static/css/build.min.css --minify

4.) Now we are ready to start the server, run the following:

$ go run main.go

This will create an empty data.db in the current directory. Alternatively, you can also pass in an existing database file as follow:

$ SQLITE_NAME=<path-to-db-file> go run main.go

5.) Now you can visit localhost:3000 and you will be able to see:

👥 Contributors

sqlite-ui's Issues

[BUG] prevent creation of tables with no names

Describe the bug
Create table method can create a table without any name

Expected behavior
table-name field cannot be empty when clicking on Create table button.

Screenshots

Update the browser dashboard image in README.md

Replace the existing image with this new one: 👇

Fiber Redirect not working

Redirect not working.
Right now, when clicking on create table or drop table button, we make request to /drop-table or /create-table but it does not redirect back to /
Check the docs to fix it.
https://docs.gofiber.io/

Add the fix to the below lines in the code.

SQLite-UI/routes/handlers.go

Line 46 in e16266b

return c.Redirect("/", 201)

SQLite-UI/routes/handlers.go

Line 58 in e16266b

return c.Redirect("/", 202)

Front end layout

As discussed in #6, incorporate tailwindcss and setup a dashboard style structure for the app.

[feat] Datatables that can be customized, sortable and filterable by column for Table Page

Is your feature request related to a problem? Please describe.
Thanks to @wliao008's #29 PR, we are able to have paginated tables.
However, in terms of UI, the lack of interactivity and features of the current plain HTML table makes it difficult to filter and customize the table view.

Describe the solution you'd like
The Datatables component should be able to render an interactive table where the user is able to:

• show/hide columns
• sort by column
• filter by column
• Download as CSV (TBC)

Describe alternatives you've considered
Existing no-code tools like NocoDB and Baserow already has such Datatable components written in Vue.
We can take reference from these implementations.
nocodb/nocodb
bram2w/baserow

Additional context
N/A

Support custom query when viewing data

When viewing data, allow the query to be customized. For ex, user might want to see a few columns instead of all, or might need to join with other tables, etc. The end result could be something like this:

[feat] support for all sqlite file extensions

Issue
Sqlite database support the following file extensions
.sqlite, .sqlite3, .db, .db3, .s3db, .sl3
but sqlite-ui can only open files with .db extension.

Solution
Make the db uri flexible and allow the users to pass in file name along with the extension

SQLite-UI/main.go

Line 19 in ea5984d

db.ConnectDB(fmt.Sprintf("./sqlite_database/%s.db", dbName))

Feat: Implement `GetSQLQuery` method

We need to display the query using which the same table can be created.

• Implement the method here:
  

  SQLite-UI/db/database.go

  Line 89 in c4a0df2

  func GetSQLQuery(tableName string) (string, error) {}

• We are receiving the result here:
  

  SQLite-UI/routes/handlers.go

  Line 24 in c4a0df2

  query, err := db.GetSQLQuery(c.Params("table"))

• Display the value here:
  

  SQLite-UI/templates/table_view.html

  Line 11 in c4a0df2

  {{.table_name}}

Execute the query as done in the GetTables() method.

Frontend for the Pages

• Need Frontend design for the Pages

[feat]: de-duplicate the layout

The current layout is duplicated across all the pages, which is not ideal if a change is needed.

Break the layout into different templates so the layout can be "composed". The desired outcome of this, is that a new page should be self-contained.

Our current layout structure is pretty simple, mainly a left nav and a right content area. I can see something like this:

{{ define "header" }}
  ... all the header + nav element here ...
{{ end }}

{{ define "footer" }}
  ... all the footer element here ...
{{ end }}

Then in any given page, for ex when viewing the table data:

{{ template "header" . }}
... the actual data specific to this page ...
{{ template "footer" . }}

Fix fiber not applying CSS

• CSS file: https://github.com/Shivansh-yadav13/SQLite-UI/blob/main/static/css/styles.css
• Fiber config : https://github.com/Shivansh-yadav13/SQLite-UI/blob/main/configs/config.go
  Maybe the folder structure needs to be changed in order for this to work or there is something missing.

Docs: Setting up project Locally

Add steps to setup project locally in the README.md

Switch to a smaller base docker image

The current docker image is base off golang:1.18, which is 900+mb when pulled down. It's not needed to run the actual end program.

There are 2 ways that this can be approached:

1. Use a 2 step build process: build the app in the golang container, then copy the resulting binary onto a smaller image, all done via the Dockerfile.
2. Build the app outside of the container, maybe via a Makefile, and copy the resulting binary to a smaller image.

Alpine is a pretty good choice, this'd cut the size down from 900+mb to around ~20mb.

[Test] Add unit test for `AddColumn`

• add unit test for AddColumn method in db package.

• SQLite-UI/db/database.go

  Line 100 in ade5d42

  func AddColumn(tableName string, columnName string, dataType string) error {

[Feature] Add option for adding columns to the table

Create a form on templates/table_view.html which will send a post request to a route which will add a column to a particular table.
The form should also have options available when adding columns like Primary Key, DataType, Not null etc.
The name of the table can be taken from the url params.

steps:

• Create a method for the above task on the db package.
• Then create a handler in the routes package which will call method from the db package.
• Create a route for the above handler.
• Create form on templates/table_view.html and send the post request to the above route.

Add CI GitHub workflow

• Add CI for existing test.
• Need to setup SQLite database in it and run tests.

feat: show the name of the db file on side barl by default expand list of tables

1. I think it'd be helpful to show the name of the db file that's passed in (or the default name if not) in place of Tables, so if I pass in

$ SQLITE_NAME=/path/to/mydata.db go run main

It will show mydata.db there, otherwise show data.db.

2. Could we always show the list of tables, and remove the collapsable toggle?

View data of a given table

When a user clicks on a table, we need to get and display the data, in an efficient way.

Something to think about:

• What if the table has millions of row.
• What if the table has tons of columns.
• What is some fields has long text or big blob.

Of these, the first is probably the biggest concern, so at the very least, have pagination in place.