Forem: Mila Wu

🔧Top Open Source AI Web Scrapers to Fire Up Your Market Research🔥

Mila Wu — Mon, 27 May 2024 06:27:44 +0000

Web scraping, in simpler words, is to scrape data and content from websites, the data is then saved in the form of XML, Excel, or SQL. On top of lead generation, competitor monitoring, market research, web scrapers can also be used to automate your data collection process.

With the help of AI web scraping tools, the limitations associated with manual or purely code-based scraping tools can be addressed: dynamic or unstructured websites can easily be handled, all without human intervention.

Here, we present a few open-source AI web scraping tools to choose from.

Reader

Reader is an offering by Jina AI. It can convert any URL to an LLM-friendly input when you append a simple https://r.jina.ai/, and you can get structured output for your agent and RAG systems at no cost.

Since its first release just this past month (April 15th, to be exact), they have served over 18M requests from the world, and the project itself has already gained 4.5K stargazers.

Aside from scraping any URL, Jina just released another feature where you can use https://s.jina.ai/YOUR_SEARCH_QUERY to search from the up-to-date knowledge on the Internet. The result includes a title, LLM-friendly markdown, and a URL that attributes the source.

Together, you can construct a comprehensive solution for LLMs, agents, and RAG systems.

LLM Scraper

LLM Scraper is a TypeScript library that can convert any webpage into structured data using LLMs. Essentially, it uses function calling to convert pages to structured data.

Simliarly to Reader, it was open-sourced just last month. It currently supports Local (GGUF), OpenAI, Groq chat models. Apparently, the author is working on supporting local LLMs via llama.cpp to lower the cost of using LLMs for web scraping.

Firecrawl

Firecrawl is an API service that can convert an URL into clean, well-formatted markdown. This format is great for LLM applications, offering a structured yet flexible way to represent web content.

This tool is tailored for LLM engineers, data scientists, AI researchers, and developers looking to harness web data for training machine learning models, market research, content aggregation. It simplifies the data preparation process, allowing professionals to focus on insights and model development, and you can self-host it to your own taste.

ScrapeGraphAI

ScrapeGraphAI is a Python library that uses LLM and direct graph logic to create scraping pipelines for websites and local documents (XML, HTML, JSON, etc.). With ScrapeGraphAI, you get to specify exactly what sort of data you want to extract.

ScrapegraphAI leverages the power of LLMs, and can thus adapt to changes in website structures, reducing the need for constant developer intervention. This flexibility ensures that scrapers remain functional even when website layouts change.

The LLMs it currently supports include GPT, Gemini, Groq, Azure, Hugging Face, as well as local models.

LangChain

What is LangChain not capable of? Not web scraping.

One of web scraping's biggest challenges is the changing nature of modern websites' layouts and content, which requires modifying scraping scripts to accommodate the changes, and LangChain also utilizes function (e.g., OpenAI) with an extraction chain, so that you don't have to change your code constantly when websites change.

If you are doing research and want to scrape only news article's name and summary from The Wall Street Journal website, it's got you covered.

To Sum Up

Of course, there is no one-size-fits-all web scraper. Do you prefer old-school traditional web scrapers or LLM-empowered ones?

Top Open Source Prompt Engineering Guides & Tools🔧🏗️🚀

Mila Wu — Fri, 03 May 2024 01:13:00 +0000

Prompt Engineering is to design and refine prompts or instructions given to a language model, in order to elicit desired responses. To make the most of the language model's capabilities while ensuring the responses are accurate and tailored to their needs, you often need to carefully craft the input provided to the model to guide its output towards specific objectives.

For this section, we divided them into two sections: The guides which will take you through the basics of prompt engineering, and the tools for managing your prompts.

The Guides

Prompt Engineering Guide

Prompt Engineering Guide is the holy grail of all guides, aiming to make it easier to stay up-to-date with prompt engineering guides, techniques, applications, and papers. If you are getting started, this is an excellent place to start.

Since going live at the end of 2022, Prompt Engineering Guide has grown to support 13 languages, its courses have educated over 3M learners, and they have put together a 1-hour lecture that provides an overview of prompting techniques, applications, and tools.

Awesome-GPTs-Prompts

Awesome-GPTs-Prompts is a curated list of prompts from the top-rated GPTs so that you can unlock the magic behind large language models more easily.

The curated list includes GPTs from the official GPT Store, prompts from the community, and some interesting resources on basic prompt engineering, how to prompt attack, and prompt protect.

Prompt Management

Langfuse

Once you have the basics of prompt engineering down, it's time to manage and test your prompts so that they are on their A game.

Langfuse is an open-source LLM engineering platform that helps teams collaboratively debug, analyze, and iterate on their LLM applications.

With it, even non-tech users can manage, version and deploy prompts. It's also pretty straightforward to rollback to a previous version of a prompt (we all make mistakes😬).

And there's more to it: tracing, monitoring, testing are also part of the Langfuse platform.

Pezzo

Pezzo is a cloud-native LLMOps platform. You can observe and monitor your AI operations, troubleshoot issues, collaborate and manage your prompts in one place, and instantly deliver AI changes.

With Pezzo, you have a centralized place for prompt management:

Design, testing and versioning of prompts
Instant prompt deployments
Observability including prompt execution history, stats and metrics
Troubleshooting to resolve issues with prompts

Agenta

