sqlpage / SQLPage

SQLpage SQLPage is an **SQL**-only webapp builder. It allows building powerful data-centric user interfaces quickly, by tranforming simple database queries into interactive websites. With SQLPage, you write simple files containing queries to your database to select, group, update, insert, and delete your data, and you get good-looking clean webpages displaying your data as text, lists, grids, plots, and forms. Examples Code Result Supported databases • SQLite, including the ability to load extensions such as *Spatialite*. • PostgreSQL, and other compatible databases such as *YugabyteDB*, *CockroachDB* and *Aurora*. • MySQL, and other compatible databases such as *MariaDB* and *TiDB*. • Microsoft SQL Server, and all compatible databases and providers such as *Azure SQL* and *Amazon RDS*. • Any **ODBC-compatible database**, such as *ClickHouse*, *MongoDB*, *DuckDB*, *Oracle*, *Snowflake*, *BigQuery*, *IBM DB2*, and many others through their respective ODBC drivers. Get started Read the official *get started* guide on SQLPage's website. Using executables The easiest way to get started is to download the latest release from the releases page. • Download the binary that corresponds to your operating system (linux, macos, or windows). • Uncompress it: • Run it: With docker To run on a server, you can use the docker image: • Install docker • In a terminal, run the following command: • - ( allows sqlpage to run sql files from your current working directory) • Create a file called index.sql with the contents from this example • Open https://localhost:8080 in your browser • Optionally, you can also mount a directory containing sqlpage's configuration file, custom components, and migrations (see configuration.md) to in the container. • For instance, you can use: • - And place your website in a folder named and your in a folder named . • If you want to build your own docker image, taking the raw sqlpage image as a base is not recommended, since it is extremely stripped down and probably won't contain the dependencies you need. Instead, you can take debian as a base and simply copy the sqlpage binary from the official image to your own image: • We provide compiled binaries only for the x86_64 architecture, but provide docker images for other architectures, including arm64 and armv7. If you want to run SQLPage on a Raspberry Pi or a cheaper ARM cloud instance, using the docker image is the easiest way to do it. On Mac OS, with homebrew An alternative for Mac OS users is to use SQLPage's homebrew package. • Install homebrew • In a terminal, run the following commands: • ODBC Setup SQLPage supports ODBC connections to connect to databases that don't have native drivers. You can skip this section if you want to use one of the built-in database drivers (SQLite, PostgreSQL, MySQL, Microsoft SQL Server). Linux and MacOS release binaries conatain a built-in statically linked ODBC driver manager (unixODBC). You still need to install or provide the database-specific ODBC driver for the database you want to connect to. Install your ODBC database driver • DuckDB • If you use docker, a DuckDB-enabled image variant is available with pre-installed DuckDB ODBC drivers • Use the suffix: or • Comes pre-configured to connect to DuckDB at inside the container • To customize connection options, set : • - To persist your DuckDB database, mount a volume: • - Snowflake • BigQuery • For other databases, follow your database's official odbc install instructions. Connect to your database • Find your connection string. • It will look like this: • It must reference the path to the database driver you installed earlier, plus any connection parameter required by the driver itself. Follow the instructions from the driver's own documentation. • Use it in the DATABASE_URL configuration option How it works SQLPage is a web server written in rust) and distributed as a single executable file. When it receives a request to a URL ending in , it finds the corresponding SQL file, runs it on the database, passing it information from the web request as SQL statement parameters. When the database starts returning rows for the query, SQLPage maps each piece of information in the row to a parameter in one of its pre-defined components' templates, and streams the result back to the user's browser. Examples • TODO list: a simple todo list application, illustrating how to create a basic CRUD application with SQLPage. • Plots, Tables, forms, and interactivity: a short well-commented demo showing how to use plots, tables, forms, and interactivity to filter data based on an URL parameter. • Tiny splitwise clone: a shared expense tracker app • Corporate Conundrum: a board game implemented in SQL • Master-Detail Forms: shows how to implement a simple set of forms to insert data into database tables that have a one-to-many relationship. • SQLPage's own official website and documentation: The SQL source code for the project's official site, https://sql-page.com • Image gallery: An image gallery where users can log in and upload images. Illustrates the implementation of a user authentication system using session cookies, and the handling of file uploads. • User Management: An authentication demo with user registration, log in, log out, and confidential pages. Uses PostgreSQL. • Making a JSON API and integrating React components in the frontend: Shows how to integrate a react component in a SQLPage website, and how to easily build a REST API with SQLPage. • Handling file uploads: An image gallery where authenticated users can publish new images via an upload form. • Bulk data import from CSV files : A simple form letting users import CSV files to fill a database table. • Advanced authentication example using PostgreSQL stored procedures • Complex web application in SQLite with user management, file uploads, plots, maps, tables, menus, ... • Single sign-on: An example of how to implement OAuth and OpenID Connect (OIDC) authentication i…