Agenta is an end-to-end LLMOps platform. It provides tools for prompt engineering and management, evaluation, human annotation, and deployment.

For Agenta, prompt engineering is taken very seriously, and they have different ways of evaluating, experimenting, and comparing your prompts.

You can either use a set of built-in evaluators that can be configured, loading test sets, or simply compare variants side by side where you can view the results of multiple variants simultaneously.

Lastly

More companies than ever have launched their own AI tools, as simple as a chatbot to automation, or project management tools. As McKinsey puts it, "Prompt engineering is likely to become a larger hiring category in the next few years".

Obtaining desired results is not that complicated, but it requires practice to provide specific, clear instructions to the computers. We have a great list for you, get started playing🤸.

📧 Subscribe to our weekly newsletter here.

🛠️Non-AI Open Source Projects that are 🔥

Mila Wu — Thu, 04 Apr 2024 08:42:46 +0000

Almost 70% of the YC W24 batch are building AI tools. This is a good indication of how the world of tech has come to.

BUT we are looking the other way this time: of all the AI products and launches, there are still products that are non-AI (or at least, not directly related to AI) and are fire🔥 ATM. What are they? How did they break through the AI storm?

Bruno

Bruno is a Git-friendly API client. Feature-wise, what makes it stand out from other popular GUI API clients out there is that Bruno stores your collections directly in a folder on your filesystem and it's a desktop app made for offline use.

Apparently, it took them 2 years to get from 0 to 500 stars, and 10 days from 500 to 5000. And now they have over 17K stargazers. This discussion might have something to do with their rapid growth, which was also debated on HN. People are passionate regarding the topic of oss monetization! Monetizaion or not, Bruno definetely got way more popular over the past 6 months.

Dub

Dub is the open-source Bitly successor. It debuted in September, 2022 as an internal tool for Vercel, but the author started a company around Dub earlier this year and recently landed #1 on Product Hunt.

It's more than just your usual link-management tool: it's a dream for marketing teams, as it comes with built-in analytics, geographic data, and branded links.

Maybe

Maybe is the story of a comeback. It was shut down in July, 2023. Not wanting to waste the $1,000,000 from investors, Maybe was open-sourced this past January as a fully transparent, open-source, community-backed personal finance app. It was hard to miss because it was trending on GitHub for the entire week.

Right now they are making improvements from the story-learned from the previous rendevous, new funding, and the power of open-source. So keep an eye out for it if you like looking at sexy finance dashboards. 📊💰

Monolith

Monolith is a CLI tool for saving complete web pages as a single HTML file.

Unlike the conventional “Save page as”, Monolith not only saves the target document, it embeds CSS, image, and JavaScript assets all at once, producing a single HTML5 document for you to store and share. Perfect if you are looking to save something for offline access, a read-it-later, or bookmarking/archiving.

It was open sourced in 2017, and the two spikes in stargazers it since had are most likely thanks to two HN submissions (2019, and 2024). All hail HN!

Puter

Puter is a cloud personal computer, and you can have all your files, apps, and games in one place accessible from anywhere at any time. So, basically a computer in your browser,

Puter has been around a while, but was recently open-sourced so that you can self-host it on any device, even from your EV car!

Lastly

So... what are you working on? Is it AI or non-AI?

🔍Underrated Open Source Projects You Should Know About 🧠

Mila Wu — Thu, 21 Mar 2024 05:30:25 +0000

For Star History's Feb edition, we were fully inspired by this post discussing underrated open-source projects that deserve more recognition, and we dived DEEP into the threads to see what we were missing out! Here are some of our favorites:

Cryptpad
Pagefind
Paperless-ngx
QPDF
River

Cryptpad

CryptPad provides a full-fledged office suite with all the tools necessary for productive collaboration.

When running locally, CryptPad is a powerful application for creating and editing documents. When you use it through a browser, CryptPad is different: it is end-to-end encrypted. No one can access your documents; all your data stays on the device you are using, not even CryptPad administrators can view the content of your files.

For teams, you can collaborate in real-time with other users. You can share documents via a link with passwords or a certain amount of time, or you can embed your documents using tags

Pagefind

Pagefind is a static search library that aims to perform well on small or large sites, while using as little bandwidth as possible, and you don't have to host any infrastructure.

Usually, you'd be spinning up your own infrastructure, or signing up for a third-party platform to add search to your website. Pagefind is basically a frontend search library and running the queries inside the users’ browser. And it works with most website frameworks: Hugo, Eleventy, Jekyll, Next, Astro, SvelteKit, etc.

Paperless-ngx

Paperless-ngx is a document management system that transforms your physical documents into a searchable online archive so you can find your physical documents easier. With features such as tags, full text search, multi-user permissions system, this is a dream for those who like to keep an organized folder of files and documents.

Paperless-ngx is the successor to the original Paperless & Paperless-ng projects, both of which are now in public archive. The original projects are not dead, but rather, continued through the open source community!

QPDF

QPDF is a CLI tool that performs content-preserving transformations on PDF files. We have another tool for managing files!

QPDF's magic begins once you have a valid PDF file:

What if you have a password-protected PDF and can’t remember the password? It can transform that file in ways that your current PDF tool can’t handle with minimal operations from your CLI.

Beyond encryption and decryption, QPDF can also modify, merge, split, and convert PDF files into different formats.

River

River is a Python library for online machine learning. Online machine learning can dynamically adapt to new patterns in the data, or when the data itself is generated as a function of time, e.g., stock price prediction, content personalization.

River supports different machine learning tasks, including regression, classification, and unsupervised learning. It can also be used for adhoc tasks, such as computing online metrics, as well as concept drift detection.

River is actually the merger between creme and scikit-multiflow, another great example of open source collaboration and continuation.

Lastly

What do you think? Are there any open-source projects that you have come across that deserve more recognition? Let us know!

👑 Top Open Source Projects of 2023 🚀

Mila Wu — Mon, 04 Mar 2024 03:05:24 +0000

Time for an End of Year Review for 2023! 🎉

AI is the keyword of 2023, but there is so much more to this past year in the realm of open source! We at Star History HQ gathered a few categories and picked our favorite project for each category, including:

AI
Database
DevTool
Front-end
Security
Web3

AI: LangChain

LangChain was first released in October 2022 as an open-source side project, a framework that makes developing AI applications more flexible. It got so popular that it was promptly turned into a startup.

In 2023, LangChain managed to gain ~70k stargazers, scoring a $10M seed round, grew exponentially product-wise: the framework now consists of several parts, integrations with 20+ different LLM providers, 10+ different vector databases, and more, lowering AI's threshold for the people. For example, they have this SQL Agent so that you can build a Q&A chain and agent over an SQL database.

2023 was the first "real" year of LLM app development, and they must now have a clearer vision of how to construct the product to better enable people to use AI.

Database: Neon

Neon is an open-source serverless Postgres offering.

Postgres took over the first spot from MySQL and became the most admired and desired database in 2023, as per Stack Overflow's annual survey. Neon is smart and stands on top of the giant by bringing Postgres to the cloud. They landed a $46M Series B funding in August 2023 (see, last year wasn't JUST about ChatGPT and AI!)

Neon uses a shared-storage architecture that separates the compute and storage. The compute part is a PostgreSQL server, the storage part is a custom-built multi-tenant storage system shared by all Postgres compute nodes.

DevTool: Nx

Nx is a set of DevTools to make it simpler for software dev teams to work within a monorepo, which is a version-controlled code repository that contains several projects. Think of Nx as a VSCode of build tools.

The funding team saw a need for tools to make it simpler for devs to store related apps and libraries in one shared code repository, and so they built Nx. You can speed up your builds and tests, locally and on CI, and integrate and automate your tooling via its plugins.

It's worth noting that they closed a $16 million Series A last year.

Front-end: Astro

Astro is a front-end framework to simplify building static websites and web applications. It has support for various popular frameworks, including React, Vue, and Svelte, so it is also possible to add dynamic elements to your pages using your preferred frameworks.

You can use Astro to build your website - whatever its purpose: event, marketing, e-commerce, blog. One key element of Astro is it automatically removes unused JavaScript and renders to HTML for better core web vitals, conversion rates and SEO, and that's what we love!

Astro had a big year. They started with ~23k stargazers and ended with a 60% increase to ~38k.

Security: Teleport

Teleport is an SSH for Clusters and Teams and aims to be the drop-in replacement for OpenSSH.

Teleport removes the need for VPNs, comes with a beautiful new Web UI, and provides a unified access plane for all your infrastructure: servers, Kubernetes, internal web apps, databases, and cloud provider APIs across all your infrastructure.

When it comes to infrastructure security, you should be skeptical and pragmatic: it'd be smart to put more trust in a product that's been around a while, so that it's trialed and tested, and probably not going anywhere anytime soon (FYI: Teleport debuted on Hacker News way back in 2016, and they recently released Teleport 15).

Web3: zkSync

Aside from AI, Web3 was also having a moment in 2023, and Crypto is more than just about currency!

zkSync is a ZK rollup, a layer-2 protocol that uses cryptographic validity proofs to provide scalable and low-cost transactions on Ethereum.

zkSync is one of the most recognized layer-2 scaling solutions for Ethereum. What makes zkSync stand out from other layer-2 protocols is that it can significantly reduce gas costs without sacrificing security or user control, making it cheaper and faster for users to interact with Ethereum smart contracts.

To Wrap Up

What a year, huh? We've witnessed some pretty amazing growth and ideas come to life.

And, thank you for following Star History through 2023. We are excited and ready for what the New Year has in store for us!

Subscribe to our weekly newsletter here.

🪸6 Text2SQL Tools to Write Stunning SQL for you ☄️

Mila Wu — Mon, 05 Feb 2024 02:30:00 +0000

Text2SQL, or Chat2SQL tools convert natural language or questions into SQL queries. Imagine having ChatGPT write beautiful, correct, and useful SQL queries for you!

These tools started to bridge the gap between non-tech users and databases, by allowing them to interact with databases using natural language and reduce the barrier to accessing and analyzing data. But with the advance of AI models, these tools now support more advanced features such as handling complex queries, joining multiple tables, or even supporting natural language conversations.

They can also help improve productivity by automating the process of generating SQL queries, thereby saving time and effort.

In this edition of Star History monthly, we have compiled a collection of open-source Text2SQL tools.

Chat2DB

Chat2DB aims to be a general-purpose SQL client and reporting tool that incorporates AI capabilities from the start. It supports connection to a handful of databases including MySQL, Postgres, Oracle, SQL Server, SQLite, ClickHouse and more.

There was a bit of drama involving Chat2DB a while ago, we won't get into details here but curious to know what you think.

SQL Chat

SQL Chat is a chat-based SQL client, and you can use natural language to communicate with your database to implement operations, such as query, modification, addition, and deletion (!) of the database.

It currently supports MySQL, Postgres, SQL Server and TiDB serverless.

It's open-sourced by Bytebase, a database migration tool for teams.

Vanna

Vanna is a Python framework that allows the training of an RAG model with queries, DDL, and documentation from a database.

You can use Vanna as is, or build your own custom UI with an existing tool (e.g. Streamlit, Slack).

It was open-sourced in July 2023, and got really popular this January.

DuckDB-NSQL

DuckDB-NSQL is a Text2SQL LLM built for local DuckDB SQL analytics tasks, by MontherDuck and Numbers Station. This can certainly help users leverage the full power of DuckDB and its analytic potential, without having to go back and forth between the DuckDB documentation and the SQL shell.

Langchain

With Langchain, you can build a Q&A chain and agent over an SQL database yourself.

LangChain also has an SQL Agent that you can add onto the chain. It can not only answer questions based on the databases’ schema and content, but also recover from errors by running a generated query, catching the traceback and regenerating it correctly.

Awesome Text2SQL

Awesome Text2SQL is a suite of curated tutorials and resources for LLMs, Text2SQL, Text2DSL, Text2API, Text2Vis and more. Most of the models are LLM+Text2SQL, and for each model, there are links for papers, code, dataset. If you want to dive deep into Text2SQL, take a look.

To Wrap up

LLM or not, you should still be extra careful when executing model-generated SQL queries. Some ways to minimize risks include describing your database schema, data; constraining the size of the output; validating and reviewing the generated SQL queries before executing them.

Lastly

If you want more AI content, check out earlier editions of the Star History open-source monthly:

🌌 5 Open-Source GPT Wrappers to Boost Your AI Experience 🎁

Mila Wu — Sat, 20 Jan 2024 02:40:52 +0000

GPT wrapper tools offer a service by "wrapping" around ChatGPT API, leveraging the underlying technology to provide additional functionality not offered directly by OpenAI. What these tools have in common is：

An easy-to-use UI.
Comes with pre-trained models and prompts.
Novice users can use them.
You can use your own OpenAI API key, so you pay for exactly what you use.

With ChatGPT, all you need is the million-dollar idea to get started.

For this issue of Star History monthly, we have located a set of open-source ChatGPT wrapper tools.

ChatGPT-Next-Web

ChatGPT-Next-Web is a cross-platform UI for ChatGPT (or Gemini), available as a web app or as a compact client on Linux, Windows and MacOS. You can also deploy ChatGPT-Next-Web on Vercel in just a minute.

Aside from the built-in prompts powered by awesome-chatgpt-prompts (Are you an ETH dev, a financial analyst, or a personal trainer today?), you can also create, share and debug your chat tools with prompt templates.

FastGPT

FastGPT is a knowledge-based QA system built on the LLM, offers out-of-the-box data processing and model invocation capabilities, and allows for workflow orchestration. You can use FastGPT to build your own AI knowledge base.

For example, feed all your docs to FastGPT, and it will organize and group them. When you later ask FastGPT relevant questions, it can directly locate the information in its brain.

OpenAI Translator

OpenAI Translator is a browser extension or a cross-platform desktop app for translation based on ChatGPT API.

It started as a Chrome extension and has now evolved into a multi-platform desktop app.

As an OpenAI Translator user, it stands out as a GPT wrapper in three ways:

The three different models that it offers: translation, polishing, and summarization. It's way more than just a translation tool, but a two-in-one. Have it summarize your readings next time.
Chrome extension: need I say more?

Flowise

Flowise provides a drag & drop UI to build your customized LLM flow.

It is a low-code UI tool for LangChain JS, written in Node Typescript, which aims to provide a seamless platform for easy experimentation flow and prototype design. With Flowise, you can:

Text file Q&A using conversational retrieval QA chain. Language translation using LLM Chain with a Chat Prompt Template and Chat Model.
If you feel handy, you can build your own GPT tools with Flowise to replace the previous two tools.

Lobe Chat

Lobe Chat is It's an all-in-one chatbot platform - TTS & STT, vision, plugins, you name it. You can also one-click deploy your private ChatGPT or LLM web app.

Lobe Chat is designed to help users make better use of OpenAI's capabilities, while also providing a wide range of applicable features. Its plugin ecosystem is a significant extension. For example, WeatherGPT, Ask Your PDF, Search Engine are just some plugins it's equipped with.

My Two Cents

I am still on the side that AI is more of an assistant tool (aka copilot, until now, that is). Its creativity stems from the human brain and what we humans have taught it. It does a great job of saving us from repetitive tasks and improving our work though.

We can learn to master using this tool and make better use of it, tho ;)

Lastly

If you want more AI content, check out earlier editions of the Star History open-source monthly:

🐬How to Install Local MySQL on Your Mac, Ubuntu, CentOS, Windows🚀🦾

Mila Wu — Fri, 17 Nov 2023 06:44:42 +0000

Introduction

A local MySQL database is installed and running on your own computer or server, allows developers to work offline without depending on a remote server, and also enables them to quickly iterate on changes without risking data loss or corruption.

Below is a tutorial on how to install a local MySQL database server on your Mac, Ubuntu, CentOS, or Windows.

Info： to interact with your local MySQL database, you can install the classic MySQL CLI.

macOS

GUI

Check out DBngin or StackBricks.

Homebrew

If you don't have Homebrew installed, you can install it by running the following command in your terminal:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Once you have Homebrew installed, you can install MySQL by running the command

brew install mysql

After the installation is complete, you can start the MySQL server by running the following command:

brew services start mysql

By default, Homebrew installs the MySQL server without a root password. To secure your installation, you should run the MySQL secure installation script. Run the following command and follow the prompts to set a root password and remove insecure defaults:

mysql_secure_installation

You can now access MySQL by running the following command:

mysql -u root -p

DMG Package

Download MySQL Community Server: go to the official MySQL website and download the corresponding DMG Archive for your macOS system.
Install MySQL Community Server: open the downloaded DMG file and follow the on-screen instructions to install MySQL Community Server. It will guide you through the installation process, including accepting the license agreement, choosing the installation location, and setting a root password for MySQL.
Start MySQL Server: once the installation is complete, you can close the installation window, and the MySQL server should already be up and running. If it doesn't, you can start the MySQL server by running the following command in the Terminal application on your Mac:

mysql -u root -p

It will prompt you to enter the root password you set during the installation. If the MySQL server is running correctly, you will be logged into the MySQL CLI mysql.

Ubuntu

To install MySQL locally on Ubuntu, you need to:

Update Package Lists: open a terminal on your Ubuntu system and run the following command to update the package lists:

sudo apt update

Install MySQL Server: run the following command to install the MySQL Server package:

sudo apt install mysql-server

Configure MySQL Server: during the installation process, you will be prompted to set the root password for the MySQL Server. Enter a secure password and remember it, as you will need it later.
Start MySQL Service: after the installation is complete, MySQL should start automatically. If it doesn't, you can start the MySQL service by running the following command:

sudo service mysql start

Verify MySQL Installation: to verify if the MySQL server is running correctly, run:

sudo service mysql status

If the service is active and running, it means MySQL is installed and running properly.

Secure MySQL Installation (optional): it is recommended to run the MySQL secure installation script to improve the security of your MySQL installation. You can run the following command to start the script:

sudo mysql_secure_installation

The script will guide you through several security-related questions and allow you to set additional security measures.

CentOS

To install MySQL locally on CentOS, follow these steps:

Update Package Lists: open a terminal on your CentOS system and run the following command to update the package lists.

sudo yum update

Install MySQL Server: run the following command to install the MySQL Server package:

sudo yum install mysql-server

Start MySQL Service: after the installation is complete, start the MySQL service by running the following command:

sudo systemctl start mysqld

Configure MySQL Server: the first time you start the MySQL service, it generates a temporary root password. You can retrieve this password by running the following command:

sudo grep 'temporary password' /var/log/mysqld.log

Note down the temporary password as you will need it for the next step.

Run MySQL Secure Installation: to secure your MySQL installation, run the following command and follow the prompts:

sudo mysql_secure_installation

The script will guide you through several security-related questions and allow you to set additional security measures. You will be prompted to enter the temporary root password generated earlier.

Start MySQL Service on Boot: to ensure that MySQL starts automatically on system boot, run the following command:

sudo systemctl enable mysqld

Verify MySQL Installation: to verify if the MySQL server is running correctly, you can run the following command:

sudo systemctl status mysqld

If the service is active and running, it means MySQL is installed and running properly.

Windows

To install MySQL locally on Windows:

Download MySQL Community Server: go to the official MySQL website and download the MySQL Community Server for Windows. Choose the appropriate version based on your system architecture (32-bit or 64-bit).
Run the MySQL Installer: once the download is complete, locate the downloaded installer file and double-click on it to run the MySQL Installer.
Select Setup Type: in the MySQL Installer, select the "Developer Default" setup type. This will install the MySQL Server, MySQL Workbench (a GUI tool for managing MySQL), and other necessary components. Click the "Next" button.
Choose Installation Type: in the "Choose a Setup Type" screen, select the "Server Only" option. This will install only the MySQL Server component. Click the "Next" button.
Configure MySQL Server: on the "Check Requirements" screen, you can choose the installation folder for MySQL Server and configure other server settings. You can also set a root password for the MySQL Server. Click the "Next" button.
Verify MySQL Installation: to verify if the MySQL server is running correctly, open the Windows Start menu, search for "MySQL Command Line Client," and click on it. It will open a command-line interface for MySQL. Enter the root password you set during the installation to log in. If the login is successful, you have successfully installed MySQL locally on your Windows computer.

Last but Not Least

Congratulations! You have successfully installed MySQL locally on your system. You can now start using MySQL for your database-related tasks.

And of course, you will need a few tools to better master and interact with MySQL, for example:

MySQL Client mysql
MySQL Shell mysqlsh introduced in MySQL 8.0 to provide more advanced features over mysql.
Top MySQL GUI client
MySQL Schema Compare Tools to Diff and Sync Database
Top Free Open Source SQL Clients
Handy mysql Commands with Examples

🐳Run ClickHouse with Docker and Connect Using MySQL Client☄️

Mila Wu — Tue, 10 Oct 2023 08:17:55 +0000

Background

ClickHouse is an open-source, high-performance columnar OLAP database management system for real-time analytics using SQL.

Docker is a software platform allowing developers to quickly build, test, and deploy applications. Docker packages software into a container, a standardized unit abstracting the underlying operating system.

Installation

Install Docker before you continue.

Server

Run the following command to start a ClickHouse server in a docker container.

docker run -d --name tutorial-clickhouse-server --ulimit nofile=262144:262144 --volume=$HOME/tutorial_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-server

Client

Run the following command to connect to the ClickHouse server with the default user.

docker run -it --rm --link tutorial-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server

Configuration

Enable SQL-driven access control and account management for the default user

By default, the ClickHouse server provides the default user account which is not allowed using SQL-driven access control and account management but has all the rights and permissions. To enable it, we need to edit a server configuration file.

Run the following command

docker cp <container>:/etc/clickhouse-server/users.xml .

which copies it to your current directory (don't miss the last dot!) on your local machine. Then use whatever editor you want to edit the file locally.

In users.xml, add <access_management>1</access_management>.

After the change, the file structure should be something like

<users>
    <default>
        <access_management>1</access_management>
    </default>
</users>

Note that this is unsafe and you should change it back to <access_management>0</access_management> after finishing your work.

Then run

docker cp users.xml <container>:/etc/clickhouse-server/users.xml
to replace the old one.

Create a user

CREATE USER user1 IDENTIFIED WITH PLAINTEXT_PASSWORD BY 'pass1';

You can check the users with the SHOW USERS command.

Create a database

CREATE DATABASE IF NOT EXISTS db1`

You can check the databases with the SHOW DATABASES command.

Grant database privileges to the user

GRANT ALL PRIVILEGES ON db1.* TO user1

Connect with the new user

Now we can connect to the server with the created user.

docker run -it --rm --link tutorial-clickhouse-server:clickhouse-server yandex/clickhouse-client --host clickhouse-server -u user1 --password pass1

You can run SHOW GRANTS to see the permissions the user has.

Create a table and run some queries

To create a table, run

USE db1;
DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
    id          integer,
    name        text,
    designation text,
    manager     integer,
    hired_on    date,
    salary      integer,
    commission  float,
    dept        integer
)
ENGINE = MergeTree()
PRIMARY KEY id
ORDER BY id;

Use SHOW TABLES to check the tables.

Run DESCRIBE TABLE employees. You will see something like

┌─name────────┬─type────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id          │ Int32   │              │                    │         │                  │                │
│ name        │ String  │              │                    │         │                  │                │
│ designation │ String  │              │                    │         │                  │                │
│ manager     │ Int32   │              │                    │         │                  │                │
│ hired_on    │ Date    │              │                    │         │                  │                │
│ salary      │ Int32   │              │                    │         │                  │                │
│ commission  │ Float32 │              │                    │         │                  │                │
│ dept        │ Int32   │              │                    │         │                  │                │
└─────────────┴─────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

Now do some inserts.

INSERT INTO employees (id, name, designation, manager, hired_on, salary, commission, dept) VALUES
    (1,'JOHNSON','ADMIN',6,'1990-12-17',18000,NULL,4)
    (2,'HARDING','MANAGER',9,'1998-02-02',52000,300,3)
    (3,'TAFT','SALES I',2,'1996-01-02',25000,500,3)
    (4,'HOOVER','SALES I',2,'1990-04-02',27000,NULL,3)
    (5,'LINCOLN','TECH',6,'1994-06-23',22500,1400,4)
    (6,'GARFIELD','MANAGER',9,'1993-05-01',54000,NULL,4)
    (7,'POLK','TECH',6,'1997-09-22',25000,NULL,4)
    (8,'GRANT','ENGINEER',10,'1997-03-30',32000,NULL,2)
    (9,'JACKSON','CEO',NULL,'1990-01-01',75000,NULL,4)
    (10,'FILLMORE','MANAGER',9,'1994-08-09',56000,NULL,2)
    (11,'ADAMS','ENGINEER',10,'1996-03-15',34000,NULL,2)
    (12,'WASHINGTON','ADMIN',6,'1998-04-16',18000,NULL,4)
    (13,'MONROE','ENGINEER',10,'2000-12-03',30000,NULL,2)
    (14,'ROOSEVELT','CPA',9,'1995-10-12',35000,NULL,1);

Finally, we can run some queries now.

SELECT designation,COUNT(*) AS nbr, (AVG(salary)) AS avg_salary FROM employees GROUP BY designation ORDER BY avg_salary DESC;
SELECT name,hired_on FROM employees ORDER BY hired_on;

Connect to ClickHouse Server with MySQL Client

ClickHouse supports MySQL wire protocol. It can be enabled by the mysql_port setting in the configuration file: /etc/clickhouse_server/config.xml.

By default, it should be <mysql_port>9004</mysql_port>.

Server

We start a ClickHouse server with the following command, and notice that port 9004 is exposed this time.

docker run -d --name tutorial-clickhouse-server -p 9004:9004 --ulimit nofile=262144:262144 --volume=$HOME/tutorial_clickhouse_database:/var/lib/clickhouse yandex/clickhouse-server

Client

Example of connecting using the command-line tool MySQL:

mysql --protocol tcp -u default -P 9004

Output if a connection succeeded:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 0
Server version: 21.8.10.19-ClickHouse

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL>

Conclusion

You've just started a ClickHouse server using Docker, connected to it, created a user, a database, a table, and executed some queries against it. You've also learned how to connect to the ClickHouse server using MySQL client.

If you like this tutorial, you might also be interested in our product Bytebase, an open-source, web-based schema change management tool, that helps you manage ClickHouse database, supporting SQL review, version control, backup and restore etc...

🐘Top Postgres GUI Clients to Master Postgres☄️🦖

Mila Wu — Wed, 20 Sep 2023 08:05:28 +0000

Postgres (or PostgreSQL) is one of the most advanced open-source relational databases on the market (alongside MySQL🐬).

It's not a must to use a GUI for managing your Postgres, it can be beneficial if you aren't a veteran at database management or if you simply prefer a graphical interface for database administration tasks.

Here, we gathered 5 of the most popular Postgres GUI Clients out there.

The Official: pgAdmin

pgAdmin is the Postgres counterpart to MySQL's phpMyAdmin, it is considered the go-to solution for Postgres users in need of a GUI. Similar to phpMyAdmin, it is open-source. The project was initiated by Dave Page, a Postgres core team member, and is backed by EnterpriseDB, a company that provides commercial Postgres solutions. The latest version is pgAdmin 4, which is a complete rewrite of pgAdmin, built using Python and Javascript/jQuery.

Mac only: Postico

Postico is a native Mac app for Postgres databases (and some compatible databases including Amazon Redshift, CockroachDB, Greenplum, and others). It's a Mac-only app, and the UI reflects it. The person behind Postico is the same developer who built postgres.app, which starts a Postgres server on your Mac.

The Power Duo: DBeaver & Navicat

Navicat was initially released in 2002 as a simple application exclusive to MySQL on Windows. It has since expanded its compatibility to include macOS and Linux and added support for a series of other mainstream databases. Now they even have a dedicated Navicat for Postgres (which comes at a lower cost compared to the premium version).

_Side note: Navicat is not open-source software and does not offer a free version, and it's not news that they have a piracy issue, so make sure you visit the official channels.
_

Other than that, Navicat is a comprehensive database management tool for your database administration needs: the feature list includes data modeling and design, data synchronization between databases, backup and restore, data import/export, and SQL Development (it has a built-in SQL editor for users to write and optimize SQL queries).

DBeaver

DBeaver and Navicat are similar in many ways, but their biggest difference is that unlike Navicat, which only provides commercial versions, DBeaver offers both open-source and commercial products. The open-source version fulfills the basic database management and administration needs, while the commercial version expands its capabilities with advanced features such as data modeling, collaboration, and security.

DBeaver started as a hobby project in 2010 and was open-sourced for use in 2013. In early 2023, they announced their first round of funding - they have gone a long way!

The Starlet: TablePlus

TablePlus first greeted the world in 2017 and is the newest addition to the list. The UI is simple and sleek without any redundant modules, making it quick and easy to get started with.

It supports a wide range of relational databases and some NoSQL databases. Initially, it was exclusive to macOS (just like Postico), but it is now available on Windows, Linux, and even iOS.

Currently, TablePlus offers two plans: a free tier with no trial time (but a limited feature) and a paid subscription model that provides extended features.

TablePlus is not open-source, but their other product is DBngin, which can spin up a local database server (Postgres, MySQL, or Redis) on your Mac. It can then be connected to TablePlus for you to visually manage your local databases.

Final Thoughts

Utilizing Postgres GUI Clients can greatly enhance your database management capabilities, instilling a sense of confidence. It is crucial to carefully select the appropriate option that aligns with your organization's needs.

While the aforementioned options provide a user-friendly interface for database operations, there may be instances where your organization requires an additional layer of control over database queries, changes, and administrative actions. In such cases, you can explore Bytebase, our open-source Database DevOps and CI/CD tools designed specifically for teams. Bytebase aims to centralize control and safeguard your organization's most valuable asset - database data.

🐬Top 5 MySQL GUI Clients to Command MySQL⚡️

Mila Wu — Mon, 11 Sep 2023 07:26:51 +0000

To interact with MySQL databases, it’s common to employ MySQL GUI clients. They enable users to visually view, create and modify database objects such as tables, rows, and columns. Some familiar features of MySQL GUI clients include SQL generator and export data, which makes designing, creating, and administering MySQL databases easier and more convenient. Here, we gathered 5 best MySQL GUI Clients on the market right now.

The Official: MySQL Workbench

MySQL Workbench is a free database design and model access tool for MySQL, meant for database architects, developers, and of course, DBAs. It is available on Windows, Linux, as well as MacOS. Since the official MySQL vendor offers it, it looks like it's going to be free and maintained for the foreseeable future.

The Old School: phpMyAdmin

phpMyAdmin is a web-based interface to MySQL and MariaDB written in PHP that was first released back in 1998. It's open-source and free to use. For over 20 years, phpMyAdmin remains one of the most popular administration tools for MySQL databases, with a large community of users and contributors.

A range of features are available (managing databases, tables, users, permissions, etc) and can be performed via the user-friendly interface, you can also execute SQL queries directly. However, being web-based has pros and cons: phpMyAdmin is available on all the platforms with a web browser, yet it can be prone to security attacks such as SQL injection, so make sure to take proper precautionary measures.

The Power Couple: Navicat & DBeaver

Navicat

Navicat's first release came in 2002 and back then, it was a simple application only available for MySQL on Windows. Now it's available on macOS and Linux, with a long list of compatible databases, including Redis, PostgreSQL, SQL Server, Oracle, MariaDB, SQLite, MongoDB, and a handful of cloud databases.

It is not open-source, nor does it have a free offering, you can only choose between the premium and lite (with a compact list of features and database support as compared to the premium versions.

DBeaver

Unlike Navicat, which only offers commercial versions, DBeaver offers both open-source and commercial products. It started as a hobby project back in 2010 and was open-sourced for use in 2013. The OS version caters to most database management and administration needs, while the commercial version extends the capabilities with additional advanced features like reverse engineering, data modeling, collaboration tools, and tech support.

Currently, it supports 80+ databases (SQL, NoSQL, document-oriented, key-value, big data, cloud, you name it). DBeaver is a desktop client, if you prefer web-based tools, they also have CloudBeaver.

The Starlet: TablePlus

Starting in 2017, TablePlus is the newbie on the list, and its modern and simple UI reflects it. It supports most relational databases and some NoSQL ones. When they just started, they only supported macOS, but it is now available on Windows, Linux, and iOS (!). It is not open-source, but the roadmap is open and anyone can open an issue on their GitHub Issue Tracker. TablePlus has two plans: a free tier (has no limit on trial time) and a paid subscription model (license) with extended features.

It's worth mentioning that DBngin, a tool to spin up a local database server (currently supports PostgreSQL, MySQL, and Redis) on your Mac, is also TablePlus' and is open-source. You can connect DBngin to TablePlus to visually manage your local databases.

Bytebase

Any of the aforementioned provides a UI for users to operate on databases, a SQL Editor, and the ability to export data. On the other hand, if your organization needs are beyond those and demands an extra layer of control over database queries, changes, and admin actions, try out Bytebase.

Bytebase is an open-source Database DevOps and CI/CD tool for teams, designed to centralize the control and secure your organization’s most valuable asset, the database data.

Final Thoughts

MySQL GUI Clients can be used to help you manage databases with more confidence. However, the best-fit tool depends on your level of familiarity with MySQL, and what you need to accomplish in the tool.

👻Top 8 Free, Open Source SQL Clients🔥

Mila Wu — Mon, 04 Sep 2023 10:03:27 +0000

In the past, the only way to access or modify a database was through the command line, which was not only not intuitive, but also prone to errors that could lead to major problems. So people began to develop tools with a graphical user interface (GUI), and that became what we now know as SQL clients, which are basically front-end applications for database services. In this post, we are taking a look at some open source SQL clients options for you to try.

SQL Chat

SQL Chat is a young player, prompting SQL clients from the traditional GUI-based to the Chat-based stage: it's powered by ChatGPT to write and polish SQL for you.

Databases supported now include MySQL, PostgreSQL and MSSQL. You can access it directly via sqlchat.ai or deploy it via Docker.

DBeaver

DBeaver is a veteran SQL client. In addition to basic visualization and management capabilities, it has a SQL editor, data and schema migration capabilities, monitor database connections, and more. It supports a full range of databases (both SQL and NoSQL). DBeaver is also hooked up with GPT-3, which converts your natural language to SQL.

Beekeeper Studio

Beekeeper Studio is a modern (aesthetic) and lightweight SQL client that supports MySQL, Postgres, SQLite, SQL Server, etc. It is available on Linux, Mac and Windows.

DbGate

DbGate works in Windows, Linux, Mac, and your web browsers without compromises in functionality. Both SQL and noSQL databases are supported, from MySQL, PostgreSQL, SQL Server, MongoDB, SQLite, to CockroachDB.

Sqlectron

Sqlectron is lightweight SQL client with simplicity in mind. It has cross-database (PostgreSQL, Redshift, MySQL, MariaDB, SQL Server, Cassandra, SQLite) and platform (Mac, Linux, Windows) support.

HeidiSQL

HeidiSQL is also a lightweight SQL client that supports a long list of databases including MariaDB, MySQL, MS SQL, PostgreSQL, SQLite, Interbase and Firebird. In addition to basic SQL client functionalities, users can export structures and data to SQL files or copy them to the clipboard or other servers.

heidisql

phpMyAdmin

phpMyAdmin was born in 1998, written in PHP, and is a classic SQL client. It was the default tool for LAMP and MAMP at that time. phpMyAdmin has grown to become one of the leading tools for managing MySQL and MySQL-like databases (e.g. MariaDB) It has over 200,000 direct downloads per month (and countless other users install it using prepackaged installations or using package managers).

pgAdmin 4

pgAdmin to PostgreSQL is what phpMyAdmin is to MySQL, and it can be used on Linux, Unix, macOS and Windows to manage PostgreSQL. pgAdmin 4, the latest pgAdmin, is a complete rewrite of pgAdmin using Python and Javascript/jQuery.

To summarize

We briefly looked at the history of a handful of SQL Clients to understand some of the more famous open source solutions, rather than comparing the product or their functions (I mean, after 20 years of refinement those vetran SQL clients can't be too bad, right?) . It seems that most of the authors began building because they were unable to find a product that fit their needs perfectly, but the story afterwards is very different: some tools were then commercialized, some continued on with a strong community, and some gradually faded out due to a change of focus.

And we wonder, with the popularity of ChatGPT, will there be more open source SQL clients like SQL Chat based on Chat interaction?

Beyond using the general SQL Client to interact with the databases, developers and DBAs also adopt a dedicated tool to compare and synchronize database schemas, go check out those tools.

And if you want an all-in-one tool to provide not only general SQL Client features, but also management capabilities such as change approval workflow, data access control, you can check out our open-source project Bytebase.